Architecture

Data Architecture: From a Table to a Distributed System

How to model, protect, copy, distribute and evolve a product's data, from a single table with keys and constraints to replicas, event streams and…

Chapter 16Architecture

Architecture Decision Records showed how to preserve the reasons behind architectural decisions. This unit turns to the data those decisions revolve around: how to describe a fact, how to prevent contradictory changes, what it actually means for "the system to know" something, where copies come from, and what to do when a process or the network stops in the middle of an operation.

This is a large subject, so the unit is organised as a sequence of sections that build on each other. We start with rows and relationships, move on to lookups, transactions and concurrent changes, and then look at replicas, distribution, partial failures, event streams and schema evolution. Database names appear here to illustrate different shapes of data and different guarantees; they are not a product ranking.

By the end of the unit you will be able to:

  • describe a data entity in terms of its types, keys, relationships, constraints and queries;
  • choose between the relational, document, key-value and graph models based on the shape of the data and the question the user is asking;
  • recognise where an index helps a lookup and where it adds a cost to every write;
  • explain a transaction, integrity and a concurrent change with a concrete example;
  • distinguish synchronous from asynchronous replication, a replica from a backup, and a source of truth from a derived dataset;
  • name the consequences of latency, a network partition, a retry and an ambiguous response;
  • pick the right mix of batch and stream processing, caches, views and migrations without claiming a universal "exactly-once".

Every number in the teaching scenarios is invented. The examples are not measurements of a real product. When the text refers to a specific version of PostgreSQL or Kafka, it describes the guarantee of that product and its documentation, not of every database.

Data, schema and queries

Data is the set of recorded facts a product has to remember or compute. A schema defines the shape of those facts: which tables or documents exist, which fields they have, which types are allowed, which keys identify records and which integrity rules apply. A query asks the system to find, add, change or combine data for a particular need.

In a relational database a table has rows and columns. A row describes one instance of an entity; a column describes one property within a defined domain of values. A booking record might hold an identifier, the customer's identifier, a time slot, a status and a creation time. The number of seats must be an integer. A price needs an exact decimal type, or a representation in the smallest currency unit, rather than a binary floating-point number that rounds. A type rules out a class of accidental mistakes, but it does not replace a business rule: a price of 0 may be perfectly valid, while a price of −12 is not.

Queries in relational databases are written in SQL (Structured Query Language). The illustrative fragment below creates a small teaching store for customers and bookings:

CREATE TABLE customers (
    customer_id bigint PRIMARY KEY,
    email text NOT NULL UNIQUE
);

CREATE TABLE bookings (
    booking_id bigint PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(customer_id),
    starts_at timestamptz NOT NULL,
    status text NOT NULL CHECK (status IN ('held', 'confirmed', 'cancelled'))
);

This is PostgreSQL syntax, not a universal command that every engine accepts. The primary key and the foreign key are not decorative labels. The first identifies a row; the second restricts customer_id to the identifier of a customer that actually exists. NOT NULL requires the column to have a value, and UNIQUE forbids the same email address from appearing twice in this table. The real rules about reusing an address or retaining personal data still have to be decided separately.

In the relational model, NULL means that a column has no ordinary value. It is not necessarily zero, an empty string or the word "unknown": the reason for the absence can vary, and the SQL type itself does not explain it. A check for NULL is written separately; the comparison column = NULL does not behave like an ordinary equality test. If "not sent yet", "the user declined" and "we never received the data" lead to different behaviour, store them as distinct states rather than collapsing them into an empty field.

A data model starts from the need to read and change a fact, not from a particular table. For example, the confirmation page has to show who made a booking, when, and for which slot; changing a booking has to store the new status and must not take a seat that is no longer available. These needs make it clear which values must be canonical, which relationships must be preserved, and which queries are the core ones.

A worked example. A teaching product takes bookings for 45-minute consultations. For the demonstration there are two customers and three slots, and each slot has one free seat; these numbers are invented. The operator needs to see bookings by week and by customer. The schema separates the customer from the booking, does not repeat the customer's name in every booking, and restricts the status to a known list. When a name changes, the team updates one customer record. When a customer cancels, the system can keep the row with a new status instead of erasing the fact that the booking once existed. This does not automatically settle retention periods or legal requirements; those need a separate product rule and a separate access rule.

Schemas change too. Adding a new status affects the constraint, older clients, reports and the code that handles statuses. That is why a requirement should name the allowed transitions rather than relying on the assumption that any text string is safe. The procedure for changing a schema is covered in Database Migrations; what matters here is that a schema is a data contract that every reader and every writer has to agree with.

Identity, keys and cardinality

A key answers the question "how do I recognise exactly this record?" A natural key is a value that already exists in the business domain and is supposed to be unique by rule, such as a ticket number. A surrogate key is an identifier the system generates, such as booking_id. A natural key is convenient when it is genuinely stable. An email address is not always a good choice, because people change their addresses and an address can be private information. A surrogate key stabilises references, but it does not by itself make the business entity unique. If the same partner number is registered twice, random identifiers will not stop the duplicate; you need a separate uniqueness constraint on the domain value.

The cardinality of a relationship describes how many records can be linked to each other:

  • One customer can have many bookings; one booking refers to exactly one customer.
  • One group booking can contain several services, and one service can appear in many bookings. This is a many-to-many relationship, which the relational model usually represents with a junction table.
  • Two records do not need to reference each other just because they are often shown on the same page. A shared screen is a reading requirement, not evidence of a shared identity.

For booked services, the price at the time of purchase can be kept in the junction row:

CREATE TABLE booking_services (
    booking_id bigint NOT NULL REFERENCES bookings(booking_id),
    service_id bigint NOT NULL REFERENCES services(service_id),
    price_at_booking numeric(12, 2) NOT NULL CHECK (price_at_booking >= 0),
    PRIMARY KEY (booking_id, service_id)
);

The pair of foreign keys expresses the relationship, and the composite primary key prevents the same service from being added to the same booking twice. The price_at_booking field is a snapshot of the cost at the moment of confirmation; the current price of the service, held in another table, may change later. This is a deliberate historical copy, not accidental duplication. If the product allows the same service several times within one booking, the key has to include a line position or a separate line identifier. The business model decides which rule applies.

A foreign key protects a relationship from values that point at a parent record that does not exist. It does not automatically decide what happens when a customer is deleted: block the deletion, anonymise the customer, cascade the deletion to their history, or keep the bookings without personal data. Choose the action according to your audit, privacy and recovery requirements. Do not add a cascading delete just because the database syntax offers one.

