postgres-performance-cli

name

postgres-performance-cli

description

Optimize query performance, tune server parameters, and analyze workloads on Azure Database for PostgreSQL Flexible Server via CLI. Use when the user wants to: (1) optimize a slow query with EXPLAIN ANALYZE, (2) identify missing or unused indexes, (3) tune memory and IO parameters, (4) analyze cache hit ratios and buffer usage, (5) profile workloads with pg_stat_statements, (6) tune VACUUM and ANALYZE settings, (7) optimize connection pooling with PgBouncer, (8) analyze wait events, (9) right-size compute tier and storage. Triggers: "optimize query postgres", "slow query", "EXPLAIN ANALYZE", "missing index", "unused index", "tune postgres", "postgres performance", "cache hit ratio", "pg_stat_statements", "work_mem", "shared_buffers", "vacuum tuning", "connection pooling", "PgBouncer tuning", "query plan", "seq scan", "index scan", "postgres benchmark", "right size postgres".

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

PostgreSQL Performance Tuning — CLI Skill

Table of Contents

Task	Reference	Notes
Azure PostgreSQL Topology	COMMON-CORE.md § Azure PostgreSQL Topology
Authentication	COMMON-CLI.md § Authentication Recipes	Entra ID or native auth
SQL via psql	COMMON-CLI.md § SQL via psql	Connect, query
Indexing Strategies	POSTGRES-AUTHORING-CORE.md § Indexing Strategies	B-tree, GIN, GiST, BRIN
Performance Analysis	POSTGRES-CONSUMPTION-CORE.md § Performance Analysis	EXPLAIN, pg_stat_statements
Monitoring	POSTGRES-CONSUMPTION-CORE.md § Monitoring	Connections, locks, sizes

---

Tool Stack

Tool	Role	Install
psql	Primary: Execute diagnostic and tuning SQL.	apt-get install postgresql-client / brew install libpq / winget install PostgreSQL.psql
az CLI	Server parameter tuning, metrics, compute/storage management.	Pre-installed in most dev environments
jq	Parse JSON from az commands	Pre-installed or trivial

Agent check — verify before first operation:

psql --version 2>/dev/null || echo "INSTALL: apt-get install postgresql-client OR brew install libpq"

---

Agentic Performance Workflow

Start here — systematic approach to performance analysis.

Step 1: Establish Baseline

-- Database-wide statistics
SELECT
    datname,
    numbackends AS connections,
    xact_commit + xact_rollback AS total_txn,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched,
    tup_inserted, tup_updated, tup_deleted,
    deadlocks,
    pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_stat_database
WHERE datname = current_database();

Step 2: Identify Top Resource Consumers

-- Top queries by total execution time (requires pg_stat_statements)
SELECT
    left(query, 120) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round((100.0 * total_exec_time / nullif(sum(total_exec_time) OVER(), 0))::numeric, 2) AS pct_total,
    rows
FROM pg_stat_statements
WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user)
ORDER BY total_exec_time DESC
LIMIT 15;

Step 3: Deep-Dive the Worst Offenders

Use EXPLAIN ANALYZE on the top queries identified above.

Step 4: Apply Optimizations

Index creation, query rewrites, parameter tuning.

---

Query Optimization with EXPLAIN

Reading EXPLAIN Output

-- Basic EXPLAIN (no execution)
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE customer_id = 42;

-- Full analysis (executes the query)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

-- EXPLAIN without executing DML
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'shipped' WHERE order_id = 1;
ROLLBACK;

Key Metrics in EXPLAIN Output

Metric	What It Means	Action
Seq Scan on large table	Full table scan, no index used	Add index on filter columns
Nested Loop with high rows	O(n*m) join, potentially slow	Check join conditions, add indexes
Sort with high cost	In-memory or disk sort	Add index matching ORDER BY
Hash Join with large hash	Large hash table built	Ensure adequate work_mem
Buffers: shared read high	Data read from disk, not cache	Check shared_buffers, table bloat
Planning Time high	Complex query plan compilation	Simplify query, check statistics

