postgres-performance

# PostgreSQL Performance Skill

Patterns for optimizing PostgreSQL performance.

## Query Analysis

### EXPLAIN ANALYZE

```sql
-- Basic explain
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

-- With more detail
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123;

-- JSON format for tools
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 123;
```

### Reading Query Plans

```
Key indicators to watch:
- Seq Scan: Full table scan (often bad for large tables)
- Index Scan: Using index (good)
- Index Only Scan: All data from index (best)
- Bitmap Heap Scan: Multiple index matches
- Nested Loop: Good for small tables, bad for large
- Hash Join: Good for large tables
- Sort: Watch for "Sort Method: external" (disk sort)
```

### Slow Query Identification

```sql
-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1 second
SELECT pg_reload_conf();

-- Query pg_stat_statements (requires extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  query,
  calls,
  total_exec_time / calls as avg_time,
  rows / calls as avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
```

## Connection Pooling

### PgBouncer Configuration

```ini
# pgbouncer.ini
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
```

### Application-Level Pooling

```typescript
// With pg-pool
import { Pool } from 'pg'

const pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  max: 20,                    // Max connections
  idleTimeoutMillis: 30000,   // Close idle connections
  connectionTimeoutMillis: 2000,
})

// Use pool
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId])
```

## Query Optimization

### Avoid SELECT *

```sql
-- Bad
SELECT * FROM orders WHERE use