PostgreSQL Prepared Transactions Limit Adjuster

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.

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