Postgresql Performance Tuning

This deck is a focused review of PostgreSQL performance tuning, covering the questions that come up most often when you want a database to run faster. You'll find cards on diagnosing slow queries with system statistics and pg_stat_statements, reading EXPLAIN plans, choosing the right index type for different workloads (B-tree, GIN, GiST, BRIN, and JSONB-specific expressions), and keeping tables healthy through vacuuming, analyzing, and managing bloat. Together these topics form a practical toolkit for finding and fixing the most common sources of sluggishness in a Postgres instance.

The material is best suited for developers and DBAs who already have some day-to-day experience with PostgreSQL and want a more structured way to deepen their knowledge. If you're preparing for an interview, tuning a production system, or simply want to stop guessing about why a query is slow, these cards will help you build a clear mental model of where to look and what to try first.

Because many of the questions hinge on understanding relationships between concepts (for example, the trade-off between a sequential scan and an index scan, or the difference between VACUUM and VACUUM FULL), spaced repetition is especially effective here. Try short review sessions over several days rather than cramming everything at once, and when you get a card wrong, take a moment to picture the underlying scenario before moving on. Pair the deck with a local Postgres instance so you can run EXPLAIN, create sample indexes, and watch autovacuum in action, turning each flashcard into something you can verify in practice.

PostgreSQL performance tuning almost always starts with finding the right queries to fix. The canonical tool is the pg_stat_statements extension, which records normalized query texts along with execution counts and timing statistics—parameters are replaced by placeholders such as $1, $2 and constant literals are often merged so similar statements collapse into one row. Once enabled, a query selecting query, calls, total_exec_time, and mean_exec_time ordered by total_exec_time desc surfaces the top offenders. Pair this with pg_stat_user_tables to compare seq_scan versus idx_scan counts and with statement logging via log_min_duration_statement (for example, set to 1000 ms) to catch anything that escapes the statistics view.

When you have a specific query in hand, EXPLAIN and its variants are how you understand what the planner is doing. Plain EXPLAIN only shows the planner's estimated plan. EXPLAIN ANALYZE actually executes the query and overlays real timings plus row counts, which lets you compare predicted versus actual. EXPLAIN (BUFFERS, ANALYZE) goes further by reporting buffer hits and misses, separating work served from cache against physical I/O. Plans are read bottom-up: the leaf nodes (sequential or index scans) execute first, then joins, then aggregates; high cost, row, or actual time values at any node are the place to investigate. Stale statistics are a common reason the planner chooses badly, so running ANALYZE on the table often resolves sudden plan flips.

The choice between a sequential scan and an index scan is a recurring motif. Even when an index exists, the planner will sometimes prefer a sequential scan because the query is expected to return a large fraction of the table; random I/O through the index would cost more than reading pages sequentially. For queries that return many rows scattered across the heap, the planner may instead choose a Bitmap Heap Scan, which first builds an in-memory bitmap of pages to visit and then reads them in physical order. For low-cardinality predicates where the planner underestimates selectivity, SET enable_seqscan = off is occasionally useful as a debugging aid, but the real fix is usually better statistics or a rewritten query. Common plan smells include ORDER BY random() LIMIT n, which forces a full sort of the whole table, and IN (SELECT ...) subqueries that the planner flattens into a hash semi-join with poor selectivity—both are usually better rewritten as EXISTS or LEFT JOIN ... WHERE NULL patterns.

Indexes are usually the highest-leverage tuning tool in PostgreSQL. The default access method is B-tree, created simply with CREATE INDEX ON t (col), and it serves equality and range queries well. For specialized data, other access methods shine: GIN supports full-text search on to_tsvector columns, JSONB containment (@>), and array membership; GiST handles geometric data, range types, and array containment as well. JSONB key lookups are best served by CREATE INDEX ON t USING GIN (col jsonb_path_ops), which is smaller and faster than the default jsonb_ops opclass for path-containment queries.

A few important patterns refine how indexes behave. A partial index adds a WHERE clause to make it smaller and faster, like CREATE INDEX active_users_email ON users(email) WHERE deleted_at IS NULL; it will only be picked up by queries whose WHERE matches exactly. A unique index that should ignore NULLs is implemented as a partial unique index: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL, since PostgreSQL otherwise treats NULLs as distinct. A covering index uses CREATE INDEX ... INCLUDE (col) to attach extra columns to the leaf level, enabling index-only scans. Such scans require the visibility map to indicate that all tuples on the page are visible to all transactions, which in turn requires reasonably fresh VACUUM work. Finally, when designing a multi-column index, place the most selective and most equality-checked column first and respect the "equality before range" rule—if your WHERE does not include the leading column, a B-tree index cannot pick the query up except through loose-index-scan tricks.

