Introduction to PostgreSQL: Architecture and Installation

PostgreSQL is an open-source, object-relational database known for standards compliance, extensibility, and reliability under demanding workloads. It has grown from an academic project into the database of choice for everything from small applications to systems handling enormous transaction volumes, precisely because it pairs a rock-solid relational core with features — rich data types, full-text search, JSON, extensions — that usually require add-on products elsewhere.

Understanding PostgreSQL begins with its process architecture. A central coordinating process accepts connections and spawns a dedicated backend process for each client. Shared memory holds the data and structures every backend uses, and a set of background processes handles writing, cleanup, and durability. Grasping this shape early makes later topics — concurrency, vacuuming, tuning — far easier to reason about.

This first lesson orients you: what PostgreSQL is, how its processes and memory fit together, and how to get a working installation. Everything else in this advanced course — indexing, query planning, partitioning, replication — builds on this mental model of how the server is organized and where your data actually lives.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.