← Back to Blog

Relational Databases: Performance and Tradeoffs

By

A relational database does more than store rows and answer queries. It keeps related data consistent, coordinates concurrent changes, and recovers committed work after failures. Those capabilities are useful, but the mechanisms behind them consume resources and sometimes make operations wait.

Understanding performance means looking at both the processing a design requires and the guarantees it provides. Reducing repeated calculations can make reads faster. Reducing checks or coordination can also improve performance, but may shift responsibility to the application, allow stale data, or weaken protection against failures.

We will start with how data is organised, follow how it is read and changed, and then examine the guarantees around those changes. The examples use PostgreSQL; implementation details differ between database engines.

Normalisation: Giving Each Fact a Home

Normalisation organises tables around the facts they represent and the dependencies between those facts. Its practical purpose is to avoid storing the same current fact in many places where the copies can disagree.

Suppose every order contains the customer’s current email:

Order IDCustomer IDCustomer email
1017sam@example.com
1027sam@example.com
1037sam@example.com

The email belongs to the customer, but it is repeated for each order. Changing it requires updating every copy. Miss one row and the database now gives different answers depending on which order is read. This is an update anomaly. Similar designs can make it awkward to store a customer before they have an order, or accidentally remove the last record of a customer when deleting an order.

Separate the facts instead:

