pg-config-tuning

name	pg-config-tuning
description	Guides PostgreSQL configuration tuning based on hardware profile, workload type, and current settings. Covers memory, WAL, checkpoints, autovacuum, planner, and connection settings with specific ALTER SYSTEM recommendations. Teaches the agent to use review_settings with individual categories and understand restart vs reload requirements.

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

-- === 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

-- 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.