postgres-performance-investigation

name	postgres-performance-investigation
description	Investigation procedures for PostgreSQL database performance issues including connection pool exhaustion, slow queries, replication lag, and storage capacity. Use this skill when investigating database latency, connection errors, or read/write performance degradation on PostgreSQL instances, both Amazon RDS and on-premises.

PostgreSQL Performance Investigation

Use this skill when an incident reports database latency, connection errors, query timeouts, or read/write performance degradation on PostgreSQL.

Step 1: Check for recent deployments

Invoke get_recent_deployments with the affected service name and hours=4.

• Deployment within 2 hours → HIGH correlation with the incident
• Deployment within 4 hours → MEDIUM correlation
• No deployment found → LOW correlation; focus on infrastructure causes

Step 2: Check CloudWatch RDS metrics (Amazon RDS only)

For Amazon RDS PostgreSQL, query CloudWatch for the instance and evaluate:

Metric	Threshold	Indicates
DatabaseConnections	> 80% of max_connections	Connection pool exhaustion
ReadLatency	> 20ms	Read performance degradation
WriteLatency	> 20ms	Write performance degradation
FreeStorageSpace	< 20% of total	Storage pressure
CPUUtilization	> 85%	Compute saturation
ReplicaLag	> 30s (read replicas)	Replication issues

For on-premises PostgreSQL, continue to Step 3.

Step 3: Query slow logs

Invoke query_db_slow_logs with:

• database_name: the affected database
• threshold_ms: 500
• limit: 20

If too many results come back, raise the threshold to 2000 and re-run.

Evaluate results for:

• Full table scans on large tables
• Missing indexes (high row counts with low index usage)
• Lock contention (queries with high wait times)
• N+1 patterns (many similar queries with slight parameter variation)

Step 4: Assess blast radius

Invoke get_service_dependencies for the affected service.

• blast_radius=CRITICAL (> 5 downstream): escalate immediately
• blast_radius=HIGH (2-5 downstream): notify downstream owners
• blast_radius=CONTAINED: standard investigation

Step 5: Determine root cause and recommend mitigation

Finding	Root cause	Immediate mitigation	Long-term fix
Recent deployment + new slow queries	Bad code change	Rollback the deployment	Add query performance tests to CI
Connection pool at max, no deployment	Traffic spike or pool misconfiguration	Increase pool size	Introduce a connection pooler such as PgBouncer
Slow queries, no deployment	Data growth beyond index capacity	Add missing indexes	Partition large tables
FreeStorageSpace < 20%	Storage pressure	Increase storage	Data retention policy
CPUUtilization > 85% with slow queries	Compute saturation	Scale instance up	Optimize queries, add read replicas

Step 6: Escalation and handoff

If the root cause is application code:

• Generate an agent-ready spec describing the change
• Include repository, file path, current behavior, expected behavior, and validation criteria

If the root cause is infrastructure:

• Route to the DBA team with slow-query details and recommended index changes