Fast Path Lock Exhaustion from Partitioning

warningperformance

Diagnosing and resolving performance degradation when queries on partitioned tables or tables with many indexes exhaust PostgreSQL's fast path lock cache.

Prompt: “Our PostgreSQL queries on a partitioned table suddenly got 10x slower. I see locks showing up in pg_locks but not blocking each other. Someone mentioned fast path lock exhaustion - how do I diagnose this and what's the fix?”

Agent Playbook

When an agent encounters this scenario, Schema provides these diagnostic steps automatically.

Fast path lock exhaustion occurs when a single query acquires more than 16 unique table/index locks, forcing PostgreSQL to use the slower shared memory lock hash table. Start by confirming the exhaustion in pg_locks, then verify the performance impact via LWLock wait events, and finally identify the root cause — typically too many partitions being scanned without pruning, or excessive indexes on the table.

1Confirm fast path lock exhaustion in pg_locks

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.

Fast path lock exhaustion causes lock manager contention during read operations postgresql.locks

2Check for LWLock:LockManager wait events

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.

Lock manager contention reduces throughput by up to 34 percent under high concurrency

3Count partitions and indexes on the affected table

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.

Partitioned table scan without pruning exhausts fast path locks partitions_total

4Verify partition pruning is working

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.

Partitioned table scan without pruning exhausts fast path locks queries_duration_max

5Audit for excessive or unused indexes

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.

Excessive indexes on nonpartitioned tables cause fast path lock exhaustion

6Implement partition pruning or reduce lock count

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.

Partitioned table scan without pruning exhausts fast path locks Excessive indexes on nonpartitioned tables cause fast path lock exhaustion