Most 'the database is slow' tickets are misdiagnosed. A practitioner's playbook for optimizing PostgreSQL - diagnose with evidence, fix the model and the queries, tame stats and bloat, and tune hardware last.

Database Optimization Without the Guesswork: A Practitioner's Playbook

The reflex, when a database gets slow, is to make the instance bigger. More vCPUs, more RAM, faster disks. It works just often enough to be dangerous: the box absorbs the symptom for a quarter, the workload grows back into it, and now you are paying double to run the same unoptimized queries. Most "the database is slow" tickets are not hardware problems. They are query, schema, or statistics problems wearing a hardware costume.

This is a playbook for fixing them in the right order: prove the bottleneck, fix the model and the queries, keep statistics and vacuum healthy, and only then touch resources. It is written for the application developer who owns the ORM and the on-call rotation, with enough of the DBA's side (autovacuum, planner costs) to know which knobs matter. The examples are PostgreSQL, because "database" usually means an OLTP SQL database, but the method transfers. Where ClickHouse makes a principle impossible to ignore, we use it to sharpen the point.

Diagnose with evidence before you touch a knob

Every wasted optimization starts the same way: someone changes a setting based on a hunch. The fix is a loop that refuses to guess. Measure, locate the bottleneck, confirm it, change one thing, measure again. The confirmation step is where most teams skip ahead, and it is the one that pays.

In PostgreSQL the evidence tool is EXPLAIN (ANALYZE, BUFFERS). Plain EXPLAIN shows the planner's intended plan and its cost estimates; adding ANALYZE runs the query and reports real timings and row counts, and BUFFERS adds the block reads so you can see whether a step hit cache or disk. On PostgreSQL 18 and later, buffer information is included automatically whenever you use ANALYZE (PostgreSQL docs), so if you see it without asking, that is why. The single most useful thing to read in that output is the gap between estimated and actual rows. This is the shape you are looking for:

Nested Loop  (cost=0.43..812.77 rows=12 width=64) (actual time=0.091..4183.220 rows=1847213 loops=1)
    ->  Index Scan using orders_status_idx on orders  (cost=0.43..8.45 rows=1 width=32)
          (actual time=0.040..611.902 rows=1847213 loops=1)
          Index Cond: (status = 'pending')
    ->  Index Scan using customers_pkey on customers  (cost=0.43..8.45 rows=1 width=32)
          (actual time=0.002..0.002 rows=1 loops=1847213)

The planner expected one pending order and got 1.8 million, so it chose a nested loop that then did 1.8 million index probes into customers. No amount of RAM fixes that plan; a fresh ANALYZE on orders (or extended statistics, if the estimate is off because of correlated columns) does.

A sequential scan in that output is not automatically a bug. It means the planner's cost model decided reading every row was cheaper than any available index. Sometimes that is correct, because the query touches most of the table; sometimes it is wrong, because no useful index exists or the statistics are lying. The plan tells you which. Every engine offers the same kind of receipt if you ask for it. ClickHouse has EXPLAIN and system.query_log, OpenSearch has slow logs and the Profile API, so the discipline transfers even when the syntax does not.

Fix the data model and the queries first

The highest-leverage optimization is almost never a setting. It is the shape of the data and the shape of the access pattern. Storing UUIDs as text instead of uuid, storing timestamps as strings, or over-normalizing a hot read path all cost more than any work_mem tweak will recover.

ClickHouse is the clearest teacher here because it is unforgiving: choose the wrong ORDER BY key or skip LowCardinality on a low-distinct-value column and queries run an order of magnitude slower, with no tuning available to rescue you. In Postgres the penalty for a poor model is quieter but just as real. The same goes for partitioning; knowing when a table should be partitioned is a modeling decision that pays off across every engine.

