Database optimization: a practical guide to faster queries

A slow endpoint is very often a slow query, and a slow query is very often a table the database had to read end to end. Learn to find those queries, read why they are slow, and fix them, and most of your database pain goes away.

New to cloud? CAMPUX is a free, build-first course. Start here →

Database optimization means making queries return faster and the server work less, usually through five moves: find the slow queries, read the execution plan, add the right indexes, rewrite queries to be sargable, and tune connection pooling and configuration. Most real-world slowness comes from missing indexes and queries that cannot use the ones you have.

If you run services in production, you meet database performance sooner or later, asked for it or not. An API times out, a dashboard crawls, a nightly job spills into the morning. The instinct is to reach for a bigger instance, and sometimes that helps for a week, but the cause is almost always a specific query doing far more work than it needs to. The good news is that the diagnosis is methodical. You do not guess. You measure, you look at what the engine is actually doing, and you change one thing at a time.

What database optimization actually means

Strip away the folklore and it comes down to a single ratio: how many rows a query reads versus how many it returns. A query that returns 50 rows but reads 5 million to find them is the shape of nearly every performance problem. The engine had to look at every row because nothing told it where the matching ones lived. Optimization is mostly the work of narrowing that gap, either by giving the engine a shortcut to the right rows or by writing the query so it can take a shortcut that already exists.

A second, quieter part of the job lives outside the query text: how your application talks to the database. Opening a fresh connection per request, or firing one query per item in a loop, can make a perfectly indexed database feel slow. So the work splits cleanly into two halves — the query and the plumbing — and this note walks both. The examples lean on PostgreSQL and MySQL syntax; the ideas carry across engines, and where a keyword differs I say so rather than pretend one dialect is universal.

Step 1: find the slow queries

You cannot fix what you have not measured, and the worst use of an afternoon is optimizing a query nobody runs. Start by letting the database tell you which statements are actually slow. Most engines can log any query that runs longer than a threshold. In PostgreSQL you set it per session or in the config:

-- log every statement slower than 500 ms (PostgreSQL)
SET log_min_duration_statement = 500;

MySQL has the same idea under a different name, the slow query log:

-- MySQL: log queries slower than 0.5 s
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;

Better still, if the pg_stat_statements extension is available in Postgres, it aggregates every query by total time spent, which surfaces the query that is individually quick but runs ten thousand times an hour — often the real culprit:

-- the ten queries burning the most total time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Pick the worst offender and look at it directly with EXPLAIN, or better, EXPLAIN ANALYZE, which actually runs the query and reports real timings and row counts rather than estimates:

-- ask the planner what it will do, and time it
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

One caution: EXPLAIN ANALYZE executes the statement, so do not run it against an UPDATE or DELETE on production unless you wrap it in a transaction you intend to roll back.

Step 2-3: execution plans and the right indexes

The execution plan is the engine showing its work. You are reading it for one word above all: scan. A sequential scan (Postgres) or full table scan (MySQL calls it type: ALL) means the engine read every row in the table. For a lookup that should return a handful of rows, that is the smell of a missing index. Compare these two plan fragments for the query above:

-- before: reading the whole table to find a few rows
Seq Scan on orders  (cost=0.00..18334.00 rows=61 width=244)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999939

That Rows Removed by Filter: 999939 line is the whole story: a million rows examined, sixty-one kept. Now add an index on the column the query filters on and the plan changes shape:

-- the fix: an index on the filtered column
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- after: jumping straight to the matching rows
Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.61 rows=61 width=244)
  Index Cond: (customer_id = 42)

An index scan means the engine used the sorted index to jump to the matching rows instead of reading the table. The cost estimate dropped from thousands to single digits. That single line is the most common database fix there is.

Two refinements earn their keep once single-column indexes feel natural. A composite index covers more than one column, and column order matters — it helps queries that filter on a leftmost prefix. An index on (customer_id, created_at) serves a query filtering both, and also one filtering customer_id alone, but not one filtering created_at alone:

-- serves WHERE customer_id = ? AND created_at > ?
CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at);

A covering index goes one step further and includes every column the query needs, so the engine answers entirely from the index and never touches the table. In Postgres you attach the extra columns with INCLUDE:

-- the index alone can answer SELECT status WHERE customer_id = ?
CREATE INDEX idx_orders_cust_incl ON orders (customer_id) INCLUDE (status);

