Why Databases Exist

8.1.1 PLAIN — in simple words#

1. A file can hold useful data perfectly well. Trouble begins when several operations must agree about which changes belong together and which values are current.
2. Imagine that Dev and Mira both open a stock file, each work from the old value and each save a replacement. The later replacement can silently erase the earlier person’s contribution.
3. The file has not necessarily become unreadable. It can remain neatly formatted and still tell the wrong combined story.
4. Another problem appears when one operation must update more than one place. Recording an order without recording its lines, or reducing stock without recording why, can leave a half-finished business operation.
5. A third problem appears when a process stops halfway through a write. The file’s presence does not prove that it contains a complete new version.
6. These problems do not mean that all files should be abandoned. They mean that coordination, recovery and validation have become requirements. A database engine supplies established mechanisms for many of them, under stated limits. [S53]
7. Adding custom locks, journals and conflict checks around files may be possible. At some point the project is building part of a database system itself, whether or not it uses that name.

8.1.2 PLAIN — a picture in your head#

1. Picture a shared notebook on a counter. Two people each photocopy its stock page and walk away to make changes.
2. Dev crosses out five and writes three after recording an issue of two items. Mira crosses out five and writes four after recording an issue of one item.
3. Dev puts his page back. Then Mira puts hers on top. The visible page says four, but the recorded actions together should have reduced five to two.
4. The later page looks complete. Nothing about its handwriting reveals that an earlier contribution disappeared.
5. Where the comparison breaks: computers can coordinate access through locks, version checks, transactions and other mechanisms. A shared file is not condemned to behave like uncontrolled photocopies. The failure comes from the specific read-modify-overwrite procedure.
6. Nor does putting the notebook in an expensive cupboard automatically fix the procedure. Moving data to a database while keeping an unsafe application sequence can preserve the same logical error.

8.1.3 PLAIN — a worked example#

1. This isolated race fixture starts with the value five. Its identifier is RACE-STOCK, and its two requested deductions are separate from every canonical shop order.

2. Applying both deductions in sequence would give 5 − 2 − 1 = 2. The final four therefore does not reflect the complete set of requested changes.
3. If the final write were Dev’s instead, the stored value would be three. Changing which person is faster changes which contribution disappears; it does not create a correct coordination rule.
4. Notice what is lost: an earlier update’s effect. The bytes can remain valid, and both programs can report that their writes succeeded.
5. A different failure occurs when a program stores a new order header and then stops before storing its lines. There is no race in that example. One operation was split into separately visible pieces.
6. The diagnosis should name the failure: lost update, partial operation, malformed representation or another specific condition. “The file broke” is too vague to guide a repair.

8.1.4 PLAIN — what is really happening inside#

1. The unsafe procedure separates reading, deciding and writing. Between those steps, another operation can act on the same old value.
2. Protecting only the final write is not enough if the decision was based on an earlier unprotected read. A lock used for the wrong interval can leave the important race intact.
3. A version check offers another idea: store the new value only if the record still has the version that was read. A failed check tells the caller to reconsider the decision against fresh state.
4. Some changes can instead be expressed directly at the database boundary: “deduct this quantity only when the stored quantity is sufficient”. That combines the condition with the update rather than calculating a replacement from a stale value outside the engine.
5. The complete operation may still contain several statements. A transaction groups the related statements, and the chosen isolation behaviour governs interaction with other work. The word transaction alone is not an explanation of every concurrency guarantee. [S01] [S66]
6. SQLite serialises writes to a database; the details of reader interaction depend on its journalling mode. A busy or conflicting operation can require waiting, retrying or failing rather than magically completing alongside another writer. [S54]
7. The lab exercises a controlled two-connection lock case and an explicit rollback. Those are concrete local observations, not proof that every application using SQL is free of races.

8.1.5 TECHNICAL — the engineer’s version#

