PostgreSQL Performance Tuning

Target version: postgres:16-alpine — this repository's gc-platform-postgres container: mem_limit: 1g, mem_reservation: 512m, no command: override (stock settings: shared_buffers=128MB, max_connections=100), no shared_preload_libraries.

Contract: diagnostics are read-only (pg_settings, pg_stat_*, EXPLAIN on SELECTs only). Every config change below is emitted ONLY as a ⚠️ HUMAN CONFIRMATION REQUIRED block — the postgres-dba agent never applies settings. Values marked ⚠️ are established heuristics, not guarantees: measure before and after every change, one parameter at a time.

Connection:

docker exec gc-platform-postgres psql -U gcplatform -d gcplatform -c "<query>"

---

1. Memory sizing vs the 1g mem_limit

Current settings (verify live):

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('shared_buffers','effective_cache_size','work_mem','maintenance_work_mem',
               'max_connections','huge_pages');

⚠️ Heuristic sizing for a dedicated 1g container:

Parameter	Stock	Suggested	Rationale
shared_buffers	128MB	≈ 256MB	Classic ⚠️ 25%-of-RAM heuristic; the container also needs room for per-backend memory, WAL buffers, and the OS page cache
effective_cache_size	4GB (!)	≈ 512–768MB	Planner hint for "how much caching exists total" (shared_buffers + OS cache). Stock 4GB is a lie on a 1g container and biases the planner toward index scans it shouldn't trust
work_mem	4MB	keep 4–8MB	Allocated PER sort/hash node PER connection. Worst case ≈ max_connections × nodes-per-query × work_mem: 100 × 4 nodes × 8MB = 3.2GB ≫ 1g. Raising it globally is how containers OOM; raise per-session for known big sorts instead
maintenance_work_mem	64MB	128–256MB	Used by VACUUM/CREATE INDEX; few concurrent users, safe to raise

⚠️ HUMAN CONFIRMATION REQUIRED

# docker-compose.yml, postgres service (restart required):
command: ["postgres",
  "-c", "shared_buffers=256MB",
  "-c", "effective_cache_size=768MB",
  "-c", "maintenance_work_mem=128MB"]

Verify after restart: SELECT name, setting FROM pg_settings WHERE name = 'shared_buffers'; and watch docker stats under load.

---

2. Checkpoint tuning

Source: https://www.postgresql.org/docs/16/wal-configuration.html

Diagnose first (see postgres-health-check §7):

SELECT checkpoints_timed, checkpoints_req,
       ROUND(checkpoints_req::numeric / NULLIF(checkpoints_timed + checkpoints_req, 0) * 100, 2) AS req_pct
FROM pg_stat_checkpointer;
SHOW max_wal_size; SHOW checkpoint_completion_target; SHOW checkpoint_warning;

⚠️ Heuristics:

• max_wal_size (default 1GB): raise to 2–4GB if req_pct > 10–20% — more WAL between checkpoints = fewer forced checkpoints = smoother I/O (cost: longer crash recovery, more pg_wal disk).
• checkpoint_completion_target = 0.9 — already the default since PG14; spreads checkpoint writes across 90% of the interval. Verify it, don't blindly set it.
• checkpoint_warning = 30s (default): logs when checkpoints are too close together; keep it as the canary.

⚠️ HUMAN CONFIRMATION REQUIRED

command: ["postgres", "-c", "max_wal_size=2GB", "-c", "checkpoint_completion_target=0.9"]

---

3. Enabling pg_stat_statements

The stock compose loads no extensions (shared_preload_libraries empty), so pg_stat_statements — the single highest-value slow-query tool — is unavailable until enabled.

Source: https://www.postgresql.org/docs/16/pgstatstatements.html ⚠️ (must be loaded via shared_preload_libraries → requires server restart, not a reload; the extension object then still needs CREATE EXTENSION per database).

⚠️ HUMAN CONFIRMATION REQUIRED

# docker-compose.yml, postgres service (restart required):
command: ["postgres", "-c", "shared_preload_libraries=pg_stat_statements",
          "-c", "pg_stat_statements.max=10000",
          "-c", "pg_stat_statements.track=all"]

Then the extension itself is a migration (repo change → dispatch coder):

⚠️ HUMAN CONFIRMATION REQUIRED

-- Applied as an EF Core migration under the superuser/migrator connection, never ad hoc:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Combine with §1/§2 if changing both: a single command: array holds all -c flags.

---

4. Slow-query triage workflow

Requires §3 enabled. Order of operations:

