SQLite and PostgreSQL — extended reference

This reference preserves the earlier detailed chapter and its citations. Start with the guided explanation, then use these sections for additional mechanisms and qualifications. References to the original lesson sequence are historical.

Part 8: Architectural case studies and synthesis

Prerequisites: Lesson 42 and the preceding foundations.

Learning outcomes

• Compare SQLite and PostgreSQL by deployment, concurrency, recovery, and operational boundaries.
• Explain SQLite writer serialization and WAL snapshots without claiming readers never affect maintenance.
• Explain PostgreSQL’s server execution, MVCC, and background responsibilities in terms of workload fit.

The same SQL does not imply the same ownership boundary

Two applications need transactions over customers and orders. One is a desktop application with a local dataset. The other is a shared service with many independent clients and centrally managed access. Choosing between an embedded library and a database server is not a choice between having transactions and lacking them. It is a decision about where execution, coordination, and operational responsibility live.

SQLite and PostgreSQL provide a useful architectural contrast. The implementation facts in this lesson are grounded in their documentation. The workload scenarios and numerical comparisons are constructed analyses, not measured product benchmarks.

SQLite’s documented boundary

SQLite is an embedded library that compiles SQL into bytecode executed by its virtual machine. Its WAL documentation describes concurrent reader behavior and a single writer at a time within the relevant database-file model, along with deployment constraints. Those facts must be interpreted in the documented environment rather than generalized to arbitrary shared network files or distributed ownership.

The useful architectural consequence is that local calls can avoid a separate application-to-database network conversation. The application also becomes responsible for the process that hosts execution and for its file lifecycle. A remote multi-client service can be built around an embedded engine, but that added service must provide its own protocol, admission, security, routing, and operational behavior.

SQLite’s appropriate-use guidance is important precisely because the boundary has strengths and limits. A local application, edge component, or bounded service can value simple deployment and local access. A workload needing a different writer or ownership topology should not assume that adding more front-end processes transparently creates distributed write capacity.

PostgreSQL’s documented boundary

PostgreSQL uses a server architecture with client connections and exposes a broad SQL and transaction interface. Its connection-establishment documentation explains the server-side conversation boundary. Its other chapters in this course document locking, MVCC, WAL, planning, and maintenance.

The architectural consequence is centralized service ownership with explicit client sessions. Several applications can use the same authority through a defined protocol and permissions. This adds communication and operational machinery while providing separation between application processes and the database service.

The general-purpose interface supports varied queries and relationships, but the application still needs suitable schemas, indexes, transaction scope, and capacity governance. A mature engine does not make an N+1 remote access pattern inexpensive or turn a hot global invariant into independent work automatically.

Scenario A: a local inventory editor

Consider an illustrative inventory editor used by one employee on a laptop. The dataset is 2 GB, most operations touch a few records, and the application must work without a network. It needs atomic updates and recoverability after a process crash. There is no requirement for several regions to write simultaneously.

An embedded design aligns execution with the local data and avoids a separately managed service. The application can perform a transaction through local calls. It must still use supported file and backup procedures, avoid unsafe concurrent access, and make its durability settings match the user’s expectations.

A separate database server could satisfy the logical requirements too, but it adds service installation, credentials, connection management, and lifecycle coordination. Those costs may be justified by a larger deployment standard or other applications sharing the data. They are not automatically beneficial for this workload.

The decisive questions are ownership, connectivity, and operational burden. A distributed architecture would introduce network failure states that the local product did not require. Simplicity here comes from matching the real scope rather than choosing a system with the largest theoretical feature set.

Scenario B: a shared SaaS authority

Now consider a service with thousands of concurrent sessions, several application instances, and centrally administered tenant access. Queries include tenant-local transactions and occasional reports. The service needs controlled upgrades, backups, monitoring, and a clear place to enforce shared state.

A database server naturally provides one conversation boundary for those applications. The architecture can pool connections and define roles. It must still prevent one application instance from opening unbounded transactions or one tenant from monopolizing scans.

