Core types: money, text, time, identity

Type families: pick by meaning

Postgres groups its built-in types into a handful of families. Modelling well starts with mapping each column to the family that matches what the value means, not the family that’s easiest to type. Before you create the next column, ask yourself: what does this value represent — a count, a moment in time, an identifier — and does my chosen type make that meaning enforced or just implied?

Numbers: never store money in a float

integer is a 4-byte signed whole number (up to ~2.1 billion); bigint is 8 bytes (up to ~9.2 quintillion). Use bigint for anything that counts events, rows, or money-in-cents at scale — integer overflow on a primary key is a real outage.

The hard rule: money is numeric or integer cents, never real/double precision. numeric is an exact decimal — it stores the digits you gave it and does base-10 arithmetic, so 0.10 + 0.20 is exactly 0.30. Floating point stores a binary approximation, and 0.1 has no exact binary representation. Watch it happen:

SELECT 0.1::float8 + 0.2::float8 AS float_sum,   -- 0.30000000000000004
       0.1::numeric + 0.2::numeric AS exact_sum;  -- 0.3

-- money column done right:
CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  total_cents bigint NOT NULL,        -- integer cents: fast, exact, no scale arg
  tax         numeric(12, 2) NOT NULL -- or fixed-scale numeric
);

numeric(12, 2) means up to 12 significant digits with 2 after the decimal. The tradeoff: numeric arithmetic is software-emulated and roughly 10–100× slower than the CPU’s native float math, and each value is variable-width. For money that’s a non-issue — correctness dominates — but don’t reach for numeric on a hot scientific aggregation where a tiny rounding error is acceptable.

Text: text and varchar(n) are the same speed

A persistent myth: varchar(50) is faster or smaller than text. In Postgres it is not. All three of text, varchar(n), and varchar share the same storage and the same operators. The only thing varchar(n) adds is a length check constraint — Postgres verifies every write is at most n characters and rejects longer ones with an error.

So the choice is purely about whether you want that constraint enforced. A length limit that reflects a real business rule (a 2-char country code) is good modelling; an arbitrary varchar(255) copied from a MySQL tutorial is noise. The Postgres-native habit is text plus an explicit CHECK when you actually need a bound — it’s clearer and you can change the bound without an awkward type alter.

CREATE TABLE users (
  id        bigint PRIMARY KEY,
  email     text NOT NULL,
  country   text NOT NULL CHECK (char_length(country) = 2)  -- real rule, explicit
);

Time: timestamptz, always

This is the single most common modelling mistake, and it’s a quiet one. There are two timestamp types:

• timestamp (a.k.a. timestamp without time zone) stores a wall-clock value and throws the zone away. It’s a naive “2026-06-02 14:00” with no anchor to a real instant.
• timestamptz (timestamp with time zone) stores an absolute instant. On the way in, Postgres converts the input to UTC and stores that; on the way out, it renders in the session’s TimeZone. It does not store the original zone — it stores the moment.

timestamptz is the right default for essentially every event, log line, created_at, and deadline. With plain timestamp, two servers in different zones inserting “now” write different absolute instants that compare wrong, and you cannot recover the truth later. Store the instant; let the application decide how to display it.

CREATE TABLE events (
  id          bigint PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),  -- absolute instant in UTC
  kind        text NOT NULL
);

-- date is for calendar dates with no time; interval is a span:
SELECT occurred_at::date AS day,
       now() - occurred_at AS age      -- age is an interval
FROM events;

Use date for true calendar dates (a birthday, a billing day) and interval for spans (“7 days”, “90 minutes”) — subtracting two timestamptz values yields an interval.

UUID: v4 random vs v7 time-ordered

uuid is a 16-byte type for keys you generate without a central sequence — useful across shards or before the row reaches the database. The version matters for index locality:

• v4 is fully random. Every insert lands at a random spot in the primary-key B-tree, so the index has no hot tail — writes scatter across pages, cache hit rates drop, and the index fragments and bloats under heavy insert load.
• v7 embeds a millisecond timestamp in the high bits, so values are roughly time-ordered. Inserts append to the right edge of the B-tree like a serial would, keeping the index compact and cache-friendly.

For a high-insert table, v7 (or a bigint identity) can be meaningfully faster to write and far less prone to index bloat than v4. Reach for random v4 only when unguessability of the key itself is a requirement.

Casts: be explicit at the boundary

Postgres will implicitly cast in some contexts and refuse in others; relying on implicit casts is how you get surprising plans (an index skipped because a comparison forced a cast) or runtime errors. Make casts explicit with ::type or CAST(x AS type) at the edges of your query, especially when comparing a column to a literal of a different type.

SELECT '42'::int + 1                 AS to_int,      -- 43
       '2026-06-02'::date            AS to_date,
       total_cents::numeric / 100    AS dollars      -- cents → display value
FROM orders;