Choosing the Right PostgreSQL Data Types

Most schema problems don't announce themselves on day 1. You pick VARCHAR(255) for everything, store money in a float because it's convenient, keep timestamps without a timezone, and it all works fine in the demo. The bill arrives 18 months later when a rounding error shows up in an invoice, or a report is off by 5 hours for every user outside your own timezone.

Data types are the cheapest correctness decision you'll ever make, and the most expensive one to change once a table has millions of rows. This is the opening article of the PostgreSQL Masterclass, and it starts here on purpose: get the types right and half the bugs never happen.

Text: stop reaching for VARCHAR(255)

The VARCHAR(255) habit comes from old MySQL defaults, not from anything Postgres cares about. In PostgreSQL, text, varchar, and varchar(n) are the same underlying storage. There is no performance gain from a length limit, and text is not slower than a short varchar.

So the rule is simple. Use text for free-form strings, and only add a length check when the limit is a real business rule you want the database to enforce:

CREATE TABLE users (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email        text NOT NULL,
  display_name text NOT NULL,
  bio          text,
  -- a real rule, expressed as a constraint rather than a type quirk
  CONSTRAINT display_name_len CHECK (char_length(display_name) BETWEEN 1 AND 80)
);

A CHECK constraint says what you mean. varchar(80) silently truncates intent into a storage detail, and when you need to change the limit later, altering a constraint is cleaner than altering a column type under load.

Numbers: never store money in a float

real and double precision are floating point. They trade exactness for range and speed, which is correct for scientific data and wrong for anything you'll add up and show to a customer. The classic failure looks like this:

SELECT 0.1::double precision + 0.2::double precision;
-- 0.30000000000000004

Multiply that error across a few thousand line items and you get invoices that don't reconcile. For money, use numeric (also spelled decimal), which stores exact decimal values:

CREATE TABLE invoice_lines (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_id  BIGINT NOT NULL,
  description text   NOT NULL,
  amount      numeric(12, 2) NOT NULL   -- up to 10 digits before the point, 2 after
);

For counts, IDs, and anything discrete, stick with the integer family: smallint, integer, bigint. Reach for bigint on identifiers you expect to grow, because migrating a busy integer primary key to bigint after you cross 2.1 billion rows is a genuinely painful day. Storage is cheap; a mid-life column-type migration is not.

Time: always store the timezone

This one causes more silent bugs than any other choice on the list. PostgreSQL has timestamp (without time zone) and timestamptz (with time zone). The name is slightly misleading. timestamptz does not store a timezone. It stores an absolute point in time as UTC, and converts to and from your session timezone on the way in and out. timestamp stores whatever wall-clock string you handed it, with no anchor to reality.

For any moment that actually happened, use timestamptz:

CREATE TABLE events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  created_at  timestamptz NOT NULL DEFAULT now()
);

Plain timestamp has a narrow legitimate use: a value with no timezone meaning, like "the store opens at 09:00 local time" where local is defined elsewhere. If you're unsure, you want timestamptz. Store instants in UTC, and let the display layer format them for whoever is reading.

For a pure calendar day with no time component, use date. For a duration, interval exists and is genuinely useful:

SELECT now() + interval '30 days' AS trial_ends_at;

Booleans, enums, and the "status column" question

Use a real boolean for true/false. Don't encode it as an integer or a single-character string; you lose the type check and gain nothing.

Status columns are where teams disagree. You have three reasonable options, and they trade flexibility against safety:

1. A native enum type. Compact and self-documenting, but adding a value requires ALTER TYPE, and removing one is awkward.
2. A text column with a CHECK constraint listing allowed values. Easy to change, still validated by the database.
3. A lookup table with a foreign key. Most flexible, best when statuses carry extra attributes like a label or sort order.

For a status that rarely changes, the CHECK approach hits a good balance:

CREATE TABLE subscriptions (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  text NOT NULL DEFAULT 'trialing'
          CHECK (status IN ('trialing', 'active', 'past_due', 'canceled'))
);

You get validation without the ALTER TYPE ceremony, and a new state is a one-line migration.

UUID, JSONB, and knowing when to stop

Two types deserve a mention now and a full treatment later.

uuid is the right type for a UUID. Storing one as text wastes space (16 bytes as uuid versus 37 as text) and skips validation. Generate them with gen_random_uuid(), built in since Postgres 13.

jsonb is tempting because it accepts anything, and that's exactly the trap. It's the correct choice for genuinely schemaless data, like a webhook payload you want to keep verbatim or user-defined custom fields. It's the wrong choice for data that has a shape you already know, because you give up constraints, foreign keys, and clean indexing. Model relational data relationally; save jsonb for the parts that are actually unstructured. We'll go deep on querying and indexing it later in the series.

A quick reference

Here's the short version to keep next to you while designing a table:

None of these choices are exotic. They're the defaults an experienced team reaches for without thinking, precisely because each one closed a bug they hit once and never wanted to hit again.

Next in the series, we take these types and arrange them into tables that stay sane as the app grows: schema design and how far to take normalization before it starts working against you.