Postgres 10.2查询分区表时出现out of shared memory错误的原因及解决方案咨询
Let’s work through your questions starting with the core one that’s probably bugging you the most: why a simple SELECT is causing this shared memory issue.
Core Explanation: SELECT does acquire locks on partitions
A lot of folks assume SELECT queries don’t use locks, but PostgreSQL requires an ACCESS SHARE lock on every table (including child partitions) it interacts with during a transaction. This is a super lightweight, shared lock—it doesn’t block other reads or most writes—but each lock still takes up a slot in the database’s shared memory pool.
Your animals table has 1000 partitions. When you run a SELECT against the parent table without targeting a specific child, PostgreSQL has to check (and lock) all 1000 child tables upfront (even if partition pruning later narrows things down, the lock acquisition happens first). PostgreSQL 10’s default max_locks_per_transaction is 64, so a single query trying to lock 1000 tables blows right past that limit, hence the out of shared memory error.
Will querying the child table directly fix this?
100% yes. If your app can use logic to pinpoint exactly which child partition holds the animalid you’re querying, your request will only need one ACCESS SHARE lock (for that single child table). That drops the lock count way below the max_locks_per_transaction threshold, and the error disappears. This is actually the most efficient fix for high-throughput scenarios with large partitioned tables.
Is increasing max_locks_per_transaction a reasonable fix for partitioned tables with updates?
It’s a viable short-term band-aid, but not always the best long-term solution:
- The upside: It directly resolves the lock limit issue. If your server has plenty of spare shared memory, cranking this parameter up (say, to 2000 or higher) will let transactions handle all 1000 partitions without hitting the cap.
- The downside: Lock slots eat into shared memory. The total number of available lock slots across your system is
max_locks_per_transaction * max_connections, so increasing this setting will boost shared memory usage. If you have high concurrency, this could lead to other shared memory constraints down the line.
For update operations, keep in mind they’ll acquire stronger locks (like ROW EXCLUSIVE), but the root problem for your SELECT queries is still the sheer number of partitions being locked per transaction.
Could other applications' queries be contributing to this?
Absolutely. If other concurrent transactions are also accessing large numbers of partitions (whether via SELECT or write operations), they’ll consume lock slots in the shared memory pool. This reduces the available slots for your app’s queries, making it more likely to hit the limit even if your individual queries wouldn’t exceed it on their own. High concurrency paired with 1000 partitions makes this problem way worse.
内容的提问来源于stack exchange，提问作者Chillax
