postgresql

PostgreSQL

Description

PostgreSQL database patterns including queries, indexing, and optimization.

When to Use

• PostgreSQL database operations
• SQL query optimization
• Schema design

---

Core Patterns

Basic Queries

-- Select with filtering
SELECT id, name, email
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Join
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;

Indexes

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at DESC);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

Migrations

-- Add column with default
ALTER TABLE users ADD COLUMN role VARCHAR(50) DEFAULT 'user';

-- Add constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Best Practices

1. Use indexes for filtered/sorted columns
2. Use EXPLAIN ANALYZE for slow queries
3. Avoid SELECT * in production
4. Use transactions for multiple operations
5. Use connection pooling

Common Pitfalls

• N+1 queries: Use JOINs or batch loading
• Missing indexes: Add indexes for WHERE/ORDER BY
• Large transactions: Keep transactions short

Gap Analysis Rule

Always identify gaps and suggest next steps to users. In case there is no gaps anymore, then AI should clearly state that there is no gap left.