PostgreSQL Max_Locks Shared Memory Auditor

The Incident (What Does the Error Mean?)

Raw error output:

ERROR:  out of shared memory
DETAIL:  max_locks_per_transaction 64 exceeded

PostgreSQL maintains a fixed-size, shared-memory lock table allocated at startup. The total number of lock objects that can exist simultaneously is hard-capped at:

max_locks_per_transaction × (max_connections + max_prepared_transactions)

With defaults (max_locks_per_transaction = 64, max_connections = 100), that's 6,400 total lock slots. Sounds like a lot. It isn't. A single pg_dump on a schema with 200 tables burns through 200+ lock slots in one transaction. A partitioned table with 100 child partitions? Every query touching it locks every partition. You hit the wall. PostgreSQL cannot allocate a new lock object. The transaction dies. If this hits your primary write path, your application is down.

The Attack Vector / Blast Radius

This is not a subtle degradation. It is a hard failure. The blast radius depends on which transaction type hits the ceiling first:

Scenario 1 — Partitioned Tables (most common in modern schemas): PostgreSQL locks every partition when a query touches a partitioned table, even for a single-row lookup in some planner paths. A table with 128 partitions × 2 concurrent transactions = 256 locks. Add 10 concurrent users and you're at 2,560 locks for a single table. This is why teams migrating to declarative partitioning suddenly hit this error with no other config changes.

Scenario 2 — pg_dump / pg_restore in production: pg_dump opens a single transaction and acquires ACCESS SHARE locks on every table in the target schema simultaneously. A schema with 300 tables will attempt to hold 300+ locks in one transaction. On a default config, this alone will trigger the error and block your backup pipeline silently — or worse, crash it mid-dump, leaving you with a corrupt backup file.

Scenario 3 — Long-running batch transactions: Any ETL or migration script that opens a single transaction and touches hundreds of tables (e.g., TRUNCATE cascades, foreign key validation passes) will exhaust the lock table. The failure is non-deterministic — it depends on what else is running concurrently — making it intermittent and hellish to reproduce in staging.

Cascading failure path: Lock table exhaustion → new transactions fail with ERROR → application connection pool fills with failed/retrying connections → connection count spikes → max_connections exhausted → full application outage.

Prevention in CI/CD

This error is 100% preventable with automated config linting. The default of 64 should be treated as a misconfiguration in any environment using partitioning or schemas with > 100 tables.

1. Checkov — IaC Policy for Terraform `aws_db_parameter_group`

# checkov custom check: check_pg_max_locks.py
from checkov.common.models.enums import CheckResult, CheckCategories
from checkov.terraform.checks.resource.base_resource_check import BaseResourceCheck

class PGMaxLocksCheck(BaseResourceCheck):
    def __init__(self):
        name = "Ensure max_locks_per_transaction is >= 128 for production RDS"
        id = "CKV_CUSTOM_PG_001"
        supported_resources = ['aws_db_parameter_group']
        categories = [CheckCategories.GENERAL_SECURITY]
        super().__init__(name=name, id=id, categories=categories,
                         supported_resources=supported_resources)

    def scan_resource_conf(self, conf):
        params = conf.get('parameter', [])
        for p in params:
            if isinstance(p, list): p = p[0]
            if p.get('name') == ['max_locks_per_transaction']:
                val = int(p.get('value', [64])[0])
                return CheckResult.PASSED if val >= 128 else CheckResult.FAILED
        return CheckResult.FAILED  # not set = default 64 = fail

2. OPA / Conftest Policy

# policy/pg_locks.rego
package postgresql

deny[msg] {
    input.resource_type == "aws_db_parameter_group"
    param := input.config.parameter[_]
    param.name == "max_locks_per_transaction"
    to_number(param.value) < 128
    msg := sprintf("max_locks_per_transaction is %v — must be >= 128 for partitioned workloads", [param.value])
}

deny[msg] {
    input.resource_type == "aws_db_parameter_group"
    not any_locks_param
    msg := "max_locks_per_transaction is not explicitly set — defaulting to 64 is unsafe for production"
}

any_locks_param {
    param := input.config.parameter[_]
    param.name == "max_locks_per_transaction"
}

3. GitHub Actions — Pre-merge Config Validation

# .github/workflows/pg-config-lint.yml
name: PostgreSQL Config Audit
on: [pull_request]
jobs:
  pg-lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Check max_locks_per_transaction
        run: |
          VALUE=$(grep 'max_locks_per_transaction' infra/postgresql.conf | awk -F= '{print $2}' | tr -d ' ')
          if [ -z "$VALUE" ] || [ "$VALUE" -lt 128 ]; then
            echo "::error::max_locks_per_transaction is '$VALUE' — must be >= 128. See runbook: docs/pg-locks-runbook.md"
            exit 1
          fi

4. Monitoring — Alert Before You Hit the Wall

Don't wait for the ERROR. Alert at 70% lock table utilization:

-- Prometheus / pg_exporter custom query
-- Add to queries.yaml
pg_lock_table_utilization:
  query: |
    SELECT
      COUNT(*) AS total_locks,
      current_setting('max_locks_per_transaction')::int *
        (current_setting('max_connections')::int +
         current_setting('max_prepared_transactions')::int) AS lock_table_ceiling,
      ROUND(
        COUNT(*) * 100.0 /
        (current_setting('max_locks_per_transaction')::int *
         (current_setting('max_connections')::int +
          current_setting('max_prepared_transactions')::int)), 2
      ) AS utilization_pct
    FROM pg_locks;
  metrics:
    - utilization_pct:
        usage: GAUGE
        description: "Lock table utilization percentage — alert at 70"

Set your alerting rule: fire PagerDuty at utilization_pct > 70. By the time you hit 100%, your on-call is already getting ERROR calls from the application layer.