1. The timeline demonstrates a lost update under an application-level read-modify-write sequence. It does not depend on torn bytes, a failed storage device or a parser defect.
2. A correct design states its invariant, its coordination boundary and the allowed failure outcome. “Expected stock must not become negative” is an example invariant; it is not a universal rule for every inventory domain.
3. An atomic update can avoid one form of lost update by computing from the value at the engine’s update boundary. It does not automatically protect a separate precondition checked earlier, a second database or an external action.
4. In PostgreSQL, the chosen transaction-isolation level affects which concurrent behaviours are permitted. Serializable transactions can fail with a serialization error, and the application needs an appropriate whole-transaction retry strategy. [S66]
5. In the supplied SQLite demonstration, connection A holds a write transaction. Connection B has a zero busy timeout and attempts to start another write transaction. It receives a lock-related failure; after A rolls back, B can proceed. [S25] [S54]
6. That demonstration uses a fresh temporary database on the authoring machine. It is not a multi-host shared-filesystem test, an observation of the user’s laptop or a simulation of a production incident.
7. Design rule for the examples: record a rejected or busy request as such. Do not relabel it a completed sale, and do not blindly rerun external actions while retrying a database operation.
8. Later chapters develop isolation levels, optimistic concurrency and retry protocols in depth. At this stage the required insight is narrower: a correct individual calculation can still produce an incorrect combined history when its coordination boundary is wrong.

8.1.6 WORDS — remember these#

1. Lost update: one accepted contribution disappearing beneath another write — a concurrency anomaly caused by insufficient coordination of read-modify-write operations.

2. Read-modify-write: read a value, compute a replacement, then store it — a multi-step sequence that needs an appropriate concurrency rule.

3. Invariant: a condition the system is meant to preserve — a stated property that every accepted state transition must maintain.

4. Coordination boundary: the interval or operation within which competing work is controlled — the scope of a lock, version condition or transactional mechanism.

5. Busy outcome: the system cannot complete the requested access immediately — a contention result that requires an explicit wait, retry or failure policy.

6. Partial operation: only part of a logically related change becoming effective — an incomplete business action whose pieces were not protected by the needed boundary.

8.2.1 PLAIN — in simple words#

1. A database is an organised body of data. A database engine is software that manages access to that data. An application is the software that uses the engine to perform the shop’s work.
2. People often use “database” for all three in casual conversation. That shortcut becomes confusing when deciding which part is responsible for a failure.
3. The engine can store tables, run queries and enforce declared rules. It does not know the shop’s intentions unless those intentions have been translated into actual structures, constraints and application logic.
4. A question such as “show the lines of order O-1042” is a query. A request such as “record this new line if its order exists” combines a data change with rules that must be checked.
5. Many engines can choose among several ways to find an answer. The user asks for the result; the engine plans how to obtain it within its supported operations.
6. This chapter focuses on transactional SQL engines. Not every product described as a database provides the same model, query language, transaction features or operational guarantees.
7. The name of a product is therefore not a substitute for understanding its particular promises and testing the parts on which your application relies.

8.2.2 PLAIN — a picture in your head#

1. Think of an archive containing labelled files, with a trained clerk at the desk. The files represent stored data. The clerk and the archive procedures represent the engine.
2. A shop application is the person submitting a request: “find this order”, “add this line”, or “give me the total for these orders”.
3. The clerk can refuse a form that violates a declared rule. But if the rule was never supplied, the clerk cannot be expected to infer it from the owner’s intentions.
4. An index is like a maintained lookup guide. A query plan is the clerk’s chosen sequence of operations for answering one request. It may use the guide, scan a collection or combine results.
5. Where the comparison breaks: the engine is not a human interpreter of truth. It executes defined operations. It can enforce a perfectly specified rule over completely fabricated source data.
6. The archive also needs care: backups, access control, maintenance and a recovery procedure. Hiring a clerk does not eliminate responsibility for those tasks; it changes how they are performed.

8.2.3 PLAIN — a worked example#

1. Mira asks, “What is the recorded value of each of our two canonical orders?” The expected answer is still O-1042: 17,100 paise and O-1043: 11,550 paise.
2. The application submits a query over agreed line quantities and agreed line prices. The query must not accidentally substitute today’s catalogue prices.
3. The engine checks the query’s structure, identifies the tables and columns, selects an execution strategy and returns rows containing the calculated values.
4. The application formats paise as rupees for display. If it prints 17,100 paise as INR 17,100.00, the calculation inside the engine could be correct while the presentation is wrong.
5. If the application asks for a sum of order totals repeated on every line, the engine may correctly return 57,300 paise for the intentionally wrong query from Chapter 1. The engine does not automatically know that the application chose the wrong grain.
6. These outcomes identify different responsibilities:

7. A useful incident report separates those cases instead of saying only that “the database is wrong”.

8.2.4 PLAIN — what is really happening inside#