Identifying Seq Scans That Need Indexes

-- Tables with high sequential scan counts
SELECT
    schemaname, relname,
    seq_scan, seq_tup_read,
    idx_scan, idx_tup_fetch,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS size,
    round(100.0 * seq_scan / nullif(seq_scan + idx_scan, 0), 2) AS seq_scan_pct
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND pg_relation_size(schemaname || '.' || relname) > 10 * 1024 * 1024  -- > 10 MB
ORDER BY seq_tup_read DESC
LIMIT 20;

---

Index Analysis

Find Missing Indexes

-- Tables with most sequential scans and no/few index scans
SELECT
    schemaname, relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC
LIMIT 15;

Find Unused Indexes

-- Indexes never used since last stats reset
SELECT
    schemaname, tablename, indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS times_used,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 1024 * 1024  -- > 1 MB
ORDER BY pg_relation_size(indexrelid) DESC;

Find Duplicate Indexes

-- Indexes with overlapping column definitions
SELECT
    a.indrelid::regclass AS table_name,
    a.indexrelid::regclass AS index_a,
    b.indexrelid::regclass AS index_b,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS size_a,
    pg_size_pretty(pg_relation_size(b.indexrelid)) AS size_b
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid < b.indexrelid
    AND a.indkey::text = b.indkey::text
WHERE a.indrelid::regclass::text NOT LIKE 'pg_%';

Index Type Selection Guide

Access Pattern	Recommended Index	Example
Equality (=)	B-tree (default)	CREATE INDEX ON orders(status)
Range (<, >, BETWEEN)	B-tree	CREATE INDEX ON orders(created_at)
Pattern (LIKE 'abc%')	B-tree with text_pattern_ops	CREATE INDEX ON users(email text_pattern_ops)
Full-text search	GIN with tsvector	CREATE INDEX ON docs USING gin(to_tsvector('english', content))
JSONB containment (@>)	GIN	CREATE INDEX ON events USING gin(metadata)
Array operations	GIN	CREATE INDEX ON items USING gin(tags)
Geospatial	GiST	CREATE INDEX ON places USING gist(location)
Large sequential data	BRIN	CREATE INDEX ON logs USING brin(created_at)
High-cardinality partial	Partial B-tree	CREATE INDEX ON orders(status) WHERE status = 'pending'

Create Index Safely on Production

-- Always use CONCURRENTLY on production tables
CREATE INDEX CONCURRENTLY idx_orders_customer_date
ON orders(customer_id, order_date DESC);

-- Verify the index was created successfully
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_orders_customer_date';

Test Indexes Without Building Them (hypopg)

Use the hypopg extension to test hypothetical indexes without disk or write overhead.

CREATE EXTENSION IF NOT EXISTS hypopg;

-- Create a hypothetical index
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders(customer_id, order_date)');

-- Run EXPLAIN — the planner considers hypothetical indexes
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';

-- Check estimated index size
SELECT indexname, pg_size_pretty(hypopg_relation_size(indexrelid))
FROM hypopg_list_indexes();

-- Clean up
SELECT hypopg_reset();

See postgres-extensions-cli § hypopg for full setup.

---

pg_stat_statements Deep Analysis

Setup

# Enable pg_stat_statements extension
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "pg_stat_statements"

# Restart required after changing shared_preload_libraries
az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

-- Create extension in database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Top Queries by Different Dimensions

-- By total time (overall impact)
SELECT left(query, 100), calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- By mean time (per-call latency)
SELECT left(query, 100), calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC LIMIT 10;

-- By calls (most frequent)
SELECT left(query, 100), calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements ORDER BY calls DESC LIMIT 10;

-- By rows returned (data volume)
SELECT left(query, 100), calls, rows,
       round((rows::numeric / nullif(calls, 0)), 2) AS rows_per_call
