Keys and Data Types That Age Well

Picking an id column feels like a five-minute decision until the day you merge two services' tables, expose ids in a public API, or try to shard. Data types are the cheapest decisions to make at design time and the most expensive to change later — pick them like they will live for a decade, because they might.

Primary keys. bigint GENERATED ALWAYS AS IDENTITY (SQL-standard, Postgres 10+) is the default that ages well: 8 bytes, sequential, which keeps B-tree inserts append-friendly, and the identity syntax prevents people manually inserting over the sequence the way SERIAL allowed. Reach for uuid when rows are created across systems that cannot share a sequence, or when you do not want guessable ids in URLs. Sequential ids keep inserts localized to the right edge of the index; random uuids scatter them (more page splits, more bloat). Postgres 18 added uuidv7() — time-ordered uuids — which gives you both properties. On earlier versions, generate v7 in the application or accept the scatter.

Time. Always timestamptz. Never bare timestamp — it stores a naive wall-clock value and some ORM will mangle it across a DST boundary eventually. Postgres stores UTC internally; the timezone you see is presentation. Convert to the user's zone at the edge, never in the column.

Money. numeric(12,2), or integer cents. Never float/double precision — binary floats cannot represent 0.1 exactly, and the rounding errors surface exactly when someone reconciles the books. If multi-currency is even remotely possible, store the currency code in its own column now.

Text. Just use text. Postgres stores varchar(n) and text identically; the length limit is a runtime check, not a storage optimization. If you need a limit, use CHECK.

jsonb. Genuinely useful for attributes that vary per row and are written and read whole — settings blobs, webhook payloads. The discipline: nothing you filter or join on lives in jsonb. Pull those fields into real columns where they get types, constraints, and B-tree index support. If you must query inside jsonb, index deliberately — a GIN index on the column, or an expression index on the hot path.

Try this: create two identical tables, one with an identity bigint PK and one with random uuids. Insert 100k rows into each and compare pg_relation_size on the table and the PK index. The uuid table will be visibly larger — that is the cost of scattered inserts.

Pitfalls

• GENERATED ALWAYS blocks manual id writes; OVERRIDING SYSTEM VALUE exists for backfills and imports.
• Epoch-in-seconds integer columns look simple until someone stores milliseconds. If you must, name it created_at_ms — or just use timestamptz.
• Undocumented type/status values accumulate silently without a CHECK list. Force the conversation.