PostgreSQL Advanced Patterns

Advanced patterns for high-performance PostgreSQL database design, querying, and optimization.

Performance Optimization

1. Effective Indexing

-- B-tree index for equality and range queries CREATE INDEX idx_users_email ON users(email);

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

-- Composite index for multiple columns CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- GiST index for full-text search CREATE INDEX idx_products_search ON products USING GiST(to_tsvector('english', name || ' ' || description));

-- GIN index for JSON queries CREATE INDEX idx_metadata_gin ON events USING GIN(metadata);

2. Query Optimization

-- Use EXPLAIN ANALYZE to understand query plans EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- Avoid SELECT * SELECT id, name, email FROM users WHERE active = true;

-- Use EXISTS instead of IN for large subqueries SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Batch operations instead of loops INSERT INTO logs (event, created_at) SELECT unnest(ARRAY['login', 'logout', 'update']), NOW();

3. Connection Pooling

import { Pool } from 'pg';

const pool = new Pool({ max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000, });

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

Advanced Query Patterns

Window Functions

-- Running totals SELECT date, amount, SUM(amount) OVER (ORDER BY date) as running_total FROM transactions;

-- Row numbering with partitions SELECT user_id, purchase_date, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date DESC) as purchase_rank FROM purchases;

-- Moving averages SELECT date, price, AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d FROM stock_prices;

Common Table Expressions (CTEs)

-- Recursive CTE for hierarchical data WITH RECURSIVE org_chart AS ( SELECT id, name, manager_id, 1 as level FROM employees WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, oc.level + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.id ) SELECT * FROM org_chart ORDER BY level, name;

-- Multiple CTEs for complex queries WITH active_users AS ( SELECT id FROM users WHERE active = true ), recent_orders AS ( SELECT user_id, COUNT(*) as order_count FROM orders WHERE created_at > NOW() - INTERVAL '30 days' GROUP BY user_id ) SELECT u.id, u.name, COALESCE(ro.order_count, 0) as recent_orders FROM active_users au JOIN users u ON au.id = u.id LEFT JOIN recent_orders ro ON u.id = ro.user_id;

JSON Operations

-- Query JSON columns SELECT data->>'name' as name, data->'address'->>'city' as city FROM customers WHERE data->>'status' = 'active';

-- JSON aggregation SELECT user_id, json_agg(json_build_object('id', id, 'title', title)) as posts FROM posts GROUP BY user_id;

-- JSON path queries SELECT * FROM events WHERE metadata @> '{"type": "purchase"}';

Database Design Patterns

1. Partitioning

-- Range partitioning by date CREATE TABLE events ( id BIGSERIAL, event_type TEXT, created_at TIMESTAMP NOT NULL ) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

2. Materialized Views

-- Create materialized view for expensive queries CREATE MATERIALIZED VIEW user_stats AS SELECT user_id, COUNT(DISTINCT order_id) as total_orders, SUM(amount) as total_spent, MAX(created_at) as last_order_date FROM orders GROUP BY user_id;

-- Refresh periodically REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- Create index on materialized view CREATE INDEX idx_user_stats_user_id ON user_stats(user_id);

3. Constraints and Validation

-- Check constraints ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);

-- Exclusion constraints CREATE TABLE bookings ( room_id INT, during TSRANGE, EXCLUDE USING GIST (room_id WITH =, during WITH &&) );

-- Domain types for reusable constraints CREATE DOMAIN email_address AS TEXT CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Z|a-z]{2,}$');

CREATE TABLE users ( id SERIAL PRIMARY KEY, email email_address NOT NULL UNIQUE );

Transactions and Concurrency

Transaction Isolation

-- Serializable transactions for critical operations BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM accounts WHERE id = 1 FOR UPDATE; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

-- Repeatable read for consistent snapshots BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT SUM(balance) FROM accounts; -- Consistent view maintained throughout transaction COMMIT;

Row-Level Locking

-- Pessimistic locking SELECT * FROM orders WHERE id = 123 FOR UPDATE;

-- Shared lock for read-only access SELECT * FROM products WHERE id = 456 FOR SHARE;

-- Skip locked rows SELECT * FROM queue WHERE processed = false FOR UPDATE SKIP LOCKED LIMIT 10;

Monitoring and Maintenance

Query Performance

-- Find slow queries SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- Check index usage SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan ASC;

-- Table bloat SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size FROM pg_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Maintenance Tasks

-- Vacuum and analyze VACUUM ANALYZE users;

-- Reindex REINDEX TABLE users;

-- Update statistics ANALYZE users;

Best Practices

• Always use parameterized queries to prevent SQL injection

• Create indexes on foreign keys for join performance

• Use connection pooling for better resource management

• Monitor query performance with pg_stat_statements

• Regular VACUUM and ANALYZE for statistics

• Use appropriate transaction isolation levels

• Avoid N+1 queries with proper joins or batching

• Implement retry logic for transaction conflicts

Integration Points

Complements:

• backend-implementation-patterns: For API data access

• tdd-workflow: For database testing

• verification-loop: For query performance checks

• security-implementation-guide: For secure queries