pg-config-tuning - Claude MCP Skill

# PostgreSQL Configuration Tuning

This skill guides systematic PostgreSQL configuration tuning using pgtuner-mcp tools, producing hardware-aware ALTER SYSTEM recommendations.

## When to Use This Skill

Use this skill when the user:

- Asks to tune or optimize `postgresql.conf` settings
- Reports running on default PostgreSQL configuration
- Asks "what settings should I change?" or "how should I configure shared_buffers?"
- Is deploying a new database and wants optimal initial configuration
- Asks about specific settings (work_mem, shared_buffers, effective_cache_size, etc.)
- Reports frequent checkpoints, high I/O, or memory pressure

## MCP Resources Available

Before calling tools, the agent can read lightweight resources for context:

- `pgtuner://settings/{category}` -- Retrieve settings by category: `memory`, `checkpoint`, `wal`, `autovacuum`, `connections`
- `pgtuner://docs/tools` -- Reference for all available tool parameters
- `pgtuner://docs/workflows` -- Recommended workflow patterns

## Related MCP Prompt

This skill corresponds to the **`health_check`** MCP Prompt (which includes settings review as Step 4). For a settings-focused session, this skill provides deeper guidance.

## Prerequisites

- The pgtuner-mcp server must be connected with a valid `DATABASE_URI`
- For best results, the agent should know the server's hardware profile (RAM, CPU cores, storage type)

## Agent Decision Logic: Gathering Context

Before recommending settings, the agent MUST understand the environment. Follow this decision tree:

```
1. Do I know the server's total RAM?
   YES -> proceed
   NO  -> ASK the user: "How much total RAM does the database server have?"

2. Do I know the storage type?
   YES -> proceed
   NO  -> ASK the user: "Is the database on SSD or spinning disk (HDD)?"

3. Do I know the workload type?
   YES -> proceed
   NO  -> ASK the user: "Is this primarily OLTP (many short transactions),
          OLAP (few complex analytical queries), or mixed?"

4. Do I know max_connections requirement?
   YES -> proceed
   NO  -> ASK the user: "How many concurrent connections do you expect?
          Are you using a connection pooler like PgBouncer?"
```

## Configuration Tuning Workflow

### Step 1: Audit Current Memory Settings

```
Tool: review_settings
Parameters:
  category: "memory"
  include_all_settings: false
```

**Key settings and tuning rules:**

| Setting | Formula | Notes |
|---------|---------|-------|
| `shared_buffers` | 25% of RAM (max ~16GB usually optimal) | Largest impact setting. More is not always better. |
| `effective_cache_size` | 50-75% of RAM | Planner hint only, does not allocate memory |
| `work_mem` | `(RAM * 0.25) / max_connections` | Per-operation, not per-connection. Can be 2-4x this for OLAP. |
| `maintenance_work_mem` | 256MB - 2GB | Used by VACUUM, CREATE INDEX. Higher = faster maintenance. |
| `huge_pages` | `try` on Linux with hugepages configured | Can improve TLB performance for large shared_buffers |

**Agent reasoning example:**
> Server has 64GB RAM, 200 max_connections, SSD, OLTP workload:
> - shared_buffers = 16GB (25% of 64GB)
> - effective_cache_size = 48GB (75% of 64GB)
> - work_mem = 80MB ((64GB * 0.25) / 200 = ~80MB)
> - maintenance_work_mem = 2GB

### Step 2: Audit Checkpoint and WAL Settings

```
Tool: review_settings
Parameters:
  category: "checkpoint"
  include_all_settings: false
```

Then separately:
```
Tool: review_settings
Parameters:
  category: "wal"
  include_all_settings: false
```

**Key settings:**

| Setting | Recommendation | Why |
|---------|---------------|-----|
| `checkpoint_completion_target` | 0.9 | Spread checkpoint writes over 90% of the interval |
| `max_wal_size` | 2GB - 8GB | Larger = fewer checkpoints = less I/O. Increase if requested checkpoints > 10% |
| `min_wal_size` | 1GB - 2GB | Keep WAL files pre-allocated |
| `wal_compression` | `on` (PG15+: `lz4` or `zstd`) | Reduces WAL volume, saves I/O |
| `wal_buffers` | 64MB (or `-1` for auto) | Auto-tuned from shared_buffers. 64MB is safe max. |
| `full_page_writes` | `on` (never turn off) | Required for crash safety |

**Validate with I/O data:**
```
Tool: analyze_disk_io_patterns
Parameters:
  analysis_type: "checkpoints"
  top_n: 10
```

IF requested checkpoints > 20% of total checkpoints, THEN `max_wal_size` is too low.

### Step 3: Audit Autovacuum Settings

```
Tool: review_settings
Parameters:
  category: "autovacuum"
  include_all_settings: false
```

**Recommendations based on workload:**

| Workload | autovacuum_max_workers | vacuum_cost_delay | vacuum_scale_factor |
|----------|----------------------|-------------------|---------------------|
| OLTP (high write) | 4-6 | 0ms (SSD) / 2ms (HDD) | 0.02 (2%) |
| OLAP (low write) | 3 | 2ms | 0.1 (10%) |
| Mixed | 4-5 | 0-2ms | 0.05 (5%) |

