PostgreSQL Performance Tuning for Developers: Indexing, Queries, and Configuration

PostgreSQL reached 55.6% adoption (Stack Overflow 2025) — it’s the most-used database among professional developers and the most loved. But with great power comes great configuration surface area. A default PostgreSQL install runs anything. A properly tuned one runs it 10x faster.

This guide covers the practical performance techniques that matter for application developers, not DBAs.

PostgreSQL 18 in 2026

PostgreSQL 18 (released September 2025) brought several performance improvements:

• Incremental backup — faster backups with less I/O impact
• Improved partitioning — better query planning for partitioned tables
• Parallel query enhancements — more operations can use parallel workers
• Merge join improvements — faster joins on large sorted datasets
• WAL compression — reduced log I/O by 20-40%

These improvements matter, but the fundamentals (indexing, queries, memory) still give the biggest returns.

1. Indexing: Where Most Performance Lives

PostgreSQL supports multiple index types. Picking the wrong one is the single biggest performance mistake developers make.

B-tree (default): Use for Equality + Range

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at);

• Equality lookups (WHERE email = 'x')
• Range queries (WHERE created_at > '2026-01-01')
• Sort order (ORDER BY created_at DESC)

B-tree is the right choice for 80% of indexes. Only use other types when you know you need them.

GiST: Use for Full-Text Search + Geometry

CREATE INDEX idx_docs_content ON documents USING GiST (to_tsvector('english', content));

• Full-text search
• Geometric/geospatial data (PostGIS)
• Range overlaps and exclusion constraints

GIN: Use for JSONB + Arrays

CREATE INDEX idx_users_preferences ON users USING GIN (preferences);
-- Query: WHERE preferences @> '{"theme": "dark"}'

• JSONB containment queries (@> operator)
• Array operations
• Full-text search (alternative to GiST)

BRIN: Use for Large, Sorted Tables

CREATE INDEX idx_logs_created ON logs USING BRIN (created_at) WITH (pages_per_range = 32);

• Massive tables (millions+ rows)
• Data inserted in roughly sorted order (time-series, logs)
• 100x smaller than equivalent B-tree index

Indexing Rules of Thumb

• Index columns used in WHERE, JOIN, ORDER BY
• Don’t index columns with low cardinality (boolean, status fields with 2-3 values)
• Use composite indexes for queries filtering on multiple columns — order columns by selectivity
• B-tree is the default for a reason — use GiST/GIN/BRIN only when you have measured the need
• Monitor index usage with pg_stat_user_indexes

2. Query Optimization with EXPLAIN ANALYZE

EXPLAIN ANALYZE is the single most important debugging tool. It shows exactly how PostgreSQL executes a query and where time is spent.

EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.created_at > '2026-01-01' 
AND u.plan = 'premium';

What to Look For

The 5-Query Audit

Run these to find your worst performers:

-- Slowest queries by total time
SELECT queryid, calls, total_exec_time / 1000 AS total_sec,
       mean_exec_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with most I/O
SELECT queryid, calls, shared_blks_read, shared_blks_hit,
       shared_blks_read * 8 / 1024 AS mb_read
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

3. Memory Configuration

PostgreSQL’s default configuration is conservative. These settings make the biggest difference:

# Memory — adjust for your server
shared_buffers = '2GB'           # 25% of RAM for dedicated DB server
effective_cache_size = '6GB'     # 75% of RAM (OS will cache the rest)
work_mem = '64MB'                # Per-operation sort/hash memory
maintenance_work_mem = '512MB'   # VACUUM, index creation

# Write-ahead log
wal_buffers = '64MB'
wal_writer_delay = '500ms'

# Planner
random_page_cost = 1.1           # For SSD (default 4.0 is for HDD)
effective_io_concurrency = 200   # For SSD

Key notes:

• shared_buffers above 25% of RAM causes diminishing returns (double caching)
• work_mem applies per query operation — a sort on 100 concurrent connections uses 100 × work_mem
• random_page_cost = 1.1 for SSDs dramatically improves query plan selection
• effective_cache_size tells PostgreSQL how much RAM the OS will use for file caching

4. Connection Pooling

PostgreSQL forks a process per connection. Beyond ~100 connections, performance degrades. Connection pooling is essential for web applications.

PgBouncer Setup

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
listen_addr = 0.0.0.0
listen_port = 6432

pool_mode = transaction is the recommended mode for web apps. It reuses connections between transactions, giving you 200 application connections on 20 database connections.

5. VACUUM and Autovacuum

PostgreSQL’s MVCC creates dead tuples. Without regular vacuuming, table bloat degrades query performance.

Default autovacuum settings work for most workloads, but high-write tables need tuning:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- Trigger at 1% dead tuples
  autovacuum_vacuum_threshold = 1000,       -- Minimum dead tuples
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000
);

Monitor Bloat

-- Table bloat estimate
SELECT schemaname, tablename, n_dead_tup,
       n_live_tup, last_vacuum, last_autovacuum,
       n_mod_since_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If n_dead_tup is consistently high and last_autovacuum is old, your autovacuum is not keeping up.

6. Essential Monitoring Queries

-- Currently running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       state, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Cache hit ratio (target: >99%)
SELECT 'index hit rate' AS name,
       (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) * 100 AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT 'table hit rate' AS name,
       sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) * 100 AS ratio
FROM pg_statio_user_tables;

-- Lock monitoring
SELECT pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Quick Reference: Fix the Most Common Problems

What to Read Next

• DevOps Pipeline with Free Tools — CI/CD pipeline with PostgreSQL integration
• Docker Compose Production Guide — Containerized PostgreSQL in production
• Install PostgreSQL on Ubuntu 26.04 — Fresh PostgreSQL setup guide

Related Articles

Deepen your understanding with these curated continuations.

View All Articles

postgresql 5 min read

Install and Configure PostgreSQL on Ubuntu 26.04

Complete guide to install PostgreSQL 18 on Ubuntu 26.04. Set up roles, databases, remote access, authentication methods, and security hardening.

Jena Apr 26, 2026

devops 5 min read

DevOps Pipeline with Free Tools: Complete CI/CD Setup Guide 2026

Build a production-grade DevOps pipeline using free tools: GitHub Actions, Docker, CodeRabbit, SonarCloud, and Render. Step-by-step CI/CD setup with zero monthly cost.

Darsh Jariwala Jun 03, 2026

linux 5 min read

wget vs curl: When to Use Each (Complete Guide)

Learn the real differences between wget and curl. When to use wget for downloads and site mirroring, and when to reach for curl for APIs and HTTP debugging.

Jena Apr 30, 2026