SaaS architecture
out of shared memory in PostgreSQL: the lock table
PostgreSQL's out of shared memory, measured on 17 and 18: how many locks one transaction gets, what pg_dump, DROP SCHEMA and partitions take, and the fix.
ERROR: out of shared memory with HINT: You might need to increase "max_locks_per_transaction" (SQLSTATE 53200) does not mean the server ran out of RAM. With that hint it means the shared lock table is full: PostgreSQL keeps one entry for every table, index and other object any open transaction has locked, and that table has a fixed size, set at server start. (The same message with the hint max_pred_locks_per_transaction comes from the predicate-lock table of SERIALIZABLE transactions, which has a fixed size: on 17 it filled at 7,871 predicate locks while the transaction held only 406 ordinary ones. Advisory locks share the ordinary table.) One transaction that touches thousands of tables — a pg_dump of a database with a schema per tenant, a migration over every tenant schema, a query on a parent table with several thousand partitions — can fill it alone.
We measured where the limit lies on PostgreSQL 17.11 and 18.6, with the defaults (max_locks_per_transaction 64, max_connections 100, max_prepared_transactions 0) and with higher values, and what the usual causes in a multi-tenant database take.
How many locks can one transaction take?
A single transaction locked empty tables one by one until it failed:
max_locks_per_transaction | Locks held when it failed (17.11) | 18.6 |
|---|---|---|
| 64 (default) | 12,785 | 14,654 |
| 128 | 24,689 | — |
| 256 | 48,497 | — |
The error named the statement that did not fit: CONTEXT: SQL statement "lock table t12780 in access share mode". The documentation describes the table as having “space for max_locks_per_transaction objects (e.g., tables) per server process or prepared transaction”, and adds that “individual transactions can lock more objects as long as the locks of all transactions fit in the lock table”. “Per server process” counts every process slot, not only connections, and the lock table can also grow into spare shared memory; the measured limit was about twice what 64 × 100 connections suggests, and roughly doubled with the setting. It is still not a number to plan against: PostgreSQL 18’s new autovacuum_worker_slots (16 by default) put 18 at 14,654, and set to 3 it gave the same limit as 17; a fresh 17 server let one transaction hold 13,006 advisory locks, the same kind of server after a day of tests 12,766.
The space is shared. With a second session already holding 6,001 locks, the first could take 6,801 more; with the second holding 12,001, only 801. Both totals came to 12,802 (a few more than one session alone, because a session’s first locks can take a fast path outside the shared table). So whether a big transaction fits depends on what else is running at that moment — the same migration can pass on a quiet server and fail on a busy one.
What fills the lock table in a multi-tenant database?
Measured on 17.11 with the default of 64, unless the row says otherwise:
| Operation | Locks | Result |
|---|---|---|
pg_dump of 2,000 schemas × 10 tables | 20,151 (sampled, at 128) | failed (exit 1, pg_dump: error: query failed: ERROR: out of shared memory), also on 18; succeeded at 128 and 256, in about 3 s |
drop schema … cascade with 1,000 tables | 7,004 (about 7 per table) | succeeded |
drop schema … cascade or drop owned by with 3,000 tables | 21,004 at 256 | failed at 64, succeeded at 256 |
alter table … add column on 2,000 tenant schemas in one transaction | 2,003 | succeeded |
| the same on 20,000 tables | — | failed at statement 12,764 (17) and 14,616 (18); succeeded at 128 |
truncate of 10,000 tables in one transaction | 60,003 rows in pg_locks, about 40,000 objects | failed at 64 (at table 3,191) and 128, succeeded at 256 |
select count(*) on a table with 2,000 partitions, no filter | 4,004 | succeeded |
the same with where tenant_id = 5 | 6 | succeeded |
the same as a prepared statement forced to a generic plan (plan_cache_mode = force_generic_plan), where tenant_id = $1 | 2,003 | succeeded, although the plan said Subplans Removed: 1999 |
the same prepared statement with the default plan_cache_mode = auto | 6 | it stayed on a custom plan, 8 of 8 executions |
Two of those rows are easy to miss. A DROP took about seven locks per table, so 3,000 tables needed 21,004. And a prepared statement forced to a generic plan locked every partition even though it then skipped all but one: pruning when execution starts does not save the locks. With the default plan_cache_mode it never switched to the generic plan in our test and took 6. Rows in pg_locks are not entries in the lock table, either: truncate holds two modes on each table and its TOAST table, so its 60,003 rows were about 40,000 entries — which is why it fitted at 256 and stopped at table 3,191 at 64. For one partition per tenant, the same effect under concurrency and the fix (one tenant_id predicate per query) are measured in partition per tenant in PostgreSQL.
How do you fix out of shared memory?
Raise max_locks_per_transaction, and restart. It cannot be changed at runtime: a set failed with 55P02: parameter "max_locks_per_transaction" cannot be changed without restarting the server; after alter system set and a reload, pg_settings.pending_restart turned true and nothing changed until the restart. Raise it on the standbys first:
max_locks_per_transaction from 64 to 128 on a primary with a hot standby- Primary raised first, standby still on 64The standby keeps answering reads.
WARNING: hot standby is not possible because of insufficient parameter settings, thenrecovery has paused: replay stops - Standby restarted on 64It does not come up.
FATAL: recovery aborted because of insufficient parameter settings— 64 is lower than the primary's 128
PostgreSQL 17.11, streaming replication. The documentation: a standby must have "the same or higher value as on the primary server".
The documentation requires the same thing — a standby must have “the same or higher value as on the primary server” — so the order is: standbys first, then the primary, each with a restart.
The memory cost was small in our containers: shared_memory_size was 143 MB at 64, 146 MB at 128, 152 MB at 256 and 187 MB at 1,024.
Or do less per transaction. A migration over 2,000 tenant schemas, one schema per transaction, took 13 locks per transaction and finished in 2.4 s without errors. pg_dump -n <schema> per schema succeeded 100 times out of 100, at about 0.8 s each — fine for restoring one tenant, slow as a backup of 2,000 (we stopped a full run at schema 820), and separate dumps do not share one snapshot across tenants. A dump of one tenant’s rows from shared tables, without a schema per tenant, is in dumping one tenant with pg_dump.
The choice is not either-or. For a schema-per-tenant design the backup has to work in one transaction, so the setting has to grow with the number of tenants; and migrations that loop over tenants should commit per tenant anyway, so that one failure does not roll back the other 1,999. Where the schema-per-tenant model hits this limit first, compared with the other isolation models, is in multi-tenant architecture: choosing an isolation model.
What we did not measure
Managed services, where changing the setting means a parameter group and a restart window.
Sources: PostgreSQL 17 documentation on lock management (max_locks_per_transaction), read on 22 October 2026. All measurements are our own, on PostgreSQL 17.11 and 18.6 in containers, on 21 and 22 October 2026.