### Step 4: Audit Connection Settings

```
Tool: review_settings
Parameters:
  category: "connections"
  include_all_settings: false
```

**Key decisions:**

| Situation | Recommendation |
|-----------|---------------|
| `max_connections` > 200 without pooler | Reduce to actual need + add PgBouncer |
| Using PgBouncer in transaction mode | `max_connections` = 50-100 is often sufficient |
| No `statement_timeout` set | Set to 30s-60s for OLTP to prevent runaway queries |
| No `idle_in_transaction_session_timeout` | Set to 60s-300s to prevent connection/vacuum blocking |

### Step 5: Audit Planner Settings

```
Tool: review_settings
Parameters:
  category: "all"
  include_all_settings: false
```

Focus on planner-related settings:

| Setting | SSD | HDD | Impact |
|---------|-----|-----|--------|
| `random_page_cost` | 1.1 | 4.0 | Low value encourages index scans (correct for SSD) |
| `seq_page_cost` | 1.0 | 1.0 | Usually leave at default |
| `effective_io_concurrency` | 200 | 2 | Prefetch for bitmap heap scans |
| `default_statistics_target` | 100-500 | 100-500 | Higher = better cardinality estimates, slower ANALYZE |

### Step 6: Cross-Validate with Health Check

```
Tool: check_database_health
Parameters:
  include_recommendations: true
  verbose: true
```

Use health check results to validate configuration choices:
- Low cache hit ratio -> confirm `shared_buffers` increase
- High checkpoint frequency -> confirm `max_wal_size` increase
- Connection saturation -> confirm `max_connections` tuning

## Output Format

### Configuration Report

**Server Profile:**
- RAM: X GB
- Storage: SSD / HDD
- Workload: OLTP / OLAP / Mixed
- PostgreSQL Version: X.X

### Recommended Changes

```sql
-- === MEMORY (requires restart) ===
ALTER SYSTEM SET shared_buffers = '16GB';           -- Was: 128MB
ALTER SYSTEM SET huge_pages = 'try';                -- Was: off

-- === MEMORY (reload only) ===
ALTER SYSTEM SET effective_cache_size = '48GB';      -- Was: 4GB
ALTER SYSTEM SET work_mem = '80MB';                  -- Was: 4MB
ALTER SYSTEM SET maintenance_work_mem = '2GB';       -- Was: 64MB

-- === WAL / CHECKPOINTS (reload only) ===
ALTER SYSTEM SET max_wal_size = '4GB';               -- Was: 1GB
ALTER SYSTEM SET checkpoint_completion_target = '0.9'; -- Was: 0.5

-- === AUTOVACUUM (reload only) ===
ALTER SYSTEM SET autovacuum_max_workers = '5';        -- Was: 3
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '0';  -- Was: 20ms

-- === PLANNER (reload only) ===
ALTER SYSTEM SET random_page_cost = '1.1';           -- Was: 4.0 (SSD)
ALTER SYSTEM SET effective_io_concurrency = '200';    -- Was: 1 (SSD)

-- === CONNECTIONS (reload unless noted) ===
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s'; -- Was: 0
ALTER SYSTEM SET statement_timeout = '30s';                    -- Was: 0
```

### Apply Changes

```sql
-- For reload-only changes:
SELECT pg_reload_conf();

-- For restart-required changes (shared_buffers, huge_pages):
-- Schedule a maintenance window and restart PostgreSQL
```

### Restart vs Reload Reference

| Requires Restart | Reload Only |
|-----------------|-------------|
| shared_buffers | effective_cache_size |
| huge_pages | work_mem |
| max_connections | maintenance_work_mem |
| wal_buffers | max_wal_size |
| | checkpoint_completion_target |
| | random_page_cost |
| | autovacuum_* settings |
| | statement_timeout |

## When to Stop and Ask the User

- **Before recommending `shared_buffers` change**: Requires PostgreSQL restart. Ask: "Changing shared_buffers requires a database restart. When is your next maintenance window?"
- **Before recommending `max_connections` reduction**: May reject connections. Ask: "Lowering max_connections could reject connections if current usage exceeds the new limit. What is your peak connection count?"
- **If workload type is unclear**: Do not guess. Ask the user.
- **If the system is a managed database** (RDS, Cloud SQL, etc.): Some settings are not modifiable. Ask: "Are you running self-managed PostgreSQL or a managed service (RDS, Cloud SQL, Aurora)?"

## PostgreSQL Version Notes

| Setting / Feature | Version Notes |
|-------------------|---------------|
| `wal_compression = lz4/zstd` | PG15+. Prior versions only support `on` (pglz). |
| `huge_pages = try` | Linux only. Not applicable on Windows. |
| `compute_query_id` | PG14+. Required for `pg_stat_statements` queryid. |
| `recovery_min_apply_delay` | Replica only (PG12+). |

## Production Safety

- All `review_settings` calls are read-only. No changes are made by the tools.
- `ALTER SYSTEM` writes to `postgresql.auto.conf`, not `postgresql.conf`. Changes are not applied until `pg_reload_conf()` or restart.
- Always verify changes with `SHOW setting_name` after reload/restart.
- Keep a backup of the original settings before making changes.