Choose who owns the database process

Two applications need products, orders, SQL, and transactions. One is an inventory editor on a single workstation. The other is a shared service used by independent application instances. Their data model may be similar, but the ownership boundary differs.

The computation-boundary lesson distinguished an embedded library from a separate server. SQLite and PostgreSQL provide concrete examples of those organizations.

Follow the calls, not a feature score

Local editor with SQLite
Application process → SQLite library → database files

Shared service with PostgreSQL
Application processes → database connections → PostgreSQL server → database files

Both paths can use transactions. They place execution and operational ownership at different boundaries.

An embedded engine can avoid a separate database service and the communication between application and server. The application still needs to manage the files, lifecycle, backups, and upgrade compatibility. A shared server centralizes database access and permissions but introduces connection management and a separately operated service.

This is not an assertion that SQLite cannot support an application server. A server can itself use SQLite internally. The distinction is whether clients coordinate through that server or attempt unsupported access patterns to the same files.

Ask where writes must coordinate

SQLite’s documented WAL mode permits readers alongside a writer, but only one writer at a time for a database. PostgreSQL supports concurrent transactions with its documented locking and isolation behavior. Neither fact says that two operations can safely update one stock item without coordination.

The ticket example remains relevant to both: define the stock rule, use the engine’s supported transaction behavior, and handle conflicts or retries correctly. More concurrent workers cannot make two buyers legitimately own the same last item.

For a small local editor, a single writer may be entirely adequate. For a shared service, evaluate write duration, conflicting records, access boundaries, recovery needs, and the expected workload. Do not infer throughput from the product name alone.

Move the application, then revisit the assumptions

The local editor becomes a service with several application instances. Should every instance simply open the same SQLite file on an arbitrary network filesystem?

Check the documented coordination boundary

No. That changes the storage and coordination assumptions. SQLite’s WAL design has specific same-host/shared-memory requirements and is not a generic network-filesystem cluster protocol. A single owning service, supported storage arrangement, or a server database may fit the new requirement. Evaluate the actual deployment rather than carrying over a local-file assumption.

Choosing an engine organization is choosing responsibilities. The next comparison changes the workload itself, showing why a point purchase and a large report favor different physical work.

Sources: SQLite serverless design, SQLite WAL behavior, and PostgreSQL architecture. The scenarios are constructed examples, not product benchmarks.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter