Skip to content
ÜÜmit Uz
All essays
ArchitectureFebruary 25, 202518 min

PostgreSQL Performance Tuning: Query Optimization and Indexing

Master PostgreSQL performance tuning. Learn query optimization, advanced indexing strategies, connection pooling, and monitoring techniques.

Ü
Ümit Uz
Mobile & Full Stack Developer

Introduction

PostgreSQL is one of the most powerful open-source relational databases, but achieving optimal performance requires understanding its internals and tuning techniques. This guide covers comprehensive performance tuning strategies for PostgreSQL in 2025, from query optimization to connection pooling and monitoring.

Query Performance Analysis

Using EXPLAIN and EXPLAIN ANALYZE

Understanding query execution plans is fundamental to performance tuning:

sql
-- Basic execution plan
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Detailed plan with actual execution time
EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2025-01-01';

-- Include buffers and WAL information
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM products WHERE category = 'electronics';

Interpreting Execution Plans

Nested Loop  (cost=0.29..16.55 rows=4 width=72)
  ->  Index Scan using users_pkey on users u  (cost=0.29..8.31 rows=1 width=36)
        Index Cond: (id = o.user_id)
  ->  Index Scan using idx_orders_user_id on orders o  (cost=0.00..8.22 rows=4 width=40)
        Index Cond: (user_id = u.id)
        Filter: (created_at > '2025-01-01'::date)

Key metrics:

  • cost: Estimated execution cost (lower is better)
  • rows: Estimated rows returned
  • actual time: Actual execution time (with ANALYZE)
  • buffers: Buffer cache hits and reads

Advanced Indexing Strategies

B-Tree Indexes

Default and most versatile index type:

sql
-- Single column index
CREATE INDEX idx_users_email ON users(email);

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

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index (index specific rows)
CREATE INDEX idx_active_users
ON users(created_at)
WHERE status = 'active';

Expression Indexes

Index computed expressions:

sql
-- Index on lowercase email for case-insensitive searches
CREATE INDEX idx_users_email_lower
ON users(LOWER(email));

-- Index on computed value
CREATE INDEX idx_products_total_price
ON products(price * quantity);

-- Now queries can use the index
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

GIN Indexes

Perfect for array and JSONB data:

sql
-- Index on array column
CREATE INDEX idx_users_tags ON users USING GIN(tags);

-- Index on JSONB
CREATE INDEX idx_products_metadata
ON products USING GIN(metadata);

-- Index on JSONB specific path
CREATE INDEX idx_products_category
ON products USING GIN((metadata->>'category') gin_trgm_ops);

-- Queries using GIN index
SELECT * FROM users WHERE tags @> ARRAY['developer'];
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';

BRIN Indexes

Efficient for very large tables with natural ordering:

sql
-- Perfect for time-series data
CREATE INDEX idx_logs_created
ON logs USING BRIN(created_at);

-- Great for append-only data
CREATE INDEX idx_events_timestamp
ON events USING BRIN(timestamp);

Hash Indexes

Fast equality comparisons:

sql
CREATE INDEX idx_users_id_hash ON users USING HASH(id);

Query Optimization Techniques

Subquery Optimization

sql
-- Slow: Correlated subquery
SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

-- Fast: JOIN with GROUP BY
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Fast: CTE (Common Table Expression)
WITH user_orders AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  GROUP BY user_id
)
SELECT u.name, uo.order_count
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

Window Functions

sql
-- Calculate running totals efficiently
SELECT
  user_id,
  order_date,
  total,
  SUM(total) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM orders;

-- Find top 3 orders per user
SELECT *
FROM (
  SELECT
    user_id,
    total,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY total DESC
    ) as rank
  FROM orders
) ranked
WHERE rank <= 3;

Materialized Views

Pre-compute expensive queries:

sql
-- Create materialized view
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_spent,
  AVG(o.total) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- Create indexes on materialized view
CREATE INDEX idx_user_summary_id ON user_order_summary(id);
CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_order_summary;

