Advanced24 min read

Indexing & Performance

Create B-tree, GIN, and GiST indexes; read query plans; tune PostgreSQL performance.

B-Tree Indexes

Default B-tree indexes accelerate equality and range queries. Partial indexes index subset of rows with WHERE clause—smaller and faster for filtered queries.

Unique indexes enforce constraints. Include columns (PostgreSQL 11+) enable index-only scans covering extra SELECT columns.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

Specialized Indexes

GIN indexes JSONB containment, full text, and arrays. GiST supports geometric and full-text data. BRIN suits very large append-only tables with natural correlation to physical order.

Choose index type matching operators in your queries—@> for JSONB needs GIN, not B-tree.

CREATE INDEX idx_data_gin ON documents USING GIN (metadata jsonb_path_ops);
CREATE INDEX idx_events_brin ON events USING BRIN (created_at);

Query Plans

EXPLAIN shows planned node tree; EXPLAIN ANALYZE executes and shows actual rows and time. BUFFERS reveals cache hits vs disk reads.

Seq Scan on large tables may be correct when retrieving most rows—indexes are not free. Look for nested loop with high row counts as join strategy issues.

Bitmap Index Scan combines multiple indexes before heap fetch
Mis-estimates from stale statistics cause bad plans—run ANALYZE
work_mem affects sort and hash join spilling to disk

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 AND status = 'open';

Index Strategies

Index foreign keys and high-selectivity filter columns. Drop unused indexes found via pg_stat_user_indexes idx_scan = 0 over monitoring period.

Avoid over-indexing write-heavy tables. Concurrent index creation (CREATE INDEX CONCURRENTLY) avoids blocking writes during build.

CREATE INDEX CONCURRENTLY idx_products_sku ON products (sku);

Performance Tuning

shared_buffers, effective_cache_size, and maintenance_work_mem are key settings—tune based on hardware and workload. pg_stat_statements identifies top resource queries.

Connection pooling (PgBouncer) essential when many app instances would exhaust max_connections. Use read replicas for reporting with lag awareness.