Database Performance Optimisation: Practical Techniques for Faster Queries

Most database performance advice gets weirdly mystical very quickly. In practice, the fastest route to a better database is usually less glamorous: measure first, find the actual slow path, and only then change schema, indexes, or query shape.

This guide is the practical version. It is the checklist I would want a team to work through before anyone starts adding random indexes or calling the whole thing a scaling problem.

Start with measurement, not guesses

The first question is not “how do we optimise the database?” It is “what is actually slow?” That might be one expensive query, a badly chosen index, N+1 behaviour in the application layer, poor pagination, slow network round-trips, connection churn, or background work running on the same hot path.

If your database supports slow query logging, turn it on. Then correlate slow queries with the actual user-facing action. You need the query text, frequency, timing, and context. A query that is slow once a day is a different problem from a query that is slightly slow on every request.

Use query plans properly

If you are on Postgres or MySQL, learn to read EXPLAIN and EXPLAIN ANALYZE. You do not need to become a database internals expert, but you do need to spot the difference between an index scan, a sequential scan, a bad join order, and an accidental sort over too much data.

The habit I like is simple:

• capture the slow query

• run it with realistic parameters

• inspect the plan before changing anything

• make one change at a time

• measure again

Indexing basics that still matter

Most performance work still comes back to indexing. The basics are boring because they work.

• Index columns you filter on frequently.

• Consider composite indexes when you filter and sort on the same fields together.

• Check whether the query can use the index in the order you expect.

• Do not keep adding indexes without checking write cost and overlap.

Composite indexes

Composite indexes are often the real fix. If you filter by tenant_id and status, then sort by created_at, a single-field index on each column might still be worse than one index designed for the full access pattern.

Over-indexing

Every index helps reads and taxes writes. Too many indexes make inserts, updates, and vacuuming heavier, and they can hide the fact that the real problem is a badly shaped query.

N+1 queries

N+1 problems are still one of the easiest ways to make a healthy database look broken. If your application loads a list of records and then fetches related data one row at a time, the issue is usually above the database rather than inside it.

Batch related reads. Join where it makes sense. Preload relationship data intentionally. Profile the application layer, not just the database.

Pagination, filtering, and sorting

Offset pagination is fine until it is not. On large datasets, big offsets become expensive because the database still has to walk past rows you are skipping. Cursor-based or keyset pagination is often the better choice when you care about consistency and scale.

Sorting is also easy to underestimate. If you sort on a field that is not supported by the right index, you may force a large in-memory or on-disk sort. That is especially painful when combined with filters that return more rows than the final page actually needs.

Avoid full table scans where they hurt

A full table scan is not automatically evil. It is a problem when it happens on a large, frequently hit table for a latency-sensitive request. Sometimes the answer is indexing. Sometimes it is reducing the result set earlier. Sometimes it is materialising a smaller reporting shape for a specific use case.

Caching and replicas

Cache after you understand the query. Do not use caching to avoid learning why something is slow.

Useful cache targets include:

• expensive read-mostly lookups

• aggregated dashboard numbers

• derived lists that do not need perfect immediacy

Read replicas help when you have real read pressure, but they also add lag and operational complexity. They are not a substitute for poor schema or poor queries.

Connection pooling and background work

If the database is constantly paying the cost of opening new connections, pooling can make a visible difference. This shows up a lot in serverless or bursty environments where application instances churn faster than a traditional server setup.

Also check whether the request path is doing work that should be offloaded: report generation, large exports, expensive recalculations, and fan-out updates often belong in background jobs or queues, not inside a user request.

Schema design and denormalisation

Normalisation is still a good default. Denormalisation is a targeted performance tool, not a religion. If a workload repeatedly needs a precomputed value, a summary table, or a flattened read model, that can be a sensible optimisation. Just make the write and sync rules explicit.

Database-specific notes

Postgres

Great general-purpose default. Learn query plans, vacuum behaviour, composite indexes, and how to spot sort or join pain early.

MySQL

Still common, still capable. The same fundamentals apply: indexes, query plans, and making sure the application is not causing the pain upstream.

MongoDB

Document shape and index design matter a lot. Avoid pretending every reporting or relational question belongs in the same document pattern.

Firestore

Firestore performance is often really a query-shape and index-planning problem. Keep reads shallow, avoid over-fetching, design collections around access patterns, and remember that security rules are not a performance strategy. My BaaS comparison and Firestore security article both connect back to this.

DynamoDB

DynamoDB rewards teams that model around access patterns up front. It punishes teams that assume they can discover those patterns later without redesign.

Common mistakes

• adding indexes without reading the query plan

• optimising the database when the real issue is application-side N+1 behaviour

• using offset pagination on very large datasets without questioning it

• caching bad queries instead of fixing them

• treating replicas or sharding as step one

• ignoring the write cost of every “helpful” index

Practical checklist

• Identify the slow request, not just the slow database.

• Enable slow query logging.

• Inspect the execution plan.

• Verify the right indexes exist for the real filter and sort pattern.

• Look for N+1 queries in the application.

• Review pagination strategy.

• Move expensive non-request work into background jobs.

• Add caching only where the data shape and freshness needs justify it.

• Measure again after every change.

Bottom line

Database optimisation is rarely about one trick. It is usually about getting disciplined: measure, inspect, simplify, index intentionally, and only escalate to more complex infrastructure when the workload actually proves you need it.