erDiagram
    CUSTOMER ||--o{ PURCHASE_ORDER : places
    CUSTOMER {
        int id PK
        string current_email
    }
    PURCHASE_ORDER {
        int id PK
        int customer_id FK
        datetime created_at
    }

Now an order refers to a customer, and the current email has one home. A foreign key can enforce that the referenced customer exists.

Normal forms provide increasingly precise rules for dependencies. For example, a non-key attribute should not depend on just part of a composite key, or indirectly on another non-key attribute. For everyday design, the useful question is: which entity does this fact describe, and what determines its value?

What changes when data is separated?

Separating data changes the work required to read and update it:

That last step introduces work, but its cost depends on how much data the database must process. Joining a few rows found through suitable indexes can be cheap. Combining large datasets requires more processing and memory; some join methods may also need temporary disk space when their working data does not fit in memory.

Fetching each table separately does not remove the work of combining them. It moves that work into the application and can add network requests. The useful question is therefore how much work the complete read requires, wherever that work happens.

When does separation become excessive?

Separation becomes a concern when common reads repeatedly need to reconstruct data from many tables, and those extra lookups or joins add a significant cost.

For example, suppose several attributes describe the same entity and are normally read and updated together. Putting each attribute in a separate one-to-one table may force every read to join those tables without preventing any additional duplication. More tables do not automatically mean better normalisation.

A different case is a reporting query that repeatedly joins large tables and calculates the same totals. The underlying design may be appropriate, but rebuilding the result for every request can still be expensive. This is where storing a prepared result may help.

Before making either change, distinguish the cost of reconstructing the data from other reasons a read may be slow:

Address those causes first. If repeatedly combining the data is still the main cost, consider a targeted change to how that read is served rather than redesigning every table.

How to compromise

If combining the same data remains expensive after improving the query, you can do that work less often. This usually means keeping the normalised tables and storing an additional result for a particular read. Deliberately storing duplicated or calculated data this way is a form of denormalisation.

For example, a report might calculate daily sales totals from thousands of order items every time someone opens it. A summary table could store one total per day instead. Reading the report becomes cheaper because the totals have already been calculated. The work has moved to maintaining those totals.

Choose the smallest change that addresses the expensive read:

For the options that store additional results, the next question is how current must the result be?

flowchart TD
    result["Store a result to make reads cheaper"] --> freshness{"Must it reflect every committed change?"}
    freshness -->|"Yes"| immediate["Update source and stored result<br/>in the same transaction"]
    freshness -->|"A delay is acceptable"| later["Refresh the stored result later"]
    immediate --> writecost["More work on each relevant write"]
    later --> stalecost["Readers may see an older result"]

A PostgreSQL materialised view follows the second approach: it stores a query’s results until refreshed. Changes to the underlying tables do not automatically update those results.

Keep the original tables as the source of truth so stored results can be checked and rebuilt. For each extra value or summary, make these decisions explicit:

The compromise is cheaper reads in exchange for additional storage, maintenance work, and possibly less up-to-date results. Apply it to the reads that need it, rather than duplicating data throughout the schema.

Indexes: Doing Less Work to Find Rows

Before duplicating data to avoid joins, check how the database finds the rows it needs.

An index is an additional structure that organises selected values and points toward matching rows. A B-tree index, a common type, keeps values ordered so the database can navigate to a relevant range instead of checking every row.

The benefit depends on selectivity: how much of the table the condition excludes. Finding one customer among millions is very different from reading most of a table. For a broad read, scanning the table can be cheaper than following many index entries.

Why more indexes can hurt

Indexes occupy storage and compete for memory. Inserts must add index entries, and updates to indexed values require maintenance. More indexes can therefore increase write work and logging, even while reducing read work. PostgreSQL has optimisations that can avoid some index maintenance for certain updates, so the cost is not identical for every write. Its indexing guide explains the general tradeoff.

An index on several columns also has an order. A B-tree index on (customer_id, created_at) is naturally suited to finding one customer’s orders within a date range. A date-only search has a different access pattern; do not assume the same index is equally efficient. PostgreSQL documents multicolumn index behaviour.

Choose indexes for recurring filters, joins, and ordering. Verify their effect with a query plan and representative data. A small development table may hide both the benefit of an index and the cost of maintaining it.

Finding rows efficiently is only part of the problem. When an operation updates several rows or tables, we also need to ensure those changes succeed or fail together. That is where transactions come in.

A transaction groups database operations into one unit that commits or rolls back. If an operation deducts stock and creates an order, those changes usually need to succeed together. Splitting them into independent commits can leave stock deducted without an order when the second step fails.

That boundary has a performance consequence. A transaction uses a connection, and locks acquired by writes normally remain until it finishes. Another transaction trying to change the same row may have to wait. Ordinary reads can still proceed, but competing writes cannot ignore the lock. See PostgreSQL’s locking documentation.

sequenceDiagram
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: Update a row
    Note over A,DB: A holds the row lock
    B->>DB: Update the same row
    Note over B,DB: B waits
    Note over A: Wait for a remote service
    A->>DB: Commit
    DB-->>B: B can continue

The remote call changes nothing in the database, yet it extends the time other writers wait. Database transactions also do not automatically roll back work already performed by a remote service.

Keeping transactions short without breaking the operation

Do validation and computation outside the transaction when they do not depend on protected database state. Keep the reads and writes that must remain coordinated inside it. Moving a critical check outside the boundary can make the operation incorrect, even if it reduces lock time.

For bulk work, bounded batches can release connections and locks between batches. The compromise is that the entire job is no longer one atomic operation. The job must tolerate partial progress and resume safely.

Transaction boundaries affect both correctness and performance. These tradeoffs become clearer when we look at the guarantees a transaction provides.

ACID: What a Transaction Guarantees

The previous section explained why related changes belong in a transaction. ACID describes four properties that make those transactions dependable: Atomicity, Consistency, Isolation, and Durability.

Each property answers a different question:

PropertyQuestion it answers
AtomicityWhat happens if only part of the operation succeeds?
ConsistencyDoes the operation leave the data in a valid state?
IsolationWhat happens when other transactions run at the same time?
DurabilityWill a successful commit survive a crash?

These guarantees require the database to do more than execute SQL. It must enforce rules, coordinate competing work, and preserve committed changes. To understand the performance tradeoffs, look at each property separately.

Atomicity: The changes succeed or fail together

Suppose placing an order requires reducing stock and inserting an order row. If the insert fails, atomicity ensures the stock reduction is rolled back too. The database does not leave half of that transaction committed.

The transaction boundary determines which changes receive this protection. It covers the database work in that transaction; it does not undo an external payment made through an HTTP call.

The performance tradeoff is how much work you group together:

For an import, smaller batches may be a useful compromise if partial progress is acceptable. For an order and its stock deduction, separate commits would weaken the all-or-nothing behaviour the operation needs.

Consistency: The data still follows its rules

Atomicity keeps changes together. Consistency asks whether those changes leave valid data behind.

For example, an order should refer to an existing customer, and a required order number should be unique. The database can enforce those rules through constraints:

The application remains responsible for rules the database has not been told how to enforce. A transaction can successfully commit the wrong calculation if no constraint rejects it.

The performance cost is checking and enforcing those rules. A foreign key requires checking a reference, while uniqueness must be protected even when concurrent requests try to insert the same value.

Removing a constraint may remove some checking work, but it also removes protection. A replacement application check needs to remain correct when requests overlap. Optimise the work around essential constraints before considering their removal.

Isolation: Concurrent transactions have defined boundaries

Several requests can use the database at once. Isolation determines which changes they can see and how competing operations interact.

For example, while one transaction updates a stock row, another transaction trying to update that row may wait until the first finishes. An ordinary read can still see previously committed data rather than the unfinished update.

Different isolation levels provide different protections. Some allow successive reads to see newly committed changes. Others keep a stable view across the transaction. Stronger protection can also require rejecting conflicting work and retrying the whole transaction. PostgreSQL documents its isolation levels here.

The performance costs can include:

Choose protection based on the operation’s correctness requirements. Keep transactions short to reduce unnecessary waiting; lowering isolation is only appropriate when the operation remains correct with the weaker guarantees.

Durability: A successful commit survives a crash

Once the database reports a durable commit, a subsequent database crash should not erase that committed work, assuming the storage system honours its persistence guarantees.

PostgreSQL supports this through a recovery log called write-ahead logging, or WAL. The log records changes so the database can restore them if it crashes before those changes have been written to the table files. Under normal durable settings, the required log records must reach durable storage before PostgreSQL reports a successful commit. PostgreSQL explains WAL here.

Normal commit flow

flowchart LR
    changes["Execute changes and generate WAL"] --> persist["Persist required WAL"]
    persist --> success["Report a successful commit"]

Changed table pages can be written later. If the database crashes before that happens, it can recover the committed changes from the stored log.

Recovery after a crash

flowchart LR
    crash["Restart the database"] --> read["Read stored WAL"]
    read --> replay["Replay changes needed for recovery"]

The performance cost is that commit may need to wait for storage. A transaction can execute its SQL quickly yet still spend time waiting at commit.

PostgreSQL offers a specific compromise: with synchronous_commit = off, it can report success before the log records reach durable storage. This may reduce acknowledgement latency, but a crash can lose recently acknowledged transactions. It does not change isolation or make partial transactions valid.

A replica protects against a different failure

Local WAL helps when the database crashes but its storage survives. If that storage is lost, the local log is lost too. Recovering then requires another copy, such as a replica or a suitable backup.

A replica is another database server that maintains a copy of the data. PostgreSQL streaming replication sends WAL from the main server, called the primary, to the replica. The replica uses that log to reproduce changes. Replication therefore builds on WAL; it does not replace it.

The performance decision is whether the primary waits for the replica before reporting a successful commit:

These are two separate decisions: waiting for the primary’s own WAL to become durable, and waiting for a replica. Asynchronous commit changes the first; asynchronous replication changes the second. PostgreSQL’s commit settings explain these options.

How to choose a compromise

ACID is not one setting to turn down when the database is slow. Each property protects something different, so first identify the source of the cost:

Only relax a guarantee when the application can tolerate the resulting behaviour. Faster acknowledgement is a useful tradeoff for some disposable data; it is a different decision for an order the customer has been told is saved.

Capacity: When More Concurrent Work Makes Things Slower

So far, we have looked at the work required by individual reads and transactions. Under load, those operations also compete with one another. A query that finishes quickly on its own may take much longer when hundreds of requests run alongside it.

Why more concurrency stops helping

Running more operations at once can increase throughput while the database has spare capacity. Eventually, however, they compete for a limited resource:

Once the limiting resource is fully occupied, more concurrent work mainly adds contention and waiting. Requests take longer without a corresponding increase in completed work.

For example, many requests updating one stock row create a bottleneck around that row. Opening more connections does not allow those updates to bypass one another’s locks.

Why increasing the connection pool can make it worse

A connection pool lets the application reuse database connections and limits how many it can use at once. When all connections are occupied, new requests usually wait for one to become available.

Increasing the pool may seem like the obvious fix for that wait. It helps if the database has spare capacity. If the database is already overloaded, it admits more work into the same bottleneck:

flowchart TD
    traffic["More requests arrive"] --> active["More simultaneous database work"]
    active --> waiting["Resource contention and lock waits"]
    waiting --> latency["Requests take longer"]
    latency --> occupied["Connections stay occupied longer"]
    occupied --> queue["More requests queue or retry"]
    queue --> active

Pool sizes also add up across application instances. Twenty instances with thirty connections each can request six hundred connections. Background jobs and extra instances running during deployments add to that total. Choose a connection budget for the whole application based on what the database can handle.

How to handle more requests than the database can serve

Keep the amount of admitted work within that capacity, and control what happens to the rest:

This is backpressure: making the caller slow down or handle rejection when the database cannot keep up. It protects the system from an ever-growing backlog, but the compromise is that some requests wait, complete later, or are refused.

Find where the waiting happens

Before changing capacity limits, distinguish three sources of delay:

Measure these separately. The database cannot report a request that has not yet obtained a connection. Increasing the pool will not release a lock or make an expensive query cheaper; it only helps when the database can use the additional concurrency productively.

Making a Compromise You Can Explain

The choices above address different problems. An index reduces the work of finding rows. A stored summary reduces repeated calculations. A smaller connection pool controls how much work reaches the database at once. Choosing between them starts with identifying the cost you actually need to reduce.

Identify the bottleneck

Break down where an operation spends its time:

For query execution, a plan shows how the database finds and combines rows. PostgreSQL’s EXPLAIN ANALYZE also runs the statement and reports actual execution measurements, allowing you to compare estimated and actual row counts. Because it executes the statement, writes have real effects; use a suitable environment. The EXPLAIN guide explains how to read these plans.

Use application timings and database monitoring alongside the plan. A fast query plan does not explain time spent waiting in the application’s connection queue.

Choose the change and accept its cost deliberately

Once the bottleneck is clear, choose a change that addresses it. Each option has a consequence to account for:

ChangePotential benefitCost or responsibility
Denormalise a frequent readLess repeated join or aggregation workMaintain duplicated values or accept staleness.
Add an indexLocate relevant rows more cheaplyMore storage and write maintenance.
Shorten a transactionRelease resources soonerPreserve the changes that must succeed together.
Use targeted locksProtect a shared ruleCompeting operations may wait, and every relevant writer must follow the locking rule.
Use asynchronous commitReduce commit acknowledgement latencyRecent acknowledged work may be lost after a crash.
Bound concurrencyKeep overload from growing without limitSome requests wait, are deferred, or are rejected.

Before applying a change, make three things clear:

Check the complete workload

Compare the result using realistic data volumes and concurrent traffic. Measure more than the operation you intended to speed up:

A faster report is useful, but its maintenance might slow order creation. A faster commit is useful only if the resulting data-loss risk is acceptable. Judge the change against the complete workload and the guarantees the application needs.

References