Indexes are not free. Each one has to be kept up to date on every insert, update, and delete, and each takes disk. So index the columns your queries filter, join, and sort on, and resist the urge to index everything. An unused index is pure overhead.

Step 4: write sargable queries

You can have the perfect index and still get a full scan, because the way a query is written decides whether the engine is allowed to use the index. A query the engine can satisfy with an index is called sargable, from "search argument able." The classic mistake is wrapping an indexed column in a function. This looks reasonable and runs slowly:

-- NOT sargable: YEAR() runs per row, index on created_at is ignored
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;

Because created_at is buried inside YEAR(), the engine must compute the function for every row before it can compare, so the index on created_at is useless. Rewrite it as a range against the bare column and the index comes back to life:

-- sargable: a range on the bare column, index is used
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

The same trap hides in other habits: a leading wildcard in LIKE '%acme' cannot use a normal index, arithmetic on a column such as WHERE price * 1.1 > 100 disables it, and an implicit type cast — comparing a text column to a number — often quietly forces a scan. The rule of thumb is simple: keep the indexed column alone on one side of the comparison, and move any calculation to the other side or to a constant. Note that some engines offer functional or expression indexes if you genuinely must index YEAR(created_at), but reaching for a range is usually cleaner.

Step 5: connection pooling and config

A tuned query can still sit behind a slow connection layer. Every new database connection costs a handshake, authentication, and memory on the server, and Postgres in particular allocates a backend process per connection. Opening one per request and closing it is wasteful; under load it can exhaust the server's connection limit entirely. The answer is a connection pool: a fixed set of connections the application borrows and returns.

The counterintuitive part is sizing. Bigger is not better. A pool larger than the database can usefully serve just moves the queue from your app into the database and adds contention. A common starting point for a CPU-bound workload is a small multiple of the core count, then measured under real load:

-- app-side pool config (example, HikariCP-style)
maximumPoolSize = 10
connectionTimeout = 3000   # ms to wait for a free connection
idleTimeout = 600000       # retire idle connections
maxLifetime = 1800000      # recycle before the server drops them

For serverless or many-instance deployments, a per-process pool multiplies badly — fifty instances with ten connections each is five hundred connections at the server. Put an external pooler in front, such as PgBouncer for Postgres, so the total stays bounded regardless of how many app instances exist. If connectionTimeout errors show up in your logs, the problem is usually pool exhaustion, not the database being slow — requests are waiting for a free connection, not for a query.

On the server side, a few settings matter more than the rest. In Postgres, shared_buffers governs how much data stays cached in memory, and work_mem controls memory per sort or hash before it spills to disk; a sort that spills is a sort that crawls. The defaults ship conservative so the database starts anywhere, which means they are almost always low for a real server. Change them deliberately, one at a time, and watch the effect rather than copying a config from a forum post written for a different machine.

Measure, read the plan, change one thing, measure again. Almost every database win comes from that loop, not from a bigger box.

A quick troubleshooting table

When something is slow and you need a starting point, match the symptom to its usual cause and the first fix to try.

Questions people also ask

What is database optimization?

Database optimization is the practice of making queries return faster and the database server do less work for the same result. In day-to-day terms it means finding the queries that are slow, reading their execution plans to see why, and then fixing the cause, usually by adding an index, rewriting the query so it can use an index, or tuning how the application connects to and configures the database.

How do indexes speed up queries?

An index is a sorted structure, usually a B-tree, that lets the database jump straight to the rows matching a condition instead of scanning every row in the table. Without an index the engine reads the whole table, which is a sequential scan; with the right index it does an index seek and touches only the rows it needs. The trade-off is that every index adds cost to inserts and updates, so you index the columns your queries filter, join, and sort on, not every column.

What is a sargable query?

A sargable query is one written so the database can use an index to satisfy it. The word is short for Search ARGument able. Wrapping an indexed column in a function, such as WHERE YEAR(created_at) = 2026, forces the engine to compute the function for every row and it cannot use the index. Rewriting it as a range, WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01', keeps the column bare so the index can be used.

What causes slow database queries?

Most real-world slowness comes from a query scanning far more rows than it returns, which usually means a missing index or a query that cannot use the index that exists. Other common causes are non-sargable predicates, returning too many columns or rows, the N+1 pattern where an application runs one query per row in a loop, and connection pool exhaustion where requests wait for a free connection rather than for the database itself.