Database Performance Without Burning Cash: Indexes, Queries, Pagination and the Hidden Cost of Bad Data Access

Performance problems are often created one query at a time. A query that takes 20 milliseconds feels harmless. Multiply it by thousands of requests, several joins, a growing dataset, and concurrent users, and the harmless query becomes an infrastructure bill.

Start With the Query, Not the Hardware

The first instinct is often to buy a larger database server. That can postpone the problem without fixing it.

Better approach: inspect the slow query, examine its execution plan, measure rows scanned versus rows returned, and fix the access pattern before adding hardware.

Indexes: The Book Catalogue

An index is like the table of contents and catalogue in a book. If you need chapter 17, you do not read chapters 1–16 first.

Index columns that are frequently used for filtering, joins, uniqueness, and ordering when the workload benefits from them.

CREATE INDEX idx_users_email ON users(email);

But indexes have a cost: they consume storage and make writes more expensive because the index must also be maintained.

Composite Indexes: Put the Important Information in the Right Order

Suppose queries commonly ask for orders belonging to one customer and sort them by date. A composite index can match that access pattern.

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

The exact design depends on the database and workload. Measure instead of guessing.

Pagination Is a Resource Boundary

Pagination is not just a UI feature. It protects the database and the network from accidentally moving an entire dataset.

Offset pagination is simple:

SELECT id, name
FROM users
ORDER BY id
LIMIT 50 OFFSET 1000;

For very large or frequently changing datasets, cursor/keyset pagination can avoid repeatedly walking past large numbers of rows.

Do Not Make the Database Do Work You Do Not Need

• Avoid SELECT * when only a few fields are needed.
• Filter as early as practical.
• Avoid unnecessary joins.
• Do not sort millions of rows if the product only displays 20.
• Do not calculate the same expensive result on every request if it can be safely reused.

ORMs Are Tools, Not Performance Guarantees

An ORM can make application code cleaner while still generating terrible SQL.

Common trap: loading 100 products and then querying the database separately for each product's category, seller, or inventory.

Fix: inspect generated SQL, use eager loading or joins where appropriate, batch requests, and monitor query counts in development and production.

Connection Pools

A connection is a scarce resource. A pool keeps a controlled number of connections available instead of creating unlimited database sessions.

Simple example: a restaurant needs a manageable number of waiters. Hiring 5,000 waiters because 5,000 customers might arrive does not make service faster; they start getting in each other's way.

Pool sizing should consider application concurrency, database limits, workload, and the number of application instances.

Transactions and Locks

Transactions provide correctness, but long transactions hold resources and can increase contention.

Keep the transaction around the atomic database operation. Do not keep a transaction open while waiting for a payment provider, calling another API, or asking a human what to do.

Read/Write Separation and Replicas

At higher scale, read replicas can move appropriate read traffic away from the primary. But replication introduces consistency considerations.

Simple example: imagine several photocopies of a notebook. They reduce reading pressure, but a copy may briefly be behind the original.

Use replicas deliberately and understand where stale reads are acceptable.

Caching: Use It Where It Fits

Cache expensive data that is requested frequently and changes less often. Define expiration and invalidation rules. Otherwise, you can make an application faster while quietly serving stale information.

Database Constraints Are Performance and Correctness Tools

Primary keys, foreign keys, unique constraints, and appropriate data types prevent invalid states and make relationships explicit. A database that permits duplicate business identities because “the application will check” is relying on every future code path to remember the rule.

Measure the Right Things

• Query latency and p95/p99 latency.
• Rows scanned versus rows returned.
• Database CPU and I/O.
• Connection pool usage.
• Lock waits and deadlocks.
• Cache hit ratio.
• Replication lag.
• Storage growth and index size.

The Goal

Good database engineering is not “make every query fast.” It is making the system predictable as data, users, and concurrent work grow.