FROM pg_stat_statements ORDER BY rows DESC LIMIT 10;

-- By temporary file usage (need more work_mem)
SELECT left(query, 100), calls,
       temp_blks_read + temp_blks_written AS temp_blks,
       round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
WHERE temp_blks_read + temp_blks_written > 0
ORDER BY temp_blks_read + temp_blks_written DESC LIMIT 10;

Reset Statistics

-- Reset pg_stat_statements (do this after major changes to re-baseline)
SELECT pg_stat_statements_reset();

---

Memory Parameter Tuning

Current Settings vs Recommendations

-- Memory-related parameters
SELECT name, setting, unit,
       pg_size_pretty(
           CASE WHEN unit = '8kB' THEN setting::bigint * 8192
                WHEN unit = 'kB' THEN setting::bigint * 1024
                WHEN unit = 'MB' THEN setting::bigint * 1024 * 1024
                ELSE setting::bigint
           END
       ) AS human_readable,
       source
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'effective_cache_size', 'work_mem',
    'maintenance_work_mem', 'temp_buffers', 'hash_mem_multiplier',
    'huge_pages', 'wal_buffers'
)
ORDER BY name;

Tuning Guidelines

Parameter	Rule of Thumb	Azure Flexible Server Notes
shared_buffers	25% of RAM	Managed by Azure; adjustable per SKU
effective_cache_size	75% of RAM	Planner hint only, no memory allocated
work_mem	2-8 MB per connection	Conservative; total = work_mem × max_connections × sorts
maintenance_work_mem	256 MB - 2 GB	For VACUUM, CREATE INDEX; set higher during maintenance
hash_mem_multiplier	2.0 (default)	Allows hash operations to use hash_mem_multiplier × work_mem

Apply Parameter Changes

# Dynamic parameters (no restart)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name work_mem --value "16384"  # 16 MB in kB

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name effective_cache_size --value "6291456"  # 6 GB in 8kB pages

# Static parameters (restart required)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_buffers --value "524288"  # 4 GB in 8kB pages

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

---

Cache and Buffer Analysis

Buffer Cache Hit Ratio

-- Overall cache hit ratio (target: > 99%)
SELECT
    sum(blks_hit) AS cache_hits,
    sum(blks_read) AS disk_reads,
    round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS hit_ratio_pct
FROM pg_stat_database;

-- Per-table cache hit ratio
SELECT
    schemaname, relname,
    heap_blks_hit, heap_blks_read,
    round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS table_hit_pct,
    idx_blks_hit, idx_blks_read,
    round(100.0 * idx_blks_hit / nullif(idx_blks_hit + idx_blks_read, 0), 2) AS index_hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 15;

Identify Tables That Don't Fit in Cache

-- Tables larger than shared_buffers (may cause cache churn)
SELECT
    schemaname, relname,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size,
    n_live_tup
FROM pg_stat_user_tables
WHERE pg_relation_size(schemaname || '.' || relname) >
      current_setting('shared_buffers')::bigint * 8192
ORDER BY pg_relation_size(schemaname || '.' || relname) DESC;

---

VACUUM and ANALYZE Tuning

VACUUM Effectiveness

-- Tables with high dead tuple ratios
SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum, last_autovacuum,
    last_analyze, last_autoanalyze,
    vacuum_count, autovacuum_count,
    analyze_count, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Stale Statistics (Trigger Re-ANALYZE)

-- Tables that haven't been analyzed recently
SELECT
    schemaname, relname,
    n_live_tup,
    n_mod_since_analyze,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > n_live_tup * 0.1  -- > 10% modified
ORDER BY n_mod_since_analyze DESC
LIMIT 15;

-- Manually analyze tables with stale statistics
ANALYZE VERBOSE your_table;

Autovacuum Tuning for Performance

# Increase autovacuum workers (default 3)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name autovacuum_max_workers --value "5"

# Reduce autovacuum delay (more aggressive, default 2ms)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name autovacuum_vacuum_cost_delay --value "0"

