postgres-performancelisted
Install: claude install-skill aiskillstore/marketplace
# PostgreSQL Performance Engineering
## Problem Statement
Performance problems compound. A query that takes 50ms at 1K rows takes 5s at 100K rows. This skill covers patterns for building performant database interactions from the start and fixing performance issues.
---
## Pattern: Query Optimization Workflow
### Step 1: Identify Slow Queries
```sql
-- Enable pg_stat_statements (if not already)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Find slowest queries
SELECT
query,
calls,
round(mean_exec_time::numeric, 2) as avg_ms,
round(total_exec_time::numeric, 2) as total_ms,
rows
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 20;
```
### Step 2: Analyze Query Plan
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM assessments
WHERE user_id = 'abc-123'
ORDER BY created_at DESC
LIMIT 10;
```
**What to look for:**
| Warning Sign | Problem | Solution |
|--------------|---------|----------|
| Seq Scan on large table | Missing index | Add index |
| High `loops` count | N+1 in join | Rewrite query, add index |
| Sort with high cost | No index for ORDER BY | Covering index |
| Hash/Merge Join with high rows | Large intermediate result | Filter earlier, better indexes |
| Buffers: shared read high | Data not cached | More RAM, or query less data |
### Step 3: Fix and Verify
```sql
-- Add index
CREATE INDEX CONCURRENTLY ix_assessments_user_created
ON assessments (user_id, created_at DESC);
-- Verify impro