Measure First
Before optimizing, measure. Use EXPLAIN ANALYZE to understand query execution plans.
EXPLAIN ANALYZE
SELECT FROM transactions
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;
If you see a sequential scan on a large table, that is your bottleneck.
Indexing Strategy
Composite Indexes
For queries that filter on multiple columns, a composite index is more effective than separate indexes.
CREATE INDEX idx_transactions_user_created
ON transactions(user_id, created_at DESC);
Partial Indexes
If you only query a subset of rows, a partial index saves space and is faster to maintain.
CREATE INDEX idx_active_users
ON users(last_login_at)
WHERE status = 'active';
Spatial Indexes
For spatial data, use GIST indexes in PostgreSQL.
CREATE INDEX idx_parcel_geometry
ON parcels USING GIST (geometry);
Query Optimization
Avoid SELECT
Only select the columns you need. This reduces I/O and memory usage.
Use LIMIT
Always use LIMIT when you do not need all rows. This helps the query planner.
Batch Updates
For bulk operations, batch your updates to avoid long-running transactions.
-- Bad: one massive update
UPDATE transactions SET status = 'processed' WHERE status = 'pending';
-- Good: batched updates
UPDATE transactions SET status = 'processed'
WHERE id IN (
SELECT id FROM transactions WHERE status = 'pending' LIMIT 1000
);
Schema Design
Denormalize Hot Paths
For frequently accessed data that requires joins, consider denormalizing. The storage cost is usually worth the query speed.
Partition Large Tables
For tables with millions of rows, partition by date or another natural key. This makes queries faster and maintenance easier.
CREATE TABLE transactions (
id serial,
created_at timestamp not null,
data jsonb
) PARTITION BY RANGE (created_at);
Conclusion
Database performance tuning is about measuring, indexing, and designing schemas for your access patterns. Start with EXPLAIN ANALYZE, add indexes for your queries, and partition when tables get large.