Postgres – Data Types

Most of your schema decisions are cheap to change. A handful are not: the type of a column with a hundred million rows in it, and anything that has already been wrong for six months. This post is about that handful.

Text

Postgres has three string types and you should use one of them.

The three are the same type underneath, with a length check bolted on. There is no performance difference between text and varchar(50) — including on storage, since Postgres stores the actual length either way:

SELECT pg_column_size('hello'::text)        AS text,
       pg_column_size('hello'::varchar(50)) AS varchar,
       pg_column_size('hello'::char(50))    AS char;

 text | varchar | char
------+---------+------
    9 |       9 |   54

char(n) pads with spaces to the full width, forever. That is the whole reason for the "never".

Raising a varchar limit is instant; lowering it rewrites the table. So a limit you picked defensively is a limit you will have to schedule a maintenance window to relax. Prefer text and a CHECK constraint if you want a rule you can change.

Numbers, and the one that must never be a float

SELECT 0.1::float8 + 0.2::float8 AS as_float,
       0.1::numeric + 0.2::numeric AS as_numeric,
       (0.1::float8 + 0.2::float8) = 0.3 AS float_equals_point_three;

      as_float       | as_numeric | float_equals_point_three
---------------------+------------+--------------------------
 0.30000000000000004 |        0.3 | f

That is not a Postgres quirk, it is binary floating point, and it is why every money column in the booking schema this track uses is numeric(10,2). Sum a million of those floats and the total is wrong by an amount an accountant will find.

Make primary keys bigint from the start. An integer key runs out at 2,147,483,647, which sounds distant until a table of events reaches it, and widening the column on a live table is a rewrite plus every foreign key that references it. The extra four bytes are the cheapest insurance in this post.

Time

There are two timestamp types and one of them is right nearly every time.

SET timezone = 'Pacific/Auckland';
SELECT now()::timestamptz AS with_zone,
       now()::timestamp   AS without_zone;

           with_zone           |        without_zone
-------------------------------+----------------------------
 2026-08-23 17:47:58.344067+12 | 2026-08-23 17:47:58.344067

timestamptz does not store a timezone. It stores an absolute moment in UTC, and renders it in the session's timezone on the way out. That is exactly what you want for "when did this happen": one unambiguous instant, displayed correctly for whoever is looking.

timestamp stores the digits you gave it with no idea what they mean. Two servers in different regions will disagree about what it represents, and neither will tell you.

Use timestamptz for events. Use date for a calendar day that is not a moment — a check-in date is the 4th of March whatever timezone you read it in, which is why the booking table stores dates rather than timestamps:

SELECT check_in, check_out, check_out - check_in AS nights
FROM   bookings LIMIT 3;

Subtracting two date values gives an integer number of days. Subtracting two timestamps gives an interval, which is a different and occasionally surprising animal — an interval of one month has no fixed length until it is added to a date.

Identifiers

The booking schema carries both an integer primary key and a UUID on every table, and that is deliberate rather than indecisive:

SELECT id, public_id FROM properties LIMIT 2;

• id bigint — the key. Small, sequential, and a b-tree index on it stays compact because new rows land at the end.
• public_id uuid — what appears in a URL. It leaks no count and cannot be guessed by adding one.

Making a random UUID the primary key costs more than it looks. It is 16 bytes rather than 8 in every index and every foreign key, and because the values are random each insert lands in a different page of the index. In the lab database the UUID index on bookings is 16 MB against 3968 kB for the index on the integer property_id.

Booleans, enums and the choice StayHub made

Postgres has a real boolean, and a real ENUM type. The booking schema uses the boolean and refuses the enum, storing statuses as varchar(20) instead:

SELECT DISTINCT status FROM bookings ORDER BY 1;
-- CANCELLED, COMPLETED, CONFIRMED, PENDING

The reasoning is worth borrowing. A native ENUM gives you validation, but adding a value needs ALTER TYPE, removing one is close to impossible, and reordering is impossible. A varchar plus a CHECK constraint gives you the same validation and an ordinary migration when the list changes — and the list always changes.

Types you will be glad exist

NULL is not the empty string

Postgres keeps these firmly apart, and if you are arriving from Oracle — where an empty string is NULL — this is the difference that will bite first:

SELECT ''  IS NULL       AS empty_is_null,      -- false
       '' = NULL         AS empty_equals_null,  -- null, not false
       NULL IS NOT DISTINCT FROM NULL AS null_matches_null;

Nothing equals NULL, including NULL, because = asks a question about two values and NULL is the absence of one. Comparisons return NULL rather than false, and a WHERE clause keeps only rows that are true — so WHERE status <> 'CANCELLED' silently drops every row whose status is NULL. Use IS NULL, or IS DISTINCT FROM when you want NULL treated as an ordinary value.

Decide per column which one means what, then make it impossible to get the other: NOT NULL plus a default, or CHECK (name <> ''). A column that allows both has two spellings of "nothing", and every query has to handle both forever.

Changing a type later

The reason this post exists. ALTER TABLE ... ALTER COLUMN ... TYPE takes an ACCESS EXCLUSIVE lock, and whether that lock is held for a millisecond or an hour depends entirely on whether the new type needs the rows rewritten:

A rewrite on a 400,000-row table is a few seconds. On a table with two hundred million rows it is an outage, because the exclusive lock blocks every read as well as every write. The migrations post covers how to do it in steps instead; the cheaper habit is to pick bigint and text now.

Mapping to your language

The row worth checking in your own code is numeric. A driver that hands you a float for a money column has undone the reason you chose the type — in Python that means confirming you get Decimal, and in Java that nobody has mapped the column to double.