PostgreSQL Performance Tuning
Optimize PostgreSQL with proper indexing, query analysis, connection pooling, and configuration tuning for production workloads.
DatabaseAdvanced25 min read
AI Summary
Quick context for PostgreSQL Performance Tuning
PostgreSQL Performance Tuning is a advanced guide in Database. Optimize PostgreSQL with proper indexing, query analysis, connection pooling, and configuration tuning for production workloads.. Key topics: indexing, performance, postgresql, query-optimization.
## Measuring Before Optimizing
Never guess at database performance. Use `EXPLAIN ANALYZE` to see what PostgreSQL actually does.
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;
```
Focus on these metrics:
- **Actual rows vs estimated rows** — large discrepancies indicate stale statistics
- **Sequential scans on large tables** — usually means a missing index
- **Nested loops vs hash joins vs merge joins** — the planner picks based on table size and available indexes
## Indexing Strategy
### B-Tree Indexes
B-tree indexes are the default and work for equality and range queries.
```sql
-- Single column index
CREATE INDEX idx_users_email ON users (email);
-- Composite index for common query patterns
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
-- Partial index — index only what you query
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
```
Composite index order matters. Put the most selective column first, or the column used in equality conditions before range conditions.
### GIN Indexes
GIN (Generalized Inverted Index) is for full-text search, arrays, and JSONB.
```sql
-- Full-text search
CREATE INDEX idx_products_search ON products USING GIN (
to_tsvector('english', name || ' ' || description)
);
-- JSONB containment
CREATE INDEX idx_events_metadata ON events USING GIN (metadata);
```
### GiST Indexes
GiST indexes support geometric data, range types, and nearest-neighbor searches.
```sql
-- Range type indexing
CREATE INDEX idx_reservations_during ON reservations USING GiST (
during tsrange(check_in, check_out)
);
```
## Query Optimization Techniques
### Avoid SELECT *
```sql
-- Bad: fetches all columns
SELECT * FROM orders WHERE user_id = 42;
-- Good: fetches only what you need
SELECT id, total, created_at FROM orders WHERE user_id = 42;
```
### Use EXISTS Instead of IN for Subqueries
```sql
-- Often slower
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = true);
-- Usually faster — stops at first match
SELECT * FROM products p
WHERE EXISTS (
SELECT 1 FROM categories c WHERE c.id = p.category_id AND c.active = true
);
```
### Materialize Expensive Aggregations
```sql
-- Create a materialized view for complex reports
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
date_trunc('month', created_at) AS month,
category,
SUM(total) AS revenue,
COUNT(*) AS order_count
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY 1, 2;
-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
```
## Connection Pooling
Every PostgreSQL connection costs memory (roughly 10MB per connection). For web applications, use a connection pooler.
```javascript
// Using pg-pool in Node.js
const { Pool } = require('pg');
const pool = new Pool({
max: 20, // Max connections in pool
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
async function query(text, params) {
const start = Date.now();
const result = await pool.query(text, params);
const duration = Date.now() - start;
console.log('Executed query', { text, duration, rows: result.rowCount });
return result;
}
```
For larger deployments, consider PgBouncer or pgcat as external poolers.
## Configuration Tuning
### Memory Settings
```sql
-- Check current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
-- In postgresql.conf (adjust for your system)
shared_buffers = '4GB' -- 25% of total RAM
work_mem = '256MB' -- Per-sort/hash operation
effective_cache_size = '12GB' -- 75% of total RAM
maintenance_work_mem = '1GB' -- For VACUUM, CREATE INDEX
```
### Parallel Query Execution
```sql
-- Enable parallel queries
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost = 0.01
parallel_setup_cost = 1000
```
## Maintenance Tasks
### VACUUM and ANALYZE
```sql
-- Check table bloat
SELECT
schemaname, relname,
n_live_tup, n_dead_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- Manual vacuum for specific table
VACUUM (VERBOSE) ANALYZE orders;
-- Enable autovacuum tuning
autovacuum_vacuum_scale_factor = 0.05 -- Vacuum when 5% of rows are dead
autovacuum_analyze_scale_factor = 0.02 -- Analyze when 2% of rows change
```
### Index Maintenance
```sql
-- Find unused indexes
SELECT
schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Reindex bloated indexes
REINDEX INDEX CONCURRENTLY idx_orders_user_date;
```
## Monitoring
Set up alerts on these key metrics:
- **Connections**: current vs max allowed
- **Lock waits**: long-running locks indicate contention
- **Replication lag**: for read replicas
- **Cache hit ratio**: below 99% signals memory or indexing issues
```sql
-- Cache hit ratio
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS hit_ratio
FROM pg_statio_user_tables;
```
PostgreSQL performance tuning is iterative. Measure, change one thing, measure again. Small, targeted changes compound into significant improvements.
Continue with related content
Suggested next reads and tools from the knowledge graph.