PostgreSQL Performance, Indexing and Backup Best Practices

PostgreSQL performance work should begin with the query and workload, not with a global setting copied from a benchmark. Indexes can accelerate a specific access pattern, but they also consume storage and add write overhead. Backups require equal attention: a fast database that cannot be restored is not production ready.

This guide uses recent PostgreSQL releases and standard psql/pg_dump tools. SQL and shell commands are illustrative. Run EXPLAIN and backup commands against a staging copy or with a reviewed production change plan. Never publish real database names, credentials, or query results containing customer data.

1. Measure the slow operation

Identify a slow query from application telemetry or PostgreSQL's approved logging/statistics tools. Record frequency, latency distribution, rows returned, and time of day. A query that runs once a month deserves different optimization effort from one executed on every page view.

Inspect its plan in a safe environment. EXPLAIN without ANALYZE estimates behavior without executing the query. EXPLAIN ANALYZE executes it and may be expensive or modify data for write statements; do not use it blindly on production.

EXPLAIN SELECT id, created_at
FROM invoices
WHERE client_id = 123
ORDER BY created_at DESC
LIMIT 20;

The table and filter are examples. Look for full scans on large tables, poor row estimates, unnecessary sorts, and joins that multiply work. Check whether statistics are current before adding an index.

2. Design an index for a real predicate

For the example query, an index on (client_id, created_at DESC) may support filtering and ordering. Test with representative data and write volume.

CREATE INDEX CONCURRENTLY idx_invoices_client_created
ON invoices (client_id, created_at DESC);

This command changes the database and consumes I/O and disk. In PostgreSQL, CREATE INDEX CONCURRENTLY reduces blocking but cannot run inside a transaction block and may leave an invalid index if interrupted. Review version-specific behavior and the migration framework before using it. An index on a tiny table may provide no benefit; multiple overlapping indexes may slow writes.

3. Re-check application behavior

After a change, compare query plan, p50/p95 latency, CPU, I/O, and write performance. Watch for cache effects that make the first test appear different from a steady workload. Keep the original query and plan in the change record. If the bottleneck moves to the network or application serialization, another index will not fix it.

4. Create a backup appropriate to recovery goals

pg_dump produces a logical backup of one database. It is useful for many restores and migrations, but it may not satisfy a low RPO or a very large database's RTO. PostgreSQL documents physical backups and continuous archiving for other needs. Use the same or compatible client/server versions according to PostgreSQL guidance.

pg_dump -Fc --dbname=exampledb --file=exampledb.dump
pg_restore --list exampledb.dump | head

These commands create and inspect a file. Run them under a restricted account, protect the output file, and verify enough free storage. pg_restore --list shows contents; it does not prove the backup can be restored successfully. Restore into an isolated test database and run application checks.

5. Test recovery after a schema or index change

Document which application release matches the backup, how migrations are replayed, and how uploads and secrets are restored. A database-only restore may not rebuild a working customer service. Measure actual restore time and compare it with the agreed RTO. Do not run test restores against the live production database.

Troubleshooting and common errors

• Index exists but query still scans: inspect selectivity, planner estimates, stale statistics, query predicates, and table size.
• Write latency increases after adding indexes: measure index maintenance cost and remove redundant indexes through a reviewed change.
• Backup completes but restore fails: inspect version compatibility, extensions, roles, permissions, and whether the dump is complete.
• CREATE INDEX CONCURRENTLY leaves an invalid index: investigate the failure and clean it up with an approved plan, then retry if warranted.
• High latency persists after tuning: correlate database time with app, network, and cache behavior.

Security considerations

Restrict who can run backups and restores; dumps may contain all customer records. Encrypt and store off-host with retention and deletion controls. Review query logs to ensure they do not expose passwords or personal information. Avoid using a production superuser for routine application queries.

FAQ

Will an index always make a query faster?

No. The planner may prefer a scan for small or low-selectivity datasets, and indexes add storage and write cost.

Is pg_dump enough for point-in-time recovery?

Not by itself. Point-in-time recovery requires an appropriate base backup and WAL archiving strategy documented by PostgreSQL.

Can I test a backup by listing its contents?

That is a useful integrity check, but the real acceptance test is an isolated restore followed by application validation.

Continue reading

Build a complete backup and recovery plan, then monitor server health around database capacity. Contact East4Serv for a scoped database operations review.

References: PostgreSQL indexes, pg_dump, and backup and restore.