PostgreSQL Query Optimization & Performance Tuning

This article covers advanced PostgreSQL query optimisation, execution plan analysis, the complete index type catalogue, extension installation procedures, and production tuning techniques. It assumes basic familiarity with PostgreSQL installation, core SQL, JSONB, and MVCC as covered in the companion PostgreSQL.md.

---

Understanding Query Execution Plans

PostgreSQL's query planner transforms SQL into an execution plan — a tree of plan nodes that each perform one operation (scan, join, sort, aggregate). EXPLAIN ANALYZE shows the plan plus actual run-time statistics.

Reading EXPLAIN Output

EXPLAIN ANALYZE
SELECT u.email, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 20;

Example output (indentation shows plan-tree depth):

Limit  (cost=1842.31..1842.36 rows=20 width=76) (actual time=45.2..45.2 rows=20 loops=1)
  ->  Sort  (cost=1842.31..1847.31 rows=2000 width=76) (actual time=45.2..45.2 rows=20 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=1765.44..1800.44 rows=2000 width=76) (actual time=42.8..44.2 rows=5032 loops=1)
              Group Key: u.id
              ->  Hash Right Join  (cost=412.50..1504.26 rows=34836 width=68) (actual time=9.1..30.1 rows=50320 loops=1)
                    Hash Cond: (o.user_id = u.id)
                    ->  Seq Scan on orders o  (cost=0.00..834.00 rows=50000 width=12) (actual time=0.01..3.2 rows=50000 loops=1)
                    ->  Hash  (cost=298.00..298.00 rows=6800 width=60) (actual time=9.0..9.0 rows=6800 loops=1)
                          ->  Seq Scan on users u  (cost=0.00..298.00 rows=6800 width=60)
                                Filter: (created_at >= '2024-01-01')
                                Rows Removed by Filter: 3200

Reading Each Line

Field	Meaning
cost=	Start-up cost..total cost in arbitrary units (relative, not milliseconds)
rows=	Planner's row estimate at this node
width=	Average bytes per output row
actual time=	Start-up ms..total ms (real time)
actual rows=	Real row count
loops=	How many times this node executed

[!TIP] Cost units are not seconds. Compare costs between plan variants for the same query. The node with the highest total cost is the bottleneck.

Node Types

Scan Nodes

Node	When PostgreSQL Uses It
Seq Scan	Full table scan — no index, or planner decides the table is small enough
Index Scan	B-tree index lookup, returns rows in index order, visits heap
Index Only Scan	All needed columns live in the index itself — no heap visits
Bitmap Heap Scan + Bitmap Index Scan	Reads index, builds a bitmap of matching page locations, then fetches pages in physical order (good for many matching rows)
Tid Scan	Scans by tuple ID (used internally, rarely in hand-written queries)

-- Seq Scan (table too small or no filter)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

-- Force an Index Only Scan with a covering index
CREATE INDEX idx_orders_status_covering ON orders (status) INCLUDE (id, amount);
EXPLAIN ANALYZE SELECT id, amount FROM orders WHERE status = 'pending';

[!WARNING] Seq Scan is not always bad — for a 100-row table it's faster than reading the index first. The problem is a Seq Scan on a 100-million-row table that could use an index.

Join Nodes

Node	Behaviour
Nested Loop	For each row in outer relation, scan inner relation. Best when outer is small and inner has an index. O(outer x inner)
Hash Join	Build a hash table on one relation, probe with the other. Best for medium-large equi-joins without indexes. O(outer + inner)
Merge Join	Both inputs sorted on join key, then merged. Best when inputs are already sorted (e.g., from indexes). O(outer + inner)

-- Force a Nested Loop by demonstrating a small outer set
SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN ANALYZE
SELECT * FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.id IN (1, 2, 3);
RESET enable_hashjoin;
RESET enable_mergejoin;

Other Common Nodes

• Aggregate — plain COUNT, SUM, etc. (single row)
• HashAggregate — hash-based GROUP BY (usually memory-bound)
• Sort — ORDER BY or implicit sort for Merge Join / GROUP BY
• Limit — stops after N rows
• Subquery Scan — wraps a subquery in the plan
• Materialize — caches inner plan output in memory (used by Nested Loop)
• Append — UNION ALL or partitioned-table scan
• Gather / Gather Merge — parallel query worker coordination