Finding which indexes are missing or unused closes the loop. In pg_stat_user_tables, tables with many seq_scan events and few idx_scan events suggest opportunities, especially when combined with high-mean-time queries found in pg_stat_statements. Unused indexes themselves are easy to list by selecting relname and indexrelname from pg_stat_user_indexes WHERE idx_scan = 0, although unique and primary-key indexes that merely enforce constraints should be retained even if unused. Foreign-key columns deserve their own index to keep cascades from doing full child-table scans, and a slow-write workload with many zero-scan indexes is a classic symptom of over-indexing. A common JSONB pitfall to remember is that a GIN index answers containment but not arbitrary equality on a nested path; for that, an expression index on the specific key is the right tool.

Two maintenance commands, ANALYZE and VACUUM, are central to keeping PostgreSQL fast. ANALYZE refreshes the row-count and distribution statistics that the planner relies on for cost estimates. It runs automatically after autovacuum, but a manual ANALYZE table_name is often worthwhile after very large UPDATE/DELETEs or partition swaps. The depth of sampling is controlled by default_statistics_target (default 100); raising this to 1000 or more for high-cardinality columns where the planner consistently underestimates can materially improve plan quality. On partitioned tables, statistics live per partition, and on PostgreSQL 14+ also on the parent—ANALYZE should be run after partition operations to keep the planner from going stale. Stale stats are a frequent cause of plan instability: the same query may flip between plans as autovacuum/ANALYZE updates estimates. For situations where a stable plan is essential and the planner's choices are unreliable, the server-level plan_cache_mode can be set to force_generic_plan or force_custom_plan, and judicious use of prepared statements will keep a plan cached server-side.

VACUUM reclaims dead tuples left behind by UPDATE/DELETE and updates the visibility map, without blocking readers or writers. VACUUM FULL is more aggressive: it rewrites the table and returns space to the operating system, but it takes an ACCESS EXCLUSIVE lock and is inappropriate on a busy system. pg_repack is the practical alternative that rewrites a table online. Bloat is the symptom of insufficient vacuuming—dead tuples and free space inflate reads—and can be detected with the pgstattuple extension or by inspecting pg_stat_user_tables. Index bloat is similarly easy to spot with pgstattuple on the index, or with queries that compare current index size against an expected size from row count. Run REINDEX CONCURRENTLY for index bloat during a maintenance window; it cannot run inside a transaction, but it rebuilds the index in parallel and swaps it in with minimal disruption and requires only extra disk space.

HOT (Heap-Only-Tuple) updates are a significant performance win when updates fit on the same page and do not modify indexed columns, since no index entry needs to change. Autovacuum itself can be tuned: the setting autovacuum_vacuum_scale_factor (and the per-table storage parameter) controls the dead-tuple threshold that triggers a run; for hot, write-heavy tables, lower values make vacuum more responsive. Live progress is visible in pg_stat_progress_vacuum. Long-running transactions can block autovacuum entirely because the vacuum worker cannot reclaim tuples visible to any active snapshot; idle-in-transaction sessions are a common cause, found via SELECT * FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)'). Note that ANALYZE alone may not be enough after a huge UPDATE: although it catches the stats up, the pages themselves remain bloated, so a follow-up VACUUM to update the visibility map is often warranted. A broader concern is transaction wraparound: PostgreSQL's 32-bit transaction IDs wrap around at roughly four billion, and the database will shut down to protect data if autovacuum falls behind on freezing. Monitor with SELECT datname, age(datfrozenxid) FROM pg_database, warn above about 1.5 billion, and treat anything approaching 2 billion as urgent.

PostgreSQL's concurrency model is MVCC, in which each row version carries the inserting transaction's xmin and the deleting transaction's xmax. A transaction's snapshot decides which versions are visible, and old versions become garbage that VACUUM eventually reclaims. This model lets readers and writers proceed without blocking each other, but it does not eliminate lock waits when multiple transactions contend for the same row. Lock waits surface in pg_stat_activity under wait_event_type = 'Lock' and can be cross-referenced with pg_locks joined against pg_stat_activity for diagnosis.