An embedded engine behind one dedicated service can also provide a central authority for a bounded workload. The service design then needs to explain writer concurrency, request scheduling, failover, and state ownership. Replicating that service is a distributed-systems project, not a configuration-free consequence of using a local library.

The comparison should not assume that either design receives weaker durability or isolation. The workload contract must remain fixed. If a local prototype turns off persistence synchronization while the server configuration waits for durable commit, the measured difference includes a guarantee change.

Scenario C: the contended stock record

Suppose every purchase competes over one stock record. The logical invariant requires accepted decrements to observe a valid remaining count. Independent CPU resources cannot all make conflicting decisions without coordination.

The protected interval matters. If an application holds the relevant protection while making twenty dependent remote calls at an illustrative 0.5-millisecond RTT, communication contributes 10 milliseconds. Moving equivalent logic into one local or server-side operation can remove those exchanges from the protected path.

This does not establish that an embedded engine always outperforms a server. A server-side procedure can also reduce exchanges. The calculation isolates a boundary decision: where is the logic executed relative to the state it must protect? Comparing products without matching that decision can obscure the real cause of a result.

A single-writer path may be efficient when each transition is very short. It can also delay unrelated writes if the same boundary includes long work. A multiworker engine can exploit independence elsewhere while still serializing the hot record. The correct choice depends on the full transaction mix, not only the existence of contention.

Deployment and failure analysis

An embedded process crash can terminate both application and database execution, while durable recovery reconstructs accepted state under the engine’s contract. A separate application crash can leave the database service running. A database-service failure affects all connected applications unless a recovery or failover architecture exists.

A local file backup must use a supported consistent mechanism. Copying a live file without accounting for its journal or WAL can produce an invalid recovery set. A server backup likewise needs its documented protocol and required history. The deployment boundary changes the tooling, not the requirement for consistency.

Upgrades have different shapes. A library upgrade can be tied to application packaging and persistent-format compatibility. A server upgrade can be coordinated independently but must preserve client protocol and extension behavior. Neither is automatically trivial at scale.

Security also differs in scope. A local file’s operating-system permissions may be a central boundary for one application. A shared service needs authenticated remote identities and tenant authorization. Putting an HTTP endpoint in front of a file does not by itself create safe row-level access.

A decision matrix

The matrix is intentionally mechanism-oriented. Product selection follows after the workload and ownership requirements are clear. A deployment standard or team expertise can also matter, but those should be stated as constraints rather than disguised as engine properties.

Where each approach becomes awkward

An embedded design becomes awkward when the application expects transparent multi-machine coordination that the library does not provide. The surrounding service can add those capabilities, but it must implement the same hard protocols studied in earlier parts. That may be worthwhile for a specialized workload, yet it is not free.

A separate general-purpose service becomes awkward when a tiny local application pays setup and communication costs unrelated to its needs. It can also become expensive when application logic repeatedly crosses the boundary for every field access. The remedy can be batching, better queries, or colocated computation rather than replacing the entire engine.

Both can be misused by holding transactions open across user interaction, ignoring indexes, treating retries as new purchases, or failing to test restore. These are application and operational errors that no topology automatically prevents.

Follow SQLite’s WAL path through concurrent readers

Consider SQLite in its documented WAL mode on a supported local filesystem. WAL mode is a deliberate journal-mode choice; it should not be assumed for every newly created database. Rather than immediately replacing every modified database page, a writer appends page versions to the WAL. A reader establishes an end mark identifying the committed WAL history it may observe and resolves page reads against that boundary. A later writer can append newer versions without changing that reader’s view. SQLite WAL

The mechanism separates one writer’s publication from existing readers’ snapshots. It does not create several independent writers to the same database file. The writer coordination boundary remains, while readers can overlap the append path. A checkpoint moves eligible committed content back into the main database file; its safe progress depends on readers that still need older history. A long reader can therefore affect maintenance even when it does not block an ordinary short writer directly.

reader R: establish end mark 100 ---- read snapshot ---- finish
writer W:             append changes; commit at 120
reader S:                              establish end mark 120
checkpoint: copy only where reader/snapshot rules permit

