Fast Path Lock Exhaustion from Partitioning

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

Fast path lock exhaustion on partitioned tables is a known PostgreSQL performance issue. Here's how to diagnose and fix it: ## Diagnosing Fast Path Lock Exhaustion **1. Check the number of partitions being accessed:** ```sql -- See how many partitions exist SELECT nmsp_parent.nspname AS schema, parent.relname AS parent_table, count(*) AS partition_count FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON parent.relnamespace = nmsp_parent.oid GROUP BY nmsp_parent.nspname, parent.relname ORDER BY partition_count DESC; ``` **2. Monitor lock acquisitions:** ```sql -- Check current locks per backend SELECT pid, count(*) FILTER (WHERE fastpath) as fastpath_locks, count(*) FILTER (WHERE NOT fastpath) as regular_locks FROM pg_locks GROUP BY pid ORDER BY regular_locks DESC; ``` **3. Check if queries are hitting many partitions:** ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_partitioned_table WHERE ...; ``` If you see many "Seq Scan" or "Index Scan" nodes (one per partition), you're likely hitting the limit. PostgreSQL has a fast path lock limit of **16 locks per backend** - exceeding this forces slower shared memory locks. ## Fixes **1. Reduce partitions accessed (best solution):** - Add partition pruning constraints to your queries - Ensure partition key is in WHERE clause - Consider partition strategy (fewer, larger partitions) **2. Increase `max_locks_per_transaction`:** ```sql -- In postgresql.conf (requires restart) max_locks_per_transaction = 256 -- default is 64 ``` **3. Use partition-wise joins/aggregates:** ```sql -- In postgresql.conf enable_partitionwise_join = on enable_partitionwise_aggregate = on ``` **4. Consider table design changes:** - If queries regularly scan many partitions, reconsider partitioning strategy - Use fewer, larger partitions (e.g., monthly instead of daily) - Add indexes to enable partition pruning **Quick diagnostic query to run:** ```sql SELECT count(*) FROM pg_locks WHERE NOT granted; -- Should be 0 SELECT relation::regclass, mode, count(*) FROM pg_locks WHERE pid = pg_backend_pid() GROUP BY relation, mode; ``` The most common fix is **adjusting your query to access fewer partitions** via better WHERE clauses on the partition key. What does your partitioning setup look like?