Cost vs Reality: Row Estimate Mismatches

The most common performance bug is a bad row estimate. When the planner thinks 10 rows will come back but 1 million actually do, it picks a Nested Loop when it should use a Hash Join.

-- Find tables with stale statistics
SELECT schemaname, tablename, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000 AND (last_analyze IS NULL OR last_analyze < now() - interval '1 day');

[!NOTE] Run ANALYZE after bulk loads. Compare EXPLAIN row estimates before and after. A >10x error indicates stale statistics.

Per-Node Timing

-- Enable tracking of individual node timing (PostgreSQL 14+)
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT count(*) FROM large_table WHERE created_at > now() - interval '7 days';

• BUFFERS — shows shared hit/read/dirtied/written blocks per node (disk I/O indicator)
• SETTINGS — shows non-default planner parameters affecting this plan
• TIMING (default on) — per-node timing in ms (adds ~2% overhead)
• SUMMARY — total timings like planning time and execution time

---

All Index Types in Depth

PostgreSQL offers six index methods. Choosing the right one is the single highest-impact optimisation decision.

B-tree (Default)

Best for: Equality (=), range (<, <=, >, >=, BETWEEN), IN, ORDER BY, LIKE 'prefix%', regex with anchored start, IS NULL/IS NOT NULL. Supports multicolumn indexes.

How it works: Balanced tree — leaf pages hold (key, tid) pairs sorted by key. Supports both ascending and descending scans. Each page is ~8 kB.

-- Single-column B-tree (default, can omit USING BTREE)
CREATE INDEX idx_users_email ON users USING BTREE (email);

-- Multicolumn B-tree — order matters!
-- Best for: WHERE a = ? AND b = ? OR WHERE a = ? ORDER BY b
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

-- Partial index — only indexes rows matching the predicate
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- This index is ~5x smaller than a full index on status + created_at

-- Index with INCLUDE (covering index, PostgreSQL 11+)
-- All columns in INCLUDE live in the index but don't affect sort order
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name, avatar_url);
-- Enables Index Only Scan for SELECT name, avatar_url FROM users WHERE email = ?;

-- Descending index for ORDER BY ... DESC
CREATE INDEX idx_events_ts_desc ON events (ts DESC NULLS LAST);

