Tools that help

Free tools that fix or explain what a checkup finds. For each: what it does, how to install it, when to run it, and what to watch out for. Before installing anything on a managed provider, check its list of supported extensions, because many don't allow every one.

pg_repack

What it does: rebuilds a bloated table or index while it stays available for reads and writes. VACUUM FULL does the same job but locks the table completely.

When to run it: when a checkup shows heavy bloat (a large share of dead or wasted space) that ordinary VACUUM won't give back, and you can't afford a lock. Run it off-peak. Fix what caused the bloat first (long transactions, autovacuum settings), or it returns.

Install:

# Debian / Ubuntu (PGDG repository), match your Postgres major version
sudo apt install postgresql-17-repack
# RHEL / Rocky / Alma (PGDG)
sudo dnf install pg_repack_17
# From source: needs pg_config on your PATH
git clone https://github.com/reorg/pg_repack && cd pg_repack && make && sudo make install

Then, once per database, as a superuser: create extension pg_repack;. The command-line client and the extension must be the same version.

Use:

# See what it would do first
pg_repack -h db.example.com -U admin -d app --table=public.events --dry-run
# Then run it. --no-kill-backend matters: by default, after the wait timeout it cancels queries that block it.
pg_repack -h db.example.com -U admin -d app --table=public.events --no-kill-backend --wait-timeout=120
# Rebuild only the indexes of a table
pg_repack -h db.example.com -U admin -d app --table=public.events --only-indexes

Watch out for:

• The table needs a primary key or a unique index on non-null columns.
• It needs free disk roughly equal to the table plus its indexes, while it works.
• It takes a brief exclusive lock at the start and end, so a long-running transaction can hold it up.
• If it is interrupted it can leave a temporary schema and trigger behind. Drop the extension and recreate it to clean up.
• Run analyze on the table afterwards.

Alternatives: pg_squeeze does the same from a background worker on a schedule (needs wal_level = logical and a preload entry). VACUUM FULL and CLUSTER are built in but lock the table for the whole rewrite.

pgstattuple

What it does: measures real bloat, instead of estimating it.

Install: it ships with Postgres contrib. create extension pgstattuple;

Use: select * from pgstattuple_approx('public.events'); is cheap and safe on big tables (it uses the visibility map). pgstattuple('public.events') scans the whole table, so avoid it in peak hours. Look at dead_tuple_percent and free_percent.

When: a checkup says a table is bloated and you want a number before you rebuild it. pg-checkup uses pgstattuple_approx automatically when it's installed.

pg_stat_statements

What it does: records time, calls, rows and I/O for every distinct query. It is the starting point for almost every performance question.

Install: add it to shared_preload_libraries in postgresql.conf, restart, then create extension pg_stat_statements; in each database. Many managed providers ship it enabled.

Use:

select round(total_exec_time) as total_ms, calls, round(mean_exec_time::numeric, 1) as mean_ms, left(query, 90)
from pg_stat_statements order by total_exec_time desc limit 10;

Reset it with select pg_stat_statements_reset(); after a fix, so you measure the new behaviour.

Watch out for: it's normalised (values replaced by $1), so it shows the shape of a query, not the exact one that ran.

auto_explain

What it does: logs the execution plan of slow queries as they happen, so you don't have to reproduce them.

Install: shared_preload_libraries = 'auto_explain', or per session with load 'auto_explain';. Then:

auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on        # real timings; adds overhead, so raise the threshold on busy servers
auto_explain.log_buffers = on

When: a query is slow only sometimes, or only in production. Turn it on for a day, then read the plans in the log (pgBadger or the log analyzer helps).

HypoPG

What it does: lets you ask "would this index help?" without building it.

Install: apt install postgresql-17-hypopg, then create extension hypopg;

Use:

select * from hypopg_create_index('create index on events (customer_id, created_at)');
explain select * from events where customer_id = 42 order by created_at desc limit 20;  -- the plan now uses the hypothetical index
select hypopg_reset();

When: before adding an index to a large table, where a wrong guess costs hours of build time and write overhead.

PgBouncer

What it does: a connection pooler. Thousands of application connections share a few dozen real ones.

Install: sudo apt install pgbouncer (or use your provider's built-in pooler).

Minimal pgbouncer.ini:

[databases]
app = host=db.internal port=5432 dbname=app

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20
max_client_conn = 2000
server_idle_timeout = 600

When: connections are near max_connections, or many are idle. Size default_pool_size near two to four times the CPU cores, not by traffic.

Watch out for: in transaction mode, session features (SET, advisory locks, LISTEN, and older prepared-statement usage) don't carry across transactions. Point clients at port 6432 and keep max_connections on Postgres well above the sum of your pools.

pgBadger

What it does: turns Postgres logs into an HTML report: slowest queries, errors over time, connections, checkpoints, locks.

Install: sudo apt install pgbadger, or brew install pgbadger.

Use:

pgbadger -f stderr /var/log/postgresql/postgresql-17-main.log -o report.html

It needs useful logging first: log_min_duration_statement = 250, log_checkpoints = on, log_lock_waits = on, log_temp_files = 0, log_connections = on, log_disconnections = on, and a log_line_prefix such as '%m [%p] %q%u@%d '.

When: after an incident, or weekly on a busy system.

pg_partman

What it does: creates and drops time-based partitions automatically.

Install: sudo apt install postgresql-17-partman, then create extension pg_partman;

When: a single table passes roughly 100 GB, or is most of your database, and old data can be dropped or archived. Partitioning lets you drop a month in a second instead of deleting rows for hours. Set it up before the table is huge: converting a very large table is much harder.

pg_cron

What it does: runs SQL on a schedule inside the database.

Install: shared_preload_libraries = 'pg_cron', restart, create extension pg_cron;

Use it for maintenance:

select cron.schedule('nightly-analyze', '0 3 * * *', 'analyze');
select cron.schedule('purge-cron-history', '0 4 * * *', $$delete from cron.job_run_details where end_time < now() - interval '7 days'$$);

Watch out for: it logs every run forever unless you purge (see above), and a failing job fails silently. The checkup flags both.

pg_buffercache

What it does: shows what is in shared_buffers right now.

Install: it ships with contrib. create extension pg_buffercache;

Use:

select c.relname, count(*) as buffers, pg_size_pretty(count(*) * 8192) as size
from pg_buffercache b join pg_class c on b.relfilenode = pg_relation_filenode(c.oid)
group by 1 order by 2 desc limit 10;

When: the cache hit rate is low and you want to know what's occupying memory, or whether the working set could fit with more RAM.

PGTune

What it does: suggests shared_buffers, work_mem, max_wal_size and friends from your RAM, CPU count and workload type. It's a web calculator at pgtune.leopard.in.ua.

When: on a new server, or after resizing one. Treat the output as a starting point: change one group of settings at a time and measure.

Where to go next

• The troubleshooting handbook has step-by-step guides for each symptom.
• When to scale covers what to try before paying for a bigger server.