Why PostgreSQL

PostgreSQL is a row-oriented, general-purpose database built for OLTP: lots of small, concurrent transactions that read and write individual rows safely. That's a different job than ClickHouse, which is built for OLAP — scanning and aggregating billions of rows at once. Knowing which category a workload falls into is the first design decision you make, before any schema or index.

OLTP vs OLAP

OLTP (Online Transaction Processing) means many short transactions touching a handful of rows each — placing an order, updating a user's profile, checking an inventory count. The access pattern is narrow and deep: WHERE id = 42, update three columns, commit. OLAP (Online Analytical Processing) means the opposite — few queries, but each one scans millions or billions of rows to compute an aggregate. Postgres is tuned for the first pattern: row-at-a-time storage, indexes for fast point lookups, and a transaction model that lets thousands of clients write concurrently without corrupting each other's data.

A brief history

Postgres traces back to the POSTGRES project at UC Berkeley in the 1980s, led by Michael Stonebraker as a successor to his earlier Ingres database. In the mid-90s it gained a SQL interface and was renamed PostgreSQL. Decades of continuous open-source development later, it's one of the most feature-complete relational databases that exists — full SQL, extensibility (custom types, functions, extensions), and a reputation for taking correctness seriously over cutting corners for speed.

ACID, briefly

Postgres is an ACID database:

• Atomicity — a transaction's changes all happen, or none do.
• Consistency — a transaction moves the database from one valid state to another, respecting constraints.
• Isolation — concurrent transactions don't see each other's uncommitted changes (with tunable strength — see Transactions & Isolation).
• Durability — once committed, a transaction's changes survive a crash (this is what the Write-Ahead Log exists for).

When Postgres is the wrong choice

• Huge analytical scans. Aggregating billions of rows across a handful of columns is ClickHouse's job, not Postgres's — Postgres stores whole rows together on disk, so a query touching two columns out of fifty still reads all fifty.
• Pure key-value caching. If you need sub-millisecond lookups of ephemeral data with no durability requirement, Redis is a better fit — Postgres's durability and MVCC machinery is overhead you don't need.
• Unstructured document dumping ground. Postgres can store JSON (see JSON & Arrays), but if literally everything is schema-less documents with no relational structure, a document database may fit the access patterns better.

None of this means Postgres can't do some of these things — it can, often well enough — but knowing the shape it was designed for tells you when you're fighting the tool instead of using it.

This module starts with Docker & Setup — getting a real Postgres instance running locally before any of the internals below mean anything concrete.