Designing a Postgres schema you won't regret in year two

Most schema mistakes are free on the day you make them. Nobody notices in the first month, because there are a few hundred rows and every query is fast. The bill arrives later, once real data exists, other systems read from your tables, and a change that would have been one line in a migration now needs a backfill, a deploy window and a careful plan.

This is the checklist I work through when I design a new schema for a business web app. The examples use PostgreSQL and a made-up job tracking app (organisations, customers, jobs), but most of the ideas carry over to other relational databases.

1. Pick your identifiers on purpose

Two decisions hide in every id column: the type, and whether it ever leaves the database.

For internal primary keys I default to bigint identity columns. They are compact, index well and are boring in a good way. Skip serial; identity columns are the SQL standard way and behave better with permissions and dumps.

What I avoid is putting sequential IDs in URLs and APIs. /invoices/1042 tells anyone how many invoices you have and invites people to try 1043. So I add a separate public identifier:

create table organisation (
  id         bigint generated always as identity primary key,
  public_id  uuid not null default gen_random_uuid() unique,
  name       text not null,
  created_at timestamptz not null default now()
);

If you want UUIDs as the primary key instead, prefer time-ordered ones (UUIDv7). Random UUIDv4 keys scatter inserts across the whole index. PostgreSQL 18 ships a built-in uuidv7() function; on older versions you can generate them in the application.

2. Never store money as a float

Floating point cannot represent most decimal amounts exactly, and those tiny errors turn into reconciliation problems. Use numeric with a fixed scale, or store integer cents. Either way, store the currency next to the amount, even if you only deal in AUD today.

create table invoice_line (
  id            bigint generated always as identity primary key,
  invoice_id    bigint not null references invoice (id),
  description   text not null,
  quantity      numeric(12, 3) not null check (quantity > 0),
  unit_price    numeric(12, 2) not null,
  tax_rate      numeric(5, 4) not null,
  currency      char(3) not null default 'AUD'
);

Notice unit_price and tax_rate live on the line, not only on a product or settings table. That is deliberate. An invoice is a historical document: if the product price or the GST treatment changes next year, last year's invoices must not change with it. Snapshot the values that were true at the time, and keep a reference to the source row if you need it for reporting.

3. Use timestamptz, everywhere

timestamp (without time zone) stores a wall clock reading with no idea where the wall was. That works right up until you have users in Perth and Sydney, or a daylight saving changeover lands on a busy night. timestamptz stores an absolute instant and converts on the way out.

My rule: every table gets created_at timestamptz not null default now(), and anything mutable gets updated_at maintained by a trigger, so it stays correct no matter which code path writes the row.

create function set_updated_at() returns trigger
language plpgsql as $$
begin
  new.updated_at := now();
  return new;
end;
$$;

For pure calendar values (a birthday, a public holiday, a due date with no time), use date. Do not fake them with midnight timestamps.

4. Make tenancy explicit from day one

Even if you only have one customer organisation today, ask whether the product will ever hold data for more than one. If the answer is "maybe", add organisation_id to every tenant-owned table now. Retrofitting it later means touching every table, every query and every index.

Then let the database stop cross-tenant mistakes, not just your application code. A composite foreign key does this neatly:

create table customer (
  id              bigint generated always as identity primary key,
  organisation_id bigint not null references organisation (id),
  name            text not null,
  email           text,
  deleted_at      timestamptz,
  created_at      timestamptz not null default now(),
  unique (organisation_id, id)
);

create table job (
  id              bigint generated always as identity primary key,
  organisation_id bigint not null references organisation (id),
  customer_id     bigint not null,
  title           text not null,
  status          text not null default 'draft'
                  check (status in ('draft', 'scheduled', 'in_progress', 'done', 'cancelled')),
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),
  unique (organisation_id, id),
  foreign key (organisation_id, customer_id)
    references customer (organisation_id, id)
);

create trigger job_set_updated_at
  before update on job
  for each row execute function set_updated_at();

With that foreign key, a job in organisation A simply cannot point at a customer in organisation B, whatever bug exists upstream. If you want another layer, PostgreSQL row level security can enforce tenant filtering on reads as well.

5. Status columns: text plus a check constraint

For status fields I usually prefer text with a check constraint over a native enum type. Adding a value to an enum is easy, but removing or renaming one is awkward. A check constraint can be replaced in a migration like any other constraint.

