Identifying Performance Issues

Database performance problems often manifest as slow applications. Start by identifying the bottleneck before optimizing.

Query Analysis

Using EXPLAIN

EXPLAIN ANALYZE
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

What to Look For

• Seq Scan: Full table scans on large tables
• High Rows: More rows scanned than returned
• Sort: Expensive sorts without index support
• Nested Loop: Can be slow with large datasets

Indexing Strategies

Index Types

• B-Tree: Default, good for equality and range queries
• Hash: Fast equality lookups only
• GIN: Full-text search and arrays
• GiST: Geometric and full-text data

Composite Indexes

-- For queries filtering on status and ordering by date
CREATE INDEX idx_orders_status_date 
ON orders (status, created_at DESC);

-- Column order matters!
-- Good: WHERE status = 'pending' ORDER BY created_at
-- Bad: WHERE created_at > '2024-01-01' (status not used)

Covering Indexes

-- Include frequently selected columns
CREATE INDEX idx_orders_covering
ON orders (customer_id)
INCLUDE (total, status, created_at);

-- Query can be satisfied from index alone
SELECT total, status FROM orders WHERE customer_id = 123;

Query Optimization

Avoid SELECT *

-- Bad: fetches unnecessary data
SELECT * FROM orders WHERE id = 123;

-- Good: fetch only what you need
SELECT id, total, status FROM orders WHERE id = 123;

Efficient JOINs

-- Ensure join columns are indexed
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Use appropriate join type
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Pagination

-- Bad: OFFSET for deep pagination
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- Good: Keyset pagination
SELECT * FROM orders 
WHERE id > 10000 
ORDER BY id 
LIMIT 20;

Schema Optimization

Denormalization

Sometimes duplicating data improves read performance:

-- Instead of joining every time
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);

-- Update via trigger or application logic

Partitioning

-- Partition large tables by date
CREATE TABLE orders (
    id SERIAL,
    created_at TIMESTAMP,
    ...
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024_q1 
PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

Connection Management

• Use connection pooling (PgBouncer, HikariCP)
• Right-size pool based on workload
• Monitor active connections

Monitoring

-- Find slow queries (PostgreSQL)
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Conclusion

Database optimization is iterative. Measure, identify bottlenecks, optimize, and measure again. Focus on the queries that matter most.