Three type decisions cause most schema regret in PostgreSQL: using timestamp instead of timestamptz, json instead of jsonb, and varchar with a length you invented.
PostgreSQL’s type system is one of its genuine advantages over other databases, and also one of the easier places to make a decision you cannot cheaply reverse. Changing a column type on a large table means a rewrite under a lock.
This is not an exhaustive catalogue — the documentation already does that well. It is the working subset, with the specific traps that produce bug reports six months after the schema was written.
Table of contents
- Text: stop agonising over varchar lengths
- Timestamps: the single most common schema mistake
- Numbers: integers, and when floating point is wrong
- JSON: always jsonb, essentially never json
- The types people forget PostgreSQL has
- Changing a type after the fact
- How this fits the rest of the stack
- FAQ
Text: stop agonising over varchar lengths
PostgreSQL has three character types and, unusually among databases, no performance difference between them. They share storage and implementation.
text— variable length, unlimitedvarchar(n)— variable length with a maximum, enforced as a constraintchar(n)— fixed length, blank-padded to n
Use text by default. varchar(n) is identical to text plus a length check, so pick it only when the limit is a real business rule rather than a number someone guessed. varchar(255) is a MySQL artefact and means nothing here.
Avoid char(n) entirely. It pads values with spaces to the declared width, which surprises people during comparisons and wastes storage. There is no scenario where it is the best choice in PostgreSQL.
The practical benefit: widening a varchar(50) to varchar(100) is a catalogue change on modern versions, but adding a limit where none existed requires a full table scan to verify. Starting with text and adding a check constraint later is more flexible than starting with a guessed limit.
Timestamps: the single most common schema mistake
If you take one thing from this page: use timestamptz, not timestamp.
The names are actively misleading. timestamptz does not store a timezone. It stores an absolute point in time — normalised to UTC internally — and converts to the session timezone on display. timestamp stores wall-clock digits with no indication of which zone they mean, which makes it ambiguous the moment two servers disagree or daylight saving shifts.
-- Wrong: ambiguous, breaks across timezones and DST
created_at timestamp NOT NULL DEFAULT now()
-- Right: an unambiguous instant
created_at timestamptz NOT NULL DEFAULT now()Both take eight bytes. There is no storage cost to being correct. The only legitimate use for plain timestamp is a genuinely floating local time — a recurring 09:00 alarm that should fire at 09:00 wherever the user is — and that is rare enough to be worth a comment when it appears.
For durations use interval, which handles calendar arithmetic properly. For a date with no time, date is four bytes and says what it means. And avoid storing timestamps as integers or strings; you lose every arithmetic and indexing benefit the native types provide.
Numbers: integers, and when floating point is wrong
The integer family is straightforward: smallint at two bytes, integer at four, bigint at eight. Use integer unless you might exceed roughly two billion, in which case use bigint from the start.
For primary keys, bigint is the safe default. Running out of integer keys on a busy table is a migration nobody enjoys, and four extra bytes per row is cheap insurance. Modern PostgreSQL prefers identity columns over the older serial pseudo-types.
-- Preferred: standard SQL identity column
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- Legacy equivalent, still widely seen
id bigserial PRIMARY KEYThe rule that matters most: never use real or double precision for money. Binary floating point cannot represent 0.1 exactly, so sums drift and comparisons fail in ways that are hard to reproduce and embarrassing to explain.
-- Wrong: floating point rounding on currency
price double precision
-- Right: exact decimal arithmetic
price numeric(12, 2)
-- Also fine: store minor units as an integer
price_cents bigintnumeric is exact and slower, because arithmetic happens in software rather than the CPU. For money that trade is obviously correct. For scientific data where you want speed and can tolerate rounding, double precision is the right tool.
JSON: always jsonb, essentially never json
PostgreSQL has two JSON types and they are not interchangeable.
json stores the raw text exactly as submitted — whitespace, key order, duplicate keys and all. Every query reparses it, and it cannot be indexed usefully.
jsonb stores a decoded binary form. Whitespace and key order are lost, duplicate keys are collapsed, and in exchange you get fast access and GIN indexing. Choose jsonb unless you specifically need to preserve the exact input text, which is a document-archival requirement rather than a database one.
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
CREATE INDEX events_payload_idx ON events USING gin (payload);
-- Containment, accelerated by the GIN index
SELECT * FROM events WHERE payload @> '{"level": "error"}';
-- Extract a value as text with ->>, as jsonb with ->
SELECT payload ->> 'user_id' FROM events;The warning that belongs alongside this: jsonb is for genuinely unstructured or variable data. A column of jsonb holding the same five keys on every row should be five columns. You lose type checking, constraints, and clean indexing, and you gain nothing but the ability to skip a migration.
The types people forget PostgreSQL has
Several native types replace patterns teams commonly build by hand.
uuid— 16 bytes, versus 36 for the text form. Usegen_random_uuid()for the default.inetandcidr— real IP address types with subnet operators and validation, instead of a varchar that accepts anything.text[]and other arrays — a genuine array type, indexable with GIN, useful for tags without a join table.tstzrangeand friends — range types with overlap operators, and an exclusion constraint that makes double-booking impossible at the database level.enum— a fixed set of values enforced by the type system. Adding values is easy; removing or reordering them is not, so a lookup table is more flexible when the set will change.
-- A booking table where overlapping reservations cannot exist
CREATE TABLE reservations (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
period tstzrange NOT NULL,
EXCLUDE USING gist (room_id WITH =, period WITH &&)
);That exclusion constraint is a good example of what the richer type system buys you. The alternative is application-level checking that races under concurrency and eventually double-books somebody.
Changing a type after the fact
ALTER TABLE ... ALTER COLUMN ... TYPE rewrites the table under an ACCESS EXCLUSIVE lock, which on a large table means real downtime.
-- Rewrites every row, holds an exclusive lock
ALTER TABLE users ALTER COLUMN id TYPE bigint;
-- Set lock_timeout so it fails fast rather than queuing all traffic
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE users ALTER COLUMN status TYPE text;
COMMIT;Some conversions skip the rewrite because the binary representation is unchanged — varchar(50) to text is one. Widening a varchar limit is another. Anything changing the actual storage, such as integer to bigint, is a full rewrite.
For a large table, the zero-downtime path is the same shape as any other heavy migration: add a new column, dual-write from the application, backfill in batches, swap reads over, then drop the old column. Tedious, but it does not require a maintenance window.
How this fits the rest of the stack
Type choices are cheap to make and expensive to revisit, because every correction is a table rewrite under a lock. Getting timestamptz, jsonb, and numeric right at the start costs nothing and saves a migration. RunxBuild managed Postgres gives you a current PostgreSQL version with backups configured before the first schema lands, and the RunxBuild hosting calculator shows the database and its storage separately, which is useful given how much a careless jsonb column can add to both.
Useful related references:
- Postgres-XL: What the Distributed Fork Was, and Why It Lost to Citus
- Postgres Switch Database: \connect, USE, and psql Defaults
- Postgres Port 5432: What to Check Before Deploying
- Databases on RunxBuild
FAQ
Should I use text or varchar in PostgreSQL?
Use text unless there is a real business rule for the maximum length. The two perform identically — varchar(n) is text plus a length constraint — so an invented limit only adds friction later.
What is the difference between timestamp and timestamptz?
timestamptz records an absolute instant, normalised to UTC and rendered in the session timezone. timestamp records wall-clock digits with no timezone, making it ambiguous across regions and daylight-saving changes. Use timestamptz unless you specifically need a floating local time.
Is jsonb always better than json?
For nearly every use, yes. jsonb is indexable, faster to query, and stored in a decoded binary form. Choose json only when you must preserve the exact input text including whitespace and key order.
What type should I use for money?
numeric with an explicit precision and scale, or an integer count of minor units. Never real or double precision — binary floating point cannot represent common decimal fractions exactly, so totals drift.
Does changing a column type lock the table?
Usually yes, with a full rewrite under an ACCESS EXCLUSIVE lock. Some conversions are catalogue-only when the storage format is unchanged, such as varchar(n) to text. For large tables, prefer the add-column-and-backfill approach.