Databases

A database stores state while enforcing rules under concurrent access and failure. The main engineering questions are data model, invariants, query patterns, transaction boundaries, recovery, and operational cost.

Use Module 4: Networks, APIs, and storage for a guided relational implementation.

Choose from requirements

Start with:

• entities, relationships, identity, and invariants;
• required queries and ordering;
• write patterns and conflicting updates;
• data size and growth;
• latency, durability, and availability objectives;
• backup, restore, retention, and deletion behavior;
• team ability to operate the system.

A relational database is a strong default for structured application state and transactional invariants. Choose another model for a concrete access or scaling need, not because the data happens to be encoded as JSON.

Relational model

A table has named attributes and rows. The relational model does not guarantee row order; use ORDER BY when order is part of the result.

Keys

• primary key: chosen stable row identity;
• candidate key: any minimal set that uniquely identifies a row;
• foreign key: value constrained to reference a key in another table;
• natural key: identity derived from domain data;
• surrogate key: generated identity without domain meaning.

Use constraints for rules the database can enforce:

• NOT NULL for required values;
• UNIQUE for alternate identities and idempotency keys;
• CHECK for value-domain rules;
• FOREIGN KEY for references;
• appropriate data types for representation bounds.

Application validation creates useful errors. Constraints protect data when another code path or a bug bypasses that validation.

SQL essentials

SQL describes a result or mutation; the database chooses an execution plan.

Core operations:

• SELECT chooses columns and rows;
• JOIN combines related rows according to a predicate;
• GROUP BY forms groups for aggregates;
• INSERT, UPDATE, and DELETE mutate rows;
• data-definition statements create and evolve schema objects.

Always specify ordering when pagination or deterministic output requires it.

NULL

NULL represents missing or unknown information and participates in three-valued logic. Comparisons such as value = NULL do not test for nullness; use IS NULL or IS NOT NULL.

Constraints, uniqueness, ordering, aggregates, and joins can treat NULL differently across products and expressions. Test the selected database's semantics.

Schema design

Model one fact in one authoritative place where practical. Normalization reduces update anomalies:

• first normal form: attributes contain values under the chosen relational domain rather than repeating groups;
• second and third normal forms: non-key attributes depend on the intended key rather than only part of it or another non-key attribute;
• stronger normal forms address additional dependency patterns.

Do not normalize mechanically. A schema should make invalid states hard and required queries understandable. Deliberate denormalization duplicates data to serve a measured workload and needs a consistency strategy.

Many-to-many relationships usually use a junction table whose constraints prevent duplicate relationships.

Transactions

A transaction groups operations into one commit or rollback boundary.

ACID is a useful summary:

• atomicity: committed effects occur together;
• consistency: transactions preserve defined invariants when code and constraints are correct;
• isolation: concurrent execution follows the database's declared visibility guarantees;
• durability: committed data survives failures covered by the configured durability contract.

ACID does not mean every business rule is automatic, every replica is immediately current, or every hardware disaster is survived.

Keep transactions complete but short. Do not hold a database transaction open while waiting for user input or an unreliable network service.

Concurrency and isolation

Concurrent transactions can produce:

• dirty reads of uncommitted data;
• non-repeatable reads of a row changed after an earlier read;
• phantoms where a repeated predicate returns a changed row set;
• lost updates when one writer overwrites another decision;
• write skew across multiple rows when an invariant is checked then changed concurrently.

Isolation-level names do not guarantee identical behavior across database products. Read the selected engine's documentation and test the anomaly relevant to the application.

Common control mechanisms include locks, multiversion concurrency control, optimistic version checks, and serializable execution. Handle conflicts at the transaction boundary. If retrying, retry the entire transaction with bounded attempts—not selected statements from a partially failed transaction.

Deadlocks can occur when transactions wait in a cycle. Keep a consistent resource order, make transactions short, let the database detect cycles, and safely retry a chosen victim transaction.

Indexes

An index is a maintained data structure that trades write work and storage for particular reads.

Common forms:

• B-tree-family indexes for equality, ordering, and ranges;
• hash indexes for equality where supported and appropriate;
• specialized text, spatial, vector, or inverted indexes for specific operators;
• partial or filtered indexes for a qualifying subset;
• covering indexes that contain all values required by a query.

For a composite index, column order matters. Whether a query can use a prefix, skip columns, or combine indexes depends on the engine and predicate.

An index can still be a poor plan when a query returns much of the table, statistics are stale, casts prevent matching, or ordering differs. Inspect the actual query plan and measure representative data.

Every index needs a supported query and a retention reason. It consumes storage and adds work to inserts, deletes, and relevant updates.

Query planning and performance

The database parses SQL, rewrites or plans it, chooses access and join strategies, then executes operators. Estimates rely on statistics and cost models; estimates can be wrong.

Use this workflow:

1. capture a slow query and its parameters;
2. reproduce with representative data and concurrency;
3. inspect the execution plan and actual row counts/timing when safe;
4. identify the expensive operator or bad estimate;
5. change one query, index, schema, or statistic;
6. compare under the same workload;
7. measure write and storage impact.