A worked example. A customer called Marta has bookings B-17, B-23 and B-31. Each booking row stores a reference to a single customer_id, so her name and contact details do not have to be copied into all three rows. In booking B-23 Marta chose a consultation and a translation; the two links live in booking_services, each with its own recorded price. When the consultation price changes next month, the old invoice will still show the price recorded in the booking, while the catalogue shows the new one. If a line item can repeat, the team adds a quantity or a separate line number rather than blindly relaxing the rules.

Relational, document, key-value and graph models

Four ways to store and connect data: relational tables, documents, key-value pairs and graphs

Different models make different operations easy. The relational model is useful when explicit relationships, constraints and queries that combine several entities matter. The document model groups related fields into one document, which can be convenient when you read a whole aggregate at once; complex relationships across many documents may require extra lookups or keeping copies in sync. A key-value store fits direct access by a known key well; it does not mean the system can efficiently find a record by any of its fields. The graph model makes nodes and relationships explicit, which pays off when the product asks many questions about paths and neighbourhoods, for example how professional contacts are connected across several hops.

This is not a split between "old" and "modern" databases. One technology can support several ways of representing data, and one system can contain several stores with different purposes. The choice starts with the shape of the main operations and with responsibility for the data. Ask: who changes this fact? Which fields must stay together? Do they need transactions between them? Under which conditions are they read? How do we find or rebuild a derived view? What can the team actually observe and recover?

Normalisation places separate facts into a structure that reduces unnecessary copies and contradictory updates. If a customer's address is repeated in every booking, then after the address changes, part of the history may hold the old value and part the new one. A reference to a single customer record removes this ambiguity for the current profile.

Denormalisation deliberately copies or precomputes information so that a specific read becomes simpler or faster. For example, a daily summary might store the total number of confirmed bookings. The reader of that summary then has to ask: when is it updated, and by whom? How do we detect a discrepancy with the canonical bookings? Can it be recomputed from scratch? A copy is not a free optimisation. It creates a reconciliation rule and sometimes a short window of staleness.

A worked example of choosing a model. A booking team considers two operations: confirming a booking with several services, and showing a daily utilisation report. Recording an order needs referential constraints and one atomic change of state, so the team keeps the canonical customers, slots, bookings and line items in relational tables. Finding active bookings by customer and date is a typical query. The analytical report can start as a query against the same tables; if measurements show that the report competes with confirmations for resources, it can be moved to a separate derived view or an analytical store. The document shape is not rejected as wrong here, but on its own it does not remove the need to check the last seat atomically. A key-value store cannot answer the report query without additional indexes or structure. A graph store is not chosen, because the key query is not, for now, a search for complex paths between people, services and organisations.

Before adding such a copy, decide who updates it, how stale it is allowed to be, and how to reconcile it with or rebuild it from the canonical data. Denormalise only after you have named the owner of the derived value, the rebuild procedure and the acceptable delay. Otherwise the team ends up with two potential sources of truth and no idea which one to show when they disagree.

Operational writes and analytical reads

OLTP (online transaction processing) describes the day-to-day changes to individual facts: create a booking, change a contact detail, cancel an order. OLAP (online analytical processing) describes queries that aggregate many facts: compare utilisation month by month, find a trend in cancellations, or estimate revenue by service. The boundary is drawn by the kind of work, not by the name of a product or engine. The same relational database can serve both kinds of query for as long as the load and the response times allow it.

Canonical data are the facts to which the product gives official meaning: a confirmed booking, its owner, its slot and the price at the moment of the agreement. Derived data are computed from canonical data: a daily summary, a search index or a cache. A copy can speed up reads, but it becomes an additional responsibility. For every derived view, name in advance its source, the owner of its updates, its acceptable age, the signal that it has gone stale, the procedure for a full rebuild, and what the system shows when the view is unavailable.

A worked example of a report. A fictional booking service has 18 months of history. Its manager wants to see the number of confirmations per day by 09:00. In this example the canonical source is the bookings table; the 18 months and the 09:00 deadline are teaching requirements, not measured data from a real product. The team first queries the tables directly and measures the query on a representative copy. If measurements show that the heavy report interferes with slot confirmations, the team adds a separate daily summary. A job that runs overnight counts bookings by date and status, updates rows on rerun without creating duplicates, and stores the time of the last computation. The report page shows up to which time changes are included. If the overnight recomputation did not finish, the manager sees an older update time, and the on-call team can rerun the recomputation from the canonical records. The deduplication rule, the time zone that defines a "day" and the treatment of cancelled bookings must be defined explicitly; otherwise the fast summary will simply reproduce the ambiguity of the source data.

A materialised view stores the result of a query between reads; it should not be described as a synchronous mirror of the source. Its freshness depends on when the team refreshes it. If the data feed a decision with legal or financial consequences, show how current the data are, or check against the canonical record. Do not assume that data from two systems form one atomic transaction just because they sit next to each other on the screen.

Indexes, storage and measuring a query

An index speeds up reads but adds work to every write

An index is an auxiliary structure that lets the database find rows without scanning the whole table on every query. A B-tree is a balanced search tree; in PostgreSQL it is the default index type and suits many equality, range and ordering checks. An index does not change the meaning of the data and does not make every query instant. Its benefit depends on the shape of the predicate, the number of rows, the distribution of values, the available memory and the plan the optimiser chooses for the specific query.

A worked example. A booking-history page looks up one customer's rows from a chosen date and shows them from newest to oldest:

SELECT booking_id, starts_at, status
FROM bookings
WHERE customer_id = 42
  AND starts_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
ORDER BY starts_at DESC
LIMIT 50;

The team first looks at the number of rows, how often the query runs and its plan; it does not add an index to every field just in case. For a lookup by customer and time range, a composite B-tree index may be appropriate:

CREATE INDEX bookings_customer_time_idx
ON bookings (customer_id, starts_at DESC);

Column order matters in a composite index. In PostgreSQL a B-tree usually narrows a range best when the condition uses the leading (leftmost) columns; the version 18 documentation also describes cases where a query that skips the first column can still use the index through a skip scan. So do not turn the leading-column rule into a universal law: check the version, the data and the plan. An index occupies additional pages, slows down every change that has to maintain it, and is not guaranteed to be used by the optimiser.

You can see the plan with the EXPLAIN command. It shows the chosen operations and their estimates, while EXPLAIN ANALYZE actually runs the query and measures its execution. Measure on representative data, not on one lucky run against an empty database; for a query that modifies data, remember that the ANALYZE variant will execute it. Compare the number of rows the planner estimated with the number actually read, and look at disk and buffer reads. No index tree by itself proves that the target page of the app will become faster.

Index storage has other designs too. The LSM tree (log-structured merge-tree) is described in the original paper by O'Neil and co-authors as a way to organise lookups in insert-heavy environments by accumulating and merging structures. This moves part of the cost into background merges, reads and space management. B-tree versus LSM is not a verdict of "outdated versus modern": the choice depends on the mix of reads and writes, the data volume, latency, recovery and the specific engine implementation.

