Put the tenant key on every table before you need a second shard

Sharding can wait, but a tenant_id column in every table and every primary key can't. A Postgres migration checklist with the SQL for each step.

App service Query layer Shard map Shard A Shard B CDC stream Warehouse tenant-scoped tenant to shard tenant key in every event
A shard map sends each tenant to shard A or B, and every change event downstream carries the tenant key.
On this page
  1. What a missing key looks like at 180 tables
  2. The migration, parents first
  3. Finding what’s left
  4. What the key gives you besides shards
DELETE FROM appointment_notes WHERE id = 81422907;

Nothing in that statement says whose note it is. A router sitting in front of two databases can’t send it anywhere without asking a third system first. A row-level security policy has no column to check. The change event it produces lands in the stream with a note id and nothing else.

I don’t think most multi-tenant systems should shard early. One well-run Postgres primary carries a business much further than people expect, and a second shard brings operational work you’d rather postpone. What you can’t postpone cheaply is the tenant key. Adding tenant_id to a new table and its primary key takes an afternoon. Adding it to a table with 90 million rows and live traffic takes a plan, and doing that across a few dozen tables is a quarter of someone’s year.

So: postpone the shards, and put the key in now.

What a missing key looks like at 180 tables

Wicklow Booking, a scheduling SaaS I’ve made up for the arithmetic, has 180 tables. Most carry tenant_id because the early ones were written carefully. Thirty-one don’t, and they’re the ones that hang off something else: a note belongs to an appointment, an appointment to a calendar, a calendar to a location, and only the location knows its tenant. Three joins to answer “whose row is this”.

The worst of the 31 is appointment_notes, at 90 million rows. One tenant, a hospital group, owns 22% of them, close to 20 million rows. That tenant is the reason anyone at Wicklow is talking about shards at all. Its month-end reporting slows everyone else down, and the obvious fix is to move it to its own database.

Today that move means 31 copy jobs, each with its own join chain, each needing the same chain again to verify. With the key in place, every one of them is WHERE tenant_id = 4107.

The migration, parents first

Order matters. Backfill calendars from locations, then appointments from calendars, then appointment_notes from appointments, so each table is only ever one join from a parent that already has the key. Before any backfill starts, ship the application change that writes tenant_id on insert, or you’ll be chasing new nulls forever.

Here is the sequence for the notes table. It works on any Postgres from 12 up.

-- 1. Add the column. Nullable, no default: a catalog change, no rewrite.
ALTER TABLE appointment_notes ADD COLUMN tenant_id bigint;

-- 2. Backfill by id range. Run from a script, one batch per transaction,
--    advancing :lo by 50000 each time. Roughly 1,800 batches for 90M rows.
UPDATE appointment_notes n
SET    tenant_id = a.tenant_id
FROM   appointments a
WHERE  a.id = n.appointment_id
AND    n.id >= :lo AND n.id < :lo + 50000
AND    n.tenant_id IS NULL;

-- 3. Make it NOT NULL without one long lock. The validated check lets
--    SET NOT NULL skip its own full scan.
ALTER TABLE appointment_notes
  ADD CONSTRAINT appointment_notes_tenant_nn
  CHECK (tenant_id IS NOT NULL) NOT VALID;
ALTER TABLE appointment_notes VALIDATE CONSTRAINT appointment_notes_tenant_nn;
ALTER TABLE appointment_notes ALTER COLUMN tenant_id SET NOT NULL;
ALTER TABLE appointment_notes DROP CONSTRAINT appointment_notes_tenant_nn;

-- 4. Composite primary key. Build the index online, then swap.
CREATE UNIQUE INDEX CONCURRENTLY appointment_notes_tenant_id_id
  ON appointment_notes (tenant_id, id);

BEGIN;
ALTER TABLE appointment_notes DROP CONSTRAINT appointment_notes_pkey;
ALTER TABLE appointment_notes
  ADD CONSTRAINT appointment_notes_pkey
  PRIMARY KEY USING INDEX appointment_notes_tenant_id_id;
COMMIT;

-- 5. Composite foreign key. The parent needs a unique index on
--    (tenant_id, id) first. Add unvalidated, validate, drop the old one.
ALTER TABLE appointment_notes
  ADD CONSTRAINT appointment_notes_appointment_tenant_fk
  FOREIGN KEY (tenant_id, appointment_id)
  REFERENCES appointments (tenant_id, id) NOT VALID;
ALTER TABLE appointment_notes
  VALIDATE CONSTRAINT appointment_notes_appointment_tenant_fk;
ALTER TABLE appointment_notes
  DROP CONSTRAINT appointment_notes_appointment_id_fkey;

A few things the SQL doesn’t say. The backfill rewrites every row, so the table bloats; pause between batches and let autovacuum keep up. Step 4 only works as written on a leaf table. If other tables reference the old primary key, move their foreign keys to the composite form first. And keep id globally unique. The new primary key no longer enforces that on its own, so the sequence has to, and you don’t want two shards handing out the same note id.

The composite foreign key is what turns the tenant column from a convention into a rule. Once it exists, the database itself refuses a note that points at another tenant’s appointment. That class of bug stops being something code review has to catch.

Finding what’s left

You need a list that shrinks. Two catalog queries give you one: tables with no tenant_id, and foreign keys that don’t include it.

-- Tables still missing the key, biggest first
SELECT c.relname, c.reltuples::bigint AS approx_rows
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname = 'public'
AND    c.relkind IN ('r', 'p')
AND    NOT EXISTS (
         SELECT 1 FROM pg_attribute a
         WHERE  a.attrelid = c.oid
         AND    a.attname = 'tenant_id'
         AND    NOT a.attisdropped)
ORDER  BY c.reltuples DESC;

-- Foreign keys (joins) that don't carry the key
SELECT con.conrelid::regclass  AS child,
       con.confrelid::regclass AS parent,
       con.conname
FROM   pg_constraint con
WHERE  con.contype = 'f'
AND    con.connamespace = 'public'::regnamespace
AND    NOT EXISTS (
         SELECT 1 FROM pg_attribute a
         WHERE  a.attrelid = con.conrelid
         AND    a.attnum = ANY (con.conkey)
         AND    a.attname = 'tenant_id');

Some tables are global on purpose, like plans and country codes. Write those names into an allowlist and run the first query in CI with the allowlist subtracted. A new table without the key then fails the build, which is when the fix still takes an afternoon.

What the key gives you besides shards

Row-level security becomes a template you stamp on every table instead of a per-table design exercise:

ALTER TABLE appointment_notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE appointment_notes FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON appointment_notes
  USING      (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

-- In the query layer, at the start of each transaction:
-- SELECT set_config('app.tenant_id', $1, true);

If the setting is missing, current_setting raises an error rather than returning every row. I like that it fails closed. Superusers and roles with BYPASSRLS skip policies entirely, so the application has to connect as neither.

Change data capture improves too. Debezium builds each event’s key from the table’s primary key, so once tenant_id is part of that key, every event carries it, deletes included. With the default replica identity a delete event has only the key columns to offer, so before this migration a deleted note reached the warehouse as a bare id, and the join that would have told you its tenant no longer had a row to join to. Afterward the warehouse can partition by tenant, or drop one tenant’s data on request, without a lookup.

And the hospital group can move. Copy its rows table by table in foreign key order, let the stream catch up, then change one row in the shard map. The query layer was already passing a tenant on every call, so it doesn’t notice.

That last step is the only part of the work that deserves to be called sharding, and Wicklow could put it off for another two years. The 31 tables it should start on Monday.