If the list of statuses needs labels, ordering or per-tenant configuration, promote it to a lookup table with a foreign key. That is a good sign the status has become data rather than code.

6. Put the rules in the database

Application validation is for friendly error messages. Database constraints are for truth. Code paths multiply over time: background jobs, admin scripts, imports, a second service. Only the database sees all of them.

The cheap wins:

• not null on everything that should never be missing. Make nullable the exception you justify.
• check constraints for ranges and simple rules.
• Foreign keys, plus an index on the referencing columns. PostgreSQL does not create those indexes for you, and unindexed foreign keys make deletes and joins slow.
• Partial unique indexes for rules that only apply to live rows.

That last one matters if you use soft deletes. A plain unique constraint on email would stop someone from re-adding a customer they had removed. A partial index handles it, and normalises case at the same time:

create unique index customer_email_active_uq
  on customer (organisation_id, lower(email))
  where deleted_at is null;

Be honest with yourself about soft deletes, though. Every query now needs deleted_at is null, and forgetting it once leaks "deleted" data. If you mostly need deletes for recovery or audit, an archive table or an event history (next section) is often cleaner.

7. Keep history for things people will ask about

The question that arrives in year two is rarely "what is the status?" It is "when did this change, and who changed it?" If you only store the current value, that answer is gone.

For the handful of fields that drive the business (status, assignee, price), keep an append-only history table:

create table job_status_change (
  id              bigint generated always as identity primary key,
  organisation_id bigint not null,
  job_id          bigint not null,
  from_status     text,
  to_status       text not null,
  changed_by      bigint,
  changed_at      timestamptz not null default now(),
  foreign key (organisation_id, job_id) references job (organisation_id, id)
);

create index job_status_change_job_idx
  on job_status_change (organisation_id, job_id, changed_at);

Write to it in the same transaction as the update. You do not need full event sourcing to get most of the value.

8. JSONB is an escape hatch, not a schema

jsonb is excellent for storing raw payloads from an external API, webhook bodies, or genuinely free-form settings. It is a poor home for fields you filter, join or report on. Once a key inside a JSON column appears in a where clause on most queries, it probably wants to be a real column with a type and a constraint.

9. Plan migrations as expand, then contract

The schema will change. What matters is changing it without locking a busy table or breaking the version of the app that is still running during a deploy.

The pattern is expand, migrate, contract: add the new shape alongside the old, move the data and the code across, then remove the old shape in a later release. For example, making a new column required on a large table:

alter table job add column priority smallint;

update job set priority = 3 where priority is null;

alter table job alter column priority set default 3;

alter table job
  add constraint job_priority_not_null check (priority is not null) not valid;

alter table job validate constraint job_priority_not_null;

alter table job alter column priority set not null;

Adding the constraint as not valid is quick, and validate constraint checks existing rows without blocking normal writes. On PostgreSQL 12 and later, set not null can then use the validated constraint instead of scanning the table again. On a big table, run the update in batches rather than one huge statement.

Indexes follow the same thinking. create index concurrently avoids blocking writes, but it cannot run inside a transaction, and many migration tools wrap each migration in one by default. Check how yours handles it before you need it.

The short version

Before the first real user signs in, I check that:

• Internal IDs are bigint identity, and public IDs are separate.
• Money is numeric or integer cents, with a currency, and historical documents snapshot their values.
• Every timestamp is timestamptz, and calendar dates are date.
• Tenant owned tables carry organisation_id, enforced by composite foreign keys.
• Rules live in constraints, and foreign keys are indexed.
• Important changes are recorded in an append-only history table.
• jsonb holds payloads, not core fields.
• The team knows how to run an expand and contract migration.

None of this is exotic, and none of it takes long on day one. That is the point: the decisions that hurt most later are usually the ones that were cheapest to get right at the start.

What would you add to the list? I am always keen to hear which schema decision bit other people hardest.

---

I'm Felix, and I build custom web apps and internal tools at Cena Advisory in Melbourne.

Comments

[Rishita Sharma]

The public_id split is such an easy win early. One thing I'd add for MVP-stage apps: put the tenant/org id on basically every table from day one, even if you only have one customer. Retrofitting that later is painful. Do you also add soft delete columns by default or only when someone asks?