Almost every slow PostgreSQL database is slow for one of six reasons: a missing or unusable index, a plan built on stale statistics, an N+1 access pattern in the application, too many connections fighting over the same cores, dead tuples that autovacuum never cleaned up, or a query that honestly has to read a lot of data. The order of operations is always the same. Measure with pg_stat_statements to find which query costs the most total time. Run EXPLAIN (ANALYZE, BUFFERS) on that query and look for the node where estimated rows and actual rows diverge, or where a filter throws away most of what it read. Change one thing. Measure again.
Key Takeaways
- Find the query first. Sort
pg_stat_statementsbytotal_exec_time, not by mean. A 4 ms query run two million times beats a 3-second report run twice a day. - Read four numbers in every plan node. Estimated vs actual rows, actual time times loops, buffers read, and rows removed by filter.
- Index shape matters more than index type. Column order, partial predicates and expression indexes decide whether the index can be used at all.
- Connections are processes. Pool with PgBouncer in transaction mode and keep the database-side pool small.
- Bloat is invisible until it is not. A long-running transaction can stop every vacuum in the cluster from reclaiming a single row.
What follows is that method in detail, with the SQL you can paste into psql today. It assumes PostgreSQL 14 or newer; where a behavior changed in PostgreSQL 17 or 18, the text says so. Nothing here requires a paid tool.
Find the Slow Query Before You Tune Anything
Enable pg_stat_statements, then rank queries by cumulative execution time rather than by average. The extension normalizes literals so the same statement shape aggregates into one row, which is the only way to see that a fast query is expensive in aggregate.
Guessing at which query is slow wastes more engineering time than any other habit in database work. PostgreSQL ships a statistics collector for exactly this, but it is off by default because it needs to be loaded at startup.
# requires a restart
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
track_io_timing = on
# then, once per database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Once it has collected a day of traffic, this is the query that matters. It ranks by total time spent, and it shows the buffer cache hit rate per statement, which tells you immediately whether a query is CPU-bound or reading from storage.
SELECT
substr(query, 1, 90) AS query,
calls,
round(total_exec_time::numeric) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
round(100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- reset the counters after a fix so the next reading is clean
SELECT pg_stat_statements_reset();
Two supporting tools are worth turning on at the same time. log_min_duration_statement = '500ms' writes every slow statement to the server log with its parameters. The auto_explain module goes further and logs the actual plan, which saves you from trying to reproduce a slow plan by hand hours later.
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.log_timing = off # timing instrumentation is the expensive part
The Ranking Trap
Engineers reflexively sort by mean_exec_time and go fix the 8-second report nobody runs. Total time is what the server actually spends. A lookup averaging 4 ms called two million times a day is 8,000 seconds of database CPU; the 3-second report called twice is 6 seconds. Fix the first one.
How to Read EXPLAIN ANALYZE Output
EXPLAIN shows the plan the planner chose and what it expected. EXPLAIN ANALYZE runs the query and adds what actually happened. Read the plan from the innermost indented node outward, and at every node compare four numbers: estimated rows against actual rows, actual time multiplied by loops, buffers read, and rows removed by filter.
EXPLAIN ANALYZE executes the statement for real, including writes. Wrap anything that modifies data in a transaction you roll back.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
UPDATE orders SET status = 'shipped' WHERE id = 98213;
ROLLBACK;
-- PostgreSQL 18 prints BUFFERS with ANALYZE by default.
-- On 17 and earlier you have to ask for it. Always ask for it.
-- SETTINGS lists any planner GUC that is not at its default,
-- which catches the session that quietly set enable_seqscan = off.
Here is a plan of the kind that shows up when a query has quietly lost its index. Read it bottom-up.
Limit (cost=0.43..812.10 rows=25)
(actual time=0.052..1842.117 rows=25 loops=1)
Buffers: shared hit=1204 read=91455
-> Nested Loop (cost=0.43..64903.22 rows=1998)
(actual time=0.051..1842.093 rows=25 loops=1)
-> Seq Scan on orders o (cost=0.00..48211.00 rows=1998)
(actual time=0.021..1836.402 rows=25 loops=1)
Filter: (status = 'refunded'::text)
Rows Removed by Filter: 1874553
Buffers: shared read=91312
-> Index Scan using customers_pkey on customers c
(cost=0.43..8.35 rows=1)
(actual time=0.002..0.002 rows=1 loops=25)
Index Cond: (id = o.customer_id)
Planning Time: 0.214 ms
Execution Time: 1842.203 ms
The sequential scan on orders looked at 1,874,578 rows and kept 25. It read 91,312 blocks, which at 8 kB per block is roughly 715 MB of I/O to produce twenty-five rows. A partial index on status turns that node into a handful of block reads. Note also that the index scan reports loops=25: its 0.002 ms is per loop, so its real contribution is 25 times that, not 0.002 ms.
These are the node types you will meet, and what to watch for in each.
| Plan node | What it does | Healthy when | Warning sign in the output |
|---|---|---|---|
| Seq Scan | Reads the whole table | Small table, or the query wants most rows | High Rows Removed by Filter |
| Index Scan | Walks the index, fetches each heap row | Selective predicate, few rows out | Huge loops under a nested loop |
| Index Only Scan | Answers entirely from the index | All needed columns are in the index | Heap Fetches in the thousands means vacuum is behind |
| Bitmap Index + Heap Scan | Collects row locations, then reads pages in physical order | Medium selectivity, thousands of rows | Heap Blocks: lossy= means work_mem was too small |
| Nested Loop | For each outer row, probe the inner side | Outer side is genuinely tiny | Estimated 200 rows, actual 400,000 |
| Hash Join | Builds a hash of the smaller input | Both sides large, join key not indexed | Batches: 8 means it spilled to disk |
| Sort | Orders rows for ORDER BY, merge join, or grouping | Sort Method: quicksort Memory: |
external merge Disk: 82304kB |
PostgreSQL Index Types and When Each One Helps
Core PostgreSQL ships six index access methods. B-tree is correct for roughly 90% of cases. GIN is for columns holding many values inside one row, such as jsonb, arrays and full-text vectors. GiST covers ranges, geometry and nearest-neighbor. BRIN is for enormous tables whose physical order tracks the column value. Hash and SP-GiST are narrow specialists.
| Type | Handles | Reach for it when | Cost |
|---|---|---|---|
| B-tree | Equality, ranges, sorting, uniqueness | Default. Anything scalar and ordered | Moderate size, cheap writes |
| GIN | jsonb containment, arrays, tsvector, trigrams | One row holds many searchable values | Large index, slower inserts |
| GiST | Ranges, geometry, nearest-neighbor, exclusion constraints | PostGIS, tstzrange overlap checks | Lossy, needs recheck |
| BRIN | Block-range summaries of ordered data | Append-only time series in the hundreds of GB | Tiny, but useless without correlation |
| Hash | Equality only | Rarely. B-tree already does equality | Slightly smaller on long keys |
| SP-GiST | Quadtrees, radix trees, text prefixes | Non-balanced or partitioned key spaces | Specialist |
Choosing the type is the easy part. The shape of the index decides whether the planner can use it at all, and four shapes cover most real work.
-- 1. Multicolumn: equality columns first, ordering column last.
-- Serves: WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 50
-- The reverse order, (created_at, tenant_id), does not.
CREATE INDEX CONCURRENTLY idx_orders_tenant_created
ON orders (tenant_id, created_at DESC);
-- 2. Partial: index only the rows the hot query touches.
-- On a job queue where 99% of rows are done, this index
-- is about 1% of the size of the full one.
CREATE INDEX CONCURRENTLY idx_jobs_pending
ON jobs (queued_at)
WHERE state = 'pending';
-- 3. Expression: required when the predicate wraps the column.
CREATE INDEX CONCURRENTLY idx_users_lower_email
ON users (lower(email));
-- 4. Covering: extra payload columns so the lookup never
-- touches the heap. INCLUDE columns are not part of the key.
CREATE INDEX CONCURRENTLY idx_orders_lookup
ON orders (tenant_id, id) INCLUDE (status, total_cents);
-- Bonus: substring search. A B-tree cannot serve LIKE '%acme%'.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
ON products USING gin (name gin_trgm_ops);
Use CONCURRENTLY on any table that takes writes. It builds without holding a write lock, at the price of taking longer, needing two table passes, and refusing to run inside a transaction block. If it fails partway it leaves an invalid index behind, which is easy to miss and does nothing but consume space.
-- Indexes left broken by a failed CONCURRENTLY build
SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;
-- fix: DROP INDEX CONCURRENTLY <name>; then rebuild
-- Indexes nothing has ever used, largest first
SELECT s.relname AS table_name,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;
Two cautions on that second query. Counters are per instance, so an index unused on the primary may be carrying a reporting workload on a replica. And they reset whenever statistics are reset or the cluster is re-created, so a fresh zero means nothing. Every index you keep is paid for on every insert, update and vacuum of that table.
Why PostgreSQL Ignores Your Index
An index exists and the planner still picks a sequential scan. The usual causes are an unconstrained leading column, a function applied to the column in the WHERE clause, a type mismatch that forces a cast on the column side, stale statistics, or a random_page_cost still set for spinning disks.
The Eight Reasons an Index Goes Unused
- Leading column not constrained. An index on
(tenant_id, created_at)does little forWHERE created_at > $1alone. PostgreSQL 18 added B-tree skip scan, which helps when the leading column has very few distinct values, but do not design around it. - A function wraps the column.
WHERE date(created_at) = '2026-07-01'cannot use an index oncreated_at. Rewrite as a half-open range:created_at >= '2026-07-01' AND created_at < '2026-07-02'. - Type mismatch. Comparing a
bigintcolumn to a text parameter puts the cast on the column, which makes the index inapplicable. Cast the parameter instead. - Low selectivity. If the predicate matches 30% of the table, a sequential scan really is cheaper. The planner is right and the index is the wrong fix.
- Stale statistics. A restore or a bulk load leaves the planner with nothing. Run
ANALYZE. This is the single most common cause after a migration. - random_page_cost is 4.0. That default assumes a random read costs four times a sequential one, which was true of spinning disks. On NVMe, 1.1 to 1.5 is closer, and changing it flips many plans to index scans.
- Correlated columns. The planner assumes independence, so
WHERE city = 'Boston' AND state = 'MA'gets estimated far too low. Extended statistics fix it. - Collation and LIKE.
LIKE 'abc%'only uses a plain B-tree under the C collation; otherwise the index needstext_pattern_ops. A leading wildcard needs a trigram index.
-- Skewed column? Give the planner a bigger histogram.
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;
-- Columns that predict each other: teach the planner the link.
CREATE STATISTICS addr_stats (dependencies, ndistinct)
ON city, state FROM addresses;
ANALYZE addresses;
-- Diagnostic only, never in production code:
-- if turning off seq scans produces a faster plan, the
-- cost model is mis-tuned, not the query.
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_seqscan;
N+1 Queries: The Slowdown That Is Not the Database's Fault
An N+1 pattern runs one query for a list, then one more query per item in that list. Each individual statement is fast, so the database looks healthy while the page takes seconds. The signature in pg_stat_statements is a statement with an enormous calls count and a tiny mean_exec_time.
The arithmetic is what makes it deceptive. One hundred lookups at 0.4 ms of database time is 40 ms of actual work. But it is also one hundred network round trips. If the application server sits 3 ms from the database, that is 300 ms of waiting; at 30 ms, three seconds. The database dashboard shows nothing wrong because nothing is wrong with the database.
# N+1: one query for orders, then one per order for the customer
orders = session.query(Order).limit(100).all()
for o in orders:
print(o.customer.name) # 100 extra round trips
# Fix A - joinedload: a single LEFT JOIN.
# Right for many-to-one (each order has one customer).
from sqlalchemy.orm import joinedload
orders = (session.query(Order)
.options(joinedload(Order.customer))
.limit(100).all())
# Fix B - selectinload: two queries, the second an IN (...).
# Right for one-to-many; a JOIN here multiplies parent rows.
from sqlalchemy.orm import selectinload
orders = (session.query(Order)
.options(selectinload(Order.items))
.limit(100).all())
Django uses select_related("customer") for forward foreign keys and prefetch_related("items") for reverse and many-to-many relations. Rails uses includes(:customer). The distinction is the same everywhere: join when the relation is single-valued, batch with a second IN query when it is multi-valued.
Deep pagination is the sibling problem. LIMIT 20 OFFSET 100000 makes PostgreSQL produce and discard one hundred thousand rows before returning anything, and it gets slower the further a user scrolls. Keyset pagination reads exactly twenty rows at any depth.
-- Page 1
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Every page after: pass the last row's keys back in.
-- The row comparison maps directly onto the index.
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Connection Pooling and Why max_connections Is Not the Lever
PostgreSQL forks an operating system process for every connection. Each backend costs memory before it does any work, and up to work_mem for every sort or hash node it runs. Raising max_connections to 2,000 buys context switching and lock contention, not throughput. Put PgBouncer in front in transaction mode and keep the database-side pool small.
A reasonable starting point for concurrently active backends is about twice the CPU core count, plus a little for storage parallelism. On an eight-core machine with NVMe that is roughly 20 to 40, not 500. Everything above that number is queueing, and queueing inside the database is more expensive than queueing in the pooler.
[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000 # app side: cheap
default_pool_size = 25 # database side: expensive
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60
max_prepared_statements = 200 # PgBouncer 1.21+
Safe, but pools almost nothing
One server connection is held for the client's whole session. Everything works, including SET, advisory locks and LISTEN. You get connection reuse and little else. Use it only when the application genuinely needs session state.
The mode that does the work
The server connection returns to the pool at COMMIT. This is what puts 5,000 clients on 25 backends. It breaks session-scoped features: SET outside a transaction, cursors held across transactions, LISTEN and NOTIFY. Prepared statements work from PgBouncer 1.21 with max_prepared_statements.
Watch the pooler, not just the database. In psql against PgBouncer's admin console, SHOW POOLS; gives you cl_waiting, the number of clients queued for a server connection. Anything consistently above zero means the pool is too small or a query is holding a backend far too long. SHOW STATS; gives per-database averages that pair well with the pg_stat_statements ranking from section one.
VACUUM, Dead Tuples, and Table Bloat
Under MVCC an UPDATE writes a new row version and leaves the old one behind; a DELETE only marks. Nothing is reusable until VACUUM runs. When vacuum falls behind, tables grow, indexes grow, index-only scans stop working because the visibility map is stale, and every scan reads more pages for the same answer.
SELECT relname,
n_live_tup, n_dead_tup,
round(100.0 * n_dead_tup /
nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum, last_autoanalyze, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
Autovacuum triggers at autovacuum_vacuum_threshold (50 rows) plus autovacuum_vacuum_scale_factor (0.2) times the row count. On a 500-million-row table that means one hundred million dead tuples accumulate before autovacuum even starts. The defaults were chosen for tables of a few million rows. Override them per table.
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.01, -- 1%, not 20%
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 0 -- do not throttle this one
);
-- Cluster-wide, if autovacuum never catches up:
-- autovacuum_max_workers = 6
-- autovacuum_vacuum_cost_limit = 2000
Tuning autovacuum is pointless if something is holding back the xmin horizon. A vacuum cannot remove a row version that any open transaction might still need, so one analyst who left a session open at 9 a.m. can make every vacuum in the cluster a no-op all day. Three things do this: long-running or idle-in-transaction sessions, inactive replication slots, and abandoned prepared transactions.
-- Oldest transactions still open
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;
-- Replication slots nobody is consuming
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;
-- Two-phase transactions somebody forgot to commit
SELECT gid, prepared, owner FROM pg_prepared_xacts;
-- Prevention, in postgresql.conf:
-- idle_in_transaction_session_timeout = '60s'
-- statement_timeout = '30s' (raise per session for reports)
Plain VACUUM makes space reusable but does not return it to the filesystem. VACUUM FULL rewrites the table and does return the space, but it holds an ACCESS EXCLUSIVE lock the entire time, blocking reads as well as writes. The pg_repack extension performs the same rewrite online and takes a brief lock only at the swap. For index bloat alone, REINDEX INDEX CONCURRENTLY is usually all you need. Finally, watch transaction age: at autovacuum_freeze_max_age (200 million by default) PostgreSQL forces an anti-wraparound vacuum that will not be deterred.
Partitioning: When It Helps and When It Hurts
Declarative partitioning by RANGE, LIST or HASH does not make queries faster by itself. It helps in three specific situations: dropping old data cheaply, pruning most of the data away because every query filters on the partition key, and making maintenance windows survivable. Outside those, it adds planning overhead and constraint restrictions for nothing.
You expire data on a schedule
Dropping a monthly partition is a catalog operation. The equivalent DELETE creates hundreds of millions of dead tuples and a vacuum storm behind it.
Every query filters on the key
Pruning removes whole partitions at plan time. Confirm it in EXPLAIN: the plan should name one or two partitions, not forty.
Maintenance no longer fits
VACUUM, ANALYZE and REINDEX run per partition. A three-hour job on one table becomes twelve fifteen-minute jobs you can schedule.
When it hurts
Queries that skip the key scan every partition. Unique constraints must include the partition key. Thousands of partitions inflate planning time and lock counts.
CREATE TABLE events (
id bigserial,
tenant_id bigint NOT NULL,
occurred_at timestamptz NOT NULL,
payload jsonb NOT NULL,
PRIMARY KEY (id, occurred_at) -- key must include the partition column
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2026_07 PARTITION OF events
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE events_2026_08 PARTITION OF events
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
-- Indexes declared on the parent propagate to every partition
CREATE INDEX ON events (tenant_id, occurred_at DESC);
-- The plan below should touch exactly one partition.
-- If it lists all of them, pruning failed and so did the design.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE occurred_at >= '2026-07-10'
AND occurred_at < '2026-07-11';
-- Retention becomes instant:
-- ALTER TABLE events DETACH PARTITION events_2025_07 CONCURRENTLY;
-- DROP TABLE events_2025_07;
Automate partition creation with pg_partman or a scheduled job that builds several months ahead. A missing future partition is not a slow query, it is a failed insert at midnight on the first of the month.
The PostgreSQL Settings That Actually Matter
Out of several hundred configuration parameters, about ten change real workloads. The rest are noise for most teams. These are the ten, with defaults, a starting point, and what each one actually does.
| Setting | Default | Starting point | What it does |
|---|---|---|---|
shared_buffers |
128 MB | 25% of RAM | PostgreSQL's own page cache. Needs a restart. The OS cache handles the rest, so more is not always better. |
work_mem |
4 MB | 16–64 MB | Per sort or hash node, per connection. A query with three sorts across 40 backends can want 120 allocations of it. |
maintenance_work_mem |
64 MB | 1–2 GB | Used by CREATE INDEX, VACUUM and ALTER TABLE. Cheap to raise; only a few backends use it at once. |
effective_cache_size |
4 GB | 50–75% of RAM | Allocates nothing. It only tells the planner how much caching to assume, which shifts plans toward index scans. |
random_page_cost |
4.0 | 1.1 on NVMe | The largest single-line win on modern storage. The default assumes spinning disks. |
max_connections |
100 | Keep it low | Pool in front instead. Every slot is a potential process. |
default_statistics_target |
100 | 100, raise per column | Histogram resolution. Raise to 500–1000 on skewed columns rather than globally. |
checkpoint_timeout / max_wal_size |
5 min / 1 GB | 15 min / 8–16 GB | Spreads checkpoint writes out. Cuts the periodic write stall on write-heavy systems. |
track_io_timing |
off | on | Makes I/O time visible in EXPLAIN and pg_stat_statements. Overhead is negligible on modern clocks. |
jit |
on | Test both | Helps long analytical queries, can cost more than it saves on short OLTP queries. Raise jit_above_cost or disable per workload. |
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
SELECT pg_reload_conf();
-- Which ones still need a restart?
SELECT name, setting, pending_restart
FROM pg_settings WHERE pending_restart;
-- Prefer per-session work_mem for known-heavy reports
-- over raising it for the whole cluster:
SET LOCAL work_mem = '256MB';
When an Index Is Not the Answer
Indexes fix selective lookups. They do nothing for a query that legitimately aggregates fifty million rows, and they cannot help when the working set is far larger than RAM. Knowing which lever to pull matters more than knowing more index types.
| Lever | Fixes | Honest cost |
|---|---|---|
| Add an index | Selective lookups, sorts, joins on unindexed keys | Write amplification and disk on every insert and update |
| Rewrite the query | Filters that cannot use an index, OFFSET pagination, accidental cross joins | Free. Should always be tried first |
Raise work_mem |
Disk sorts and hash joins that spilled to batches | Multiplies across nodes and connections; an out-of-memory kill is worse than a slow sort |
| Materialized view | An expensive aggregate read many times | Staleness. Concurrent refresh needs a unique index and still does the full work |
| Read replica | Read throughput, isolating reporting from OLTP | Does nothing for one slow query; adds replication lag as a correctness problem |
| Cache in Redis | Repeated identical reads at high volume | Invalidation is the entire problem, and it is now your problem |
| More RAM | A working set that does not fit in cache | Money. Frequently cheaper than the engineering alternative |
| Separate analytics engine | Scans of hundreds of millions of rows to produce a handful of numbers | A second system to run, load and reconcile |
Two cases deserve a blunt answer. If the working set is 200 GB and the machine has 32 GB of RAM, no index will fix the storage reads; buy memory before you buy complexity. And if a query scans a hundred million rows to produce twelve numbers on a dashboard, a row-oriented OLTP engine is the wrong tool, and the right move is a separate analytical store rather than another index.
The Order to Work In
A Repeatable Tuning Pass
- 1. Rank.
pg_stat_statementsordered bytotal_exec_time. Pick the top entry. - 2. Explain.
EXPLAIN (ANALYZE, BUFFERS)with realistic parameters, not a toy value. - 3. Locate the waste. The node with the worst estimate gap, the largest buffer read, or the highest rows-removed count.
- 4. Check statistics first.
ANALYZEthe table before designing anything. It is free and it is often the whole fix. - 5. Change exactly one thing. An index, a rewrite, or a setting. Not three at once.
- 6. Re-measure. Reset the statement counters, run production traffic, compare.
- 7. Then look at the system. Dead tuple percentage, pooler wait counts, checkpoint frequency.
The bottom line: PostgreSQL is rarely slow in a mysterious way. It is slow because a plan is doing work that a different plan would not do, and EXPLAIN (ANALYZE, BUFFERS) shows you exactly which node is doing it. Measure before you change anything, change one thing at a time, and treat every index you add as a permanent tax on writes that has to earn its place.
Frequently Asked Questions
Why is my PostgreSQL query slow even though the column has an index?
Usually one of five things. The index's leading column is not constrained by the query. A function wraps the column in the WHERE clause, so date(created_at) = '2026-07-01' cannot use an index on created_at. The parameter type does not match the column type, which puts the cast on the column side. The statistics are stale, so the planner believes a sequential scan is cheaper. Or random_page_cost is still 4.0 on SSD hardware where 1.1 is closer to reality. Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated rows to actual rows: a gap of 10x or more points at statistics, while a matching estimate with a sequential scan chosen anyway points at cost settings.
How do I read EXPLAIN ANALYZE output in PostgreSQL?
Read from the innermost, most indented node outward, and check four things at each node. Estimated rows against actual rows tells you whether the planner had good information. Actual time is reported per loop, so a node's real cost is its actual time multiplied by its loop count. Buffers: shared read counts 8 kB blocks fetched from outside the buffer cache, which converts directly into megabytes of I/O. Rows Removed by Filter is work the query did and threw away, and a large number there is the clearest signal that an index is missing. Remember that timing at each node includes its children, so subtract child time to see what a node cost on its own.
How many connections can PostgreSQL handle?
PostgreSQL forks a process per connection, so each one costs memory before it does any work, plus up to work_mem for every sort or hash node it runs. Pushing max_connections into the thousands adds context switching and lock contention rather than throughput. A practical starting point for concurrently active backends is roughly twice the CPU core count plus an allowance for storage parallelism, so 20 to 40 on an eight-core machine. Run PgBouncer in transaction mode in front, let thousands of application clients connect to the pooler, and keep the database-side pool small.
Does VACUUM lock my table in PostgreSQL?
Plain VACUUM and autovacuum take a lock that allows concurrent SELECT, INSERT, UPDATE and DELETE, so normal traffic keeps running. VACUUM FULL is different: it rewrites the table and holds an ACCESS EXCLUSIVE lock for the whole rewrite, blocking reads as well as writes. VACUUM FULL is the only built-in way to hand disk space back to the operating system, but pg_repack does the same job online with a brief lock only at the swap. If only the indexes are bloated, REINDEX INDEX CONCURRENTLY is usually enough and much less disruptive.
When should I partition a PostgreSQL table?
Partition when you expire old data on a schedule and want DROP TABLE instead of a DELETE that creates hundreds of millions of dead tuples; when nearly every query filters on the partition key, so pruning removes most of the data at plan time; or when maintenance on a single table no longer fits in an available window. Partitioning does not speed up queries on its own. If queries do not filter on the key, every partition gets scanned and planning overhead is added on top. Keep the partition count in the tens to low hundreds, make sure any unique constraint includes the partition key, and create future partitions ahead of time.
What is the fastest way to find bloat in a PostgreSQL database?
Start with pg_stat_user_tables and look at n_dead_tup alongside last_autovacuum: a large dead-tuple count with an old or null autovacuum timestamp is the clearest signal. For a byte-level measurement, install the pgstattuple extension and call pgstattuple('tablename'), which reports the free-space percentage directly by scanning the relation. It is accurate but it reads the whole table, so run it off-hours on large relations. Before repacking anything, check pg_stat_activity and pg_replication_slots for something holding the xmin horizon, because repacking a table while vacuum is blocked simply refills the space.
References: PostgreSQL documentation: Using EXPLAIN, Index Types, Routine Vacuuming, PgBouncer configuration