I'll help you diagnose and fix fast path lock exhaustion in PostgreSQL. This is a common issue with partitioned tables where queries need to acquire locks on many partitions. ## Diagnosis **Check fast path lock exhaustion:** ```sql -- Monitor fast path locks vs regular locks SELECT pid, wait_event_type, wait_event, query, (SELECT COUNT(*) FROM pg_locks l WHERE l.pid = a.pid AND l.fastpath = true) as fastpath_locks, (SELECT COUNT(*) FROM pg_locks l WHERE l.pid = a.pid AND l.fastpath = false) as regular_locks FROM pg_stat_activity a WHERE state = 'active' ORDER BY regular_locks DESC; ``` **Check how many partitions your queries touch:** ```sql -- See partition access pattern SELECT schemaname, tablename, n_tup_ins + n_tup_upd + n_tup_del as modifications, seq_scan, idx_scan FROM pg_stat_user_tables WHERE tablename LIKE 'your_table_%' -- your partition pattern ORDER BY seq_scan + idx_scan DESC LIMIT 20; ``` **Key indicator**: If you see queries regularly hitting >16 partitions, you're likely hitting fast path exhaustion (PostgreSQL's fast path limit is 16 locks per backend). ## Solutions ### 1. **Partition Pruning** (Best fix) Ensure your queries use partition key in WHERE clauses: ```sql -- Bad: scans all partitions SELECT * FROM partitioned_table WHERE customer_id = 123; -- Good: prunes to specific partition SELECT * FROM partitioned_table WHERE partition_date = '2026-03-31' AND customer_id = 123; ``` Check if pruning is working: ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE your_partition_key = 'value'; -- Look for "Subplans Removed" in output ``` ### 2. **Increase max_locks_per_transaction** ```sql -- Check current setting SHOW max_locks_per_transaction; -- default is 64 -- In postgresql.conf, increase it: -- max_locks_per_transaction = 256 -- Requires restart ``` ⚠️ This uses more shared memory but helps with lock table contention. ### 3. **Reduce partition count** If you have too many partitions (hundreds): - Consolidate smaller partitions - Use larger time windows (monthly instead of daily) - Consider if you really need that many partitions ### 4. **Connection pooling with statement routing** Use PgBouncer or similar to reduce concurrent connections hitting many partitions. ## Quick temporary workaround ```sql -- Reduce partition count accessed by limiting time range SET enable_partition_pruning = on; SET constraint_exclusion = partition; ``` Would you like me to help you analyze your specific table structure or queries to implement the best fix?

Query `pg_locks` and check the `fastpath` column — if you see 'f' (false) for locks held by your slow queries, you've confirmed fast path slot exhaustion. Each backend has only 16 fast path slots (FP_LOCK_SLOTS_PER_BACKEND), and any lock beyond that migrates to the slow shared memory path. This is the smoking gun for this scenario, as described in the `fast-path-lock-exhaustion-read-contention` insight. Run `SELECT relation::regclass, mode, fastpath FROM pg_locks WHERE pid = <your_backend_pid>` to see which relations forced slow path.

Look at `pg_stat_activity` for `wait_event_type = 'LWLock'` and `wait_event = 'lock_manager'`, or check CloudWatch Database Insights if you're on RDS/Aurora. If LWLock:LockManager is consuming >10% of your database load (or 34% under high concurrency), the lock manager contention is throttling throughput significantly — the `lock-manager-throughput-degradation` insight shows this can reduce throughput by up to 34%. This confirms the performance impact matches the lock exhaustion pattern.

Run `SELECT count(*) FROM pg_inherits WHERE inhparent = 'your_table'::regclass` to count partitions, and check `SELECT count(*) FROM pg_indexes WHERE tablename = 'your_table'` for indexes per partition. The math is simple: (partitions × indexes_per_partition) + parent_table_locks. If a table has 12 partitions with 1 index each, a query touching all partitions acquires 26 locks (12 + 12 + 1 parent + 1 parent index), exceeding the 16-slot limit by 10 locks. Use the `partitions` metric to track this over time, and reference the `unpruned-partition-scan-lock-exhaustion` insight.

Run `EXPLAIN` on the slow queries identified by `postgresql-queries-duration-max` and look for 'Partitions removed' or 'Partitions scanned'. If the query scans all partitions when it should only touch one or two, partition pruning isn't working — usually because the WHERE clause doesn't include the partition key as a constant value. The `unpruned-partition-scan-lock-exhaustion` insight explains that PL/pgSQL requires `EXECUTE format()` to inject runtime values as constants for pruning to work. A query scanning 2 partitions acquires ~6 locks (staying under 16), but scanning all 12 acquires 26.

Query `pg_stat_user_indexes` for tables with 20+ indexes, and check `idx_scan = 0` to find unused ones — the `excessive-indexes-lock-exhaustion` insight shows that even a simple SELECT acquires locks on all indexes, whether used or not. A table with 20 indexes can exhaust fast path slots on its own, without partitioning. Drop unused indexes or consolidate multiple single-column indexes into multi-column indexes where appropriate. Even on non-partitioned tables, too many indexes cause the same lock exhaustion pattern.

Based on your findings, fix the root cause: add partition key columns with constant values to WHERE clauses (use `EXECUTE format()` in PL/pgSQL), drop unused indexes (especially on highly partitioned tables), or consolidate partitions if the partition strategy creates too many. The goal is to keep total locks per query under 16. Test with EXPLAIN to confirm pruning works and rerun the slow query — you should see near-instant elimination of LWLock:lock_manager wait events and recovery of the original query performance.