Row-oriented storage keeps the fields of one record close together, which is often convenient for inserting and reading a complete booking. Column-oriented storage groups values by field and can reduce the amount read when an analytical query scans many rows but needs only a few columns. In the Parquet format, data inside a row group are stored by column, and statistics help skip groups that cannot match the filter. This is not a general speed-up for every query: reading whole records, frequent small updates, the row-group size and compression all change the result. For a modest volume in a row-oriented table, a simple index or a plain query without a separate analytical format may be enough.

Transactions and the limits of their guarantees

ACID stands for atomicity, consistency, isolation and durability. These are four properties of the transactional model, but the concrete guarantee depends on the engine, its configuration and where the transaction ends.

  • Atomicity: a group of changes is committed together or rolled back together. A booking confirmation and its line items must not remain half-written.
  • Consistency: a transaction must move the data from one valid state to another. The database can enforce the foreign keys and constraints it has been given, but it cannot guess a hidden business rule that the developer never wrote down.
  • Isolation: concurrent operations interact according to a given isolation level. Calling something a "transaction" does not mean that every possible race condition disappears automatically.
  • Durability: after a successful commit, the system promises to keep the result within its documented settings. The write-ahead log policy, the hardware, replication and failover all affect what can be survived.

A worked example of one transaction. Marta confirms a consultation and a translation. The server begins a transaction; reserves one slot; creates the booking; records the two chosen services and the price of each at the moment of purchase; writes the status "confirmed"; and then commits. If a constraint is violated while the second service is being written, the server rolls back the entire transaction: the slot is not left reserved without the complete purchase. The email notification is not part of the relational database transaction, so a separate decision is needed about how to notify the customer safely after the commit. We look at that gap in the section on the outbox pattern.

Hold every transaction up against the requirement. Which records exactly does it cover? Who else can change the same facts? Is a consistent snapshot needed? What happens on failure? Is there an external call between the start and the end? Do not keep a transaction open while a person fills in a form or while a remote partner's service responds over the network. A transactional database can protect the atomicity of its own writes, but it does not turn networked systems into one shared database. The next external action needs its own recovery plan, such as the transactional outbox.

Concurrent access: the last-seat example

Two concurrent transactions competing for the last seat in a slot

SQL gives you tools for constraints and transactions, but two operations that are each correct on their own can conflict if they change the same fact at the same time. State that was checked only in application code before the write goes stale while the request travels over the network. The check and the change must be atomic, or protected by a constraint or a lock at the storage level.

The full race scenario. Slot S-9 has a capacity of 20, and 19 seats are confirmed. Marta and Oleh open the last seat at almost the same moment. Both browsers show an availability of 1. That is the same stale picture, not a right for each of them to have the seat. If the server first reads the counter and then, separately and without re-checking the condition, adds a booking, both operations can see 19 and push the counter to 21.

One teaching implementation in PostgreSQL uses a conditional update:

WITH reserved AS (
    UPDATE slots
    SET confirmed_count = confirmed_count + 1
    WHERE slot_id = 9
      AND confirmed_count < capacity
    RETURNING slot_id
)
INSERT INTO bookings (slot_id, customer_id, status)
SELECT slot_id, 42, 'confirmed'
FROM reserved
RETURNING slot_id;

The example assumes that capacity and confirmed_count are declared NOT NULL. A CHECK on its own does not reject NULL: a condition whose result is unknown is not false. After the existing rows have been checked and corrected, the schema additionally refuses to exceed capacity:

ALTER TABLE slots
ADD CONSTRAINT slots_capacity_not_exceeded
CHECK (confirmed_count >= 0 AND confirmed_count <= capacity);

For the first customer, the conditional update changes 19 to 20, and the same statement then inserts the booking. Until that statement finishes, a concurrent update of the same row may have to wait. Under the READ COMMITTED isolation level (each statement sees only committed changes), PostgreSQL re-checks the condition on the current version of the row after the wait. The condition confirmed_count < capacity is now false, so the second statement inserts no booking and returns zero rows. The application tells the customer that the seat has just been taken and offers another slot. This is a single atomic statement: if the booking insert fails, the counter change is not committed either.

The application reports success only when the statement has actually returned a booking row. Because the conditional update and the insert happen in one statement, they succeed or fail together; if they were separate SQL statements, both changes would have to be wrapped in one transaction and rolled back together whenever the update found no seat or the insert failed. Also decide whether the counter is canonical state or a derived sum of the booking records, and how to restore it after an error. Other designs may use a uniqueness constraint, a row lock or a SERIALIZABLE transaction; their guarantees depend on the specific database and the structure of the data.

Another anomaly is the lost update. Marta and Oleh open the same version of a profile; Marta changes the phone number and Oleh changes the language. If both send back the whole old object, the last write can return the phone number to its previous value. If each updates only their own field, there may be no conflict at all. When a full row replacement is needed, optimistic version control detects that the data changed after they were read:

UPDATE customers
SET phone = $1, version = version + 1
WHERE customer_id = $2 AND version = $3
RETURNING version;

In this fragment, $1, $2 and $3 are parameters that the application passes separately from the query text. If zero rows come back, the version has already changed: the application reads the new state and offers to merge the changes, or to repeat the action after a check. It does not silently overwrite the newer value. Every write path must increment version; otherwise the check will miss an update made elsewhere. Pessimistic locking is useful for a short critical section when waiting for the competing operation is cheaper than merging afterwards; it holds the resource longer and can increase latency. The choice depends on how often conflicts happen and what a failure costs.

Under REPEATABLE READ, a PostgreSQL transaction reads a stable snapshot, so reading again will not show changes that other transactions committed in the meantime. That still does not remove every serialisation anomaly. Suppose a clinic must always have at least two doctors on call. Two parallel transactions each see that both doctors are on call, and each takes only its own doctor off call. They change different rows, so both can complete, even though afterwards nobody is on call. This is write skew: each individual change looks valid against its own snapshot, but the shared invariant is broken. This rule needs a mechanism that serialises the shared decision: for example, locking a shared schedule row, or using the SERIALIZABLE level and retrying the entire transaction on a controlled error. A constraint works only when the schema can express the invariant precisely; an ordinary PostgreSQL CHECK validates the new or updated row and cannot guarantee a condition across rows such as "at least two doctors are on call".

Under SERIALIZABLE, the database can detect a conflict and abort one transaction with a serialisation failure. The client must then, under bounded conditions, retry the entire logical transaction with all its checks, not just the last SQL statement. The retry needs a limit on attempts, a delay and safe behaviour when a response is lost. If an email or a payment request has already gone out, retrying the whole operation can perform that side effect a second time; this calls for an idempotency key or a separate intent log, which we come back to later in this unit.