-- 1. Worst by TOTAL time (the queries costing the most aggregate):
SELECT LEFT(query, 100) AS query, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       rows,
       round((shared_blks_hit * 100.0 / NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 15;

-- 2. Worst by MEAN time (individual slow queries):
SELECT LEFT(query, 100), calls, round(mean_exec_time::numeric, 1) AS mean_ms, rows
FROM pg_stat_statements
WHERE calls > 20
ORDER BY mean_exec_time DESC LIMIT 15;

Interpretation: high calls × mean = hot path (index or N+1 — the latter is code → database-reviewer); low hit_pct = cache/index problem; huge rows vs returned rows = missing predicate index. pg_stat_statements normalizes parameters ($1) — it shows query shapes, not literals.

Remediation: index/query fixes are code/migration changes → database-reviewer + coder. Instance-side only: pg_stat_statements_reset() to re-measure after a change:

⚠️ HUMAN CONFIRMATION REQUIRED

SELECT pg_stat_statements_reset();

---

5. EXPLAIN (ANALYZE, BUFFERS) — SELECTs only

EXPLAIN (ANALYZE, BUFFERS)
SELECT …;   -- ⚠️ SELECTs only: ANALYZE EXECUTES the statement, so never run it on DML

Read the plan in this order:

1. Actual vs planned rows (rows=X … actual … rows=Y): a 100×+ mismatch = stale statistics → ANALYZE <table> (⚠️ human) or the planner lacks a predicate.
2. Node time hotspots: the top-cost node isn't always the culprit; look for nodes where actual time jumps vs their children.
3. Buffers: shared hit = cache, shared read = disk. High reads on a hot query = index opportunity. BUFFERS output is only meaningful with ANALYZE.
4. Seq Scan on large tables: fine for analytics, a bug for point lookups. Never add an index yourself — recommend it via database-reviewer.
5. Nested Loop with high loops=: classic N+1 shape at the plan level.

---

6. pgbouncer / connection pooling

Why: max_connections=100 and each backend costs ≈ 5–10MB; pooling lets hundreds of app connections share a few dozen backends.

Source: https://www.pgbouncer.org/features.html — transaction pooling (the mode you want) supports only what fits inside a single transaction. It BREAKS:

Feature	Why it breaks under transaction pooling
SET (session-level)	Session state is not pinned to a backend; the next query may run on another backend
LISTEN / NOTIFY	Notifications are session-bound; pooled clients never (reliably) receive them
PREPARE (client-side prepared statements)	The prepared plan lives on one backend; pgbouncer ≥ 1.21 can proxy them only if max_prepared_statements > 0
Session-level advisory locks (pg_advisory_lock)	Lock held by a backend that the client is detached from after the transaction (transaction-level pg_advisory_xact_lock IS safe)
Cursors WITH HOLD	Survive transaction end → meaningless when the backend changes

Repo-specific verdict

• ✓ SET LOCAL app.tenant_id = … is transaction-pooling-safe. It scopes to the current transaction, which is exactly what transaction pooling preserves — the RLS tenant GUC pattern (ADR-0038, canonical GUC app.tenant_id) works behind pgbouncer. (Session-level SET app.tenant_id without LOCAL would NOT be safe.)
• ✓ WolverineFx (ADR-0015) is transaction-pooling-safe. Its PostgreSQL transport does not use LISTEN/NOTIFY — it polls queue tables with ORDER BY … LIMIT n FOR UPDATE SKIP LOCKED (verified: https://wolverinefx.net/guide/durability/postgresql — "PostgreSQL Messaging Transport", Polling, "Dequeue Performance"). The repo's current stack (WolverineFx.Marten outbox, DurabilityAgent store-and-forward, bus not yet booted per ADR-0020) is plain SQL + polling — exactly what transaction pooling preserves.
• ⚠️ Residual: re-verify this verdict if any future component explicitly uses LISTEN/NOTIFY (custom NOTIFY triggers, cache-invalidation libraries, trigger-based NOTIFY) — those connections must bypass pgbouncer (direct connection string).
• ⚠️ Npgsql prepared statements: Npgsql 8+ prepares statements by default. Behind pgbouncer this requires pgbouncer ≥ 1.21 with max_prepared_statements > 0, or set Max Auto Prepare=0 / No Reset On Close=true per Npgsql's pgbouncer guidance. Verify against the Npgsql version in backend/ before rollout.

⚠️ HUMAN CONFIRMATION REQUIRED — pooling is an infra rollout (new compose service), emitted for humans only:

# Sketch — not applied by this agent:
pgbouncer:
  image: edoburu/pgbouncer:latest   # ⚠️ pick a pinned, actively-maintained image
  environment:
    DB_HOST: postgres
    POOL_MODE: transaction
    MAX_PREPARED_STATEMENTS: "100"   # pgbouncer ≥ 1.21, for Npgsql prepared statements
  depends_on:
    postgres:
      condition: service_healthy

Rollout order: verify Wolverine transport → verify Npgsql version → pool the backend's DefaultConnection (gc_kourou_app_login) only → keep MigrationConnection and any LISTEN/NOTIFY consumer direct.