Avoid “fixing” every sequential scan. Scanning can be correct and efficient for small tables or broad queries.

N+1 queries

N+1 behavior performs one query for a parent set and another query per parent. Detect it from query counts and traces. Fetch required related data in a bounded join or batch when that is actually cheaper and does not multiply rows uncontrollably.

Pagination

Offset pagination is simple but can become expensive and unstable under concurrent changes.

Keyset or cursor pagination continues from a unique ordered key:

SELECT id, title
FROM tasks
WHERE id > ?
ORDER BY id
LIMIT ?;

The ordering must be deterministic; add a unique tie-breaker when the primary sort key is not unique. Cursor semantics under insert, update, and delete are part of the API contract.

Migrations

Treat schema changes as ordered, reviewed code.

A migration should define:

• supported starting schema;
• data transformation and invariant checks;
• lock and runtime impact;
• compatibility with old and new application versions;
• failure and recovery behavior;
• verification after completion.

Test both an empty database through all migrations and a representative previous database through the upgrade path. A successful command that silently loses data is a failed migration.

Prefer backward-compatible expand/migrate/contract sequences when application versions may overlap. Do not promise reversible down migrations when restoring the previous data shape would be destructive or ambiguous.

Storage and durability

Database engines commonly organize data in pages, cache pages in memory, and record recovery information in a write-ahead log or equivalent structure. Commit behavior depends on log flushing, filesystem, device, replication, and configuration guarantees.

Do not infer durability from API success without understanding the configured contract. Backups protect different failures than logs or replicas.

Sharding, Replication, and Partitioning

These mechanisms solve different problems:

• partitioning: divides data into pieces, possibly within one database instance;
• sharding: places partitions across independently operated database nodes;
• replication: maintains copies of data for availability, locality, or read capacity.

Partitioning

Range, hash, list, and time-based partitioning can improve maintenance or prune data for matching queries. Poor keys create skew or queries that touch every partition.

Replication

Replication introduces lag, failover, topology, and consistency choices. A replica may be stale, and promotion can risk unavailable or unreplicated writes depending on the design.

Replicas are not backups: accidental deletion and corruption can replicate too. Test failover and restore independently.

Sharding

A shard key determines data placement. Good keys distribute storage and load while keeping common transactions and queries local. Cross-shard joins, uniqueness, transactions, rebalancing, and hot keys add operational complexity.

Do not shard until a measured single-node or ownership requirement cannot be met more simply. Adding nodes often reduces local simplicity before it increases useful capacity.

During a network partition, a distributed system cannot guarantee both linearizable consistency and successful responses from every isolated side for the same data. Real designs choose behavior per operation and failure mode; “CP” or “AP” is not a complete database specification.

See Distributed systems for broader consistency and coordination concepts.

Other data models

Model	Useful when	Questions to ask
Document	aggregate-shaped records evolve together	cross-document constraints and query/index needs
Key-value	lookup is primarily by exact key	range queries, transactions, eviction, and value size
Wide-column	high-scale partitioned access follows known keys	partition design, hot keys, and consistency
Graph	relationship traversal is primary	mutation, traversal depth, and operational maturity
Time series	timestamped append/query/retention dominates	cardinality, downsampling, late data, retention
Vector index	approximate similarity search is required	recall, filtering, update, cost, and evaluation

“NoSQL” does not remove schemas, invariants, or transaction decisions. They move into data design, application logic, or product-specific guarantees.

ORMs and query builders

An ORM maps application objects to database operations. It can improve ordinary CRUD and migration ergonomics, but it does not remove SQL, transaction, index, or query-plan responsibility.

Watch for hidden N+1 queries, implicit transactions, unbounded relationship loading, and schema changes generated without review. Use direct parameterized SQL where it is clearer.

Security

• use parameter binding; never concatenate untrusted values into SQL syntax;
• grant the application only required database privileges;
• separate migration and runtime privileges where practical;
• protect credentials and rotate them through a defined process;
• encrypt network connections outside trusted local boundaries;
• restrict backup access and include backups in deletion and retention policy;
• log administrative and security-relevant activity without sensitive query values.

An ORM reduces some injection risk only when its parameter APIs are used correctly. Dynamic identifiers and raw query fragments still need allowlists or safe construction.

Backup and recovery

Define recovery objectives, then choose backups and logs that can meet them.

A useful backup process includes:

1. consistency-safe capture;
2. encryption and access control;
3. retention and deletion;
4. integrity verification;
5. restore into an isolated environment;
6. application-level checks after restore;
7. measured recovery point and recovery time.

A backup is unproven until a restore succeeds. High availability reduces some downtime; it does not replace recoverable history.

Review checklist

• Are identities and relationships enforced by constraints?
• Does every query requiring order use an explicit deterministic order?
• Are transaction boundaries aligned with invariants?
• Have relevant concurrency conflicts been tested?
• Does every index support a measured query?
• Are migrations tested from empty and previous states?
• Are parameter binding and least privilege used?
• Has backup restoration been demonstrated?
• Are replication or sharding claims tied to explicit failure behavior?