postgres-performance-tuning

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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 — if `postgres-dba` is installed, that agent never applies settings; otherwise `database-reviewer` or the main agent does not either. Values marked ⚠️ are **established heuristics**, not guarantees: measure before and after every change, one parameter at a time.

**Connection:**

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

---

## 1. Memory sizing vs the 1g mem_limit

Current settings (verify live):

```sql
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

```yaml
# 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):

```sql
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

```yaml
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

```yaml
# 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** (a repo change). If `coder` is installed, dispatch to that agent. Otherwise the main agent loads the `coder` skill and writes the migration inline.

⚠️ HUMAN CONFIRMATION REQUIRED

```sql
-- 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:

```sql
-- 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

From fmflurry/settings-opencode, MIT.

More from fmflurry/settings-opencode