Architecture
Client/server model, processes, clusters, databases, schemas
With Postgres running from Docker & Setup, it's worth being precise about what's actually listening on the other end of that connection before writing any SQL.
Client / server model
Postgres is a client/server database: a single long-running server process (the postmaster) listens on port 5432 and accepts connections. Critically, the postmaster doesn't handle queries itself — for every new connection, it forks a dedicated backend process to serve that one client for the lifetime of the connection.
This is different from thread-per-connection designs. One process per connection is simple and gives strong isolation (one backend crashing doesn't take down the server), but process creation is comparatively expensive — which is why a high connection count becomes a real cost, not just a number. That tradeoff is exactly why connection poolers like PgBouncer exist; see Scaling & Pooling.
Alongside backend processes, the postmaster also starts a handful of
permanent background processes: the background writer (flushes
dirty pages from shared buffers), checkpointer (see
Write-Ahead Log), autovacuum launcher (see
VACUUM & Autovacuum), and WAL writer. You can
see all of these with ps aux | grep postgres inside the container.
Database cluster, databases, schemas, tables
The term database cluster in Postgres does not mean a group of
machines — it means the entire collection of databases managed by one
running Postgres server instance, all living under one data directory
on disk (/var/lib/postgresql/data in this repo's container).
"Cluster" here is Postgres-specific terminology and trips up anyone
coming from a distributed-systems background. One docker compose up gives you one cluster, which can contain many databases. It has
nothing to do with multiple machines — that's what
Replication and
Scaling & Pooling are about.
Inside a cluster:
- A database is an isolated namespace — connections attach to exactly one database at a time, and by default databases can't query across each other.
- A schema is a namespace inside a database — a way to group
tables without needing separate databases. Every database starts
with a
publicschema, and objects created without specifying a schema go there by default. - A table lives inside a schema, addressed as
schema_name.table_name(or justtable_nameif it's in yoursearch_path, which defaults to includingpublic).
CREATE SCHEMA sales;
CREATE TABLE sales.orders (id serial PRIMARY KEY);
-- fully qualified
SELECT * FROM sales.orders;OLTP, again
This process-per-connection, cluster/database/schema/table structure exists to serve the OLTP workload Postgres is built for: many concurrent clients, each doing small, isolated units of work against a shared, structured dataset.
Next: SQL Basics — creating that structure and putting rows into it.