Time, audit and retention

Time in data answers at least two different questions. "When exactly did the event happen?" needs an unambiguous point in time. "Which local time did the customer choose?" needs a time zone and calendar rules. UTC (Coordinated Universal Time) helps store and compare event moments across servers; a calendar time zone such as Europe/Oslo is needed to interpret a local schedule and daylight-saving transitions. The string "02:30" on its own is not enough if the date falls on an hour that is repeated or skipped.

Keep event time, receipt time and processing time apart. A partner may have created an event yesterday, your service received it today, and the queue processed it later still. The fields occurred_at, received_at and processed_at mean different things; a single timestamp cannot answer all these questions. In PostgreSQL the timestamptz type stores a point in time and displays it according to the session's time zone setting; it does not store the original zone name as part of the value. If you need to reproduce a scheduling rule, store the zone name separately.

Retention is part of the data design too. Before launch, decide which facts must remain for billing, support and audit; which fields can be deleted or anonymised; who can access the log; how the retention period applies to copies, exports and backups; and how to prove that the procedure was carried out. The applicable legal periods depend on the jurisdiction and the product, so a qualified specialist has to check them. Do not promise that deleting a row from the main table instantly erases it from every replica and backup.

A soft delete marks a record as deleted or inactive without removing its row immediately. This helps preserve references or undo a mistaken action, but it changes queries and constraints: the ordinary list has to hide such records, and a unique name may stay taken. A soft delete is not the same as physical erasure or anonymisation. Keep current state and event history separately only when the product requirements genuinely need the history; a row with the status "cancelled" does not automatically reproduce all the earlier changes.

A fictional example: auditing a player transfer. A teaching football-statistics site receives a register of players' club memberships from a fictional official league. Under the agreed contract, this feed is authoritative for the fact that a transfer has been registered; the site is responsible for its own internal profiles and for mapping external identifiers to internal ones. In a real integration the team separately checks the feed's authentication, the schema version and the rules for corrections; the source's authority covers only the facts its contract defines. All names and events below are invented. The stable keys are player_id=pl_042, team_id=tm_A and team_id=tm_B. The names "Team A" and "Team B" may change or be translated differently, so they are not used as keys. The source sends event_id=evt-2026-0042, revision=1: the player moves from tm_A to tm_B with effective_at 2026-08-01T00:00:00Z. The site accepted this version at 2026-08-02T04:12:00Z and stored observed_at separately from effective_at. If the source provides only a calendar date without a time, the system stores effective_date together with calendar-date precision and leaves effective_at undefined; it does not invent midnight as an exact moment.

Two days later the same event_id arrives as revision=2: the source corrects effective_at to 2026-08-03T00:00:00Z, and the portal observes the correction at 2026-08-04T04:12:00Z. This is not a second transfer. The intake log keeps both versions, the source, the revision number, the observation time and a digest of the content, normalised according to the source's contract; the canonical transfer record points to the currently accepted revision. So the system can answer both "which fact does the source confirm now?" and "what did the portal know on 2 August?". A repeat of the same tuple (source_system, event_id, revision) with the same digest of the normalised content is a no-op: redelivery does not create a new fact. If the tuple matches but the digest differs, both observations are kept as a conflict to be reconciled; the accepted version, the canonical transfer and the projection are left unchanged until a decision is made under the source's contract.

The current team roster is a derived projection of the canonical history, not the only place where the fact is stored. In this fictional product a player has at most one active club registration at a time; if the real domain allows loans or several parallel registrations, the invariant itself and the projection key have to change. A rebuild for player_id=pl_042 applies the accepted transfers ordered by effective_at and a defined conflict rule. After a correction it recomputes the state as of the required date and updates the player's row by the stable player_id. If the chain from tm_A to tm_B does not match the previous membership, or a mapping for an external ID is missing, the system flags the discrepancy for review rather than deriving the team from a name. Access to the raw content, its retention period and the visibility of the audit log are defined separately; a digest of the normalised content and a reference to the source are often enough for diagnosis without copying unnecessary data into a general-purpose log.

Replication, high availability and backups

Replication transmits changes from one node to another, often to survive a failure or to spread read load. It does not make the copy independent of a logical error: an accidental deletion can propagate in exactly the same way. A backup has a different purpose: to return the system to a usable state after loss, error or an unwanted change. Its value is proven only by a tested restore, not by a green indicator saying that the copy job succeeded.

With asynchronous replication, the primary node can acknowledge a write before the standby has applied it. The standby then briefly shows an older value, and a failover can also lose a change that had not yet been transferred. A synchronous mode changes write latency and write availability when a node or a link is lost. Read the meaning of these modes in the product's documentation: "replication is enabled" is not evidence of any particular acceptable data-loss window.

A worked example of a stale read. At 10:05 Marta changed her address on the primary database, which acknowledged the write. Her profile page immediately reads from a standby replica, which has not applied the change yet and shows the old address. This is the expected lag of an asynchronous replica, not proof that the database rejected the write. If the screen must show a user's own change immediately, the application reads from the authoritative node or otherwise guarantees read-your-writes behaviour; for a report where a short delay is acceptable, it can read the replica and show how current the data are. If the customer, looking at the old address, edits the profile again, the row's optimistic version helps avoid overwriting the newer change. The team defines this behaviour before users run into it; the replica itself remains useful for availability and for scaling reads.

GitLab's historical outage in January 2017 illustrates the difference between a backup and a replica. In its postmortem the company distinguishes two intervals: GitLab.com was unavailable for about 18 hours, and restoring the service took more than 18 hours. At that time the backups were not working, and the replica was not a backup for disaster recovery. This account was published by GitLab itself and describes the system and processes of that time; it says nothing about the company's current infrastructure. The practical lesson drawn from the postmortem's concrete actions: monitor not only that the backup job completes, but also how long a restore to a clean environment takes, whether the transaction log is available, and who is responsible during an incident.

PostgreSQL logical replication can transmit an initial snapshot and the changes that follow, but it does not copy schema definitions automatically. A separate consumer has to know whether it has received the new structure, how it copes with a change in field order, and how to recover a missed interval. PITR (point-in-time recovery) combines a base backup with an archive of transaction logs, but the procedure needs to be genuinely tested.

Partitioning and distributing data across nodes

Partitioning, sharding and hot spots

Partitioning splits one logical table into smaller physical parts by a key, for example by the month of a date. This can narrow the area a query reads and make it easier to manage old ranges. It does not automatically reduce all the work of a query: if a read is not restricted by the partition key, the engine may have to visit many parts. In PostgreSQL, how you can express constraints and the rules for uniqueness may depend on the partition key; read the limitations of the specific version before changing the design.