-- Unique index (implicit on PRIMARY KEY / UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_username ON users (username);

[!TIP] Index column order — place the most selective column first (highest cardinality). For WHERE a = ? AND b BETWEEN ? AND ?, index (a, b) — the equality column first, then the range column.

BRIN (Block Range INdex)

Best for: Very large tables (millions to billions of rows) where data is physically correlated with value order — time-series, log tables, append-only event tables, monotonically increasing IDs.

How it works: Summarises each contiguous block of pages (default 128 pages ~1 MB) with the min/max value. The index itself is tiny — often 0.1% of the table size vs. 30-40% for a B-tree.

-- Basic BRIN index
CREATE INDEX idx_events_ts_brin ON events USING BRIN (created_at);

-- BRIN with custom pages_per_range (trade-off: precision vs. size)
-- Smaller pages_per_range = more precise but larger index
CREATE INDEX idx_events_ts_brin_dense ON events USING BRIN (created_at)
WITH (pages_per_range = 32);

-- Larger pages_per_range = smaller index but more false positives
CREATE INDEX idx_events_ts_brin_sparse ON events USING BRIN (created_at)
WITH (pages_per_range = 512);

-- Multicolumn BRIN (order doesn't matter — summarises block, not key)
CREATE INDEX idx_logs_brin ON logs USING BRIN (server_id, logged_at);

-- BRIN on UUID (if inserted in roughly sequential order)
CREATE INDEX idx_sessions_brin ON sessions USING BRIN (session_id)
WITH (pages_per_range = 64);

[!WARNING] BRIN requires correlated physical order. If rows are inserted randomly, BRIN degenerates to "scan everything." Use COPY ... ORDER BY before creating the BRIN index, or use VACUUM + re-insert.

GiST (Generalized Search Tree)

Best for: Geometric data (point, polygon, circle), range types (tsrange, daterange, int4range), full-text search (balanced with GIN), nearest-neighbour (ORDER BY <->), exclusion constraints, and cube/earthdistance extensions.

How it works: Height-balanced tree with a user-defined "penalty" function that decides how to split pages. Supports lossy indexes (the index indicates a superset of matching rows, then rechecks).

-- Geometric queries — find points within a polygon
CREATE INDEX idx_locations_geo ON locations USING GiST (coord);
EXPLAIN ANALYZE SELECT name FROM locations
WHERE coord <@ polygon '((48.85,2.29),(48.87,2.29),(48.87,2.38),(48.85,2.38))';

-- Exclusion constraint: prevent overlapping reservations
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    during TSRANGE NOT NULL,
    EXCLUDE USING GiST (room_id WITH =, during WITH &&)
);

-- Nearest-neighbour search (kNN) — find 10 closest venues
CREATE INDEX idx_venues_coord ON venues USING GiST (coord);
EXPLAIN ANALYZE SELECT name, address, coord <-> point(2.3488, 48.8534) AS dist
FROM venues
ORDER BY coord <-> point(2.3488, 48.8534)
LIMIT 10;

-- Full-text search with GiST (slower to query, faster inserts)
CREATE INDEX idx_docs_fts_gist ON documents USING GiST (to_tsvector('english', body));

GIN (Generalized Inverted Index)

Best for: Full-text search (tsvector), JSONB (@>, ?, ?|, ?&), arrays (any array operator), pg_trgm (trigram ILIKE %foo%), and ltree (hierarchical labels).

How it works: Stores a mapping from component (word, array element, JSONB key) to list of tuples containing it. Supports lossy indexes with a recheck phase (when fastupdate is on).

-- Full-text search (standard use case)
CREATE INDEX idx_articles_fts ON articles USING GIN
    (to_tsvector('english', title || ' ' || body));

EXPLAIN ANALYZE SELECT title FROM articles
WHERE to_tsvector('english', title || ' ' || body)
  @@ to_tsquery('english', 'postgresql & optimisation');

-- JSONB path existence (fast)
CREATE INDEX idx_users_prefs ON users USING GIN (preferences jsonb_path_ops);
EXPLAIN ANALYZE SELECT * FROM users
WHERE preferences @> '{"theme": "dark", "notifications": {"email": true}}';

-- JSONB top-level key existence
CREATE INDEX idx_users_prefs_gin ON users USING GIN (preferences);
SELECT * FROM users WHERE preferences ? 'theme';
SELECT * FROM users WHERE preferences ?| ARRAY['theme', 'layout'];

-- Array column: find users with any of these tags
CREATE INDEX idx_items_tags ON items USING GIN (tags);
SELECT * FROM items WHERE tags && ARRAY['urgent', 'blocked', 'critical'];

-- Trigram index for ILIKE '%substring%'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
EXPLAIN ANALYZE SELECT * FROM products WHERE name ILIKE '%wireless%mouse%';

[!NOTE] jsonb_path_ops creates a smaller (4x) and faster index for @> queries but does NOT support ?, ?|, ?&. Choose jsonb_path_ops if you only use containment (@>). Use the default for general JSONB queries.

SP-GiST (Space-Partitioned GiST)

Best for: Quad-trees (2D point clustering), kd-trees (multidimensional), radix trees (strings with common prefixes — phone numbers, IP addresses, URLs).

How it works: Splits the search space into partitions that are themselves partitioned recursively. Unlike GiST, partitions do not overlap.

-- Quad-tree over 2D points (millions of points, spatially clustered)
CREATE INDEX idx_trajectory_points ON gps_tracks USING SPGiST (coord);

-- K-d tree over multidimensional data
CREATE INDEX idx_sensors_readings ON sensors USING SPGiST (x, y);

-- Radix tree for string prefix search
CREATE INDEX idx_phones_spgist ON phone_directory USING SPGiST
    (phone_number text_ops);
SELECT * FROM phone_directory WHERE phone_number LIKE '+1-212-%';

-- IP address ranges
CREATE INDEX idx_ip_blocks ON ip_geo USING SPGiST (ip_range inet_ops);
EXPLAIN ANALYZE SELECT * FROM ip_geo WHERE ip_range >>= '8.8.8.8'::inet;

Hash Index

Best for: Simple equality comparisons (=) only. No range, no sort, no ORDER BY.

How it works: Applies a hash function to the key, stores the hash + tuple pointer in a bucket. PostgreSQL 10+ — fully crash-safe.

-- Basic hash index
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);