1. A SQL engine parses a statement and connects its names to known objects. It can reject syntax it cannot recognise or references that do not resolve.
2. For a query, it forms a plan using available operations such as scans, filters, joins and aggregation. PostgreSQL’s EXPLAIN displays a representation of such a plan. [S65]
3. During execution, the engine reads or changes data through its storage and transaction machinery. The concrete layout is an implementation detail, developed later in the book.
4. Metadata describes objects such as tables, columns, constraints and indexes. This is data about the managed structure, not an independently verified description of the physical shop.
5. The application receives rows or an error through a programming interface. It must handle both, including cases where a connection fails and the final transaction outcome is uncertain from the client’s perspective.
6. An execution plan is useful evidence about the work the engine intends to do. An estimated cost is not automatically a measured duration on the user’s machine. [S65]
7. We preserve the distinction between “the engine returned this result for these stored inputs” and “the real-world events were complete and correct”. The latter requires evidence beyond query execution.

8.2.5 TECHNICAL — the engineer’s version#

1. The term database management system, or DBMS, refers to the software and facilities managing databases. In our examples, SQLite is the embedded engine and Python is the application runtime. PostgreSQL is used as a separately identified documentation comparison. [S50] [S52]
2. The schema is the declared structure and rules visible to the engine. The broader domain contract also includes requirements outside that declaration, such as who may approve a stock adjustment or whether a supplied observation is authentic.
3. SQL separates many logical requests from their physical execution strategy. Two equivalent-looking queries may receive different plans, and changing data volume or available indexes can change the plan. That is why later performance claims must be measured rather than inferred from query appearance. [S65]
4. EXPLAIN ANALYZE in PostgreSQL executes the statement to obtain actual execution statistics. It should not be used casually on a modifying statement under the assumption that it is a harmless description-only operation. [S65]
5. A database constraint provides an enforceable rule only within its defined semantics and active configuration. A foreign key that has not been enabled in the SQLite connection is not protecting the relationship merely because its declaration appears in a schema file. [S59]
6. Bound query parameters keep data values separate from SQL text. They do not authorise the caller, validate a business rule or make a dynamically selected table name safe by themselves. The lab uses fixed SQL and bound values. [S20]
7. An ORM, a web framework and a database engine can each participate in one request. A convenient application abstraction does not erase the underlying transaction or query semantics. Diagnose at the relevant boundary instead of treating the abstraction’s name as an explanation.
8. The chapter’s archive metaphor is original teaching material. The documented implementation claims are specifically attributed; no assertion is made that every DBMS uses the same planner, storage organisation or failure behaviour.

8.2.6 WORDS — remember these#

1. Database engine: the software managing stored data operations — the component that executes supported queries, changes and integrity mechanisms.

2. DBMS: the database-management software — facilities for defining, accessing and administering databases.

3. Query: a request for a result from data — an expression evaluated under a data model and its language semantics.

4. Query plan: the engine’s proposed route to an answer — an execution structure using operations such as scans, filters, joins and aggregation.

5. Metadata: information describing other managed information — structural definitions such as columns, constraints and indexes in this context.

6. Bound parameter: a value supplied separately from query text — data passed through a driver’s parameter interface rather than inserted into SQL by string concatenation.

8.3.1 PLAIN — in simple words#

1. An embedded database engine runs inside the application’s process. The application calls the engine directly, like calling other library functions.
2. A client-server database engine runs as a separate service. The application sends requests through a connection, and the server process performs database work.
3. SQLite is an example of the embedded arrangement. PostgreSQL uses a client-server arrangement. These describe where the engine runs, not whether one is “real” and the other is “fake”. [S50] [S52]
4. An embedded engine can manage durable files. A server engine can run on the same computer as its client. “Embedded” does not mean “temporary”, and “server” does not necessarily mean “a faraway cloud machine”.
5. Either arrangement can sit behind a website. The important question is which processes open the database and who coordinates access.
6. The right comparison includes deployment, concurrency, failure boundaries, access control and operational responsibility. Counting how many people can visit a website is not enough to choose the database architecture.

8.3.2 PLAIN — a picture in your head#

1. In an embedded design, imagine the shop assistant keeping a skilled filing routine at the same desk. A request moves from the application to that routine without travelling to a separate service.
2. In a client-server design, imagine sending a request to a dedicated records office. The office manages its own work and sends a result back.
3. The records office could be in the same building or another city. Its separation is a process and service boundary, not necessarily a long physical distance.
4. Where the comparison breaks: a library call and a network request differ in scheduling, memory boundaries, transport errors and authentication, not merely in how far a clerk walks.
5. The metaphor also does not imply that the embedded application is the only possible user of a file. Multiple SQLite connections can coordinate through the engine’s mechanisms, subject to SQLite’s concurrency limits. [S54]
6. What would be unsafe is to assume that several unrelated machines can freely edit a database file on a shared drive with no architecture-specific concerns. The filesystem and locking assumptions matter. [S51]