A worked example with historical data. A teaching service holds 6 years of bookings, and the measured monthly report reads only one month. The team trials partitioning the table by month of creation and checks the plan for that report. Before keeping the design, it also checks a lookup by customer without a date, a report across all years, the check for a duplicated external number, a backup rebuild and deletion at the end of the retention period. If those operations now have to scan every partition, or global uniqueness cannot be guaranteed with the chosen design, the team has to change the key, the index or the way records are identified. It does not create monthly partitions just because the calendar happens to divide naturally into months.

Sharding distributes different keys across several separate nodes. This can increase total capacity, but it requires a routing rule, key migration, balancing, monitoring of imbalance, and a defined behaviour for queries that span several nodes. A badly chosen key creates a hot shard; changing the key may require moving a large share of the data. A transaction across two shards also has its own cost. As long as measurements show that a single database meets the requirements, the complexity of distribution is not a free reserve for the future.

A complete plan for the team must include a test distribution of data, a criterion for making the switch, a way to verify that records are placed correctly, a plan for copying or redistributing data, monitoring of hot keys, a rollback and a recovery procedure. A decision to segment by organisation or by customer must also pass an isolation review: a routing error can expose data to the wrong owner.

Network failures, timeouts, clocks and retries

A timeout answers the question "how long did the client wait?", not "did the server perform the operation?". The server may have accepted a payment or created a booking and simply failed to send the response in time. Because the response was lost, the client sees an error even though the change has already happened. That is why a retry after a timeout can create a duplicate unless the team has designed a way to recognise the identity of a request.

A client can retry a transient error with a limited number of attempts, growing intervals and random jitter in the delay, so that many clients do not hit the service at the same moment. The retry must fit into the overall deadline of the request, respect the partner's limits, and distinguish a transient error from an operation that was rejected. Unlimited retries pile more load onto a dependency that is already failing. An idempotency key, as documented by the provider, lets you request the same logical action again, but it is not permission to change the action's parameters under the same key.

A wall clock can jump because of a time correction, a time-zone setting or a daylight-saving change. To measure a timeout's duration, a process uses a monotonic clock if the language and platform provide one; the calendar timestamp is kept for logs and for people. Across several machines, neighbouring events may carry the same time or appear in swapped order. If causal order is critical, the team must store a sequence from the source, a version number or a separate event order, rather than deriving it from the wall clock. Systems built on physical clocks with bounded uncertainty, such as Spanner, document this complexity openly instead of pretending that all clocks are accurate.

A worked example of an ambiguous response and recovery. A partner sends a teaching webhook event about a payment with the invented identifier event_781. In one local transaction the server records the external identifier and changes the canonical payment state; the connection drops before a successful 2xx response is sent. The partner does not know whether the write was committed, so it resends the event. A unique constraint on the pair "partner, event identifier" links the retry to the previously stored result, so the server acknowledges receipt again without crediting the payment twice. If the server crashed before the transaction committed, neither the receipt record nor the payment change was saved, so the retry runs the whole operation. If a separate task has to run after the commit, for example granting access, the same transaction also writes a record to the outbox; a recovered worker picks up the unfinished event and repeats the external call with a stable idempotency key, if the partner supports one. When the outcome of an external action is unknown and there is no key, first reconcile the state with the partner rather than creating a new action at random. If duplicates arrive in parallel, the unique constraint or an atomic registration picks a single winner. This constraint does not prove that the payer is authentic; authenticity is checked under a separate contract for the partner's signature and identity.

Consistency, network partitions and quorums

CAP stands for consistency, availability and partition tolerance. It is the name of a result about how distributed reads and writes can behave during a network partition. In their formal treatment, Gilbert and Lynch consider linearisable consistency of a shared object and the availability of requests to nodes that have not failed. Once a network partition has happened, it is impossible to guarantee both that every request gets a response and that every node behaves like one sequential, up-to-date store. This is not the folk rule "pick any two out of three": the partition is a failure condition, and the actual trade-off becomes visible precisely while it lasts.

The word "consistency" in CAP is not the same as the consistency property of ACID discussed under transactions. There it means a valid transactional state according to the constraints; here it means the formal behaviour of operations in a distributed system. Likewise, availability does not simply mean "the process is running" or "there is a status page": in the formal model, every request to a reachable node must end with a response. Name the guarantee your product needs, for example whether a booking can be confirmed in both isolated data centres, instead of labelling the whole system with the single word "consistent".

A linearisable read behaves as if all completed operations happened in one global order, each at some point between its start and its response; after an acknowledged write, a subsequent read must not show an older state. Under eventual consistency, copies can differ for a while, but, assuming new changes stop and propagation resumes, they should converge to a common state. That sets no bound on the delay and does not mean that every conflict will be merged in the way the business wants. If a screen reads a profile from a replica, it has to be designed around an acceptable window of staleness; if it reads the result of a payment, such a delay may be unacceptable.

Consensus helps nodes agree on a shared order of values or records under defined assumptions about the network and the number of failures. Raft is a published algorithm for managing a replicated log: the participants elect a leader, and a record can become agreed once the required majority has confirmed it. This does not make every operation instant, and it does not allow a minority to safely accept independent, conflicting writes if the system has to keep a single order.

A worked example with a network partition. A booking system runs on three nodes and must not allow two people to get the same last seat. After the connection between nodes is lost, one side sees two nodes and the other sees one. In a design where the log advances only with a majority, the pair can continue writing after an election and quorum confirmation; the isolated node does not confirm that write. Part of the service may respond with a temporary error or wait, and the customer needs to see a clear "try again" state. A design that instead lets each half write locally may improve availability on each side, but later has to resolve two confirmations of the same seat. The product owner has to choose which consequence is acceptable, and the engineer has to record how the system behaves when the connection is restored.

A quorum is a threshold of votes or responses defined by a specific protocol; the presence of the word alone does not guarantee a fresh read or a durable write. For quorum reads and writes, check whether the sets of replicas overlap, how values are versioned, how conflicts are detected, when an acknowledgement is considered durable, and what happens when the majority is lost. A formula based on the number of replicas cannot be lifted out of the rules of the specific store.

Distributed operations, the outbox and idempotency

A distributed transaction tries to agree on one logical action across several independent resources, for example a local database and a message broker. Two-phase commit (2PC) asks the participants to prepare first and then commits the outcome. Both the participants and the coordinator must support this protocol; a coordinator failure, a delayed participant and provider limitations all affect availability. Do not describe an ordinary network call over HTTP (Hypertext Transfer Protocol) made inside a transactional function as an atomic write to two services.

A transactional outbox stores an event in the same database and the same transaction as the business change. A separate publisher reads rows that have already been committed and delivers them to the broker. If the database transaction rolls back, the event row rolls back with it; if the process crashes after delivery but before marking the row "sent", the event can arrive again. That is why the consumer needs its own idempotency: a repeat of the same event must not create the business effect a second time. The outbox reduces the dual-write problem; it does not guarantee a one-time external effect.