-- Concurrent refresh (doesn't block reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

Connection Pooling

PgBouncer Configuration

ini
# pgbouncer.ini

[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 3
max_db_connections = 50
server_idle_timeout = 600
server_lifetime = 3600

listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

Application-Side Pooling

javascript
// Node.js with pg
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'myapp',
  user: 'user',
  password: 'password',
  max: 20, // Maximum pool size
  min: 5,  // Minimum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Use pool
async function getUser(id) {
  const client = await pool.connect();
  try {
    const result = await client.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0];
  } finally {
    client.release();
  }
}

// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

Performance Monitoring

pg_stat_statements

Track query performance:

sql
-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View slow queries
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time,
  stddev_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Find most frequent queries
SELECT
  query,
  calls,
  total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Monitoring Queries

sql
-- Check active connections
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  state_change,
  query
FROM pg_stat_activity
WHERE state = 'active';

-- Find long-running queries
SELECT
  pid,
  now() - query_start as duration,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- Check table sizes
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan as index_scans,
  idx_tup_read as tuples_read,
  idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Configuration Tuning

PostgreSQL Configuration

ini
# postgresql.conf

## Connection Settings
max_connections = 100
shared_buffers = 4GB          # 25% of RAM
effective_cache_size = 12GB   # 50-75% of RAM
work_mem = 32MB               # Per operation
maintenance_work_mem = 512MB

## WAL Settings
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

## Query Planner
random_page_cost = 1.1        # For SSD
effective_io_concurrency = 200

## Logging
log_min_duration_statement = 1000  # Log slow queries
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

## Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

Partitioning

Table Partitioning

sql
-- Create partitioned table
CREATE TABLE orders (
  id SERIAL,
  user_id INTEGER NOT NULL,
  total NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2025_q1 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Create indexes on partitions
CREATE INDEX idx_orders_2025_q1_user ON orders_2025_q1(user_id);
CREATE INDEX idx_orders_2025_q2_user ON orders_2025_q2(user_id);

-- Query uses partition pruning
SELECT * FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at < '2025-02-01';

List Partitioning

sql
CREATE TABLE users (
  id SERIAL,
  name VARCHAR(255),
  status VARCHAR(50),
  created_at TIMESTAMP
) PARTITION BY LIST (status);

CREATE TABLE users_active PARTITION OF users
  FOR VALUES IN ('active', 'pending');

CREATE TABLE users_inactive PARTITION OF users
  FOR VALUES IN ('inactive', 'suspended');

Vacuum and Analyze

Manual Vacuum and Analyze

sql
-- Analyze table to update statistics
ANALYZE users;

-- Vacuum to reclaim space
VACUUM users;

-- Full vacuum (requires exclusive lock)
VACUUM FULL users;

-- Vacuum and analyze
VACUUM (ANALYZE) users;

-- Vacuum specific table with verbose output
VACUUM (VERBOSE, ANALYZE) orders;

Autovacuum Tuning

sql
-- Per-table autovacuum settings
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 1000
);

Performance Tips

1. Use Connection Pooling

Always use a connection pooler to reduce connection overhead.

2. Optimize JOIN Order

Let PostgreSQL's query planner determine the best join order.

3. Use Prepared Statements

Reduce parsing overhead:

javascript
// Node.js prepared statement
const query = 'SELECT * FROM users WHERE email = $1';
const values = ['user@example.com'];
const result = await pool.query(query, values);

4. Avoid SELECT *

Select only the columns you need:

sql
-- Bad
SELECT * FROM users WHERE id = 1;

-- Good
SELECT id, name, email FROM users WHERE id = 1;

5. Use Transactions Wisely

sql
-- Batch operations
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100);
INSERT INTO order_items (order_id, product_id, quantity)
  VALUES (currval('orders_id_seq'), 10, 2);
COMMIT;

Monitoring Tools

PostgreSQL Metrics

sql
-- Database size
SELECT pg_size_pretty(pg_database_size('myapp'));

-- Table and index sizes
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::text)) as total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::text) DESC;

-- Cache hit ratio
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;

Conclusion

PostgreSQL performance tuning is a continuous process of monitoring, analyzing, and optimizing. By mastering query optimization, advanced indexing, connection pooling, and proper configuration, you can achieve exceptional performance for your database workloads.

Related essays

ArchitectureMarch 24, 2025

Technical Interview Preparation Guide 2025

Read
ArchitectureMarch 22, 2025

Scalability Patterns: Horizontal vs Vertical Scaling in 2025

Read
ArchitectureMarch 20, 2025

Microservices Architecture: Service Mesh, API Gateway & Patterns

Read
Next essay
Serverless Architecture: AWS Lambda and Beyond