Which Postgres Datatype do I Choose?

I host all my projects and services on 1 Postgres database instance (see article). In Postgres you make tables with columns, and each column has 1 data type. You can for example make columns for numbers, or for text, or for JSON, or a lot of others data types. At first glance there are multiple data types that have the same purpose, like the 7 data types related to numbers: smallint, integer, bigint, numeric, decimal, real and double precision. In this article I'll give a small overview of the built-in Postgres data types and which you should use.

Cheatsheet

If you don't care about the explanations and just want to know which you should use, here you go:

Integers: integer (bigint if you expect a value to be >2.1 billion)

Decimals: numeric(p,s) for precise floats like money, double precision for "scientifically accurate enough"

Text: text

Boolean: boolean

Timestamp: timestampz

IDs: serial or uuid

JSON: jsonb

Example: This website

My personal website is a SvelteKit app that mostly just displays non-changing text, but I do store some data in my central database, like blog articles or data for my projects (see article).

For convenience, I use the popular ORM Drizzle. Drizzle has also developed DrizzleKit, which I use to push my schema to the database.