A complete booking-event example. Within a transaction, the service confirms booking B-23 and adds a booking_confirmed event to the outbox table, with an immutable event_id, the booking identifier, its version and a timestamp. After the commit, the publisher reads the event and sends it to the email service. If the broker's response is lost, the publisher redelivers with the same event_id. The consumer has a processed_events table that is unique by consumer name and event_id. It registers the event and creates a notification task in one local transaction; if the key already exists, it returns the earlier result without creating a second task. A separate worker can retry sending the email, and its attempts have a visible status and limits. This guarantees controlled reprocessing within these tables, but the external email provider may still be unable to say whether it accepted the message when the connection dropped. If the provider supports an idempotency key, pass a stable key according to its contract.

"Exactly-once" processing is not a universal property of a message sent across an arbitrary network: the sender may not know whether the receiver has already committed the effect. Apache Kafka describes transactional processing within Kafka and the conditions for keeping a consumer's read position consistent with new records in topics; an external database requires that database's cooperation or an idempotent operation. First pin down the scope of the guarantee: the producer and the topic, reading and writing within Kafka, an update of one relational database, or a real action at a partner. For a long business sequence across several stores, teams often consider a saga: a series of local transactions with explicit compensating actions. Compensation cannot always literally undo a message that has already been sent or a service that has already been delivered.

Batch processing, recomputation and correcting history

Batch processing takes a bounded dataset, runs a finite computation and writes the result. Unlike a continuous stream, a job can read a range that is already complete, such as a day or a month. The practical questions stay the same: how to separate out a repeat, how to confirm that the input is complete, where to write a partial result, how to rerun the job after a crash, and how to recompute the past after a rule has been corrected.

MapReduce is a programming model in which a map function produces intermediate key-value pairs and a reduce function combines the values for a shared key; the original paper by Dean and Ghemawat also describes scheduling the pieces, worker failures and exchanging results. Not every team needs a large distributed engine. The same discipline is useful for a small overnight job: split the input by stable keys, make every piece safe to rerun, and check the totals before publishing.

Batch computations can read the full historical dataset to rebuild a projection, or only new records for an incremental count. A full recomputation is easier to reconcile but has a cost; an incremental one is cheaper in the normal cycle, but an error in its state can accumulate. Store the formula version, the boundary of the input data, the job identifiers, the errors of individual pieces and the reconciliation totals. Run large recomputations in chunks, with rerunnable writes and limited load on the canonical store.

A fictional example of a batch import and a correction. As in the revision audit example, the fictional site imports pages of the official register every day. For event_id=evt-2026-0042 the batch first receives revision=1, then the same page again because a request was retried, and later revision=2 with a corrected transfer date. Before publishing, the batch checks that the pages and the source's control totals are complete. It writes every envelope it receives to the version log and, in one transaction, adds any not-yet-seen combination of source_system, event_id and revision, updating the pointer to the canonical transfer only if the new version genuinely supersedes the previous one under the source's contract. A unique constraint on that combination makes a repeat safe: the same tuple with the same digest of the content, normalised according to the source's contract, is a no-op. If the tuple matches but the digest differs, both observations are kept for reconciliation, and the accepted revision and the canonical pointer stay unchanged until a decision is made under the source's contract. revision=2 leaves revision=1 in the log but does not add a second transfer. A late redelivery of revision=1 does not bring back the old state. If the source does not guarantee monotonically increasing numbers, or does not define a "corrects event" relationship, the importer does not guess the order from the time of receipt: it needs a rule from the source or a manual resolution of the conflict.

Once the versions have been accepted, the batch rebuilds the projection for the affected player_id ordered by effective_at when the source gives an exact moment, or by effective_date when the contract gives only a calendar date. Delivery order is not the order of the business event, and a day is not turned into an invented midnight. If two incompatible changes are only precise to the day and the source's contract defines no stable order, they are left for reconciliation and the current projection is not guessed. The same applies when an event with only an effective_date and an event with an exact effective_at fall on the same calendar date at the source and the contract does not define their relative order: that pair goes to reconciliation, and so does any other set of incompatible events without a defined order. For a small, bounded dataset it is simpler to reassemble the player's history from scratch, check the sequence of from_team_id/to_team_id, and perform an idempotent upsert of the single current row keyed by player_id. Applying events incrementally is cheaper, but it needs a controlled checkpoint and a way to undo the outdated effect of a corrected version. A full replay of the same range must not double the transfers or the roster rows. Before publishing a new generation of the projection, the team reconciles it against a snapshot of the current roster from the same source and moment: the reconciliation looks for missing external IDs, unknown player_id values, extra records and differing team_id values. Each mismatch is flagged with a reference to the incoming revision; a player's name is not automatic proof of identity. Such a snapshot verifies the current state within its coverage, but it does not prove the completeness of older history that the source no longer keeps.

Event streams, read positions and load

Stream processing reads events as they arrive over time, stores its read position and keeps a result up to date. An event has a key, a payload and an order defined by the source. In Kafka a topic consists of partitions; order is guaranteed within a single partition, not automatically across all partitions. The key used to route events therefore affects both parallelism and the ability to read the records about one entity in order.

An offset is the position of the next message in a partition, from which the consumer will continue reading. A consumer group distributes partitions among processes, but a rebalance can change which process owns a partition. A consumer that saves its offset before writing the result can lose that result after a crash; one that writes the result first and then saves the position can perform the write twice. The order in which the position is committed, the transaction and the idempotency of the write must all match the guarantee you need.

A worked example of a status stream. Booking events carry the booking_id, a version number, the new status and the event time. The stream is routed by booking_id, so the transitions for one booking are read in sequence within one partition. The consumer discards a version older than the one already stored and updates the projection only if the new version is the expected one; an unexpected gap sends the record to a controlled recovery from the canonical table. If the process falls behind, the team monitors the age of the oldest unprocessed event and the length of the queue, not just the fact that the consumer is running. When the event rate exceeds the processing rate for a long time, a growing backlog forms; adding consumers helps only if there are free partitions, room for the work and no shared bottleneck.

Kafka documents at-most-once and at-least-once delivery modes. A transactional producer together with a consumer in read_committed mode (reading only committed transactions) can atomically tie what was read to new records written to Kafka topics, but this guarantee does not automatically extend to an arbitrary external service (see the outbox and idempotency). Long windows based on event time also need a policy for late events and a watermark: the point after which the system treats the data as complete enough for a preliminary result. Field and parameter names depend on the engine and its version, so check them against the version you use.

