Five tiers of database performance engineering across relational and NoSQL systems: advanced indexing, sargable query rewrites, keyset pagination, schema design, memory tuning, and infrastructure scaling. Composite and covering indexes, MongoDB and Redis scripts, and connection-pooled keyset pagination across seven backend stacks.
Emmanuel Maneswa
Full Stack Software Engineer
Database performance problems rarely announce themselves as database problems. They show up as a checkout API that used to respond in 40ms and now takes 3 seconds, or a ledger reconciliation job that finishes in minutes on a small test dataset and grinds for hours in production. By the time the symptom reaches an engineer, the actual cause is usually buried three layers down: a missing composite index, a non-sargable predicate, an OFFSET pagination query scanning 200,000 discarded rows, or a connection pool sized for a demo, not for production concurrency.
This post is a working reference across five tiers of database performance engineering, indexing, query optimization, schema design, memory and configuration tuning, and infrastructure scaling, applied consistently across both relational systems (PostgreSQL, MySQL, Oracle, MSSQL) and NoSQL systems (MongoDB, Redis, DynamoDB). Every technique here is one I've reached for while debugging a genuinely slow production query, not a benchmark run in isolation.
Not every index is a B-Tree, and picking the wrong structure either fails to help or actively degrades write throughput for no benefit.
=), never ranges or sorting, but are marginally faster than a B-Tree for pure equality when the column has very high cardinality (e.g., a UUID primary key looked up only by exact match). In practice, the performance difference rarely justifies giving up range-query capability, so B-Tree remains the default even here.@>) in PostgreSQL. A GIN index on a JSONB column lets WHERE metadata @> '{"corridor": "ZWE-ZAF"}' seek directly, instead of scanning every row's JSON blob.daterange && daterange), and nearest-neighbour search. In banking systems, GiST is the right structure for overlap detection, for example, finding whether a new interest-rate period conflicts with an existing one.A composite index on (tenant_id, created_at) can serve a query filtering on tenant_id alone, or on tenant_id AND created_at, but cannot efficiently serve a query filtering on created_at alone. This is the leftmost prefix rule: a composite index is only usable from its leftmost column inward. Column order is therefore a cardinality and query-pattern decision, not an arbitrary one: put the column with the highest selectivity that appears in the most queries' WHERE clause first (commonly a tenant or account identifier), followed by the column used for range filtering or sorting (commonly a timestamp).
-- PostgreSQL: composite index, leftmost-prefix ordered — tenant_id (equality filter) before created_at (range/sort)
CREATE INDEX CONCURRENTLY idx_transactions_tenant_created
ON transactions (tenant_id, created_at DESC);
A standard B-Tree index stores only the indexed columns plus a pointer back to the full row (a CTID in PostgreSQL, a RID in SQL Server). Reading any column not in the index means a second I/O operation: the heap fetch, following that pointer back to the table. A covering index eliminates this second trip by including the additional columns a query needs directly in the index leaf node, using PostgreSQL's INCLUDE clause or an equivalent composite key in engines without a native covering syntax:
-- PostgreSQL: covering index — INCLUDE adds columns to the leaf node without adding them to the sort key
CREATE INDEX CONCURRENTLY idx_transactions_covering
ON transactions (account_id, created_at DESC)
INCLUDE (amount, status);
-- MSSQL: identical INCLUDE syntax
-- CREATE INDEX idx_transactions_covering ON transactions (account_id, created_at DESC) INCLUDE (amount, status);
-- Oracle: no native INCLUDE clause — achieve the same effect with all needed columns in the composite key
-- CREATE INDEX idx_transactions_covering ON transactions (account_id, created_at, amount, status);
When every column a query needs is present in the index, the database performs an index-only scan and never touches the table heap at all, the single highest-leverage indexing technique for read-heavy, high-frequency queries.
Most tables have a status column where one value (PENDING, FAILED) is rare relative to the whole (SETTLED, COMPLETED). Indexing the entire column wastes memory and write throughput maintaining entries for rows that are never queried by that predicate. A partial index (PostgreSQL) or filtered index (MSSQL) indexes only the rows matching a condition:
-- PostgreSQL: partial index — only PENDING rows are indexed, kept small and cheap to maintain on every write
CREATE INDEX CONCURRENTLY idx_transactions_pending
ON transactions (created_at)
WHERE status = 'PENDING';
On a table with millions of settled transactions and a few thousand pending ones, this index can be orders of magnitude smaller than a full index on status, and every INSERT/UPDATE on a non-pending row skips maintaining it entirely.
Every index accelerates reads and taxes writes: each INSERT, UPDATE, or DELETE must maintain every index on the affected columns. A table with eight overlapping indexes, several of which are redundant prefixes of others, pays that write cost eight times over for query patterns that only three of them actually serve. Diagnose this with the database's own usage statistics (pg_stat_user_indexes in PostgreSQL, sys.dm_db_index_usage_stats in SQL Server) and drop indexes with near-zero scan counts. A composite index on (account_id, created_at, status) also makes a separate index on (account_id) alone redundant, the leftmost prefix rule means the composite index already serves that query pattern.
SELECT * and its NoSQL equivalent (fetching the full document) pull every column across the network, deserialize every field into application memory, and defeat covering indexes outright, since the database can't serve the query from the index alone if it doesn't know which columns you actually need. Always project explicitly:
-- Avoid
SELECT * FROM transactions WHERE account_id = 'acct-42';
-- Prefer — matches a covering index exactly, and moves less data over the wire
SELECT id, amount, status, created_at FROM transactions WHERE account_id = 'acct-42';
A predicate is sargable ("Search ARGument ABLE") when the database can use an index to evaluate it directly. Wrapping an indexed column in a function destroys this, the optimizer can no longer seek into the index and falls back to scanning every row to evaluate the function per-row.
-- Non-sargable: YEAR() wraps the indexed column — the optimizer must evaluate this function on every row
SELECT * FROM transactions WHERE YEAR(created_at) = 2026;
-- Sargable: an equivalent range predicate lets the optimizer seek directly into the index
SELECT id, amount, status FROM transactions
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- Non-sargable: UPPER() wraps the indexed column
SELECT * FROM accounts WHERE UPPER(email) = 'JANE@BANK.COM';
-- Sargable: normalize the data itself (a generated column, or a case-insensitive column type),
-- never the predicate at query time
SELECT id, email FROM accounts WHERE email_normalized = 'jane@bank.com';
The rule generalizes: any transformation, a function call, an implicit type cast, string concatenation, applied to the column inside the WHERE clause forces a full scan. Apply the transformation to the input value instead, or precompute and store it as its own indexed column.
The query planner chooses between a nested loop join (for each row in the outer table, probe the inner table, efficient when the outer set is small) and a hash join (build an in-memory hash table of the smaller side, then probe it with the larger side, efficient when both sides are large and an index isn't available on the join column). Forcing the wrong strategy, or starving the planner of accurate statistics, is a common source of query plans that look fine in EXPLAIN on a small dataset and collapse in production.
For subqueries, prefer EXISTS over IN when checking for the presence of related rows: EXISTS can short-circuit on the first match, while IN typically materializes the full subquery result set before comparing, a meaningful memory difference when the subquery returns thousands of rows.
-- IN materializes the full subquery result before comparing
SELECT * FROM accounts a WHERE a.id IN (SELECT account_id FROM flagged_transactions);
-- EXISTS short-circuits on the first match per outer row
SELECT * FROM accounts a
WHERE EXISTS (SELECT 1 FROM flagged_transactions f WHERE f.account_id = a.id);
Pagination Latency vs Page Depth
OFFSET pagination asks the database to count past and discard every row before the requested page, an O(N) cost that grows linearly with page depth. Page 1 is instant; page 10,000 forces the database to scan and throw away everything before it, every single time. Keyset (seek) pagination replaces the offset with a WHERE predicate on the last row seen, letting the index seek directly to the right position regardless of how deep the page is.
-- OFFSET pagination: the database scans and discards 50,000 rows before it can return page 1,001
SELECT id, amount, status FROM transactions
ORDER BY created_at
OFFSET 50000 LIMIT 50;
-- Keyset pagination: the WHERE clause seeks directly to the right leaf node — O(1) regardless of depth
SELECT id, amount, status FROM transactions
WHERE created_at > '2026-08-01T14:32:00Z' -- the last_seen value returned by the previous page
ORDER BY created_at
LIMIT 50;
The tradeoff: keyset pagination can't jump directly to an arbitrary page number ("go to page 47"), only to "the next 50 after this cursor." For infinite-scroll feeds and deep transaction history, an interface pattern that never needed arbitrary page-jumping in the first place, this is the correct tradeoff to make.
Column type choice is a memory and disk layout decision, not a naming preference. An INT (4 bytes) versus a BIGINT (8 bytes) doubles storage for every row and every index entry on that column, at billions of rows this compounds into a real difference in buffer cache pressure. A VARCHAR(255) on a column that only ever holds a 3-character currency code wastes nothing on disk in most engines (variable-length types store only what's used) but does waste planner statistics accuracy and index bloat headroom, size columns to their actual domain. For monetary values, never use floating-point types (FLOAT, DOUBLE, float64, JavaScript's native number), they cannot represent decimal fractions like 0.10 exactly in binary and will silently accumulate rounding error across enough transactions. Use DECIMAL(19,4) or the engine's exact-precision numeric type, sized to the largest expected amount and the currency's minor-unit precision.
Third normal form (3NF) eliminates redundancy and guarantees update anomalies can't occur, exactly the property a transactional ledger needs. But a read-heavy reporting dashboard querying a fully normalized schema pays a join-heavy tax on every request. Strategic denormalization duplicates specific, carefully chosen data (a customer's current tier stored directly on the transaction row, rather than joined from a customers table on every query) to trade a small, controlled amount of write complexity (keeping the duplicate in sync) for a large read-latency win on the hot path. The discipline that keeps this safe: denormalize derived, rarely-changing data into read models or materialized views, and never denormalize the system of record itself, the ledger's core tables stay in 3NF; a separate reporting schema can be as denormalized as the read pattern demands.
Partitioning splits one logical table into multiple physical segments, transparently to most queries, by range (commonly a date column, ideal for time-series ledger data), list (a fixed set of discrete values, e.g., partitioning by region code), or hash (evenly distributing rows by a hash of a key, useful when there's no natural range or list boundary, e.g., partitioning by tenant_id across a multi-tenant platform).
-- PostgreSQL: range partitioning a ledger table by created_at
CREATE TABLE ledger_entries (
id BIGINT NOT NULL,
tenant_id BIGINT NOT NULL,
amount DECIMAL(19,4) NOT NULL,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE ledger_entries_2026_07 PARTITION OF ledger_entries
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE ledger_entries_2026_08 PARTITION OF ledger_entries
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
-- A query filtered to August prunes every partition except ledger_entries_2026_08 — it is never scanned
SELECT * FROM ledger_entries WHERE created_at >= '2026-08-01' AND created_at < '2026-09-01';
The payoff is partition pruning: a query with a filter on the partition key never touches partitions outside its range, turning a scan across a 500-million-row table into a scan across a single 15-million-row monthly partition. Sharding takes this a step further by distributing partitions across separate physical database instances entirely, the right move once a single instance's I/O or CPU capacity is the actual ceiling, not before.
Database Memory & Configuration Tuning Parameters
| Memory Structure | MySQL | PostgreSQL | Oracle DB | MSSQL |
|---|---|---|---|---|
| Buffer Cache / Buffer Pool | innodb_buffer_pool_size | shared_buffers | DB_CACHE_SIZE | max server memory |
| Sort Memory | sort_buffer_size | work_mem | SORT_AREA_SIZE / PGA | index create memory |
| Join / Hash Buffers | join_buffer_size | hash_mem_multiplier (× work_mem) | HASH_AREA_SIZE / PGA | min/max memory grant |
| Write Buffer / Redo Log | innodb_log_buffer_size | wal_buffers | Redo Log Buffer | log buffer |
| Max Connections | max_connections | max_connections | SESSIONS / PROCESSES | max worker threads |
Every relational engine keeps the same three memory structures under different names, and mistuning any of them produces the same failure mode regardless of vendor: excessive disk I/O for data that should be served from memory.
sort_buffer_size in MySQL, work_mem in PostgreSQL) bounds how much memory a single sort or hash operation can use before spilling to disk. Undersized, every ORDER BY or hash join on a moderately large result set spills to a temp file, disk I/O for an operation that should be pure memory. This is set per-operation, not globally, so raising it too aggressively on a high-concurrency system can exhaust memory when many sessions sort simultaneously.innodb_log_buffer_size in MySQL, wal_buffers in PostgreSQL, the redo log buffer in Oracle) holds transaction log records before they're flushed to durable storage. Undersized under write-heavy load, transactions block waiting for log buffer space to free up, a direct throughput ceiling on write-heavy ledger workloads.None of these parameters have a universal correct value, they're a function of available system memory, workload shape, and concurrency, and should be tuned against a representative production-like load test, not copied from a blog post's example numbers.
Every new database connection costs a TCP handshake, authentication, and, in PostgreSQL's case, spawning an entire new backend process, expensive enough that opening a fresh connection per request is a direct throughput ceiling under load, and a source of CPU spent on context switching between far more connections than the database can usefully execute concurrently. PgBouncer (PostgreSQL) and ProxySQL (MySQL) sit between the application and the database, maintaining a small pool of real connections and multiplexing many client requests across them. Application-level pools (HikariCP in Java, database/sql's pool in Go, SQLx's PgPoolOptions in Rust) serve the same purpose within a single service and should always be explicitly sized, never left on framework defaults, which are tuned for nothing in particular.
Read replicas, kept current via WAL streaming (PostgreSQL) or binary log replication (MySQL), let read-heavy traffic (reporting, dashboards, balance checks that can tolerate a few hundred milliseconds of staleness) scale horizontally without adding load to the primary that handles writes. The operational discipline this demands: replication lag is real and must be monitored, a read immediately following a write, on the same request, needs to either read from the primary or tolerate seeing stale data. Never assume a replica is caught up; measure it.
For hot, rarely-changing lookups in a banking system, current FX rates, account tier configuration, feature flags, cache-aside with a short TTL is almost always the right default: simple to reason about, and the blast radius of a stale read is small and time-bounded.
A ledger table retaining every transaction since inception eventually dwarfs the working set that actually matters for day-to-day OLTP traffic, most queries only ever touch the last 90 days. Moving cold partitions (the range-partitioned tables from Tier 3 make this a metadata operation, not a row-by-row delete) to an analytical object store, S3 with a columnar format, BigQuery, or Snowflake, keeps the operational database's working set small and its buffer cache effective, while preserving the historical data for compliance and analytics workloads that are better served by a warehouse anyway.
// MongoDB: compound index — tenant_id first (equality filter), created_at second (range/sort)
db.transactions.createIndex({ tenant_id: 1, created_at: -1 });
// MongoDB: covered query — the projection matches the index exactly, no document fetch required
db.transactions.find(
{ tenant_id: "acct-42" },
{ _id: 0, amount: 1, status: 1, created_at: 1 }
).sort({ created_at: -1 }).limit(50);
The same leftmost-prefix principle from relational composite indexes applies here: MongoDB's compound index is only usable from its first field inward, and a query filtering only on created_at cannot use this index efficiently.
# Redis: pipelined batch GET — commands are queued client-side and sent in a single round trip
PIPELINE START
GET balance:acct-42
GET balance:acct-43
GET balance:acct-44
GET balance:acct-45
PIPELINE EXEC
Without pipelining, fetching 200 account balances for a batch settlement run costs 200 network round trips. Pipelined, every Redis client library queues commands locally and sends them as a single batch, collecting all replies in one round trip, the single highest-leverage Redis optimization for any batch read or write pattern.
DynamoDB has no query planner to optimize around, its performance is entirely a function of key design chosen at table-creation time. A partition key with high cardinality (an account ID, not a low-cardinality status flag) spreads load evenly across DynamoDB's internal partitions, avoiding hot-partition throttling. A composite sort key (created_at#transactionId) lets a single query retrieve a time-ordered range of a partition's items directly, DynamoDB's equivalent of a relational composite index's leftmost-prefix range scan, with no equivalent to a runtime "add an index" migration once the access pattern is chosen.
The two techniques with the highest production impact, keyset pagination and properly bounded connection pooling, applied together across seven backend stacks. The anti-pattern in every language below is the same: OFFSET-based pagination combined with an unbounded or default connection pool, a combination that performs fine in development and degrades under real concurrency and real data depth.
Keyset Pagination & Connection Pooling: OFFSET vs Seek
1// C#: EF Core keyset pagination + compiled query — O(1) per page regardless of depth
2private static readonly Func<AppDbContext, string, DateTime, int, IAsyncEnumerable<TransactionDto>> GetPageCompiled =
3 EF.CompileAsyncQuery((AppDbContext db, string accountId, DateTime lastSeenCreatedAt, int pageSize) =>
4 db.Transactions
5 .Where(t => t.AccountId == accountId && t.CreatedAt > lastSeenCreatedAt) // sargable range predicate
6 .OrderBy(t => t.CreatedAt)
7 .Take(pageSize)
8 .Select(t => new TransactionDto { Id = t.Id, Amount = t.Amount, CreatedAt = t.CreatedAt })); // explicit projection
9
10public async Task<List<TransactionDto>> GetPageAsync(string accountId, DateTime lastSeenCreatedAt, int pageSize)
11{
12 var results = new List<TransactionDto>();
13 await foreach (var row in GetPageCompiled(_db, accountId, lastSeenCreatedAt, pageSize))
14 results.Add(row);
15 return results; // compiled query skips EF's expression-tree translation cost on every call
16}Optimization Techniques Summary
| Technique | Target Tier | Read Impact | Write Impact | Complexity | Best Use Case |
|---|---|---|---|---|---|
| Composite / Covering Index | Tier 1: Indexing | High ↑ | Moderate ↓ (extra index to maintain) | Low | High-frequency filtered reads (e.g. account_id + created_at lookups) |
| Partial / Filtered Index | Tier 1: Indexing | High ↑ | Low ↓ (only matching rows indexed) | Low | Sparse status flags, e.g. WHERE status = 'PENDING' |
| Sargable Predicate Rewrite | Tier 2: Query Optimization | High ↑ | None | Low | Range-based date/timestamp filters replacing function-wrapped columns |
| Keyset / Seek Pagination | Tier 2: Query Optimization | High ↑ (constant time) | None | Medium | Infinite-scroll feeds, deep transaction history pagination |
| Strategic Denormalization | Tier 3: Schema Design | High ↑ | Moderate ↓ (extra writes to sync) | Medium | Read-heavy aggregate dashboards, reporting tables |
| Range / Hash Partitioning | Tier 3: Schema Design | High ↑ (partition pruning) | Low ↓ | High | Ledger tables partitioned by created_at or tenant_id |
| Buffer Pool / Cache Tuning | Tier 4: Memory Tuning | High ↑ | Neutral | Medium | High-concurrency OLTP workloads with a hot working set |
| Connection Pooling | Tier 5: Infrastructure | High ↑ (reduced connection churn) | High ↑ | Low | Any service issuing frequent short-lived queries |
| Read Replicas | Tier 5: Infrastructure | High ↑ (horizontal read scale) | Neutral | Medium | Read-heavy reporting offloaded from the primary |
| Cache-Aside with Redis | Tier 5: Infrastructure | Very High ↑ | Low ↓ (cache invalidation overhead) | Medium | Hot, rarely-changing lookups: balances, FX rates, config |
Read this table as a triage tool: when a query is slow, start from the top of this list and work down. Indexing and sargability fixes are almost always lower-complexity and higher-impact than infrastructure changes, exhaust Tier 1 and Tier 2 before reaching for a read replica or a caching layer to paper over a query that a covering index would have fixed outright.
Database performance engineering is a five-tier discipline, indexing, query sargability, schema design, memory configuration, and infrastructure scaling, and the tiers are ordered by leverage, not by difficulty. A missing composite index or a non-sargable predicate wrapped around a timestamp column routinely explains a 100x latency difference that no amount of read-replica scaling or cache tuning would have fixed, because those higher-tier techniques would have been scaling a fundamentally inefficient query pattern, not fixing it.
The patterns that compound across every engine in this post, sargable predicates, covering indexes, keyset pagination, bounded connection pools, precision-correct data types for money, are not vendor-specific tricks. They are the same underlying principle expressed in PostgreSQL's INCLUDE clause, MongoDB's compound index projections, and DynamoDB's key design: give the database engine exactly what it needs to answer the query directly, and nothing it has to work around. In the ledger and core banking systems I've built, the queries that caused production incidents were never the ones running exotic aggregate logic, they were the simple lookups that had quietly stopped being sargable, or the pagination endpoint that nobody noticed had degraded to a linear scan, until the dataset grew large enough to make it visible.