Introduction Prepared statement cache bloat occurs when applications or connection poolers create prepared statements faster than they are recycled, eventually exhausting PostgreSQL's shared memory allocated for prepared statements. This causes `ERROR: out of shared memory` and `ERROR: too many prepared statements` errors.

Symptoms - `ERROR: out of shared memory` when executing queries - `ERROR: too many prepared statements (max 1000)` in application logs - Gradual increase in shared memory usage correlating with application uptime - `pg_prepared_statements` view shows thousands of entries - Connection poolers reporting `PREPARE` failures on pooled connections

Common Causes - PgBouncer in `transaction` pooling mode with `pool_mode = transaction` not cleaning up prepared statements - Application frameworks (Hibernate, Django ORM) generating unique prepared statements per query variation - Missing `DEALLOCATE` calls for dynamically created prepared statements - High cardinality of query text variations causing each unique query to consume a slot - Default `max_prepared_transactions` too low for the workload

Step-by-Step Fix 1. **Check current prepared statement count**: ```sql SELECT COUNT(*) FROM pg_prepared_statements; SELECT name, statement, prepare_time FROM pg_prepared_statements LIMIT 20; ```

  1. 1.Check the max_prepared_transactions setting:
  2. 2.```sql
  3. 3.SHOW max_prepared_transactions;
  4. 4.`
  5. 5.Increase max_prepared_transactions if needed:
  6. 6.```sql
  7. 7.ALTER SYSTEM SET max_prepared_transactions = 200;
  8. 8.-- Requires restart
  9. 9.`
  10. 10.Configure PgBouncer to handle prepared statements correctly:
  11. 11.```ini
  12. 12.; pgbouncer.ini - Use session pooling for apps using prepared statements
  13. 13.[databases]
  14. 14.production = host=db port=5432 dbname=production

[pgbouncer] pool_mode = session max_prepared_statements = 100 server_reset_query = DEALLOCATE ALL; ```

  1. 1.Clean up orphaned prepared statements:
  2. 2.```sql
  3. 3.-- Deallocate all prepared statements for the current session
  4. 4.DEALLOCATE ALL;

-- For two-phase commit prepared transactions, check and clean SELECT gid, prepared, owner, database FROM pg_prepared_xacts; COMMIT PREPARED 'gid_value'; -- or ROLLBACK PREPARED 'gid_value'; ```

  1. 1.Configure connection pool to limit prepared statements in application code:
  2. 2.```python
  3. 3.# SQLAlchemy - disable server-side prepared statements
  4. 4.engine = create_engine(
  5. 5."postgresql+psycopg2://user:pass@host/db",
  6. 6.execution_options={"isolation_level": "AUTOCOMMIT"},
  7. 7.pool_pre_ping=True
  8. 8.)
  9. 9.# Disable implicit prepared statements
  10. 10.from sqlalchemy.dialects import postgresql
  11. 11.engine.dialect.server_side_cursors = False
  12. 12.`

Prevention - Use `pool_mode = transaction` in PgBouncer only with `server_reset_query = DEALLOCATE ALL` - Limit prepared statement usage in ORM configurations - Monitor `pg_prepared_statements` count with alerting at 80% of max - Use connection poolers that support prepared statement tracking (PgBouncer 1.18+) - Set `max_prepared_transactions = 0` if two-phase commit is not needed - Regularly restart connection pools during deployment cycles to flush caches