A checkpoint stores the read position together with the processing state needed to resume after a restart. If the checkpoint and the result are written to different stores without a shared transaction, a result committed first may be repeated, and a position saved first may skip the result. The simplest safe strategy often accepts repeats and makes the write idempotent; another is to commit the position and the output through one supported transactional mechanism. A log lets you replay a range for as long as it is retained. Using a log does not by itself make a system event-sourced: the log is the source of truth only when the product deliberately stores every meaningful change in it and can reconstruct state from it unambiguously.

Backpressure arises when the source supplies work faster than the consumer can finish it. The system has to bound its buffer, slow down or reject incoming work, or scale processing in line with the partitions; without such a plan, the queue and the time to result will keep growing. Not every task needs a stream. If the business can wait for an overnight total and the result can be recomputed from the canonical tables, a batch job is simpler to run and to recover. A stream is worth its complexity when latency matters or when other systems must receive changes without periodically reading everything.

Change data capture and rebuilding a search index

CDC (change data capture) passes changes from the canonical store to another consumer without relying on a periodic full scan of all tables. PostgreSQL logical decoding can hand changes from the log to an external reader; logical replication first creates an initial snapshot and then applies subsequent changes. The consumer must store its position so that a crash restart neither skips changes nor causes silent duplication. A schema change is not transmitted automatically as a row event, so the application and the target index need a separate compatibility plan.

A complete search-rebuild example. The current search index, bookings_v1, contains inconsistent documents after the rules changed. The source of truth remains the bookings in the relational database, not the search index. The team creates bookings_v2 with a new field mapping and starts a process that takes a consistent snapshot and records the log position at which that snapshot matches the state of the source. While it scans the rows, the process captures the new change events that occur after that boundary; once the initial load is finished, it applies them in order of record version. A repeat of an event with the same version neither creates a second document nor overwrites a newer state.

Before switching, the team reconciles the number of active bookings, a sample of documents by identifier and several search results against the canonical records. It waits until the index lags by no more than the threshold set by the product requirement, checks the error signal, and then, in a single atomic request, moves the alias bookings_search from v1 to v2. Read queries go through the alias; consumers do not remember the name of the physical version. The team keeps v1 for the duration of the rollback window and does not delete it until after the post-switch checks. If search returns wrong results, the alias is moved back, the process is fixed and the reconciliation is repeated. A complete index without a consistent change position is not enough: a booking may have changed between the scan and the switch.

A cache, a materialised view and a search index are different forms of derived data (see operational writes and analytical reads). They differ in interface, but each has a source, a delay, update rules and a recovery plan, and each needs a defined acceptable staleness. A cache's time to live, its invalidation keys, its behaviour after an outage and its full rebuild must all be visible. Do not use a cache as final proof of a payment or of the right to the last seat unless its acceptable staleness guarantees that.

The current search document can be rebuilt from the complete, up-to-date canonical record. But if the team needs to answer "what did we know last month?" and the system kept only the present row, the earlier historical shape can no longer be derived from it. Such a requirement calls for storing versions or events separately, together with retention rules for them. An index can restore a required read only to the extent that the stored source contains the necessary facts.

Schema evolution and zero-downtime changes

The data lifecycle and how a schema evolves over time: expand, backfill, switch readers, contract

DDL (Data Definition Language) changes tables, constraints and other database objects. But a deployed product has more than one reader: older versions of the service, background jobs, replicas, reports and integrations may still expect the old fields. Plan a migration as a change to the contract between versions that are alive at the same time, not as an ALTER command issued in the hope that every process will be updated at once.

The expand-and-contract approach carries out a compatible change in several steps. First add the new field or the new table in a way that lets the old code keep working. Deploy code that reads both shapes and writes the new one; an old reader may still exist. Backfill the historical rows in batches that can be rerun and observed. Check completeness and switch reads to the new shape. Only after the old readers are gone, stop writing the old shape and remove the old structure. Every stage needs a metric, a stop condition and a safe direction for rollback.

With PostgreSQL logical replication, schema definitions are not copied automatically; both sides have to coordinate tables and added columns so that the stream does not stop. Integration events likewise need compatibility between old and new consumers. For every change, decide who else reads the field, how long the old process lives, how to roll back the code after historical data have been backfilled, what happens to reports, and how to verify the copy. A migration file without a compatibility plan is not a complete procedure.

A fictional example: adding transfer history. The site's initial schema stores only players.current_team_id, so it shows the current roster but cannot explain earlier transfers or when they took effect. The team weighs changing this table in place against a separate historical model. It chooses additional tables:

  • roster_source_revision, with a unique key on source_system, event_id, revision, for the delivered versions;
  • roster_transaction, for one canonical transfer, with an internal transfer_id, player_id, from_team_id, to_team_id, effective_at or effective_date with an explicit precision, observed_at and a reference to the accepted version. Each source_system, event_id pair maps to exactly one canonical transfer_id;
  • players.current_team_id remains as a compatible projection for the old readers.

External and internal keys are linked by foreign keys or a controlled mapping register. New source records have a mandatory source identifier, event identifier, revision and observed_at. effective_at is filled in when the source defines a moment; when the source gives no time, the date and its precision are stored separately. For old rows these fields may be unknown: an explicit state such as provenance=legacy_unknown is better than an invented date or source_event_id.

The rollout proceeds in stages:

  1. Add the tables and compatible code that still reads the old projection. Before readers are switched, every writer (every importer) must update the canonical history and the old current-state projection in one transaction; otherwise an old process will silently change the roster without a corresponding event in the history.
  2. Load the recoverable versions from the source's archive in small, idempotent batches. Leave gaps and unknown provenance visible; do not disguise them as precise historical facts.
  3. Build the new projection as a shadow generation, compare the roster on a control date with the old table and with an authoritative snapshot, review every discrepancy, and only then switch readers.
  4. Keep supporting the old field for a defined rollback window. A code rollback may go only to a compatible writer that continues to update the history and the projection atomically in one transaction. If no such version exists, keep imports paused until a version compatible with the dual write has been restored and verified. Do not resume work on the previous writer without the dual write: it could change the current roster without a matching historical fact.
  5. Remove the old structure after its readers have disappeared, reconciliation is complete and recovery has been verified. If the team cannot recover the old history, it can keep only the verified current state and start the full history from the launch date; that completeness boundary must be visible in the product and in reports.

Serialisation turns a structured record into a format that can be stored or transmitted. For example, JSON (JavaScript Object Notation) can carry a booking event with field names, an identifier and a version number. Compatibility depends on the reader's rules, not on the file having a .json extension: does it ignore unknown fields? Is the new field optional? What does a missing value mean? Has the type changed?

