Skip to content
Architect's Handbook Wiki

Database performance

Technique · Chapter 10

The idea in brief

Section titled “The idea in brief”

The database is a key part of most systems, so improving system performance means improving database performance. An architect should be conversant with schema design, indexing, scaling, and concurrency even when a DBA owns the details.

Designing an efficient schema

Section titled “Designing an efficient schema”

An efficient, well-designed schema is the foundation of database performance.

Normalization

Section titled “Normalization”
  • Designing tables (relations) and columns (attributes) to meet data requirements while minimizing redundancy and increasing integrity.
  • Keep the minimal number of attributes needed; group logically related attributes together; minimize redundancy → easier consistency, smaller database.

Denormalization

Section titled “Denormalization”
  • A deliberate performance/scalability strategy applied after normalizing — not skipping normalization.
  • Shortens execution time of certain queries by storing redundant copies or grouping data to minimize joins.
  • Trade-offs: improves reads but hurts writes; needs a compensating action (and DB constraints) to keep redundant data consistent; enlarges the database/disk footprint.
  • Also used to keep historical data (e.g. store the address on each order so past orders show the address at order time). Alternatives without denormalizing: treat address as an immutable value object (new record on change), or reconstruct history from an event-driven system / data audit. See Domain-driven design.

Keys

Section titled “Keys”
  • Primary key — column(s) that uniquely identify a row.
  • Foreign key — column(s) holding another table’s primary-key value to reference its row.
  • Create primary-key and foreign-key constraints to enforce integrity.

Data types

Section titled “Data types”
  • Pick the most appropriate type per column; also weigh size and nullability.
  • Choose a type that holds all possible values but is the smallest sufficient one → efficiency in performance and storage.

Using database indexes

Section titled “Using database indexes”

Indexes are stored on disk, associated with a table/view, to speed retrieval. Two main types:

  • Primary / clustered index — orders rows physically on disk by the primary key. A table can have only one (rows sort in one order). Common; skip only for very small tables that will stay small (index overhead not worth it). An unordered table (no clustered index, relying on secondary indexes) is a heap.
  • Secondary / non-clustered index — one or more columns ordered logically, acting as pointers to the rest of the record; its order doesn’t match the physical disk order. Provides alternate access keys (e.g. a foreign key or any column often used in joins, WHERE, ORDER BY, GROUP BY). Example: an Order table keyed by OrderId but often queried by CustomerId benefits from a non-clustered index on CustomerId.

Having too many indexes

Section titled “Having too many indexes”
  • Every insert/update must also update index records → extra transaction overhead; indexes consume extra disk.
  • More indexes = more work for the query optimizer (the DBMS component that picks the most efficient execution plan, e.g. full table scan vs. index use) → can hurt performance.
  • Be selective: well-chosen indexes speed access; unnecessary ones slow inserts/updates and data access.

Scaling up and out

Section titled “Scaling up and out”
  • Don’t scale blindly. First ensure the schema is well designed, indexes are applied properly, and the application is optimized to remove bottlenecks.
  • Then, if resource use is high: scale up first (better machine, more processors/memory) — it’s simpler.
  • Scale out only if scaling up is insufficient — it adds complexity: horizontal partitioning of tables, data replication, and more complex disaster-recovery/failover planning.

Database concurrency

Section titled “Database concurrency”

A relational DB handles many simultaneous connections; performance only helps if it also handles concurrent access/change correctly. Concurrency control keeps concurrent transactions from violating data integrity.

Database transactions

Section titled “Database transactions”
  • A transaction is a sequence of operations executed as a single unit of work — it either completes entirely or isn’t committed at all.
  • Provides recovery from failure, isolation from concurrent modifications, and durable persistence once complete.

Optimistic vs. pessimistic concurrency control

Section titled “Optimistic vs. pessimistic concurrency control”
  • Optimistic (optimistic locking): assumes conflicts are rare; runs transactions without locking, checking for conflicts at change time. On conflict, one succeeds and the others fail.
  • Pessimistic (pessimistic locking): assumes conflicts are likely; locks required resources for the transaction’s duration. Barring a deadlock, the transaction completes. Most DBMSs offer multiple lock types (e.g. some allow concurrent reads of a locked record, some don’t).

Consistency models

Section titled “Consistency models”

CAP theorem (Brewer’s theorem)

Section titled “CAP theorem (Brewer’s theorem)”

A distributed system can guarantee only two of these three:

  • Consistency — every read returns the latest data or an error; transactions commit fully or roll back.
  • Availability — the system always responds to every request.
  • Partition tolerance — with data partitioned across servers, the system keeps working if a node fails.

Traditional RDBMSs favor consistency + availability with strong (immediate) consistency → ACID. Some NoSQL databases favor availability + partition tolerance, accepting eventual consistency → BASE. See Availability.

ACID model

Section titled “ACID model”

For consistency + availability; transactions are:

  • Atomicity — all modifications happen or none do (e.g. credit + debit inserts both commit or neither).
  • Consistency — data ends in a valid state honoring integrity constraints; otherwise the transaction aborts with an error.
  • Isolation — concurrent transactions’ changes are isolated from each other (via isolation levels / locking).
  • Durability — once committed, changes persist permanently (e.g. via a transaction log usable to recreate state before a failure).
  • Strong consistency limits performance and scalability.

BASE model

Section titled “BASE model”

For availability + partition tolerance (some distributed NoSQL); trades strong for eventual consistency to gain scalability/speed:

  • Basic availability — the DB responds most of the time; conflicts can occur and a response may report a failure.
  • Soft state — system state can change over time even without new transactions (due to eventual consistency).
  • Eventual consistency — changes eventually propagate everywhere; absent further changes, data becomes consistent. Reads may return stale data until updates apply; the system doesn’t check every transaction’s consistency.
Section titled “Related concepts”

Citations

Section titled “Citations”
  • Software Architect’s Handbook (Packt, 2018), Ch.10 “Database performance”, pp. 794-817.