The timeline shows two coherent reader views rather than a promise of identical data across readers. R must not accidentally observe half of W’s new state. S can begin after W’s commit and observe the later view. Checkpointing respects the active read boundaries instead of treating every committed page as immediately reusable history.

A read transaction that later tries to become a writer introduces another case. If its earlier snapshot is no longer eligible for a write upgrade, continuing from that stale view can be unsafe. SQLite documents busy outcomes associated with competing writers and snapshot upgrade constraints. Application recovery should end the failed transaction and repeat the complete logical operation when required; blindly retrying only the final statement can preserve a stale decision. Acquiring write intent earlier can change when contention is discovered, while also reserving the writer boundary earlier.

This is a concrete example of the distinction from Lesson 21: snapshot availability for reading does not mean arbitrary future writes remain valid against that snapshot. Retry handling belongs around the invariant-preserving transaction, with a separate operation identity where uncertain completion is possible.

Contrast the rollback-journal failure argument

SQLite’s rollback-journal architecture provides a useful alternative recovery argument. Before changing protected database pages, the engine preserves information needed to restore the earlier state under its journal protocol. Commit and recovery depend on carefully ordered file operations and the filesystem assumptions described by the engine. WAL instead changes how new page versions are recorded and later checkpointed. Both are transaction mechanisms; neither is equivalent to simply writing a serialized application object to a file. Atomic commit

The comparison should follow crash positions. If a process stops before a transaction has a valid commit boundary, recovery must avoid exposing its partial changes. If a valid durable commit exists, recovery must preserve the accepted result according to the configured durability contract. A directory entry, journal file, or database page cannot be interpreted independently of the protocol that gives it meaning.

These mechanisms also explain why the copy operation matters for backup. An ordinary file copy can observe different pieces at different instants. A supported backup procedure establishes a coherent recoverable result. Stopping all access and copying a complete required file set is a different procedure from copying only the main file while writes continue. Neither embedded deployment nor a small dataset makes the coherence obligation disappear.

PostgreSQL separates sessions while sharing database state

PostgreSQL’s server architecture gives each client a session and uses server-side execution resources while sharing storage and coordination structures. Its documented connection process uses a server process for a client connection in the conventional architecture. A pool can reuse sessions across application requests, reducing repeated connection setup and bounding the number of active database conversations. The pool does not itself supply transaction isolation. Connection handling

Within that service, independent transactions can execute concurrently while conflicting operations coordinate through the relevant transaction and structural mechanisms. MVCC allows many reads to use suitable versions. Writes still maintain indexes, produce WAL, and contend where they affect the same protected facts. Vacuum and related maintenance reclaim eligible obsolete state; old snapshots can postpone that eligibility. These are additional responsibilities beyond accepting SQL over a socket.

A server can therefore have idle connections, runnable statements, blocked statements, background I/O, and retained transactions at the same instant. Connection count is a poor substitute for useful throughput. Increasing a pool from 100 to 1,000 sessions may add waiting memory and scheduling work while leaving the stock row, WAL device, or memory bandwidth unchanged. The measurements from Lesson 42 should identify the limiting stage before changing the admission boundary.

Memory accounting illustrates the issue. In a hypothetical execution budget, 200 simultaneously active queries each have two operators using 16 MiB apiece. Their operator allocations total 200 × 2 × 16 = 6,400 MiB, or 6.25 GiB, before other process and shared memory. This is an illustrative workload calculation, not a claim that a PostgreSQL setting gives one global fixed query limit. Real operator budgeting, parallel execution, and hash-memory behavior need the relevant configuration rules.

Compare the same purchase operation

Suppose one item remains and two clients each request it. The business operation needs a single accepted decrement. A portable fragment expresses the guarded mutation:

UPDATE inventory
SET remaining = remaining - 1
WHERE product_id = 73 AND remaining > 0;

The application checks whether the statement changed one row. This fragment expresses an important guard, but it is not the entire purchase protocol. Creating the order, storing the operation result, and recording any publication obligation belong in the appropriate transaction. Authorization and price validation also belong to the stated application contract. The transaction cannot be considered successful merely because an optimistic client animation has run.

