How to Fix PostgreSQL 'max_prepared_transactions Too Small' Error in Production

Threat/Impact Level: HIGH | Exploitability/Downtime Risk: HIGH | Time to Fix: 10 mins

TL;DR

• What broke: max_prepared_transactions is set to 0 or a value lower than the number of concurrent two-phase commit (2PC) transactions your workload is issuing, causing PREPARE TRANSACTION to fail with a hard error.
• How to fix it: Increase max_prepared_transactions in postgresql.conf to at least match max_connections, then reload Postgres. Requires a full server restart — not just pg_reload_conf().
• Use the Client-Side Sandbox above to paste your postgresql.conf and auto-generate the corrected parameter block.

---

The Incident (What Does the Error Mean?)

Raw error output from application logs or psql:

ERROR:  max_prepared_transactions is not large enough
DETAIL:  Increase max_prepared_transactions to allow more prepared transactions.
HINT:  Set max_prepared_transactions to at least the number of prepared transactions.

Or from a distributed transaction coordinator (e.g., Pgpool-II, Patroni, or a Java XA driver):

PSQLException: ERROR: prepared transaction with identifier "txn_xid_00042" could not be stored

Immediate consequence: Every PREPARE TRANSACTION call beyond the configured slot limit fails immediately and rolls back. In XA/distributed transaction environments, this causes partial commits across nodes — the worst possible outcome for data consistency.

---

The Attack Vector / Blast Radius

This is a cascading consistency failure, not just a performance hiccup.

Default value is 0. PostgreSQL ships with max_prepared_transactions = 0, which completely disables two-phase commit. Any application or middleware relying on XA transactions (Java EE, JTA, Pgpool-II, Citus distributed writes) will fail silently or loudly depending on error handling.

Blast radius under load:

1. Connection pool hits the limit → PREPARE TRANSACTION errors spike.
2. Transaction coordinator marks the transaction as failed → issues ROLLBACK.
3. Distributed coordinator on the other node may have already committed → split-brain data state.
4. Orphaned prepared transactions from previous crashes accumulate in pg_prepared_xacts → table bloat, autovacuum lag, and eventually transaction ID wraparound risk if left unresolved.

Check for orphaned transactions right now:

SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared ASC;

Any row older than a few minutes in a healthy system is a zombie transaction holding locks and preventing VACUUM.

---

How to Fix It

Basic Fix

Locate your postgresql.conf (typically /etc/postgresql/15/main/postgresql.conf or /var/lib/pgsql/data/postgresql.conf):

- max_prepared_transactions = 0
+ max_prepared_transactions = 200

Rule of thumb: Set max_prepared_transactions equal to max_connections. Each connection can hold at most one prepared transaction at a time. Going higher wastes shared memory; going lower creates the exact failure you're debugging.

This requires a full PostgreSQL restart — SELECT pg_reload_conf() will NOT apply this change:

# Systemd
sudo systemctl restart postgresql

# Docker
docker exec -it postgres_container pg_ctl restart -D /var/lib/postgresql/data

Verify after restart:

SHOW max_prepared_transactions;

Enterprise Best Practice

Hardcoding a static value is a trap. Under autoscaling or connection pooler reconfiguration, max_connections drifts and max_prepared_transactions falls behind again.

1. Tie the value to max_connections in Terraform/Ansible:

- max_prepared_transactions = 100
+ max_prepared_transactions = {{ postgresql_max_connections }}

In your Terraform RDS parameter group (AWS RDS PostgreSQL):

  parameter {
    name  = "max_prepared_transactions"
-   value = "0"
+   value = "{DBInstanceClassMemory/12582880}"  # AWS formula-based dynamic value
    apply_method = "pending-reboot"
  }

2. Clean up orphaned prepared transactions immediately:

-- Run for each orphaned gid found in pg_prepared_xacts
ROLLBACK PREPARED 'your_orphaned_gid_here';

3. Alert on prepared transaction age in your monitoring stack (Prometheus + postgres_exporter):

- alert: OrphanedPreparedTransaction
  expr: pg_prepared_xact_age_seconds > 300
  for: 2m
  labels:
    severity: critical
  annotations:
    summary: "Prepared transaction older than 5 minutes on {{ $labels.instance }}"

---

💡 Tired of pasting proprietary configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

Prevention in CI/CD

The goal: Make a max_prepared_transactions = 0 config a build-breaking lint failure, not a 3am incident.

1. Checkov (IaC Scanning for Terraform RDS)

Add a custom Checkov policy to your pipeline:

# checkov/custom_checks/check_rds_prepared_tx.py
from checkov.terraform.checks.resource.base_resource_check import BaseResourceCheck
from checkov.common.models.enums import CheckResult, CheckCategories

class RDSMaxPreparedTransactions(BaseResourceCheck):
    def __init__(self):
        name = "Ensure max_prepared_transactions is not 0 for RDS PostgreSQL"
        id = "CKV_CUSTOM_RDS_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 p.get('name') == 'max_prepared_transactions':
                if str(p.get('value', '0')) == '0':
                    return CheckResult.FAILED
        return CheckResult.PASSED

2. OPA/Conftest Policy for postgresql.conf

# policy/postgres_config.rego
package postgres

deny[msg] {
  input.max_prepared_transactions == 0
  msg := "max_prepared_transactions must be > 0 when using distributed transactions or connection poolers"
}

deny[msg] {
  input.max_prepared_transactions < input.max_connections
  msg := sprintf("max_prepared_transactions (%v) must be >= max_connections (%v)",
    [input.max_prepared_transactions, input.max_connections])
}

Run in CI:

conftest test postgresql.conf.json --policy policy/

3. Pre-commit Hook for postgresql.conf Changes

#!/bin/bash
# .git/hooks/pre-commit
if grep -E '^max_prepared_transactions\s*=\s*0' postgresql.conf; then
  echo "BLOCKED: max_prepared_transactions=0 disables 2PC. Set to match max_connections."
  exit 1
fi