PostgreSQL Foundations
PostgreSQL is an open-source, standards-compliant relational database that pairs a strict SQL engine with an extensible type and index system built to be extended rather than replaced.
//
Search across all documentation pages
PostgreSQL is an open-source, standards-compliant relational database that pairs a strict SQL engine with an extensible type and index system built to be extended rather than replaced.
This page lays the conceptual groundwork for the rest of the PostgreSQL Core & SQL group: how a cluster, database, schema, and table relate to each other, how a single query actually gets executed under the hood, and where PostgreSQL's design choices differ from other relational systems.
A running PostgreSQL server is called a cluster, and a single cluster can host many independent databases even though the word "cluster" has nothing to do with multiple machines.
Each database is a separate namespace for schemas, and a client connection attaches to exactly one database for its entire session.
Inside a database, a schema groups related tables, views, functions, and other objects the way folders group files.
PostgreSQL ships a default schema named public, but production systems typically define their own schemas to separate applications, tenants, or logical modules.
Every object you create, from a table to a custom type, is itself a row in a system catalog table, which is why PostgreSQL can describe its own structure using ordinary SQL.
Think of the cluster as a filing cabinet, each database as a drawer, each schema as a folder inside that drawer, and each table as a labeled binder inside that folder.
PostgreSQL also implements the SQL standard closely, which means most SELECT, JOIN, and constraint syntax will look familiar to anyone coming from another relational database.
Where PostgreSQL diverges from the standard, it usually adds capability rather than replacing it, such as native array and JSON types alongside standard scalar types.
A PostgreSQL cluster runs as a supervising process called the postmaster, which listens for new connections and forks a dedicated backend process for each one.
That one-process-per-connection model means every session gets full isolation from other sessions, but it also means connection count directly consumes memory and CPU scheduling overhead.
When a backend receives a query, PostgreSQL parses the SQL text into a parse tree, rewrites it according to any views or rules, and hands it to the planner.
The planner is a cost-based optimizer that estimates the cheapest way to satisfy the query using table and column statistics gathered by ANALYZE.
The chosen plan is handed to the executor, which pulls rows through a tree of operators such as sequential scans, index scans, joins, and sorts.
Underneath all of this, PostgreSQL uses multiversion concurrency control, or MVCC, so readers never block writers and writers never block readers.
MVCC works by keeping multiple versions of a row and letting each transaction see a consistent snapshot based on when it started.
Every change is first recorded in the write-ahead log, or WAL, before the corresponding data page is modified in memory, which is what makes crash recovery possible.
Old row versions that are no longer visible to any transaction become dead tuples, and the autovacuum process reclaims that space in the background.
client -> postmaster (forks) -> backend
backend: parse -> rewrite -> plan -> execute
execute: WAL record -> shared buffers -> (checkpoint) -> disk
This pipeline, from parse to WAL to disk, is why PostgreSQL can guarantee durability while still batching physical writes for performance.
PostgreSQL's catalog-driven design is also what makes it extensible without forking the core engine, since extensions like pgvector or PostGIS simply register new types, operators, and index access methods in the same catalog tables the core engine uses.
As of PostgreSQL 18.4, the project follows an annual major-version release cycle, with 18 as the current stable line and 17 still receiving maintenance updates.
That predictable cadence matters for planning upgrades, since crossing a major version typically calls for a logical dump/restore or logical replication cutover rather than an in-place binary swap.
A single PostgreSQL cluster scales vertically quite far, but eventually workloads need to spread reads, writes, or both across more than one node.
Streaming replication, connection pooling, and purpose-built extensions each address a different part of that scaling problem, and reaching for the wrong one for the workload is a common architecture mistake.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Vertical scaling (bigger primary) | Simplest to operate, no distributed complexity | Hits hardware ceilings, single point of failure | Most workloads before read/write volume outgrows one machine |
| Read replicas (streaming replication) | Offloads read traffic, cheap to add | Replication lag, no extra write capacity | Read-heavy apps and reporting |
| Connection pooling (PgBouncer) | Cuts backend/connection overhead without app changes | Adds a hop, transaction-mode pooling breaks some session features | High-concurrency apps with many short-lived connections |
| Managed PostgreSQL (RDS, Cloud SQL) | Offloads patching, backup, and failover to the provider | Less control over extensions and kernel tuning | Teams without dedicated database operations capacity |
None of these approaches replace the others, and most production systems layer several of them together as load grows.
shared_buffers require a full restart.A cluster is the running server process and its data directory, while a database is one namespace within that cluster that commonly shares the cluster with several sibling databases.
It means a long-running report query never blocks a concurrent write, a write never has to wait for readers to finish, and each transaction simply sees a consistent snapshot of the data instead of partial changes from others.
Every change is durably recorded in the WAL before the in-memory page is considered committed, which is what lets PostgreSQL replay the WAL from the last checkpoint and rebuild any lost in-memory state after a crash.
A reload re-reads postgresql.conf for settings that can change live, while a restart recycles the postmaster and every backend, which is required for settings like shared_buffers that are fixed at startup.
Not to get started, but understanding that the planner reorders and rewrites your SQL explains most "why is this slow" surprises later on.
Because types, operators, and index access methods are just rows in system catalogs, extensions can register new ones without patching the core engine's source code.
Dead tuples accumulate, tables bloat, and in the worst case the cluster approaches transaction ID wraparound, which forces PostgreSQL into a protective, disruptive cleanup mode.
Yes; 18 is the current stable major version, and 17 remains on the supported maintenance line for teams not yet ready to upgrade.
An extension can add new data types, operators, and index access methods that participate in the planner and executor exactly like built-in ones, not just add callable functions.
Yes, using separate databases or separate schemas, though each isolation boundary has different implications for backup scope and connection accounting.
Each connection is a full backend process consuming memory and competing for CPU scheduling, so hundreds of idle connections are cheap but hundreds of active ones are not.
Because its durability model, WAL-before-data-page writes plus replay on crash, has been stable and battle-tested for decades, with new features added around that core rather than replacing it.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 15, 2026