8.3.3 PLAIN — a worked example#

1. Consider three proposed arrangements for Mira’s Corner. They are alternative teaching designs, not descriptions of an existing deployment.

2. Designs A and B use an embedded engine, but B still serves network clients. The browsers do not directly open its SQLite file.
3. Design C adds a separate database-service boundary. The application must handle database connections, service availability and whatever authentication and permissions are configured.
4. None of these drawings proves that the chosen design meets a particular load target. That requires a workload, measurements and failure tests.
5. The classroom lab uses A’s local engine arrangement, with in-memory or newly created temporary files. It does not contact a hosted database, expose an internet service or change any user database.
6. To keep experiments distinct, the log records the actual Python and SQLite versions used. The cited PostgreSQL behaviour is not labelled as executed merely because a similar concept appeared in the SQLite lab.

Figure 8.1 — Embedded SQLite executes in the application process; a client-server design sends requests to a separate database process. Neither picture determines the business rules or proves a performance claim.

8.3.4 PLAIN — what is really happening inside#

1. In an embedded arrangement, calls enter the library in the same process. SQLite then performs storage and coordination through the operating-system interfaces available to it. [S50]
2. In a client-server arrangement, client and server exchange requests and results over a connection. The server manages the database files; clients should not bypass that service by editing those files themselves. [S52]
3. Process separation changes failure handling. If the application process ends, the server process may continue. If the connection fails, the application cannot always conclude that the server did nothing.
4. A connection pool is a set of reusable connections. It can reduce repeated setup work, but it does not make the server’s capacity unlimited or safely preserve arbitrary transaction state between unrelated requests.
5. With SQLite, putting the database behind one application service can be different from having many machines directly access a shared database file. The project’s usage guidance specifically distinguishes these situations. [S51]
6. The architecture must match the coordination boundary. If several independently deployed services all need to change shared authoritative data, the design needs a clear owner for those writes and a tested concurrency model.
7. Merely changing a connection string or moving a file to a remote folder does not supply that design.

8.3.5 TECHNICAL — the engineer’s version#

1. Embedded describes a library executing in the host application process. Client-server describes clients communicating with a separate database-service process. PostgreSQL clients and servers need not be on the same host. [S50] [S52]
2. SQLite’s “serverless” terminology means no separate database server process. Do not confuse that meaning with a cloud provider’s usage-based function or managed-service product category. [S50]
3. SQLite permits many readers but one writer at a time for a database. Write-ahead logging changes how readers and a writer can overlap; it does not create arbitrarily many simultaneous writers. [S54]
4. For multiple machines directly opening one database file, filesystem latency and locking behaviour become important. SQLite’s own guidance recommends a client-server engine when many client programs send SQL to the same database over a network. [S51]
5. Those cautions are not a prohibition on every web application using SQLite. A single application server can receive many network requests while controlling local engine access. Whether that arrangement is sufficient is a workload and reliability question, not a slogan.
6. For the foundations lab, Python connections use isolation_level=None and explicit SQL transaction statements. That makes the demonstration’s transaction boundaries visible rather than relying on a driver’s implicit transaction-opening conventions. [S20]
7. A different driver, engine or transaction mode requires a separate compatibility review. Python’s driver API, SQLite’s SQL semantics and PostgreSQL’s server behaviour must not be conflated.
8. The test suite reports its local environment and whether a test used memory or a temporary file. This disclosure matters more than presenting a diagram with the word production beneath it.

8.3.6 WORDS — remember these#

1. Embedded database: an engine included in an application process — database functionality supplied through library calls rather than a separate server process.

2. Client-server database: an engine operating as a separate service — an arrangement in which clients exchange requests and results with a database server.

3. Connection: a session through which an application interacts with an engine — a context carrying database access and potentially transactional state.

4. Connection pool: a managed set of reusable connections — a bounded resource-sharing mechanism, not unlimited server capacity.

5. Process boundary: a separation between running programs — an isolation and communication boundary that changes failure and resource handling.

6. Serverless, in SQLite: no separate database server is required — SQLite’s embedded-engine meaning, distinct from some cloud-service uses of the word.

