Schema migrations on a shared multi-tenant PostgreSQL table

In a multi-tenant SaaS with shared tables, “a migration runs once” is the advantage — and also the risk: one ALTER TABLE touches every tenant at the same moment. Most schema changes on PostgreSQL are fast. What makes them dangerous is not their duration but the lock they need, and what happens to everyone else while they wait for it.

We measured common migrations on PostgreSQL 17.10, in a throwaway container with default settings: one invoices table with 2,074,910 rows across 100 tenants (195 MB), row-level security with FORCE, and a second session reading and writing as another tenant through the application role.

Why does a fast ALTER TABLE stop every tenant?

Because it waits in line, and everyone else waits behind it. Adding a nullable column takes 1.7 ms on its own. We ran it while a three-second report on one tenant’s invoices was still going. The ALTER TABLE waited 2.5 seconds for that report to finish — and a simple query from another tenant, started half a second later, waited 2.0 seconds behind the ALTER TABLE. Nothing was wrong with either query; the migration needs an exclusive lock, cannot get it while the report runs, and every new query queues behind the waiting migration.

With set lock_timeout = '200ms' in the migration’s session, the ALTER TABLE gave up after 200 ms, and the other tenant’s query — started after the migration had given up — took 5 ms. The worst any query waits is then the timeout itself: 200 ms instead of as long as the slowest running query. Then you try again, a few seconds later. On a shared table a lock timeout is not optional; it decides whether a one-millisecond change becomes an outage for all tenants.

Which migrations rewrite the whole table?

Measured on the same 2,074,910 rows:

A constant default is stored in the catalogue and costs nothing — so does now(), which is evaluated once. A volatile one — a random UUID, clock_timestamp() — makes PostgreSQL write every row again, and the documentation is explicit: “Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten.” For five seconds nobody could write an invoice. Add the column without a default, set the default straight away with ALTER COLUMN … SET DEFAULT (that only applies to new rows and does not rewrite), then fill the existing rows in batches.

A constraint is cheapest in two steps: NOT VALID takes the strong lock for 2 ms — provided you commit straight away; kept in an open transaction it blocked readers for as long as that transaction lasted — and then enforces the rule on new rows; VALIDATE CONSTRAINT checks the existing rows under a lock that, as the documentation says, is only SHARE UPDATE EXCLUSIVE — in our test it blocked neither reads nor inserts. The same trick removes the scan from SET NOT NULL: with a validated check (paid_on is not null) in place, PostgreSQL skipped the scan and the statement took 2.1 ms instead of 179.

Why can’t you change the type of tenant_id under row-level security?

Because the policy depends on it. alter table invoices alter column tenant_id type bigint was refused straight away:

ERROR:  cannot alter type of a column used in a policy definition
DETAIL:  policy p on table invoices depends on column "tenant_id"

The way through is to drop the policy, change the type and create the policy again — and the one thing that matters is doing that in one transaction. We did: drop policy, alter type, create policy, commit, in 2.8 seconds, almost all of it the rewrite. DDL in PostgreSQL is transactional, and the transaction holds the table’s ACCESS EXCLUSIVE lock from the DROP POLICY until the commit, so other sessions wait rather than see the table without its policy. Run the same steps as separate migrations, and in between row-level security finds no policy and denies everything: with the policy dropped, the application role counted 0 invoices and an insert failed with new row violates row-level security policy. Every tenant sees an empty product from the moment the policy is gone until it is back. Check how your migration tool groups statements into transactions before you rely on it.

The rewrite itself is the other reason this is one of the decisions you do not get to undo: on a large shared table, changing the key type of the column every policy uses costs an ACCESS EXCLUSIVE lock for as long as the table takes to rewrite.

A checklist for migrations on shared tables

1. Always a lock_timeout in migration sessions, with a retry. Without it, a fast migration behind one slow query stops every tenant.
2. No volatile defaults on existing tables. Add the column, fill it in batches, set the default afterwards.
3. Constraints in two steps: NOT VALID (and commit), then VALIDATE CONSTRAINT. For NOT NULL, validate a CHECK (… IS NOT NULL) first; after SET NOT NULL the helper check can go.
4. Indexes CONCURRENTLY — outside a transaction block, and check afterwards: a concurrent build that fails leaves an invalid index behind.
5. Changes to tenant_id or any column a policy uses: drop, alter and recreate the policy in one transaction, and make sure your migration tool does not split it.

How long long-running operations on one tenant hold locks — deleting a tenant, dropping a partition — is in deleting one tenant in PostgreSQL.

What we did not measure

Foreign keys added NOT VALID, changing a type that is binary-compatible (no rewrite), migrations on partitioned tables, and migration tools themselves. The durations are for 2 million rows on one machine; the lock behaviour is what carries over.

Sources: the PostgreSQL 17 documentation on ALTER TABLE (rewrites with volatile defaults, NOT VALID, VALIDATE CONSTRAINT and its lock, SET NOT NULL skipping the scan), read on 1 October 2026. All measurements are our own, on PostgreSQL 17.10, on 1 October 2026.