Is pg-config-tuning safe to install?

SaferSkills independently audited pg-config-tuning (Agent Skill) and scored it 100/100 (green). The audit ran 55 deterministic rules across Security, Supply Chain, Maintenance, Transparency, and Community; it found 0 high-severity and 0 lower-severity findings. The full rule-by-rule trace and per-finding evidence are below. Free, methodology-open.

CategoryWeightCategory scoreContribution

Securityprompt, exec, net, exfil, eval

35%

100

35.0 pts

Supply chainhash, typosquat, maintainer, lockfile

20%

100

20.0 pts

Maintenancestaleness, pinning, CI

15%

100

15.0 pts

TransparencySKILL.md, perms, README

15%

100

15.0 pts

Communityinstalls, verify, response

15%

100

15.0 pts

Findings & checks · 0 flagged

Securityscore 100 · 0 findings

✓—All security checks passedNo findings in this category for the latest scan.pass

Supply chainscore 100 · 0 findings

✓—All supply chain checks passedNo findings in this category for the latest scan.pass

Maintenancescore 100 · 0 findings

✓—All maintenance checks passedNo findings in this category for the latest scan.pass

Transparencyscore 100 · 0 findings

✓—All transparency checks passedNo findings in this category for the latest scan.pass

Communityscore 100 · 0 findings

✓—All community checks passedNo findings in this category for the latest scan.pass

The primary manifest — the file an agent reads to learn what this artifact does.

skills/pg-config-tuning/SKILL.md · 1 file

skills/pg-config-tuning/SKILL.md9.8 KB · Markdown

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

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.

pg-config-tuning— agent skill

Is pg-config-tuning safe to install?

PostgreSQL Configuration Tuning

When to Use This Skill

MCP Resources Available

Prerequisites

Agent Decision Logic: Gathering Context

Configuration Tuning Workflow

Step 1: Audit Current Memory Settings

Step 2: Audit Checkpoint and WAL Settings

Step 3: Audit Autovacuum Settings

Step 4: Audit Connection Settings

Step 5: Audit Planner Settings

Step 6: Cross-Validate with Health Check

Output Format

Configuration Report

Recommended Changes

Apply Changes

Restart vs Reload Reference

When to Stop and Ask the User

PostgreSQL Version Notes

Production Safety

Audit the pieces. Scan the whole. Decide.

Is pg-config-tuning safe to install?

PostgreSQL Configuration Tuning

When to Use This Skill

MCP Resources Available

Related MCP Prompt

Prerequisites

Agent Decision Logic: Gathering Context

Configuration Tuning Workflow

Step 1: Audit Current Memory Settings

Step 2: Audit Checkpoint and WAL Settings

Step 3: Audit Autovacuum Settings

Step 4: Audit Connection Settings

Step 5: Audit Planner Settings

Step 6: Cross-Validate with Health Check

Output Format

Configuration Report

Recommended Changes

Apply Changes

Restart vs Reload Reference

When to Stop and Ask the User

PostgreSQL Version Notes

Production Safety

Audit the pieces. Scan the whole. Decide.