PostgreSQL

The default choice at the middle of this spoke’s embedded↔distributed axis, and until now a page the wiki did not have. Written from the project’s own documentation (T1, first-party): the architectural fundamentals and the MVCC introduction.

Process model

Client/server, with a supervisor process and one backend process per connection:

“The PostgreSQL server can handle multiple concurrent connections from clients. To achieve this it starts (“forks”) a new process for each connection. From that point on, the client and the new server process communicate without intervention by the original postgres process.”

The supervisor stays up waiting for connections; “client and associated server processes come and go.” This is the fact behind every piece of Postgres operational advice this spoke will meet later — a connection is an OS process, not a thread, which is why connection pooling is mandatory at scale and why the per-connection memory cost is a real capacity limit. Compare turso at the embedded end, where there is no server process at all, and vitess at the distributed end, where the process you connect to is a proxy.

MVCC

MVCC is the concurrency design, stated in the docs as a snapshot rule:

“Each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.”

And the consequence readers actually care about:

“In MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.”

The strictest isolation level is delivered through Serializable Snapshot Isolation (SSI), which the docs describe as an innovation of the project’s — serializability without the lock contention of two-phase locking. Table- and row-level locks remain available for applications that would rather manage conflict explicitly. The stated rationale: MVCC “minimizes lock contention in order to allow for reasonable performance in multiuser environments,” and “proper use of MVCC will generally provide better performance than locks.”

Position in the spoke

OLTP on a single node is the case every other page here is defined against — database-sharding is what you do when one Postgres or MySQL stops being enough, local-first-architecture is what you do when the round trip to it is the problem, and planetscale sells the escape from it. Recording the base case makes those readable as departures.

Not covered by these two pages, and open: WAL and crash recovery, the query planner, replication, and what SSI costs under contention.