When a query is blocked, the first line of defense is pg_cancel_backend(pid), which asks the backend to abort its current query; if that signal is ignored, the stronger pg_terminate_backend(pid) closes the entire connection. Two recurring causes of lock problems deserve attention. The first is deadlocks from transactions touching rows in different orders—a classic anti-pattern is updating two accounts by primary key in opposite directions; the fix is to always acquire locks in a stable order, typically by primary key. The second is idle-in-transaction sessions, in which a connection has opened a transaction and never committed or rolled back, holding row locks and blocking vacuum progress. The idle_in_transaction_session_timeout setting is a safety net: it kills such sessions after a configurable interval, and you can also hunt them proactively with SELECT pid, state, age(now(), xact_start) FROM pg_stat_activity WHERE state = 'idle in transaction'.

Isolation level affects which anomalies can occur. PostgreSQL defaults to READ COMMITTED, which is sufficient for most workloads. When stricter guarantees are needed, SET TRANSACTION ISOLATION LEVEL SERIALIZABLE prompts the database to detect serialization conflicts, but applications must be prepared to retry on the 40001 SQLSTATE. AUTOCOMMIT is on by default at the client driver level; every statement runs in its own transaction unless wrapped in BEGIN..COMMIT, which means accidental idle-in-transaction behavior usually traces back to drivers or middlewares holding implicit transactions across multiple statements. For bounding long-running statements broadly, statement_timeout can be set per role with ALTER ROLE app SET statement_timeout = '30s', with per-session overrides available as well.

A few settings carry most of the weight on a tuned PostgreSQL server. shared_buffers controls the database's own page cache, with a common starting point of about 25% of RAM on dedicated servers, leaving the rest to the operating system page cache. effective_cache_size is a planner hint rather than an allocation—it tells the optimizer how much OS plus database cache is realistically available, typically 50–75% of RAM—and influences index-scan versus sequential-scan decisions. work_mem is per-operation memory for sorts and hash joins; setting it high enough to avoid disk spills on complex queries yet low enough that a hundred concurrent queries do not exhaust RAM is the art, with 16–64 MB being typical. Per-session temp-table work benefits from raising temp_buffers. For diagnosing whether the database is I/O- or cache-bound, track_io_timing costs only a small overhead but unlocks I/O timing in EXPLAIN (BUFFERS, ANALYZE) and pg_stat_statements; pg_buffercache lets you inspect what is currently resident in shared_buffers; pg_stat_io (PG16+) reports per-backend I/O patterns; and a quick sanity check of cache hit rate can be done by computing sum(heap_blks_hit) over sum(heap_blks_hit) + sum(heap_blks_read) over pg_statio_user_tables, with ratios above 0.99 being desirable.

Connection management is the other big lever. PostgreSQL forks a backend process per connection, so large numbers of clients create context-switch and memory pressure. PgBouncer or an application-level pool is the standard answer. PgBouncer offers three pool modes: session, in which a client owns a connection until disconnect; transaction, where the connection is released back to the pool at COMMIT (the most efficient for typical OLTP workloads); and statement, in which the connection is reused after every single statement but which breaks features like prepared statements and session-level settings. The server-side cap is max_connections, adjustable globally and per-database or per-role via ALTER DATABASE / ALTER ROLE. Tablespaces offer another knob: hot tables can be placed on fast SSD storage while archive tables live on cheaper disks, all within a single PostgreSQL cluster. Connection limits and authentication are governed by pg_hba.conf, which decides who can connect from where using what method (trust, md5, scram-sha-256, peer, cert, ident), with scram-sha-256 being the modern default over md5 because it never transmits the password and provides a mutual challenge.

When a single server isn't enough, the first move is usually read scaling via streaming replication. A primary publishes its write-ahead log (WAL) to one or more replicas, which replay the stream and serve read traffic—routing SELECTs to replicas with awareness of replication lag. The essentials on the primary are wal_level set at least to replica, max_wal_senders sized to accommodate the replicas, and archive_mode on if you want point-in-time recovery. The standby is seeded from pg_basebackup, then connects using primary_conninfo in postgresql.auto.conf. Asynchronous replication keeps commit latency low but accepts possible data loss on failover; synchronous replication waits for at least one standby to acknowledge before COMMIT, trading latency for durability. Both are physical, byte-for-byte copies. Logical replication, by contrast, replicates row changes by primary key and supports cross-version and subset replication, making it useful for migrations and selective feeds. Lag is monitored through pg_stat_replication, where replay_lag shows how far behind a replica is.