For example, a zero-row result can mean exhausted stock or an absent product under this fragment. The surrounding operation must distinguish those outcomes if the user interface or audit contract requires them; the update alone intentionally supplies only the guarded mutation result.

In a SQLite-hosted service, the writer boundary orders competing write activity within the relevant database. In PostgreSQL, the conflicting row updates coordinate through its transaction machinery while independent records can continue on other execution resources. Both must handle the loser and uncertain replies according to the application protocol. A timeout after commit is possible in either architecture.

An important distinction appears when the application first reads stock and later decides how to update. A snapshot-based decision may require a guarded write, locking, serializable validation, or a transaction retry, depending on the operation and engine. Replacing that logic with an unguarded assignment derived from an old read is not an equivalent optimization. The exact locking error or retry API differs; the invariant does not.

Quantify writer occupancy without inventing a benchmark

Assume a bounded writer path takes two milliseconds of exclusive occupancy per transaction, including the waits that actually prevent another writer from progressing. Its idealized ceiling is 500 transactions/s. At 100 transactions/s, the demanded occupancy is 20%. If a long reporting action unnecessarily holds the same exclusive boundary for 200 ms, it consumes the occupancy of 100 ordinary writes. Average service time and its variance both affect the waiting experience.

These are hypothetical resource numbers. They do not estimate SQLite or PostgreSQL throughput. Their purpose is to expose which interval is exclusive. A persistence stage might pipeline with later work under an engine’s protocol, so adding every stage’s duration into one serial interval can also be wrong. The comparison must establish actual ownership and overlap before calculating a ceiling.

If independent tenants use distinct database ownership domains, they may gain independent writer capacity. They also gain separate connection, backup, placement, and migration obligations. A transaction crossing those domains is no longer automatically one local transaction. Partitioning is therefore an application and failure-model change, even when each individual partition uses the same embedded library.

Maintenance changes the apparent winner

Take a hypothetical append rate of 4 MB/s during a ten-minute long-reader interval. That produces 2,400 MB of appended bytes. This figure is not automatically the resulting SQLite WAL file size: checkpoint eligibility, reuse, repeated page updates, and implementation behavior affect that result. It is the amount of history-producing activity that the maintenance design must examine. In PostgreSQL, old snapshots can similarly retain obsolete versions, but the physical representation and cleanup mechanisms are different.

The fair operational comparison includes a sustained period, not only a warm ten-second run. Record checkpoint and vacuum behavior, retained history, device writes, restart time, and the latency experienced while background work runs. A design that performs beautifully until the first maintenance boundary has not established its steady-state envelope.

The table describes architectural boundaries, not equivalent feature checkmarks. A single PostgreSQL primary is not a complete disaster-recovery plan, and three application processes opening one SQLite file are not three independent replicas. The desired failure contract determines which surrounding components must exist.

Choose the smallest ownership model that meets the contract

The offline editor benefits from local authority because it must work without a server. The shared SaaS benefits from a centrally governed service boundary because many applications need current shared state. The hot-stock workload benefits from short protected transitions in either deployment. These conclusions follow from the requirements and paths described above; they are architectural analysis rather than vendor claims about all customers.

For all three, the decision remains reviewable only when configuration is explicit: journal or persistence mode, isolation, application transaction scope, acknowledgment boundary, storage environment, and recovery procedure. The word embedded describes placement. The word server describes a boundary. Neither word, by itself, specifies every correctness or performance property.

Architectural synthesis

The important contrast is the location of authority and the responsibilities around it. SQLite demonstrates how a complete local database can live inside an application. PostgreSQL demonstrates a server-oriented boundary with broad query and transaction facilities. Their documented mechanisms should be evaluated against the same invariant, workload, durability, and failure model.

The next case study separates another pair of layers: an embedded storage engine and a distributed database. That distinction explains why adopting an efficient local key-value library does not itself create replication, routing, or repair.

Sources and further reading

• SQLite architecture
• SQLite write-ahead logging
• PostgreSQL 18 connection handling

Course index · Lesson plan