8.4.1 PLAIN — in simple words#

1. A transaction groups database work so that a related set of changes can succeed together or be rolled back together.
2. For example, recording an accepted request and deducting its stock should not become two unrelated successes if the application requires both to belong to one operation.
3. Beginning a transaction does not mean it has succeeded. A commit accepts its database changes; a rollback abandons its uncommitted changes. [S01] [S25]
4. A query asks for an answer from the data visible under the applicable rules. It may run inside a transaction or be handled as a statement with its own transaction boundary, depending on the engine and client mode.
5. A transaction cannot invent a missing business rule. If the application records the wrong quantity and that quantity satisfies all declared checks, committing the transaction can reliably preserve the wrong input.
6. A database rollback also cannot pull a sent email out of someone’s inbox or bring a physically handed-over notebook back to the shelf.
7. The useful promise is therefore specific: these database changes are grouped under these engine, configuration and failure assumptions. “Everything everywhere is safe” is not a transaction guarantee.

8.4.2 PLAIN — a picture in your head#

1. Imagine filling in a two-part shop form behind a counter screen. One part records the request; the other records the corresponding stock deduction.
2. Before the complete form is accepted, it is still a proposed operation. If a required check fails, the proposal can be discarded rather than exposing an incomplete version.
3. That is a starting picture for grouping database changes. It does not imply that all intermediate bytes are physically written at the same instant.
4. Where the comparison breaks: engines use concrete logging and recovery mechanisms to make a transaction appear atomic at their boundary. Those mechanisms depend on storage behaviour and configuration. The screen is only an analogy. [S53]
5. The analogy also hides concurrent readers. Different isolation levels can expose different committed snapshots or require retries; a universal “everyone sees the same page forever” story would be wrong. [S66]
6. Finally, the screen does not cover the whole world. A courier leaving while the form is being prepared is an external action, not something the database can undo by discarding the form.

8.4.3 PLAIN — a worked example#

1. Use a separate teaching product P-DEMO with five available units. Request Q-01 asks for four units. Request Q-02 later asks for two. These identifiers do not replace any canonical order.
2. The first request is accepted only if sufficient stock is available. Five minus four leaves one.
3. The second request cannot deduct two from one under this fixture’s non-negative-stock rule. It is rejected, leaving the stored quantity at one and creating no accepted Q-02 request record.
4. Now return to the initial five in a fresh database. Deduct four, insert Q-01 and deliberately raise an exception before commit. An explicit rollback leaves stock at five and no accepted request record.
5. The operation’s intended SQL shape is:

BEGIN IMMEDIATE;
UPDATE demo_stock
SET quantity = quantity - ?
WHERE product_id = ? AND quantity >= ?;
-- The application must check that exactly one row changed.
INSERT INTO demo_requests(request_id, product_id, quantity)
VALUES (?, ?, ?);
COMMIT;

6. The question marks stand for bound values supplied by Python, not literal SQL to paste unchanged into every database console. An unsuccessful precondition must trigger the application’s rollback path before an acceptance record is inserted.
7. The test deliberately raises an ordinary Python exception inside an open SQLite transaction. It is not a power failure. The result demonstrates the application’s rollback logic for that controlled failure.
8. The transaction says nothing about whether four goods were physically handed over. The fixture describes accepted database requests only.

8.4.4 PLAIN — what is really happening inside#

1. The engine begins a transaction and applies the conditional update. A result indicating zero changed rows tells the application that its acceptance condition was not met for the named product.
2. The application must inspect that result. If it inserts an acceptance record anyway, wrapping the statements in a transaction merely groups an incorrect decision.
3. If the update succeeds, the application inserts the request record. A duplicate request identifier causes a constraint error instead of a second independently identified acceptance.
4. The code catches failures, rolls back an active transaction and re-raises an error for the caller. It does not report success because one earlier SQL statement happened to finish.
5. If all required work succeeds, the code commits. Later chapters explain how durable engines protect committed changes through logging and recovery, and where configuration can weaken the promise. [S04] [S53]
6. Not every statement error automatically rolls back the whole SQLite transaction. Our application therefore uses an explicit rollback path rather than assuming that any exception has already restored all earlier changes. [S25]
7. After a connection failure near commit, the caller may need to establish the outcome before retrying. The local educational labs do not implement a production idempotency or reconciliation protocol for uncertain commits.

8.4.5 TECHNICAL — the engineer’s version#

