postgres-infrastructure

name	postgres-infrastructure
description	PostgreSQL infrastructure and instance configuration: memory, shared_buffers, work_mem, WAL, checkpoints, storage, Linux, containers, Kubernetes, connection pooling, pgbouncer, networking, extensions, configuration files, and server sizing.

PostgreSQL Infrastructure

Treat configuration as workload-dependent. Explain parameter interactions and validate with observed metrics.

How To Approach A Configuration Request

1. Get the ground truth first: RAM, CPU, storage type, connection count, and workload shape (OLTP vs analytics vs mixed). Config advice without these is guessing.
2. Read current settings with source and mutability (scripts/01, scripts/07): what was set, where, and whether changing it needs a restart (context in pg_settings).
3. Change one dimension at a time and validate against the metric that motivated it (checkpoint stats, temp files, connection states, wait events).
4. State parameter interactions explicitly — memory settings compound per connection and per parallel worker.
5. On managed platforms, check the parameter is mutable at all before recommending it.

Memory Model

Total pressure ≈ shared_buffers + OS page cache + Σ per-backend memory. Per-backend memory is the dangerous part: each query node that sorts or hashes can use up to work_mem (× hash_mem_multiplier for hashes, × parallel workers).

Parameter	Starting point	Interaction to state
shared_buffers	25% of RAM (less in small containers; diminishing returns past ~16-32GB for many workloads)	Competes with OS cache, which PostgreSQL relies on heavily
work_mem	4-64MB depending on RAM/connections	Per sort/hash node, per worker — not per query, not per connection; raise per-session for known big queries instead of globally
maintenance_work_mem	256MB-1GB	Vacuum, index builds; autovacuum_work_mem overrides for autovacuum, multiplied by workers
effective_cache_size	50-75% of RAM	Planner hint only; allocates nothing
huge_pages	try on Linux with large shared_buffers	Needs vm.nr_hugepages provisioned

Temp-file counters in pg_stat_database and log_temp_files are the evidence that work_mem is actually too small — tune from those, not from formulas alone.

WAL And Checkpoints

Checkpoint tuning goal: checkpoints driven by checkpoint_timeout (time), not by max_wal_size (volume). Requested (forced) checkpoints appearing in pg_stat_bgwriter/pg_stat_checkpointer mean max_wal_size is too small for the write rate.

• max_wal_size: raise until timed checkpoints dominate; cost is longer crash recovery and more disk.
• checkpoint_timeout 15-30min with checkpoint_completion_target 0.9 spreads write bursts.
• wal_compression = on cuts full-page-image volume cheaply.
• Commit latency = WAL fsync latency; measure with pg_test_fsync. synchronous_commit = off trades a small loss window for latency where acceptable (never for financial writes).

Connections And Pooling

Backends are processes. Thousands of connections cost memory and scheduler churn even when idle. Rule of thumb for active backends: a small multiple of CPU cores.

• App-side pools multiply: 50 services × 20 pool size = 1000 connections. Sum them before sizing max_connections.
• PgBouncer transaction pooling collapses idle app connections onto few backends. Transaction mode breaks session state: no session-level SET, advisory locks, LISTEN/NOTIFY, or named prepared statements (PgBouncer 1.21+ supports protocol-level prepared statements).
• Raising max_connections to fix connection exhaustion usually relocates the outage; pooling fixes it.

Common Pitfalls

1. Setting work_mem high globally "because RAM is free" — it multiplies by node, worker, and concurrent query.
2. Tuning shared_buffers in a container without container-aware memory limits, then getting OOM-killed (a SIGKILL to one backend restarts the whole instance).
3. Ignoring pg_settings.context and wondering why a change didn't apply (postmaster context = restart; also check pending_restart).
4. Editing postgresql.conf when ALTER SYSTEM (postgresql.auto.conf) silently overrides it — check sourcefile in pg_settings.
5. Running Kubernetes probes that query the database aggressively, so a slow instance gets restarted into a worse state.
6. Leaving the OOM killer able to target PostgreSQL on dedicated hosts (vm.overcommit_memory = 2, adjust oom_score_adj for the postmaster).
7. Benchmarking storage with defaults and attributing the result to PostgreSQL configuration.

References

• references/configuration-storage.md - memory sizing, WAL/checkpoint tuning, pooling modes, storage, Linux, and container guidance.
• ../postgres/references/architecture.md - process, memory, WAL, storage, checkpoint, and XID internals.
• ../postgres/references/best-practices.md - configuration tuning and operational defaults.

Scripts

• scripts/01-instance-settings.sql - key configuration values.
• scripts/02-wal-checkpoint-stats.sql - WAL and checkpoint health.
• scripts/03-connection-profile.sql - connection usage by state and database.
• scripts/04-memory-settings.sql - memory-related parameters and per-query risk surface.
• scripts/05-worker-parallelism.sql - worker, parallel, autovacuum, and background process settings.
• scripts/06-storage-tablespaces.sql - tablespace locations and relation storage distribution.
• scripts/07-configuration-files.sql - parsed configuration, HBA, and file rule status.

Cross-Skill Routing

• Evidence that motivates tuning (waits, temp files, checkpoint stats) comes from postgres-monitoring.
• Autovacuum throughput and maintenance windows go to postgres-operations.
• WAL settings serving replication (senders, slots, wal_level) go to postgres-ha-replication.
• Parameter mutability on RDS/Cloud SQL/Azure goes to postgres-cloud.
• pg_hba.conf, TLS, and listener exposure go to postgres-security.