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.

Database optimization is the practice of making a database serve its workload faster and cheaper by reducing the work each query performs - through better data modeling, query design, index strategy, and maintenance - before adding capacity. The order matters: systems scale by doing less work per query, not by adding more hardware. This is a playbook for doing it in that order. The examples are PostgreSQL, because "database" usually means an OLTP SQL database, but the method is engine-agnostic - and where another engine like 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 actually runs the query and reports real timings and row counts, and BUFFERS adds the actual block reads so you can see whether a step hit cache or disk (EnterpriseDB). The single most useful thing to read in that output is the gap between estimated and actual rows. When the planner expects 10,000 rows and gets 10 million, it has already chosen the wrong join strategy and the wrong access path, and no amount of RAM will fix a plan built on a bad estimate (pganalyze).

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 correctly, because the query touches most of the table; sometimes wrongly, because no useful index exists or the statistics are lying to it (oneuptime). 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. A well-designed model reduces redundancy, improves query performance, and keeps the system scalable as it grows; the physical schema - data types, normalization choices, partitioning - directly determines performance. Picking bigint where int suffices, storing timestamps as text, or over-normalizing a hot read path all cost more than any work_mem tweak will recover.

ClickHouse is the clearest teacher here precisely 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, no tuning available to rescue you. The lesson reflects straight back to Postgres, where 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, so query count scales linearly with result size. 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. The patterns for hunting expensive queries look similar whether you are reading Postgres logs or OpenSearch slow logs.

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 - a query that should take milliseconds can take tens of seconds. The fix is often a single ANALYZE to refresh the statistics, 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, 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 well above its default of 200, which was calibrated for spinning disks decades ago and badly underuses modern SSDs. The cross-engine echo is ClickHouse's 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. A common starting point is roughly 25% of system RAM.
  • work_mem - memory per sort or hash operation before it spills to disk. Slow ORDER BY, DISTINCT, or hash joins frequently trace back to this; in-memory always beats a disk spill (oneuptime).
  • random_page_cost - the planner's estimate of random I/O cost. The default of 4.0 assumes spinning disks; on SSDs, lowering it to 1.1-2.0 better reflects reality and encourages index scans where they belong.

Connections are the other resource that quietly caps throughput. Each PostgreSQL connection forks a dedicated OS process consuming 5+ MB of RAM plus context-switching overhead, 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; a reasonable backend pool size is roughly 3-5x the core count, since a connection can only use one core at a time. Sizing the pool into the thousands defeats the purpose - you have just moved the contention.

The ClickHouse reflection here is memory pressure under query: the same principle that you tune resource limits to the workload, not the workload to imagined headroom.

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. Choosing the right index for each query beats adding one more.

The same backfire pattern recurs: 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. Optimization is subtraction at least as often as addition.

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: if EXPLAIN shows a plan that is already optimal and the query is still too slow, you have an architecture question, not a tuning one. That is also where capacity planning stops being guesswork and becomes a measured, repeatable process.

Key takeaways

  • Measure before you tune. Read EXPLAIN (ANALYZE, BUFFERS) and look first at estimated-versus-actual row counts; a 10x divergence means the plan was doomed before execution.
  • The model and the queries dominate. Data types, normalization, partitioning, and killing N+1 patterns move performance far more than any single GUC.
  • Watch the maintenance layer. Stale statistics and autovacuum falling behind cause sudden regressions; ANALYZE and per-table autovacuum tuning fix most of them.
  • Tune hardware-adjacent settings last, and pool connections at roughly 3-5x core count rather than letting thousands of processes fight for the box.
  • Some fixes backfire. Over-indexing, premature denormalization, and replicas-as-bandaids add cost without removing the cause.
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

Optimization without guesswork is mostly a sequencing discipline: 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.