1. The familiar acronym ACID names atomicity, consistency, isolation and durability. Each term needs a boundary. Atomicity concerns the transaction’s database changes, not all external effects of the surrounding request. [S01]
2. Consistency means preserving the relevant declared invariants when correct application logic and engine checks are applied. It is not a claim that the engine verifies the truth of every input or automatically enforces every cross-row business condition.
3. Isolation controls interaction between concurrent transactions. Its exact guarantees depend on engine semantics and the chosen level. A stronger level may reject work that must be retried; “serializable” does not mean “no failures can occur”. [S66]
4. Durability is the persistence promise for committed work under specified failures and configuration. An in-memory database supplies no survival promise after that process loses its memory. Our rollback test is not a storage-durability test.
5. With explicit transaction control, the exception path checks connection.in_transaction before issuing ROLLBACK. The application should not hide the original failure by incorrectly assuming a transaction is still active after every possible error. [S20] [S25]
6. The conditional stock update and request insert run in the same transaction. If the insert fails, the preceding deduction must be rolled back. A unit test specifically checks this path instead of testing only the successful case.
7. Duplicate identifiers are rejected in this lab, not treated as a full exactly-once protocol. A production retry mechanism would compare request identity, payload and previously committed outcome under a durable concurrency-safe design.
8. A transaction that writes one database and sends an email is not automatically atomic across both. One later pattern is to record the intention to send in the database and process it separately with explicit retry semantics; the complete protocol is outside this chapter.
9. Scope of execution: SQLite is executed locally. PostgreSQL isolation and asynchronous-commit claims are documentation comparisons, not results from an executed PostgreSQL server. [S04] [S66]

8.4.6 WORDS — remember these#

1. Transaction: a defined group of database work — a unit with a controlled commit or rollback boundary.

2. Commit: accepting the transaction’s database changes — successful completion under the engine’s stated persistence and visibility semantics.

3. Rollback: abandoning uncommitted database work — restoration of the transaction’s prior database state, not reversal of unrelated external actions.

4. Atomicity: the grouped changes take effect together or not at all — a transaction property at a specified resource boundary.

5. Isolation: rules for concurrent work seeing and affecting data — the semantics governing interaction between overlapping transactions.

6. Durability: the promised survival of committed work — persistence under defined failures, engine configuration and storage assumptions.

8.5.1 PLAIN — in simple words#

1. Choosing a database does not finish the job. Someone must know how to protect it, monitor it, change it and recover it.
2. A backup is a recoverable copy or representation of data. A file called backup is not proof that it can actually restore the required information.
3. A restore test asks a concrete question: can we rebuild a usable system from the retained material and verify the result?
4. Permissions must be deliberate. A program that can change every table is harder to contain when it has a defect or its credentials are misused.
5. Storage can fill, operations can slow and a migration can fail. Monitoring should reveal conditions that someone is prepared to act on, rather than collecting impressive numbers that nobody reads.
6. Managed services change which organisation performs particular tasks. They do not remove the application owner’s need to understand its responsibilities and test the promised recovery path.
7. A good operational plan names the owner, evidence and response for each important failure. “The database is reliable” is too general to serve as that plan.

8.5.2 PLAIN — a picture in your head#

1. Think of a shop safe. Buying a good safe does not decide who holds its keys, who checks its contents or how the business continues if the building cannot be entered.
2. A spare key is not a copy of the contents. A photograph of the contents is not necessarily enough to reconstruct what was inside.
3. Likewise, a replica, backup, export and screenshot have different properties. The word copy should not conceal their different recovery uses.
4. Where the comparison breaks: a database can preserve a transactionally consistent backup while activity continues, using an engine-supported mechanism. A camera pointed at moving paper forms cannot model that mechanism. SQLite provides a backup API for copying database content consistently. [S55]
5. Also, a logical error can be faithfully copied. If the original accepts an unwanted deletion, a current replica may reproduce it. An independent recovery history is a different need from an immediately available copy.
6. The practical question is “which failure can this retained artifact recover from?”, followed by a real restoration exercise.

8.5.3 PLAIN — a worked example#