-- Typical use case: large lookup table with exact-match queries
CREATE INDEX idx_product_sku ON products USING HASH (sku);
EXPLAIN ANALYZE SELECT * FROM products WHERE sku = 'SKU-42A-991';

[!WARNING] Hash index limitations — no range queries, no ORDER BY, no unique constraint, no index-only scans. Use cases are rare: B-tree matches = too and adds range/sort for free.

---

Extension Installation and Configuration

All extensions follow a two-step pattern: install the OS package (if required), then CREATE EXTENSION in the target database.

PostgreSQL in Docker

When PostgreSQL runs inside a Docker container, there's no apt or dnf — you have two approaches:

Option A: Pre-built image with extensions (recommended)

Use or extend an image that already has the extensions you need:

# Dockerfile
FROM postgres:16

RUN apt-get update && apt-get install -y --no-install-recommends \
    postgresql-16-postgis-3 \
    postgresql-16-pgvector \
    postgresql-16-cron \
    postgresql-16-pgmq \
    && rm -rf /var/lib/apt/lists/*

# TimescaleDB needs a separate repo
RUN apt-get install -y gnupg postgresql-common \
    && yes | /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh \
    && apt-get update && apt-get install -y timescaledb-2-postgresql-16 \
    && rm -rf /var/lib/apt/lists/*

Or use official extension images directly:

# docker-compose.yml snippet with pre-built images
services:
  # Standard PostgreSQL + contrib
  postgres:
    image: postgres:16

  # TimescaleDB
  timescaledb:
    image: timescale/timescaledb:2-pg16

  # pgvector
  pgvector:
    image: pgvector/pgvector:0.7.0-pg16

Option B: Connect from a sidecar container

If you can't modify the PostgreSQL image, install psql + pgxnclient in a sidecar container and connect to your PostgreSQL:

# Run an admin container that connects to the Postgres service
docker run --rm -it --network my_network \
  postgres:16 sh -c "
    apt-get update && apt-get install -y pgxnclient
    pgxn install pgmq
    psql -h postgres -U myuser -d mydb -c 'CREATE EXTENSION pgmq;'
  "

Enabling shared_preload_libraries

Extensions like pg_cron, pg_stat_statements, and TimescaleDB need shared_preload_libraries set before the container starts. Pass it via command or config file:

# docker-compose.yml
services:
  postgres:
    image: postgres:16
    command: >
      -c shared_preload_libraries=pg_cron,pg_stat_statements,timescaledb
      -c cron.database_name=mydb
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: secret

Or use a custom config file:

services:
  postgres:
    image: postgres:16
    volumes:
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql:ro
    command: -c 'config_file=/etc/postgresql/postgresql.conf'

With init.sql:

-- Runs once on first container start
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS timescaledb;

[!TIP] SQL files placed in /docker-entrypoint-initdb.d/ run automatically once the first time the container starts. Rebuild the container (not just restart) to re-run them.

Checking what's available in your container

# Connect and check
docker exec -it my_postgres psql -U myuser -d mydb -c "
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;
"

# Or via psql in the container
docker exec -it my_postgres psql -U myuser -d mydb
# Then: \dx  -- list installed extensions
#       SELECT * FROM pg_available_extensions;

[!NOTE] pg_available_extensions shows what's available in the container's filesystem (installed by apt-get in the Dockerfile). \dx shows what's currently CREATE EXTENSION'd in this database.

Prerequisites

-- Most extensions live in the contrib package
-- Ubuntu / Debian:
--   sudo apt install postgresql-contrib postgresql-16-postgis postgresql-16-pgvector timescaledb-2-postgresql-16

-- RHEL / Rocky / Alma:
--   sudo dnf install postgresql16-contrib postgresql16-postgis postgresql16-pgvector

-- Verify available extensions
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

PostGIS (Geolocation / Spatial)

CREATE EXTENSION postgis;
SELECT postgis_full_version();

CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(Point, 4326)  -- WGS84 lon/lat
);
CREATE INDEX idx_places_location ON places USING GiST (location);

-- Distance query (in meters)
SELECT name,
       ST_Distance(location, ST_SetSRID(ST_MakePoint(2.3488, 48.8534), 4326)::geography) AS dist_m
FROM places
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(2.3488, 48.8534), 4326)::geography, 5000)
ORDER BY location <-> ST_SetSRID(ST_MakePoint(2.3488, 48.8534), 4326)::geography
LIMIT 20;

-- Spatial join: find all POIs inside a district polygon
SELECT p.name, d.name AS district
FROM places p
JOIN districts d ON ST_Within(p.location::geometry, d.boundary);

[!TIP] PostGIS GEOMETRY vs. GEOGRAPHY — GEOMETRY is planar math (faster, flat Earth, use for small areas). GEOGRAPHY is spherical math (accurate globally, slower).

pgvector (Embeddings / Vector Search)

CREATE EXTENSION vector;

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

-- Approximate nearest neighbour index (IVFFlat)
CREATE INDEX idx_documents_embedding ON documents
USING IVFFLAT (embedding vector_cosine_ops) WITH (lists = 100);

-- HNSW index (PostgreSQL 16+, more accurate, slower to build)
CREATE INDEX idx_documents_embedding_hnsw ON documents
USING HNSW (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 200);

-- Cosine similarity search
SELECT id, content, 1 - (embedding <=> '[0.0012, -0.034, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.0012, -0.034, ...]'::vector
LIMIT 10;

-- Distance types:
--   <->   Euclidean (L2)
--   <=>   Cosine
--   <#>   Inner product (negative dot product)

-- Tune IVFFlat probes at query time
SET ivfflat.probes = 10;

TimescaleDB (Time-Series)

CREATE EXTENSION timescaledb;

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);
SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => interval '1 day');

-- Time-series aggregation
SELECT time_bucket('15 minutes', time) AS bucket,
       sensor_id, AVG(temperature) AS avg_temp
FROM sensor_data
WHERE time > now() - interval '7 days'
GROUP BY bucket, sensor_id ORDER BY bucket;

-- Continuous aggregate (materialised downsampling)
CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket, sensor_id,
       AVG(temperature) AS avg_temp, MAX(temperature) AS max_temp
FROM sensor_data GROUP BY bucket, sensor_id;

pgmq (Message Queue — Redis-like)

CREATE EXTENSION pgmq;

-- Create a queue
SELECT pgmq_create('task_queue');

-- Send messages
SELECT pgmq_send('task_queue', '{"task": "process_invoice", "invoice_id": 4242}');

-- Read messages (non-destructive, 30s visibility timeout)
SELECT * FROM pgmq_read('task_queue', 10, 30);

-- Pop messages (destructive — dequeue)
SELECT * FROM pgmq_pop('task_queue');

-- Archive a processed message
SELECT pgmq_archive('task_queue', msg_id => 1);

-- Monitor queue depth
SELECT pgmq_queue_length('task_queue');

[!NOTE] pgmq vs. Redis — pgmq keeps messages in PostgreSQL tables: zero extra infrastructure, transactional guarantees. At high throughput (>10K msg/s), Redis is faster. At <1K msg/s with ACID guarantees, pgmq wins.

pg_cron (Scheduled Jobs)

CREATE EXTENSION pg_cron;

-- Run a VACUUM every night at 02:00
SELECT cron.schedule('nightly-vacuum', '0 2 * * *', 'VACUUM ANALYZE');

-- Refresh a materialised view every hour
SELECT cron.schedule('refresh-analytics', '0 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_analytics$$);

-- List scheduled jobs
SELECT * FROM cron.job;

-- Pause a job
SELECT cron.unschedule('nightly-vacuum');

[!WARNING] pg_cron requires shared_preload_libraries = 'pg_cron' in postgresql.conf and a PostgreSQL restart.

uuid-ossp (UUID Generation)

CREATE EXTENSION "uuid-ossp";

SELECT uuid_generate_v4();  -- Random UUID
SELECT uuid_generate_v1();  -- Time-based + MAC

-- Alternative (PostgreSQL 13+, no extension needed):
SELECT gen_random_uuid();

-- Use as primary key default
CREATE TABLE accounts (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL
);

hstore (Key-Value Store)

CREATE EXTENSION hstore;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attrs HSTORE
);

INSERT INTO products VALUES (1, 'Widget', 'color => red, weight => 1.5');
SELECT * FROM products WHERE attrs ? 'color';
SELECT * FROM products WHERE attrs @> 'color => red';

-- GIN index for hstore
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

[!NOTE] hstore is a legacy extension (pre-JSONB). JSONB supersedes it for almost all use cases. hstore is still useful for very simple text-to-text key-value pairs with slightly lower overhead.

---

Optimization Techniques

VACUUM

PostgreSQL's MVCC creates dead tuples when rows are updated or deleted. VACUUM reclaims that space.

-- Standard vacuum (safe, concurrent)
VACUUM orders;

-- Full vacuum (exclusive lock — blocks reads/writes, use offline)
VACUUM FULL orders;

-- Verbose — see what it did
VACUUM VERBOSE ANALYZE orders;

-- Check dead tuple ratio per table
SELECT relname, n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY dead_pct DESC NULLS LAST;

[!WARNING] For zero-downtime bloat reclaiming without exclusive locks, use pg_repack: CREATE EXTENSION pg_repack; pg_repack --table orders --dbname mydb

ANALYZE

Updates table statistics that the query planner depends on.

-- Analyze a single table
ANALYZE orders;

-- Analyze specific columns (faster for large tables)
ANALYZE orders (status, created_at, user_id);

-- Check when each table was last analyzed
SELECT schemaname, tablename, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 0
ORDER BY n_mod_since_analyze DESC;

Autovacuum Tuning

-- Check current autovacuum settings
SELECT name, setting, unit, short_desc
FROM pg_settings WHERE name LIKE 'autovacuum%';

-- Per-table override: table with heavy writes
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- vacuum when 1% of rows dead
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_cost_limit = 2000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_analyze_threshold = 500
);

-- Disable autovacuum for append-only tables
ALTER TABLE logs SET (autovacuum_enabled = false);

pg_stat_statements (Query Performance Monitoring)

-- Enable (requires shared_preload_libraries = 'pg_stat_statements' + restart)
CREATE EXTENSION pg_stat_statements;

-- Top 10 queries by total execution time
SELECT substring(query, 1, 80) AS query_short,
       calls, total_exec_time / 1000 AS total_sec,
       mean_exec_time AS mean_ms, rows,
       shared_blks_hit + shared_blks_read AS total_blks,
       100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- Queries that spill to temp (work_mem too small for sorts/hashes)
SELECT substring(query, 1, 80), calls, temp_blks_written,
       temp_blks_written / NULLIF(calls, 0) AS avg_temp_per_call
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 10;

-- Reset statistics (for benchmarking)
SELECT pg_stat_statements_reset();

Connection Pooling (PgBouncer)

Each connection uses ~5-10 MB of memory. With 1000 connections, that's 5-10 GB before any query runs.

-- Check current connections
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Kill idle connections (before maintenance)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND state_change < now() - interval '30 minutes';

PgBouncer config (/etc/pgbouncer/pgbouncer.ini):

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 50
max_client_conn = 500
max_db_connections = 100
idle_transaction_timeout = 60

Memory Tuning

Key postgresql.conf parameters for a dedicated database server:

# --- Memory ---
shared_buffers = 8GB              # 25% of RAM (max ~8GB on Linux without huge pages)
effective_cache_size = 24GB       # 75% of RAM (tells planner how much OS cache is available)
work_mem = 64MB                   # per-operation sort/hash memory
maintenance_work_mem = 2GB        # VACUUM, CREATE INDEX

# --- Parallel queries ---
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# --- Planner ---
random_page_cost = 1.1            # 4.0 default is for HDDs — set to 1.1 for SSDs!

# --- WAL ---
wal_buffers = 64MB
max_wal_size = 16GB
checkpoint_completion_target = 0.9

# --- Autovacuum ---
autovacuum_max_workers = 3
autovacuum_vacuum_cost_limit = 2000

[!TIP] Set random_page_cost = 1.1 (SSD) or 1.0 (NVMe). The default 4.0 assumes spinning rust. With SSDs, random reads are almost as fast as sequential — the planner will favour index scans over Seq Scan.

Query Anti-Patterns

-- BAD: Function call in WHERE prevents index use
SELECT * FROM orders WHERE date_trunc('day', created_at) = '2024-06-01';
-- GOOD: Range query (index-friendly)
SELECT * FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02';

-- BAD: Non-sargable LIKE
SELECT * FROM products WHERE name LIKE '%search_term%';
-- GOOD: Trigram index
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%search_term%';

-- BAD: SELECT * with many columns (wider than index can cover)
SELECT * FROM orders WHERE status = 'pending';
-- GOOD: Only fetch needed columns
SELECT id, amount, created_at FROM orders WHERE status = 'pending';

-- BAD: COUNT(*) on large table without filter
SELECT count(*) FROM logs;
-- GOOD: Use estimate from pg_class
SELECT reltuples::bigint AS estimated_count FROM pg_class WHERE relname = 'logs';

---

Performance Health Check Checklist

-- 1. Cache hit ratio (should be > 99% for hot data)
SELECT 'index hit' AS type,
       100.0 * sum(idx_blks_hit) / nullif(sum(idx_blks_hit + idx_blks_read), 0) AS hit_pct
FROM pg_statio_user_indexes
UNION ALL
SELECT 'table hit',
       100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0)
FROM pg_statio_user_tables;

-- 2. Index usage (tables with most seq scans)
SELECT schemaname, tablename, seq_scan, idx_scan,
       round(100.0 * idx_scan / nullif(seq_scan + idx_scan, 0), 2) AS idx_scan_pct
FROM pg_stat_user_tables
ORDER BY idx_scan_pct ASC LIMIT 10;

-- 3. Dead tuple ratio (need VACUUM)
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_pct DESC;

-- 4. Longest running queries
SELECT pid, now() - query_start AS duration, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle' AND query_start < now() - interval '30 seconds'
ORDER BY duration DESC;

-- 5. Unused indexes (idx_scan < 10)
SELECT schemaname, tablename, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan < 10 AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 6. Temporary file usage (work_mem too small)
SELECT datname, temp_files, temp_bytes, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database WHERE temp_files > 0 ORDER BY temp_files DESC;

---

Summary Decision Matrix

Index Type	Best For	Don't Use For
B-tree	Equality, range, ORDER BY, prefix LIKE	Low-cardinality columns, LIKE '%foo%'
BRIN	Time-series, append-only logs (correlated order)	Random-order inserts, small tables
GiST	Geometry, range types, kNN, exclusion constraints	Equality-only lookups
GIN	Full-text search, JSONB, arrays, trigram ILIKE	Write-heavy workloads (slow inserts)
SP-GiST	Quad-trees, radix trees, IP ranges	General-purpose, nearest neighbour
Hash	Equality only (rare)	Range queries, ORDER BY, unique constraints
pgvector IVFFlat	Large vector embeddings (>100K rows)	Small datasets (Seq Scan is faster)
pgvector HNSW	High-accuracy vector search	Build-time sensitive workloads

---

Further Reading

• PostgreSQL documentation: Performance Tips
• Using EXPLAIN — official walkthrough
• pgMustard EXPLAIN visualiser — colour-coded plan diagram
• depesz EXPLAIN analyser — paste plan, get formatted breakdown
• PgBouncer docs — connection pool configuration reference
• PostgresqlCO.NF — parameter tuning calculator by server specs
• TimescaleDB docs
• pgvector GitHub
• PostGIS documentation