The Complete Production SOP: How to Deploy and Run PostgreSQL Query Optimization via pg_stat_statements in Autonomous AI Agents
Production SOP to install and run PostgreSQL Query Optimization via pg_stat_statements in Codex, Claude Code, and Cursor. Fix slow queries deterministically.
Technical Overview & Architectural Context
Without explicit procedural boundaries, autonomous coding agents tasked with database performance tuning routinely hallucinate speculative index recommendations, generate redundant multi-column B-trees, or attempt naive whole-table queries that trigger production lock contention. In many instances, agents run synthetic `EXPLAIN` tests on local mock fixtures that do not reflect production table statistics or actual buffer pool dynamics, failing to capture cold-disk reads versus shared buffer cache hits. The `postgres-pg-stat-statements-profiling` skill enforces a deterministic Standard Operating Procedure (SOP) across Claude Code, OpenAI Codex, Cursor, and Windsurf. By binding agent execution to real telemetry emitted by `pg_stat_statements`—specifically cumulative `total_exec_time`, mean call latency, normalized query hashes, and disk I/O metrics (`shared_blks_read` vs `shared_blks_hit`)—the agent is prevented from guessing index definitions. It systematically profiles root bottlenecks, inspects query plans with `EXPLAIN (ANALYZE, BUFFERS)`, and ensures index optimizations yield measured latency improvements while guarding production resources.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Environment Setup & SKILL.md Mounting
Create the local skill manifest directory and inject verified procedural rules into the agent runtime path.
Autonomous runtimes discover capabilities through standardized directory locations such as `~/.agents/skills/` or `.cursor/skills/`. Placing the canonical `SKILL.md` inside your workspace ensures Claude Code and Cursor parse the frontmatter triggers (`find slow queries postgres`, `query pg_stat_statements`, `calculate buffer cache hit ratio`) and adopt the bounded SOP whenever profiling tasks are submitted.
mkdir -p .cursor/skills/postgres-pg-stat-statements-profiling && curl -fsSL https://askills.dev/api/skills/postgres-pg-stat-statements-profiling/raw -o .cursor/skills/postgres-pg-stat-statements-profiling/SKILL.md---
name: "postgres-pg-stat-statements-profiling"
description: "Identify slow queries, buffer cache misses, and lock contention using pg_stat_statements."
metadata:
author: "PostgreSQL"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "find slow queries postgres"
- "query pg_stat_statements"
- "calculate buffer cache hit ratio"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---
# PostgreSQL Query Optimization via pg_stat_statements
Profiles PostgreSQL workloads: tracking total_exec_time, mean_exec_time, shared_blks_read vs shared_blks_hit ratios, query normalization hashes, and diagnosing missing indexes.
## SOP Rules
1. Ensure pg_stat_statements extension is enabled.
2. Isolate queries by total_exec_time DESC and calls DESC.
3. Validate cache hit ratio: shared_blks_hit / (shared_blks_hit + shared_blks_read) >= 0.99.
4. Run EXPLAIN (ANALYZE, BUFFERS) before drafting SQL index migrations.Setting `shell_exec: false` forces the agent to formulate read-only SQL scripts and structural index migration suggestions for user approval rather than running unprompted execution against production connections.
Ensure the directory permissions match the workspace owner (`chmod 755 .cursor/skills`); restrictive permissions can prevent Claude Code and Windsurf background processes from discovering the skill during intent indexing.
Database Engine Configuration & Extension Initialization
Configure postgresql.conf with required preload libraries and instantiate the telemetry extension.
Because `pg_stat_statements` requires tracking memory allocations across server worker processes, it must be declared inside `shared_preload_libraries`. The agent relies on this extension being initialized in the target database schema to fetch normalized execution hashes and buffer statistics.
psql -U postgres -d production_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"# PostgreSQL configuration parameters for pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.save = onConfigure `pg_stat_statements.track = top` to exclude internal function calls, saving both system memory and agent parsing context by removing recursive internal queries.
Modifying `shared_preload_libraries` requires a full PostgreSQL daemon restart; running `SELECT pg_reload_conf();` is insufficient and will yield an extension initialization error.
Intent Matching & Deterministic Telemetry Querying
Trigger the agent to retrieve normalized slow queries and compute buffer cache hit ratios.
Invoke the agent using exact trigger keywords. The skill guides the agent to query `pg_stat_statements` using a deterministic aggregate query that calculates execution statistics, average latency per call, and the shared buffer cache hit ratio, rather than executing arbitrary unstructured queries.
SELECT
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_exec_ms,
round(mean_exec_time::numeric, 2) AS mean_exec_ms,
round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS hit_ratio,
substr(query, 1, 100) AS normalized_query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY total_exec_time DESC
LIMIT 10;Always filter out queries with very low call counts (`calls > 50`) so the agent focuses on queries causing sustained cumulative latency rather than transient cold-start anomalies.
Do not retrieve full query strings directly into context for high-complexity queries; instruct the agent to use `substr(query, 1, 120)` or `queryid` to prevent token limits from exhausting prompt capacity.
Verification, Index Optimization & Quality Gate
Validate query execution plans with EXPLAIN (ANALYZE, BUFFERS) and generate non-blocking index migrations.
Once the target query hash is identified, the agent must formulate a non-blocking migration (`CREATE INDEX CONCURRENTLY`) and evaluate the plan before and after to verify that heap scans transition to index scans without degrading buffer hit metrics.
psql -U postgres -d production_db -f migrations/20260401_add_orders_status_created_at_idx.sql-- Step 1: Verification plan before index creation
-- EXPLAIN (ANALYZE, BUFFERS) SELECT id, total_amount FROM orders WHERE status = 'pending' AND created_at >= NOW() - INTERVAL '7 days';
-- Step 2: Non-blocking index creation to prevent table locks
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_created_at
ON orders (status, created_at)
INCLUDE (total_amount);
-- Step 3: Verification statement
COMMENT ON INDEX idx_orders_status_created_at IS 'Optimizes pending order dashboard scan per pg_stat_statements hash 402839210';Always mandate `CREATE INDEX CONCURRENTLY` in production environments; standard `CREATE INDEX` acquires an `ACCESS EXCLUSIVE` lock that halts concurrent writes.
`CREATE INDEX CONCURRENTLY` cannot execute inside a standard multi-statement transaction block (`BEGIN ... COMMIT`). Ensure your migration runner executes this outside a transaction.
Real-World Scenario: Diagnose Production API Bottleneck & Mitigate Cache Thrashing
“We are observing high database CPU utilization and elevated p99 latency during checkout. Use the postgres-pg-stat-statements-profiling skill to find slow queries postgres, calculate buffer cache hit ratio, and propose an index optimization.”
Runtime Troubleshooting & Failure Modes
Root Cause: The PostgreSQL server does not have pg_stat_statements loaded into shared memory at startup. It was either omitted from postgresql.conf or the server was reloaded with pg_reload_conf() instead of a full service restart.
echo "shared_preload_libraries = 'pg_stat_statements'" | sudo tee -a /etc/postgresql/16/main/postgresql.conf && sudo systemctl restart postgresqlRoot Cause: The autonomous agent wrapped the proposed index migration inside an ORM or migration harness default `BEGIN; ... COMMIT;` transaction wrapper.
-- In Prisma, Flyway, or node-pg-migrate, disable transactions for concurrent index creation:
-- @transaction: false
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status_created_at ON orders (status, created_at);Frequently Asked Questions (PostgreSQL Query Optimization via pg_stat_statements SOP)
Q:Can I use PostgreSQL Query Optimization via pg_stat_statements across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill conforms to the universal SKILL.md specification supported across all modern agent runtimes. Simply symlink or copy the skill directory into `.cursor/skills/` or `~/.agents/skills/` to enable deterministic triggering across all compatible coding assistants.
Q:How does PostgreSQL Query Optimization via pg_stat_statements prevent context window overflow during long agent sessions?
The SOP directs the agent to query only top aggregated metrics filtered by `calls` and execution time limits, and to truncate raw SQL string literals using `substr(query, 1, 100)`. This stops the agent from dumping thousands of raw query lines into the prompt context.
Q:Does this skill require escalated root or container permissions?
No. The skill has a security score of 98/100 and specifies `shell_exec: false` and `net_access: false`. It instructs the agent to generate read-only diagnostic SQL scripts and validated migration DDL for execution through your existing database client pipelines.
Ready to equip your autonomous agent with PostgreSQL Query Optimization via pg_stat_statements?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.