1. Mira proposes two classroom recovery targets: lose no more than one hour of accepted work, and restore a usable order view within thirty minutes. These are invented targets for discussion, not measurements or recommendations for a real business.
2. Suppose the last usable backup represents 10:00 and a failure occurs at 12:20. Without additional retained changes, the backup alone leaves a two-hour-twenty-minute gap.
3. The existence of a backup therefore does not establish the one-hour target. The schedule, restore point and any additional recovery material must be compared with the actual target.
4. Suppose a tiny test database restores in one second on an idle machine. That observation does not establish a thirty-minute recovery time for a future system containing many tables, external dependencies and much more data.
5. The supplied lab performs a narrower test: copy a small synthetic SQLite database through its backup API, query the destination and compare the canonical order totals with the source.
6. It checks that the copied destination contains O-1042 at 17,100 paise and O-1043 at 11,550 paise. It does not time a production recovery, restore external documents or demonstrate an off-site backup policy.
7. The distinction between a target and tested evidence should appear in the operational report, not remain an unspoken caveat.

8.5.4 PLAIN — what is really happening inside#

1. A useful backup must correspond to a coherent database state. Copying a live engine’s files without its documented procedure can miss related state or combine incompatible moments.
2. SQLite’s backup interface copies database pages through the engine. PostgreSQL’s pg_dump creates a consistent logical export of one database while activity can continue. Those are different mechanisms with different scopes. [S55] [S67]
3. A recovery plan may also need application code, schema versions, configuration, required extensions and access to protected supporting material. A data copy alone is not always a complete working service.
4. After restoration, check more than whether the database opens. The exercise checks counts, keys, foreign-key consistency and known totals because each can expose a different class of mistake.
5. Change control matters too. If an application assumes a new column but the restored schema is older, the data may be intact while the service still fails to work.
6. The owner must also decide who may inspect backups and rejected input. These can contain information that the ordinary application hides from most users.
7. This book’s practice package contains synthetic material only. Its backup demonstration does not create or validate a real retention, encryption or privacy-control programme.

8.5.5 TECHNICAL — the engineer’s version#

1. Distinguish the recovery point objective, the tolerated age or amount of lost work, from the recovery time objective, the tolerated time to resume a specified level of service. In this book they are planning terms, not guarantees achieved by running a tiny lab.
2. Define the recovery unit precisely: one database, several databases, application files, external objects, identity configuration or the complete business service. A test covering only one unit should not be reported as recovery of all of them.
3. A PostgreSQL logical dump concerns a database and its exported objects; cluster-wide objects such as roles and tablespaces need separate attention. The documented backup scope should drive the recovery checklist. [S67]
4. The local SQLite backup test uses the driver’s backup method, then checks destination values and integrity observations. It does not emulate a failed filesystem or assert that every permitted SQLite deployment has been tested. [S20] [S55]
5. Record execution context: engine version, schema version, source boundary, destination and validation results. Do not use a command’s zero exit status as the only evidence that the restored business state is correct.
6. A migration is a controlled change to a schema or related data. Keep its preconditions and rollback or recovery plan explicit; blindly executing an old migration again can be different from retrying a failed transaction.
7. Security controls must apply to ordinary databases, backup artifacts, exports and diagnostic copies according to their purpose. Adding a noindex tag to an online preview, for example, is not an access-control mechanism.
8. The operational checklist is an original planning aid. Product-specific backup statements are sourced; the worked recovery objectives are labelled assumptions. No certification or live-service recovery claim follows from them.

8.5.6 WORDS — remember these#

1. Backup: retained material intended for restoration — a copy or representation with a defined consistency and recovery scope.

2. Restore test: trying the recovery procedure and inspecting its result — evidence about a specified backup and environment rather than a promise about all future failures.

3. Recovery point objective: how much recent work the plan may lose — a stated acceptable recovery-point gap, not an achieved measurement by itself.

4. Recovery time objective: how long recovery may take — a target for restoring a defined service level under a stated scenario.

5. Migration: a controlled evolution of structure or data — a versioned change with preconditions and a recovery strategy.

6. Recovery unit: the things the restoration claim includes — an explicit scope such as one database or a complete application service.

8.6.1 PLAIN — in simple words#

1. A small, stable dataset read by one program may not need a running database service. A well-specified file can be the simplest adequate representation.
2. A local application needing queries and transactions may benefit from an embedded engine without adding a separate database server.
3. A shared service with many independent writers may need a client-server architecture, but the decision should follow its actual workload and operating requirements.
4. Do not decide from row count alone. A small table with high contention and strict correctness requirements can be harder to operate than a large read-only file.
5. Do not decide from a fashionable feature list either. A feature is useful only when it addresses a requirement the system actually has.
6. The correct output of an early design discussion is a clear set of requirements and trade-offs. A product name can come afterwards.
7. The goal is the least complicated arrangement that satisfies the necessary behaviour and can be maintained by the responsible team—not the fewest lines in the first demo.

