The slow database that gets blamed in your incident review is rarely broken. In most cases it's missing an index, drowning in connections, or running memory settings sized for hardware from two decades ago, and each of those takes under an hour to fix. This guide covers the decisions that matter, in the order they matter. The examples use PostgreSQL, now the most-used database among developers at 55.6% in the 2025 Stack Overflow survey, but the reasoning applies to any relational engine.
Database optimization is the practice of improving how a database stores, retrieves, and processes data so that queries return faster and hardware does less work. It covers schema design, indexing strategy, query planning, memory configuration, and connection management, and it is usually the cheapest performance improvement available.
Indexing strategy is where the largest wins live
The first optimisation happens before any data exists, because no index rescues a bad data model, so design tables around the ten queries your application will run most often. The same discipline applies to API design, where contracts follow the calls you will actually receive.
From there, most real-world database optimisation comes down to indexes. Sentry's engineers made the point plainly in their post on missing indexes: unindexed queries degrade quietly as tables grow, and the fix is frequently a one-line migration. The default B-tree index is correct roughly 90% of the time, partial indexes cover a small slice of a table cheaply, and composite indexes must match the query's column order to be used at all.
Finding gaps is a solved problem. EXPLAIN ANALYZE shows the actual plan for a query, and the pg_stat_statements extension ranks queries by cumulative time, which matters because the query burning the most total minutes per day is rarely the slowest individual one. Audit the other direction too, since any index showing zero scans in pg_stat_user_indexes should be dropped.
Memory defaults assume a machine you retired years ago
PostgreSQL ships with settings conservative enough to start on tiny hardware. The resource configuration documentation lists the shared_buffers default at 128MB, which on a modern 64GB server leaves nearly all of your RAM doing nothing for the database. The usual starting point is 25% of system RAM, and the documentation notes that allocations beyond 40% rarely help because the operating system's page cache holds database pages as well.
Two other parameters carry most of the remaining weight. work_mem caps memory per sort or hash operation, and because it applies per operation rather than per connection, one complex query across a hundred connections can multiply a generous setting into an out-of-memory kill. effective_cache_size allocates nothing; it tells the planner how much combined cache likely exists, which favours index scans when set honestly at 50 to 75% of RAM. Change one parameter at a time and measure against a recorded baseline.
Connections fail before queries do
PostgreSQL creates a dedicated operating system process for every connection, and as the Stack Overflow engineering blog explains, establishing and tearing down those processes is expensive exactly when concurrency peaks. The standard remedy is a pooler such as PgBouncer, which shares a small set of real connections across thousands of application clients. ScaleGrid's pgbench benchmark measured PgBouncer at roughly 60% higher throughput than direct connections at every client count tested, and the unpooled setup stopped working entirely at 200 clients.
There is an honest caveat. Percona's benchmark found that with 56 long-lived connections, going direct beat PgBouncer by 2.5x, because a proxy hop buys nothing when there is no churn to absorb, though past 150 concurrent clients the pooler wins clearly. If serverless functions or autoscaling containers talk to your database, pooling is not optional, and it deserves treatment as a cloud architecture decision rather than an afterthought.
Autovacuum falls behind long before you shard
PostgreSQL's UPDATE and DELETE leave dead row versions for vacuum to reclaim later, and when autovacuum falls behind, tables bloat and cache fills with rows no transaction can see. The routine vacuuming documentation shows why: the default scale factor of 0.2 waits until 20% of a table is dead rows, which on a billion-row table means 200 million dead tuples before cleanup begins. For large, frequently updated tables, set it per table at 0.01 to 0.05, and beyond a certain size use time-based partitioning, which turns retention into a near-instant partition drop instead of hours of vacuum debt.
When a tuned single instance runs out of headroom, scale in the boring order: cache hot reads first, add read replicas second, partition third, and shard last, because sharding forces every query, migration, and transaction to become shard-aware.
FAQ
How do I find slow queries in PostgreSQL?
Enable pg_stat_statements and sort by total execution time to find the queries consuming the most cumulative resources. Then run EXPLAIN (ANALYZE, BUFFERS) on each and look for sequential scans on large tables, disk-spilling sorts, and row estimates far from reality.
When do I need a connection pooler?
Add PgBouncer once you have short-lived connections, more than about 150 concurrent clients, or any serverless components. Percona's benchmarks show direct connections can win at low concurrency with long-lived connections, so tiny deployments can wait, but most production web workloads cross the threshold early.
Checklist
Enable pg_stat_statements and record baseline p50/p99 latency and throughput.
Run EXPLAIN ANALYZE on the top queries by cumulative time and fix sequential scans on large tables.
Set shared_buffers, work_mem, and effective_cache_size deliberately, one change at a time.
Put PgBouncer in transaction mode in front of the database and lower max_connections.
Tune autovacuum scale factors per table and partition large time-series tables.
None of this work is glamorous, but it separates databases that degrade gracefully from databases that page you at 3 a.m. BeyondPixl Studio designs and tunes production data layers every week, so if yours is slower than it should be, talk to our engineering team about a database performance review.