# Increase cost limit (default 200)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name autovacuum_vacuum_cost_limit --value "1000"

---

Connection Pooling (PgBouncer)

Enable and Configure

# Enable built-in PgBouncer
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name pgbouncer.enabled --value "true"

# Set pool mode (transaction is recommended)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name pgbouncer.pool_mode --value "transaction"

# Set default pool size
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name pgbouncer.default_pool_size --value "50"

# Set max client connections
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name pgbouncer.max_client_conn --value "5000"

Connect Through PgBouncer

# PgBouncer listens on port 6432
psql "host=$FQDN port=6432 dbname=$DB user=$USER sslmode=require"

PgBouncer Sizing Guide

Workload	pool_mode	default_pool_size	max_client_conn
Web app (short txns)	transaction	20-50	2000-5000
Microservices	transaction	10-30 per service	1000-3000
Batch processing	session	5-20	200-500
Mixed workload	transaction	30-50	3000-5000

---

Wait Events Analysis

-- Current wait events
SELECT
    wait_event_type, wait_event, count(*) AS sessions
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

-- Wait event history (snapshot)
SELECT
    pid, usename, state,
    wait_event_type, wait_event,
    left(query, 100) AS query_preview,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
ORDER BY duration DESC;

Common Wait Events and Actions

Wait Event Type	Wait Event	Meaning	Action
Lock	relation	Table lock contention	Check blocking queries
Lock	transactionid	Row-level lock wait	Review transaction isolation
IO	DataFileRead	Reading from disk	Increase shared_buffers, add indexes
IO	WALWrite	WAL write bottleneck	Check storage IOPS
LWLock	BufferMapping	Buffer pool contention	Increase shared_buffers
Client	ClientRead	Waiting for client input	Application-side latency

---

Compute Right-Sizing

Current Resource Usage vs SKU

# Current SKU
az postgres flexible-server show \
  --resource-group $RG --name $SERVER \
  --query "{sku:sku.name, tier:sku.tier, storage:storage.storageSizeGb, iops:storage.iops}" -o table

# CPU utilization (last 24h)
az monitor metrics list \
  --resource "/subscriptions/$SUB/resourceGroups/$RG/providers/Microsoft.DBforPostgreSQL/flexibleServers/$SERVER" \
  --metric "cpu_percent" \
  --interval PT1H --start-time $(date -u -d '24 hours ago' +%Y-%m-%dT%H:%M:%SZ) \
  --aggregation Average Maximum -o table

SKU Recommendation Guide

Avg CPU	Max CPU	Memory Pressure	Recommendation
< 20%	< 50%	Low	Consider downgrading tier
20-60%	< 80%	Low	Current tier is appropriate
> 60%	> 80%	Low	Upgrade vCores
Any	Any	High (swap/temp files)	Upgrade to Memory Optimized tier

---

Must

• Establish a performance baseline before making changes
• Use EXPLAIN (ANALYZE, BUFFERS) for query-level diagnosis
• Create indexes with CONCURRENTLY on production tables
• Test parameter changes in non-production environments first
• Monitor cache hit ratios (target > 99%)
• Enable pg_stat_statements for workload profiling

Prefer

• pg_stat_statements over log-based query analysis
• hypopg to test hypothetical indexes before building them
• pg_hint_plan to diagnose planner issues by forcing specific plans
• Partial indexes for selective queries
• CONCURRENTLY for index and reindex operations
• Transaction pool mode for PgBouncer (web workloads)
• Incremental parameter tuning over large jumps
• ANALYZE after significant data changes

Avoid

• Guessing at performance issues without data
• Over-indexing tables (each index adds write overhead)
• Setting work_mem too high (multiplied by connections × operations)
• VACUUM FULL as a performance fix (use regular VACUUM + REINDEX)
• Ignoring temp_blks_written in pg_stat_statements (signals work_mem issues)
• Running EXPLAIN ANALYZE on destructive DML without wrapping in a transaction