SaaS architecture
Schema migrations on a shared multi-tenant PostgreSQL table
ALTER TABLE on a table every tenant shares, measured on PostgreSQL 17: the lock queue, lock_timeout, rewrites, NOT VALID, and changing tenant_id under RLS.
Now-Next builds SaaS products and takes over existing ones. What we build
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.
How do you run a schema migration on a shared multi-tenant table without downtime?
On a table every tenant shares, downtime usually comes from the lock queue rather than from the migration itself: in our measurement a 1.7 ms ALTER TABLE behind one three-second report made another tenant’s simple query wait 2.0 seconds. Give every migration session a lock_timeout and retry: the other tenants then wait at most the timeout itself (111 ms in a 100,000-row repeat run, instead of 2 seconds), and keep each DDL statement in a short transaction of its own, because once the migration has its lock everyone waits until it commits. Avoid what rewrites or scans the whole table under a strong lock: add columns without a volatile default and fill them in batches, add constraints NOT VALID and commit straight away before validating them, and build indexes CONCURRENTLY; a change to a column that a row-level security policy uses still blocks every tenant for as long as the rewrite takes (2.8 s here), but done in one transaction with the policy, no tenant ever sees the table without it. Most of these steps are measured below on 2 million rows across 100 tenants; the checklist at the end puts them in order.
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. While the migration waits, the worst any query waits is then the timeout itself: 200 ms instead of as long as the slowest running query. Once the migration has its lock, everyone waits until its transaction ends, so keep the DDL in a short transaction of its own. 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.
ALTER TABLE ADD COLUMN during a three-second report: who waits for whomWithout lock_timeout
- Report, tenant AReads the shared table for three seconds and holds its lock until it ends.Lock queue: empty
- MigrationNeeds an ACCESS EXCLUSIVE lock, cannot get it while the report holds ACCESS SHARE, and waits in line.Queue: the migration
- Simple query, tenant B, half a second after the migrationWould not conflict with the report, but queues behind the waiting migration.Queue: migration, then tenant B
- Report endsThe migration gets its lock after 2.5 s of waiting and runs in milliseconds; then tenant B runs, after 2.0 s of waiting.Queue: empty
With lock_timeout = '200ms' in the migration's session
- MigrationWaits behind the same report and gives up after 200 ms; you try again later.Queue: the migration, for 200 ms at most
- Simple query, tenant B, during those 200 msQueues behind the migration until it gives up: in our repeat run 111 ms.Queue: empty after 200 ms
- Simple query, tenant B, after thatNothing in line before it: it took 5 ms.Queue: empty
Measured on PostgreSQL 17.10, 2,074,910 rows, default settings. The waiting times are the ones in the two paragraphs above. The queue states were sampled from pg_locks and pg_blocking_pids() in a repeat run on PostgreSQL 17.11 with 100,000 rows on 27 October 2026: the report held ACCESS SHARE, the migration waited for ACCESS EXCLUSIVE blocked by the report, and tenant B waited blocked by the migration (2,506 ms and 2,013 ms); a query started during the 200 ms waited 111 ms.
Which migrations rewrite the whole table?
Measured on the same 2,074,910 rows (changing a varchar limit is measured separately in value too long for type character varying: larger is free, smaller rewrites):
| Migration | Time | Other tenants meanwhile |
|---|---|---|
add column note text | 1.7 ms | behind a running report: waited 2.0 s (above) |
add column currency text not null default 'EUR' | 1.5 ms | idem |
add column ref uuid not null default gen_random_uuid() | 5.0–5.2 s | insert from another tenant waited 4.7 s |
add constraint … check (total_cents >= 0) | 104–264 ms | reads blocked for the duration |
same, not valid | 2.1 ms | reads blocked for the duration |
validate constraint | 115–142 ms | not blocked: read 5 ms, insert 9 ms |
alter column … set not null | 179 ms | not measured |
set not null with a valid check (… is not null) in place | 2.1 ms | not measured |
create index | 1.06–1.09 s | insert waited 0.77 s (reads were not measured) |
create index concurrently | 1.28 s | not blocked: insert 3 ms |
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.
Why does it say cached plan must not change result type?
cached plan must not change result type (SQLSTATE 0A000) means a prepared statement’s result columns changed after it was prepared, for example because a migration ran while the application kept its connections open. The documentation says PostgreSQL “will force re-analysis and re-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes”; when that re-analysis gave other columns, we got this error. We measured it on PostgreSQL 12.22, 17.11 and 18.6, with identical results for psql, psycopg, asyncpg, pgJDBC and pgx.
With select *, and with RETURNING *, ADD COLUMN, DROP COLUMN, RENAME COLUMN and ALTER COLUMN TYPE all triggered it. An explicit column list survives ADD COLUMN and changes to columns it does not name, but not a type change on a column it does name: even varchar(20) to varchar(40) failed, while name::text survived. A dropped or renamed column in the list gives column "name" does not exist (42703) instead, which no retry and no setting below cured. What the client saw after ADD COLUMN under select *:
| Client | Outside a transaction | Inside a transaction |
|---|---|---|
| psycopg 3.3.6 | in autocommit fails on every retry until a ROLLBACK, 100 of 100 queries over five connections; with psycopg_pool once per connection, 5 of 100 | error, then 25P02; clean after ROLLBACK |
| Prisma 6.19.3 | raw query P2010 on every retry; ORM queries name their columns and survived | error, then 25P02; still failing after rollback |
| pgx 5.11.0 | fails once per connection; 5 of 100 over a pool of five | error, then 25P02; clean after rollback |
| pgJDBC 42.7.14 | none reaches the application | error, then 25P02; none with autosave=conservative |
| asyncpg 0.32.0 | none, except from an explicit prepare() object | InvalidCachedStatementError, then 25P02 |
| Rails 8.1.4 | none reaches the application | PreparedStatementCacheExpired, then 25P02; the transaction block did not run again |
| Prisma 7.10.1 | no error | no error |
DISCARD PLANS and both forced values of plan_cache_mode changed nothing. DEALLOCATE ALL works only on the connection that runs it: psycopg’s next query succeeded, and pgx got one 26000 first. A new connection worked too, and so did not preparing at all: prepare_threshold=None, prepareThreshold=0, default_query_exec_mode=exec, statement_cache_size=0 and prepared_statements: false. pgx’s cache_describe mode is no way out: it failed with bind message has 5 result formats but query has 6 columns (08P01).
For a deploy, this means old instances survive an ADD COLUMN if they name their columns: over five psycopg connections, 0 of 100 queries failed. Change the type of a named column, or drop or rename one, only after the last old instance has stopped.
Behind PgBouncer 1.26.1 with statement tracking on, the driver’s own recovery stops working, because the statement on the server belongs to PgBouncer. pgJDBC, including with autosave=conservative, pgx and asyncpg kept failing, also after a rollback; psycopg kept failing in autocommit and recovered after a rollback. Rails and Prisma we did not measure there. DEALLOCATE ALL from a client cleared only one of two server connections. The documentation says “you can run RECONNECT on the PgBouncer admin console after doing the migration to force a re-prepare of the query and make the error go away”, and that cleared both. pgJDBC, pgx and asyncpg with preparing switched off did not fail at all.
A checklist for migrations on shared tables
- Always a
lock_timeoutin migration sessions, with a retry. Without it, a fast migration behind one slow query stops every tenant What the error does to the rest of the transaction, and a retry loop that was measured, are in canceling statement due to lock timeout. - No volatile defaults on existing tables. Add the column, fill it in batches, set the default afterwards.
- Constraints in two steps:
NOT VALID(and commit), thenVALIDATE CONSTRAINT. ForNOT NULL, validate aCHECK (… IS NOT NULL)first; afterSET NOT NULLthe helper check can go. - Indexes
CONCURRENTLY— outside a transaction block, and check afterwards: a concurrent build that fails leaves an invalid index behind. - Changes to
tenant_idor any column a policy uses: drop, alter and recreate the policy in one transaction, and make sure your migration tool does not split it. PostgreSQL has noCREATE POLICY IF NOT EXISTS; how to make a policy migration repeatable, and what two deploys at once do to it, is in CREATE POLICY IF NOT EXISTS in PostgreSQL. - Name your columns in anything prepared, and deploy the new application before you change the type of a named column, drop it or rename it: old instances still hold the old statements.
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. The cached plan section: PostgreSQL 17 documentation on PREPARE and the PgBouncer configuration reference (max_prepared_statements), read on 31 October 2026; measured on PostgreSQL 12.22, 17.11 and 18.6 with psycopg 3.3.6, pgJDBC 42.7.14, pgx 5.11.0, asyncpg 0.32.0, Rails 8.1.4 and Prisma 6.19.3 and 7.10.1, and on 17.11 through PgBouncer 1.26.1, on 31 October 2026.