8.6.2 PLAIN — a picture in your head#

1. Imagine choosing between a personal notebook, a staffed records desk and a full records office. The office is not automatically the best answer to every note-taking problem.
2. For one person keeping a fixed list of shelf labels, the office adds overhead without solving a pressing coordination issue.
3. For several people accepting overlapping orders while protecting shared stock, the notebook may be too weak unless a substantial coordination process surrounds it.
4. Where the comparison breaks: a simple-looking embedded database can provide substantial transactional functionality, while a large service can be badly designed. Physical size and organisational grandeur are poor measures of data-system correctness.
5. Choosing less infrastructure is not the same as ignoring failure. Even the notebook needs a decision about backup, meaning and who may change it.
6. Choosing more infrastructure is not the same as buying automatic truth. The application must still ask the right questions and model the right facts.

8.6.3 PLAIN — a worked example#

1. These are three synthetic workloads, described before any product choice.

2. “Investigate” matters. The table is a reasoning aid, not a benchmark result or a universal product verdict.
3. For the local workstation, test a representative order, a rejection, a duplicate identifier and restoration of a backup. The happy path alone is not the workload.
4. For the shared service, test simultaneous requests, lock waits, failed connections and maintenance events. Include the team’s ability to operate the chosen arrangement.
5. For the fixed file, test unsupported versions, malformed contents and how the application selects the intended release. “Read only” does not mean “no validation needed”.
6. When a requirement changes, revisit the decision. Adding a second independent writer can matter more than adding another thousand unchanged rows.

8.6.4 PLAIN — what is really happening inside#

1. Every design allocates responsibility somewhere. With a file, the application may perform more validation, searching, coordination and recovery itself.
2. With an engine, some responsibilities move into tested engine mechanisms, but the application still chooses schemas, transactions and business rules.
3. With a separate service, the system gains a clear database-process boundary and also gains connection handling, deployment and service-management work.
4. Operational cost is therefore more than license or hosting cost. It includes time spent understanding failures, restoring data and correcting a poor fit between architecture and workload.
5. Measurements should represent the intended workload. A benchmark consisting only of one fast lookup does not establish behaviour during overlapping writes, backup or schema changes.
6. The database is part of the application’s design. It cannot be chosen meaningfully in isolation from who writes, what must stay consistent, what can fail and who will recover the system.

8.6.5 TECHNICAL — the engineer’s version#

1. Start with a requirement record containing the data model, read/write patterns, contention points, consistency needs, failure scenarios, access boundaries and operational owner.
2. Add concrete quantities only when they have a basis: anticipated record sizes, peak concurrent work, acceptable latency, growth and recovery targets. Label estimates as estimates rather than presenting invented numbers as measurements.
3. SQLite’s usage guidance supports local application storage and distinguishes it from situations with many network clients directly issuing database work. Treat that guidance as a starting point, then test the actual proposed architecture. [S51]
4. File formats and database engines are not mutually exclusive categories. A database may use files internally, import CSV, expose JSON and create logical backups. The useful distinction is which component supplies which guarantee.
5. A “single source of truth” is an architectural responsibility, not a magic table property. Identify which representation is authoritative for each decision and how derived copies are updated or reconciled.
6. Avoid premature promises about seamless future migration. Different engines can differ in types, constraints, SQL, isolation and operational tooling. Portability requires tests of the features actually used, not confidence that both products accept SELECT.
7. The chosen teaching progression is intentional: first import records without losing their meaning, then use a local transactional engine, then model relationships. More demanding concurrency and distribution are introduced only after those foundations are visible.
8. The chapter does not choose a live storage product for the user’s company. It provides the framework and bounded demonstrations needed to make such a decision from evidence.

8.6.6 WORDS — remember these#

1. Workload: the work the system must actually handle — a defined mixture of reads, writes, sizes, timing and contention.

2. Contention: operations competing for a shared resource — overlapping demand that can require waiting, rejection or coordination.

3. Authoritative representation: the record a decision is defined to rely on — a source-of-truth role assigned by the architecture.

4. Portability: the ability to move a defined workload between environments — compatibility of the used semantics and operations, not merely similar syntax.

5. Operational owner: the person or team responsible for running and recovering the system — a named responsibility beyond writing its initial code.

6. Design trade-off: a choice that improves some requirements at a cost to others — an explicit comparison of behaviour and responsibility under stated assumptions.