A fictional example: versioning an event contract. A message in the new format can carry event_id, revision, player_id, from_team_id, to_team_id, and effective_at or effective_date with effective_precision; the envelope holds source_system and observed_at. First the team deploys readers that accept both the new shape and the old events without revision/effective_at, marking the provenance of the old ones as unknown. It checks how every live reader version behaves with both formats; ignoring an unknown field is allowed only when the contract confirms it, not because JSON is assumed to tolerate it. Next the producer adds the new fields as optional for v2 events. Once all producers have migrated and the old queues have been checked, the new fields can be made mandatory for new events; old records are not rewritten as if they had always had a known effective_at. Removing old fields or changing their types waits until the old consumers are gone. Backward compatibility here means that a new reader has a defined interpretation of old messages; forward compatibility means that an old reader works with new messages under verified rules. Both properties depend on the specific schema, the serialiser and the reader's implementation.

Choosing a store and giving an agent access to data

Start the choice from the states that must stay correct and the queries that are actually needed. Compare options on measured load, acceptable staleness, behaviour under peaks, volume of history, recovery, permissions, observability, cost and the team's experience. A managed service can take over part of the day-to-day work, but it does not remove responsibility for access, backups, configuration, costs and the decisions that a provider cannot make on the product owner's behalf.

A service boundary does not remove data dependencies. If booking confirmation and customer notification belong to different services, a network call, retries, latency and the question of who owns the canonical state all appear between them. How responsibility is divided affects where data are stored and how changes are coordinated; this architectural trade-off is discussed in detail in the book Software Architecture: The Hard Parts.

A worked example of an agent request. An operator asks: "How many bookings were there last week?" The agent calls a controlled reporting tool with the dates and the time zone. The tool applies the operator's permissions, reads only the fields it needs, and returns the number, the scope of the report and how current the data are. The answer "37, data updated at 09:00" names the source and the freshness boundary; the model does not invent precision the tool did not return. For the request "confirm the last seat", the agent does not compose an arbitrary write to the table: it calls a domain operation that checks capacity and version within a transaction. The operation returns "confirmed", "seat taken" or an indeterminate result with a retry key. After a timeout the agent does not claim that the action did not happen; it checks the state by that key or asks the operator to resolve the uncertainty.

An application built around an artificial intelligence (AI) model adds one more layer: the system can explain a query or propose an action, but authority is defined by the tool, the server and the database. A hint in the prompt text must not grant access to another customer's data or widen write permissions. An agent's ordinary account should not get broader rights than its specific task needs.

Give the agent narrow access to the fields it needs through a role-based view or a parameterised query, with limits on rows, fields, time range and response size. The ownership check must run in the tool or the store, not only in the text of an instruction. Route changes through a domain operation with a domain check, a version check or a retry key; a significant external action can be sent for separate approval. Log the tool name, the request identifier, the user, the version that was read and the result, without unnecessary private values. Where needed, check the behaviour against control scenarios. The model does not confirm that an action was carried out: check the state that the operation returned.

A worked example of choosing storage for the booking service. The team has canonical customers, slots and bookings, a daily report, and search by date and customer. It verifies the last-seat invariant and starts with a single relational database, where a booking is protected by a constraint and a transaction. The report first reads the same tables; only after measurements confirm that it interferes with operational writes does the team add a materialised summary labelled with how current it is. The search index stays derived and is rebuilt from the canonical records. The team chooses a managed database if its capacity and contract are sufficient for the availability requirements and a dedicated in-house on-call engineer is not needed; before choosing, it checks recovery, replica lag and access controls. Sharding, a consensus service or a streaming engine are not added for the sake of hypothetical future load. The decision is revisited after a measured bottleneck, a new recovery requirement or a change in a domain invariant.

Summary

Start from written-down invariants, real queries and recovery boundaries. Within a single store, constraints, atomic transactions and concurrency control protect shared records; for replicas, queues, indexes and external services, define latency, retries, ordering and recovery separately. Every derived view has a named canonical source and a verifiable way to rebuild it; history is kept when the product requires it. Choose the simplest architecture that meets the requirements with measurements and defined guarantees, and introduce a more complex one only for a concrete reason, with an owner, monitoring and a rollback plan.

Sources and further reading

All sources were checked on 23 September 2026. The teaching scenarios (bookings, the football register, the webhook, the three-node cluster and others) are invented and do not describe a real product, league or provider.

Data, schema, keys and constraints

  • PostgreSQL Global Development Group, PostgreSQL 18 documentation: Table basics (defining tables and columns) and Constraints, including primary keys, unique constraints, foreign keys with referential integrity, and the limit of CHECK constraints across rows. These pages describe PostgreSQL 18 behaviour. The junction-table example is a teaching example; the product owner must decide the deletion semantics.

Data models and distributed data

Operational and analytical workloads, indexes and storage

  • PostgreSQL 18: Materialized views, which explain that the result is stored and refreshed by a separate command; EXPLAIN, which describes the query plan and cost estimates; Introduction to indexes and Multicolumn indexes. The distinction between operational and analytical work and the report example are a teaching synthesis; a design with freshness and recovery must be a requirement of a specific product. The index example has to be verified against the actual plan and the specific database version.
  • Patrick O'Neil et al., The Log-Structured Merge-Tree, Acta Informatica, 1996. The paper describes a design; it does not promise the same advantage under every workload.
  • DuckDB, Querying Parquet with Precision. Columnar storage and row-group statistics in Parquet.

Transactions and concurrency

Time, audit, replication and backups

Partitioning

  • PostgreSQL 18: Table partitioning, describing partition pruning, boundaries and key limitations.

Failures, retries, clocks and consistency

Distributed operations, batches and streams

  • AWS (Amazon Web Services), Prescriptive Guidance: Transactional outbox pattern, which warns about redelivery and the need for an idempotent consumer. The outbox and processed-events tables in this unit are a teaching implementation, not a ready-made library.
  • Apache Kafka 4.1, Design: topics, offsets and delivery guarantees, including the restriction of exactly-once to Kafka's own transactional domain and the need for the external system's cooperation. Apache Kafka 4.1, Protocol: routing by key and partition; it concerns client-server requests and responses, not automatic schema compatibility of business events. The booking stream description is a teaching example; cross-partition ordering and external writes have their own limitations.
  • Jeffrey Dean and Sanjay Ghemawat, MapReduce: Simplified Data Processing on Large Clusters, USENIX Symposium on Operating Systems Design and Implementation (OSDI), 2004. Revision order, snapshot completeness and correction rules are defined by the contract of the specific source.

Change data capture, search and schema evolution

Storage choice and AI agents

  • Software Architecture: The Hard Parts (book page). Architectural trade-offs in dividing responsibility for data between services.
  • OpenAI API documentation, Agents, describing multi-step work with tools.
  • Anthropic, Building Effective AI Agents, which discusses verification of agentic workflows. These materials do not define the behaviour of a database or an external service.