Replication slots are convenient but risky: an inactive slot prevents WAL from being deleted, eventually filling the disk, so unused slots should be dropped promptly. WAL bloat can also be caused by a broken archive_command or by very long transactions, both of which hold onto segments until they can be released. PITR is enabled by continuous WAL archiving plus base backups, with restoration picking up at recovery_target_time, recovery_target_xid, recovery_target_lsn, or a named restore point. Tools that orchestrate WAL shipping include pgbackrest, WAL-G, and barman, all of which integrate continuous archiving with base-backup management. Routine backups come in two flavors: pg_dump produces a portable logical SQL dump that works across major versions, while pg_basebackup produces a physical copy suitable for spinning up replicas and restoring onto a compatible major version.

For very large tables—think billions of rows—partitioning is a key tool. A partition key with a clear value distribution, such as a date range, a discrete list like region, or a hash of an identifier, lets PostgreSQL prune irrelevant partitions at planning time and reuse space efficiently. RANGE partitioning on time is the most common because old partitions can be dropped in milliseconds to expire data, and it dramatically reduces vacuum cost since each partition is much smaller than the whole. Hash partitioning is useful for sharding by an identifier when there is no natural range, while LIST partitioning suits discrete keys like region. Maintenance includes DROP PARTITION for retention and ensuring ANALYZE runs after big partition operations so the planner has fresh statistics. Continuous aggregates and retention policies in TimescaleDB provide a higher-level, time-series–optimized alternative built on top of partitioning.

Many schema design choices have surprisingly large performance consequences. UUID primary keys of the v4 flavor are random and cause B-tree fragmentation because newly inserted rows land at unrelated pages; switching to UUID v7 (time-ordered) or pairing with a sortable column greatly improves insert locality. SERIAL is the old way to create auto-incrementing keys via a sequence plus a default; IDENTITY, written as GENERATED BY DEFAULT AS IDENTITY, is the SQL-standard equivalent and has cleaner permissions. A common footgun occurs when manually inserting into a serial column without invoking nextval, which lets the sequence lag behind MAX(id); a quick SELECT setval('seq', (SELECT MAX(id) FROM t)) realigns them. Generated columns, declared as colname int GENERATED ALWAYS AS (other_col * 2) STORED, work well as index expressions provided the underlying expression is truly immutable—a function index on now() fails because now() is STABLE rather than IMMUTABLE. Function volatility matters generally: IMMUTABLE functions may be inlined into index expressions and precomputed, STABLE functions may be inlined within a transaction, and VOLATILE functions must be re-evaluated for every row and cannot appear in indexes.

Several query idioms deserve particular attention. CTEs before PostgreSQL 12 were optimization fences: the planner could not push predicates into them. CTEs are inlined by default in PG12+, which is usually a win; if the old behavior is needed, prefix with MATERIALIZED. LATERAL joins evaluate a subquery per outer row, which is the natural shape for top-N-per-group queries. Window functions are the standard way to compute running totals—a pattern such as SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING) walks forward without collapsing rows. DISTINCT ON (col) ORDER BY col, ts is the idiomatic PostgreSQL way to pick a single representative row per group; ROW_NUMBER() OVER (PARTITION BY ...) WHERE rn = 1 is the portable equivalent. Random sampling via ORDER BY random() LIMIT n sorts the entire table and is almost always the wrong approach—TABLESAMPLE BERNOULLI(1) or a pre-sampled random column does the same job without sorting. ON CONFLICT (key) DO NOTHING or DO UPDATE SET ... makes inserts idempotent.

Online migrations of large tables are a recurring need. Adding a NOT NULL column with a default is metadata-only from PostgreSQL 11 onward, so the alter itself is instant; the real work is the backfill, which must be batched (UPDATE ... WHERE id BETWEEN ... with commits between batches) with pauses for VACUUM to keep bloat under control. Adding a foreign key to a busy table can be done without a long write-blocking scan by creating the constraint NOT VALID and then running VALIDATE CONSTRAINT in a second step; row-level security via CREATE POLICY ... USING (...) plus ALTER TABLE ... ENABLE ROW LEVEL SECURITY is how multi-tenant filtering is enforced cleanly. Replacing a giant table atomically is the rename-swap pattern: build the new table, populate it (perhaps with both writes going to old and new during a transition), then in a single transaction rename old to a backup name and new to the production name. Foreign-data wrappers such as postgres_fdw and file_fdw expose remote data as if it were local tables.

Beyond core PostgreSQL, a few extensions shape specialized workloads. TimescaleDB fits time-series data with hypertables, continuous aggregates, and retention policies. pgvector adds k-nearest-neighbor search through IVFFlat and HNSW indexes. JSONB deserves one final word: a GIN index answers containment (@>) efficiently, but arbitrary equality on a nested key is not indexed—build an expression index on the specific path for that pattern.