On the query side, the most common application-level killer is the N+1 problem: an ORM fetches a list with one query, then issues one more query per row to load a relationship. One hundred rows becomes 101 round trips. It hides well because each individual query is fast; the slow list endpoint only shows up under real data volumes. The fix is eager loading (JOIN or the ORM's batch-load primitive), not a bigger database. This is the second-order benefit of reading slow query logs: the same parameterized query repeated dozens of times with only the ID changing is an N+1 signature, whether you are reading Postgres logs or hunting expensive queries in OpenSearch.

The maintenance layer teams forget: statistics and bloat

Two PostgreSQL-specific failure modes account for a surprising share of "it was fine yesterday" incidents, and both live in the maintenance layer that application teams rarely watch.

The first is stale statistics. The planner's choices are only as good as its picture of the data, and that picture goes stale after bulk loads, schema migrations, or faster-than-expected growth. When statistics diverge from reality, the planner picks sequential scans over index scans and nested loops where a hash join belonged, exactly as in the plan above. The fix is often a single ANALYZE, which is worth trying before any structural change when a query regresses suddenly.

The second is table bloat, a direct consequence of how PostgreSQL implements concurrency. Under MVCC, an UPDATE or DELETE does not remove the old row version; it marks the old tuple dead and writes a new one, so concurrent transactions keep a consistent snapshot. Autovacuum reclaims those dead tuples later, but its default trigger is lazy. With autovacuum_vacuum_scale_factor at its default of 0.2 (PostgreSQL docs), a 10-million-row table accumulates 2 million dead rows before autovacuum even starts, bloating pages, wasting cache, and slowing every scan that has to read past the corpses.

The remedy is to make autovacuum more aggressive where it matters, per table rather than globally. Lower the scale factor on large, write-heavy tables (values near 0.01 trigger vacuuming after roughly 1% of rows change), and raise autovacuum_vacuum_cost_limit, whose default of -1 inherits vacuum_cost_limit's 200. Two things to know about that limit: it was calibrated long before SSDs and badly underuses them, and it is split proportionally across all running autovacuum workers, so raising it on one table does less than you would expect if several workers are busy (PostgreSQL docs). The ClickHouse cousin of this is the too-many-parts problem, background merges falling behind write volume, and its mutation handling. Different mechanism, same shape: a background process that must keep pace with writes, and degrades quietly when it cannot.

Tune resources last, not first

Only after the model, the queries, and the maintenance layer are sound do hardware-adjacent settings earn their keep. PostgreSQL ships with deliberately conservative defaults, and a handful of parameters do most of the work:

  • shared_buffers: how much memory Postgres uses for its own page cache. The manual's starting point for a dedicated server is 25% of RAM, and it notes that going past 40% rarely helps because the OS cache does the rest (PostgreSQL docs).
  • work_mem: memory per sort or hash operation before it spills to disk, 4MB by default. Slow ORDER BY, DISTINCT, or hash joins frequently trace back to this. Remember it is per operation, not per query, so a complex query under concurrency can use many multiples of it.
  • random_page_cost: the planner's estimate of random I/O relative to sequential. The default of 4.0 already assumes most random reads hit cache; the manual says to lower it when the dataset fits in memory or the random-access penalty is small (PostgreSQL docs). On NVMe-backed instances we usually land between 1.1 and 2.0, which nudges the planner toward index scans where they belong.

Connections are the other resource that quietly caps throughput. Each PostgreSQL connection is an OS process with its own caches and context-switch cost, so a few thousand idle application connections can starve the box before a single heavy query runs. A pooler like PgBouncer lets many clients share a small set of backend connections. For sizing, start near the PostgreSQL wiki's formula of cores * 2 + effective_spindle_count, where the spindle count is zero for a dataset that fits in cache (PostgreSQL wiki), so roughly 2-4x cores on a cached, SSD-backed box, and then measure. Sizing the pool into the hundreds just relocates the contention.

The optimizations that backfire

Some of the most confident "fixes" make things worse. The clearest is over-indexing. Every index speeds reads that match it and taxes every write that touches the table, because each INSERT and non-HOT UPDATE must update all of them; ten indexes turn one insert into eleven writes. Beyond write amplification, redundant indexes bloat storage, slow backups, pressure the buffer pool, and can push the planner toward a worse index choice, producing erratic performance. Keeping the index set lean is itself an optimization, and choosing the right index for each query beats adding one more.

The same backfire pattern recurs elsewhere: premature denormalization that turns one clean join into three update paths to keep in sync, oversharding before the data justifies it, and the read replica added to mask an N+1 that a single eager load would have erased. Each adds operating cost and a new failure mode while leaving the root cause in place.

When to optimize, and when to re-architect

There is a real line between a query you can fix and a workload the engine was never built for. Pushing heavy analytical aggregations through an OLTP Postgres instance is the canonical example. Past a point, the answer is not a better index but a columnar engine like ClickHouse for the analytical slice, with Postgres staying the system of record. Full-text relevance at scale points the same way, toward a search engine.

The diagnostic loop is what tells you which side of the line you are on. Signals that you have an architecture question rather than a tuning one:

  • EXPLAIN shows a plan that is already optimal (correct estimates, the right index, no spills) and p95 is still over budget.
  • The query legitimately needs to scan a large fraction of a big table, because it is an aggregation over history rather than a lookup.
  • The indexes you need to make reads fast are the same ones making writes too slow, and you cannot drop either side.
  • Statistics and vacuum are healthy, the pool is sized, and the only fix left is a bigger box every quarter.

That is also where capacity planning stops being guesswork and becomes a measured, repeatable process.

Where to look first

Symptom Most likely cause First thing to check
Slow list endpoint, fine in isolation N+1 queries from the ORM Repeated identical query with changing ID in slow log
Query slow right after a bulk import or migration Stale planner statistics Run ANALYZE; compare estimated vs actual rows in EXPLAIN
Writes degrading over time Over-indexing or table bloat Audit indexes; check dead-tuple ratio and autovacuum lag
Full table scan where an index exists Bad statistics or wrong index shape EXPLAIN (ANALYZE, BUFFERS); verify index matches predicate
High latency under load, light per-query cost Connection saturation Backend connection count vs core count; add a pooler

Prove the bottleneck with the plan, fix the model and the queries, keep statistics and vacuum honest, and reach for bigger hardware only when the evidence says the work itself cannot shrink any further. If you are staring at a slow database and not sure which layer is lying to you, we do this for a living, across PostgreSQL, ClickHouse, OpenSearch, and the rest of the stack.