Skip to content

existential-birds/beagle / plugins/beagle-python/skills/postgres-code-review

postgres-code-review

Verified

Reviews PostgreSQL code for indexing strategies, JSONB operations, connection pooling, and transaction safety. Use when reviewing SQL queries, database schemas, JSONB usage, or connection management.

FileKindSize
SKILL.mdskill md2.9 KB
references/connections.mdmarkdown7.8 KB
references/indexes.mdmarkdown4.6 KB
references/jsonb.mdmarkdown5.6 KB
references/transactions.mdmarkdown13.1 KB

references/indexes.md

View on GitHub
# Indexes

## Critical Anti-Patterns

### 1. Missing Index on WHERE Clause

**Problem**: Sequential scan on large tables causes slow queries.

```sql
-- BAD: No index on email
SELECT * FROM users WHERE email = 'user@example.com';

-- GOOD: Create index
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
```

```python
# Check query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
# Look for "Seq Scan" (bad) vs "Index Scan" (good)
```

### 2. Wrong Column Order in Composite Index

**Problem**: Index not used if query doesn't match leftmost columns.

```sql
-- BAD: Index doesn't match query pattern
CREATE INDEX idx_orders_wrong ON orders(status, user_id);
SELECT * FROM orders WHERE user_id = 123;  -- Won't use index!

-- GOOD: Match query pattern
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 123;  -- Uses index
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';  -- Uses index
```

**Rule**: Put high-selectivity columns first, match WHERE clause order.

### 3. Not Using Partial Indexes

**Problem**: Indexing entire table when only subset is queried.

```sql
-- BAD: Index includes all rows
CREATE INDEX idx_orders_status ON orders(status);

-- GOOD: Only index active orders
CREATE INDEX idx_orders_active ON orders(user_id, created_at)
WHERE status = 'active';

SELECT * FROM orders
WHERE status = 'active' AND user_id = 123
ORDER BY created_at DESC;  -- Uses partial index
```

### 4. Missing Index on Foreign Keys

**Problem**: Slow JOINs and cascading deletes.

```sql
-- BAD: No index on foreign key
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id)
);

-- GOOD: Index foreign keys
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id)
);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
```

### 5. Not Using EXPLAIN ANALYZE

**Problem**: Guessing instead of measuring query performance.

```python
# BAD: Assuming query is fast
cursor.execute("SELECT * FROM orders WHERE user_id = %s", (user_id,))

# GOOD: Verify with EXPLAIN
cursor.execute("""
    EXPLAIN ANALYZE
    SELECT * FROM orders WHERE user_id = %s
""", (user_id,))
print(cursor.fetchall())
# Check: Index Scan vs Seq Scan, actual time, rows

# Then run actual query
cursor.execute("SELECT * FROM orders WHERE user_id = %s", (user_id,))
```

### 6. Over-Indexing

**Problem**: Slows down writes, wastes space.

```sql
-- BAD: Too many indexes on rarely-queried columns
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_updated_at ON users(updated_at);
CREATE INDEX idx_users_last_login ON users(last_login);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- GOOD: Only index frequently-queried columns
CREATE INDEX idx_users_email ON users(email);  -- Used for login
CREATE INDEX idx_users_username ON users(username);  -- Used for lookup
-- Skip indexes on created_at, updated_at, last_login if rarely queried
```

### 7. Not Using Covering Indexes

**Problem**: Index scan followed by table lookup (heap fetch).

```sql
-- BAD: Index on id only, must fetch name from table
CREATE INDEX idx_users_email ON users(email);
SELECT id, name FROM users WHERE email = 'user@example.com';

-- GOOD: Include name in index (covering index)
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name);
SELECT id, name FROM users WHERE email = 'user@example.com';
-- "Index Only Scan" - no heap fetch needed
```

### 8. String Pattern Matching Without Index

**Problem**: LIKE with leading wildcard can't use B-tree index.

```sql
-- BAD: Can't use standard index
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email LIKE '%@example.com';  -- Seq Scan

-- GOOD: Use trigram index for pattern matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING gin(email gin_trgm_ops);
SELECT * FROM users WHERE email LIKE '%@example.com';  -- Uses GIN index
```

## Review Questions

1. Do all WHERE and JOIN columns have indexes?
2. Are composite index column orders optimized for queries?
3. Would partial indexes reduce index size and improve performance?
4. Are foreign keys indexed?
5. Has EXPLAIN ANALYZE been used to verify query plans?
6. Are there redundant or unused indexes?
7. Would covering indexes eliminate heap fetches?
8. Are pattern matching queries using appropriate index types (GIN, trigram)?
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall