Database performance basics

A slow app is usually a slow query, and a slow query is usually a missing index or a bad connection pattern.

1. Find the slow queries

PostgreSQL — enable the slow-query log and look:

-- requires pg_stat_statements (an extension)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

MySQL — the slow query log, or SHOW FULL PROCESSLIST to see what is running now.

2. Index what you filter by

An index turns a full-table scan into a lookup.

CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);

• Index columns used in WHERE, JOIN, and ORDER BY.

• Composite indexes serve multi-column filters — order matters (most selective / always-used column first).

• Too many indexes slows writes and bloats storage. Index deliberately.

• Verify with EXPLAIN ANALYZE that a query actually uses the index you added.

3. Fix connection handling — the silent killer

Most web apps open a new database connection per request. Under load, connection setup dominates. Use a connection pool: a pooler in the app (e.g. HikariCP, Prisma's pool) or a server-side pooler (pgbouncer for PostgreSQL).

Symptoms of no pooling: the database hits its max-connection limit while CPU sits idle, and latency spikes under concurrency but is fine in single-user testing.

4. Select only what you need

• SELECT * transfers and materialises columns you never use.

• SELECT with a LIMIT for list views.

• Avoid N+1 patterns: fetching a list then running one query per item. Batch or join instead.

5. Keep the database on fast storage, with enough RAM

• NVMe makes a real difference for write-heavy workloads.

• RAM matters because the database caches hot data — if the working set does not fit, every query hits disk.

• See Sizing your server for how to reason about this.

6. Vacuum and maintain (PostgreSQL)

Autovacuum handles most of it, but on heavy-write tables keep an eye on bloat. VACUUM ANALYZE after big data changes refreshes the planner's statistics.

Related reading

• PostgreSQL basics

• Sizing your server