The Postgres Tools Worth Setting Up Before You Need Them

Postgres tooling advice usually turns into a list of GUI clients. The useful set is broader and mostly less visual.

1. Query statistics, before anything else

The extension that records execution statistics per query is the first thing to enable on any database you care about. It answers the only question that matters when something is slow: which query, and how often.

SELECT calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time, not mean. A query taking 8ms and running two million times a day is a much bigger problem than one taking 900ms a few times an hour, and it is the one nobody notices.

2. Query plans, once you know where to look

EXPLAIN (ANALYZE, BUFFERS) on the specific slow query. Reading plans takes practice, but you get most of the value from three things:

• A sequential scan on a large table that should have used an index
• A row estimate that is wildly different from the actual count — usually stale statistics
• A nested loop over far more rows than expected

The overwhelming majority of slow queries turn out to be a missing index or a query that forgot to bound its result set. Exotic explanations exist, but they are rare and expensive to chase — exhaust the boring ones first.

3. Migrations in version control

Whatever tool your stack provides, use it, and never change production schema by hand. The specific tool matters far less than the discipline: schema changes must be reviewable, replayable and present in the repository.

The failure this prevents is not dramatic. It is a column someone added directly in a client, six months ago, that exists in production and in nobody's local database.

4. A GUI client, for reading

Exploring an unfamiliar schema, eyeballing data shape, checking a foreign key — a good client is much faster than the command line for all of it.

Use it read-only where you can. The convenience of making a quick change is exactly the convenience that produces the divergence above.

5. Connection pooling, once you have more than a handful of clients

Postgres connections are relatively expensive. Serverless environments and containerised applications that open connections freely will exhaust the limit surprisingly quickly.

The symptom is distinctive: everything is fine, then everything fails at once, then it recovers. If that pattern sounds familiar, look at connections before you look at query performance.

6. Backups you have restored

An untested backup is a belief, not a backup. Restore one into a scratch database on a schedule you actually keep — quarterly is enough — and time it. Knowing that a restore takes forty minutes is information you want before you need it, not during.

7. Statistics and autovacuum, which you will otherwise blame the query for

The failure that looks most like a query problem and is not: planner statistics drifting out of date, so the planner chooses a bad plan for a query that was fine last month.

The symptom is distinctive — a query that was fast becomes slow with no code change and no obvious growth in data. The tell is in the plan: a row estimate wildly different from the actual count. Before rewriting anything, refresh the statistics and re-check.

The related one is autovacuum falling behind on a heavily updated table, leaving dead rows the planner still has to step over. Watching dead tuple counts on your busiest tables costs nothing and explains a whole category of mystery slowdown that otherwise burns days.

The order to work in when something is slow

Most wasted debugging time comes from starting in the wrong place. This order is boring, and it is faster:

1. Which query? Statistics extension, sorted by total time. Do not proceed without an answer.
2. Is it slow now, or was it always slow? Newly slow points at statistics, data growth or a plan change. Always slow points at a missing index or the query itself.
3. What does the plan say? Look for the sequential scan, the bad row estimate, the nested loop over far too many rows.
4. Is it the query or the connections? If everything is slow simultaneously and then recovers, it is connections — and no amount of indexing will help.
5. Only then change something, one thing, and measure again.

An index added without step one is a guess that costs write performance forever. Half of them do not help, and nobody ever removes the ones that did not.

Two things worth doing before you need them

• Enable the statistics extension on day one. It records nothing about the past, so enabling it during an incident means waiting for data while the incident continues.
• Set a statement timeout. A default that kills any query running absurdly long turns "the database is down" into "one endpoint returns an error", which is a considerably better Tuesday.

Where none of this helps

Problem	Where the fix is
N+1 queries from an ORM	Your application code
Unbounded result sets	Your application code
Data model that fights every query	Your schema design

These are the three most common causes of "the database is slow", and none of them are database problems. Tools will show you the symptom quickly and accurately. The fix is upstream — which is why we keep the tool list short and spend the time on the queries instead. Where the database lives matters too — see four deployment routes compared.