PostgreSQL Architecture and Philosophy: Process Model, Catalogs, and Extensibility

Learning outcomes

Imagine that the fictional ServiceHub field-service application is moving from a single-machine prototype into a shared PostgreSQL deployment. A dispatcher asks a simple question: “Which database process owns these files, and which database am I actually connected to?” A developer replies, “the PostgreSQL cluster,” while an infrastructure engineer assumes “cluster” means several high-availability nodes. Both are using familiar words, but they are describing different things. That ambiguity becomes dangerous once you configure backups, roles, extensions, or failover.

This first lesson builds the vocabulary that later chapters will depend on. PostgreSQL is a client/server relational database management system, but its process model, catalog-driven design, database/schema boundaries, and extension mechanism are distinct enough that transferring a MySQL or SQLite mental model without adjustment leads to subtle mistakes.

1. Start with the overloaded word “cluster”

In PostgreSQL documentation, a database cluster is the collection of databases and shared metadata managed by one server instance and normally rooted in one data directory. The utility initdb creates that cluster. This definition does not imply high availability, multiple hosts, automatic failover, or replication. A completely standalone laptop installation can contain one PostgreSQL database cluster.

An HA cluster, by contrast, is an operational topology in which multiple PostgreSQL server instances or nodes cooperate with replication, routing, monitoring, and failover tooling. Later chapters build that topology explicitly. For now, keep the two meanings separate: “database cluster” is a PostgreSQL storage/instance boundary; “HA cluster” is a deployment architecture.

Client application / psql / driver          |          | PostgreSQL protocol (often through libpq or a driver)          v+---------------- one PostgreSQL server instance ----------------+| supervising postgres process                                   ||      |                                                         ||      +--> backend process for connection A                     ||      +--> backend process for connection B                     ||      +--> auxiliary/background processes                       ||                                                                || shared memory + WAL + system catalogs + relation storage       |+------------------------------|---------------------------------+                               v                  one database cluster / data directory                  + database: postgres                  + database: servicehub_lab                  + database: template1                  + cluster-wide metadata

The diagram is intentionally simplified. It is a responsibility map, not a promise that every SQL statement touches every component. PostgreSQL also uses shared memory and several specialized background processes, and recovery or replication topologies add more process types. Chapter 02 will make those details observable.

2. One server, many databases, many schemas

A running PostgreSQL server instance connects to one database cluster. Inside that cluster are multiple databases. A normal client session connects to exactly one database at a time. Inside a database are schemas, which act as namespaces for objects such as tables, views, functions, and types.

This is a meaningful difference from MySQL terminology. In MySQL, DATABASE and SCHEMA are effectively synonyms for the same namespace. In PostgreSQL, a database is a stronger isolation boundary than a schema. A three-part name such as other_database.public.orders is not a built-in cross-database reference mechanism.

SELECT current_database() AS database_name,       current_schema()   AS first_schema_in_path,       current_user       AS effective_role,       session_user       AS login_role;SHOW search_path;SELECT datnameFROM pg_catalog.pg_databaseWHERE datallowconnORDER BY datname;SELECT nspname AS schema_nameFROM pg_catalog.pg_namespaceORDER BY nspname;

On a new local installation you will commonly see databases such as postgres, template0, and template1. Exact names beyond those defaults depend on how the package or container was initialized. The schema list will include PostgreSQL-owned schemas such as pg_catalog and may include public. Do not turn any one installation’s output into a universal inventory.

3. The server process and per-connection backends

The executable named postgres runs the database server. One supervising server process accepts connection attempts and creates a separate backend process for each accepted client connection. That backend carries session state and executes the client’s statements while cooperating through PostgreSQL’s shared memory and storage machinery.

This process-per-connection model matters operationally. Ten open sessions are not merely ten entries in a socket table: they correspond to server-side backends and consume resources. A connection pool can therefore affect memory pressure, process count, transaction lifetime, and failure behavior. Those consequences are deferred until the performance chapter; here, the goal is simply to prove that a session has a server-side process identity.

SELECT pg_backend_pid() AS my_backend_pid,       current_database() AS database_name,       current_user AS effective_role,       application_name,       client_addr,       backend_type,       stateFROM pg_catalog.pg_stat_activityWHERE pid = pg_backend_pid();

The pid returned by pg_backend_pid() is useful diagnostic evidence for the current session. It is not an application identifier and it is not stable across reconnects. When the session ends, that backend exits; a later connection receives its own process.

For an optional host-level confirmation, a Linux or macOS operator can inspect PostgreSQL processes with the operating system’s process tools. On Windows, PowerShell can use Get-Process postgres. Host output varies by packaging and permissions, so SQL views remain the portable baseline for this course.

4. Auxiliary processes do work that no client “owns”

Not all PostgreSQL processes correspond to a user connection. A running instance normally has auxiliary processes that perform background responsibilities such as checkpoint coordination, background writing, WAL writing, autovacuum coordination, and other maintenance. Recovery and replication can add additional process types. The precise list changes with server state and major version, so memorize responsibilities rather than a frozen process list.

SELECT backend_type, count(*) AS process_countFROM pg_catalog.pg_stat_activityGROUP BY backend_typeORDER BY backend_type;

This view is intentionally a better teaching tool than a screenshot of ps. It lets PostgreSQL tell you how it classifies observed backends. Some auxiliary processes are exposed through statistics views other than pg_stat_activity, and some details require additional privileges. Later lessons use the dedicated statistics views appropriate to WAL, replication, I/O, vacuum, and checkpoints.

5. System catalogs: PostgreSQL describes itself with relations

PostgreSQL stores much of its metadata in system catalogs. These are relations in the pg_catalog schema that describe databases, roles, tables, columns, indexes, types, functions, dependencies, extensions, and many other objects. This catalog-driven architecture is one reason PostgreSQL can support rich introspection and extensibility.

Catalogs are not all scoped identically. For example, pg_database contains cluster-wide database metadata, while a table such as pg_class describes relations visible within the database to which the session is connected. The SQL-standard information_schema provides another metadata interface, but it intentionally exposes a more portable and privilege-filtered view of the system.

SELECT oid, datname, datistemplate, datallowconnFROM pg_catalog.pg_databaseORDER BY datname;SELECT c.oid,       n.nspname AS schema_name,       c.relname AS relation_name,       c.relkindFROM pg_catalog.pg_class AS cJOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespaceWHERE n.nspname NOT IN ('pg_catalog', 'information_schema')ORDER BY n.nspname, c.relnameLIMIT 20;

The numeric oid values are internal object identifiers. They are useful for catalog joins and diagnostics, but application schemas should not casually treat them as durable business keys. PostgreSQL can rebuild or replace objects during dump/restore, upgrades, or DDL operations.

6. Settings are observable too

Configuration in PostgreSQL is represented through run-time parameters, historically called Grand Unified Configuration (GUC) parameters. Instead of guessing where a value came from, query pg_settings. It exposes the current setting, source, unit, context, and whether a restart is pending for many parameters.

SELECT version();SHOW server_version;SHOW server_version_num;SELECT name, setting, unit, context, source, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('data_directory', 'port', 'max_connections',               'shared_buffers', 'data_checksums')ORDER BY name;

Do not confuse psql --version with SHOW server_version. The first describes the client executable installed on the machine where you ran the command. The second is returned by the server that accepted your connection. Those versions can differ, which becomes important for upgrades and administrative tools.

7. Extensibility is deliberate, not magical

PostgreSQL can be extended with new functions, data types, operators, index operator classes, procedural languages, foreign data wrappers, and more. An extension packages related database objects so they can be installed and versioned as a unit with CREATE EXTENSION. PostgreSQL ships documentation for additional supplied modules, and a broader ecosystem provides third-party extensions.

There are two separate questions: “Does PostgreSQL know about this extension?” and “Is this extension installed in this database?” The view pg_available_extensions answers the first for extension packages visible to the server; the catalog pg_extension answers the second.

SELECT name, default_version, installed_version, commentFROM pg_catalog.pg_available_extensionsWHERE name IN ('plpgsql', 'pg_trgm', 'pgcrypto')ORDER BY name;SELECT extname, extversionFROM pg_catalog.pg_extensionORDER BY extname;

Availability is not approval. An extension can introduce native code, new privileges, configuration dependencies, upgrade constraints, or backup/recovery implications. Even “trusted” extensions have a specific PostgreSQL security meaning; the label is not a blanket statement that every use is harmless. Chapter 19 develops the extension lifecycle in depth.

8. Compare the three engine boundaries without ranking them

A local single-user application may benefit from SQLite’s embedded simplicity. A shared service may need a network server. PostgreSQL may be selected for capabilities such as advanced SQL, extensibility, specific data types, transactional behavior, or ecosystem fit. None of those statements means “always choose PostgreSQL.” Architecture follows workload, operational responsibility, and team capability.

9. A deliberately wrong approach: cross-database qualification

A learner coming from another system may assume that because one PostgreSQL database cluster contains several databases, a session can simply qualify an object with a database name:

-- Intentionally wrong in ordinary PostgreSQL SQL:SELECT *FROM other_database.public.work_orders;

PostgreSQL does not implement ordinary cross-database references this way. A representative server error reports that cross-database references are not implemented. The repair is to connect to the target database, model closely related objects inside schemas of the same database where appropriate, or deliberately introduce a mechanism such as a foreign data wrapper when cross-database access is justified. Later chapters teach those tools rather than hiding the boundary.

10. Hands-on lab: prove the architecture from two sessions

This lab is read-only except for opening client connections. If PostgreSQL is not installed yet, read the workflow and perform it after Lesson 3. Use a disposable local server, not a production endpoint.

Step 1 — verify client and server separately

psql --version

SELECT version();SELECT current_database(), current_user, session_user, pg_backend_pid();

Record both version strings. A mismatch is not automatically a problem; it is evidence that “client version” and “server version” are distinct compatibility dimensions.

Step 2 — open a second psql session

Run the identity query in both sessions. Each should have its own pg_backend_pid(). From either session, observe both rows:

SELECT pid, usename, datname, application_name, state, backend_typeFROM pg_catalog.pg_stat_activityWHERE backend_type = 'client backend'  AND datname = current_database()ORDER BY pid;

Step 3 — inspect cluster/database/schema evidence

SELECT datname, datistemplate, datallowconnFROM pg_catalog.pg_databaseORDER BY datname;SELECT current_database(), current_schema();SHOW search_path;

Step 4 — inspect extension and setting evidence

SELECT extname, extversion FROM pg_catalog.pg_extension ORDER BY extname;SELECT name, setting, context, source, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('port','data_directory','max_connections','data_checksums')ORDER BY name;

Verification checklist

• You can explain why two sessions have two backend PIDs.
• You can name the current database without confusing it with the whole cluster.
• You can identify at least one schema and explain what search_path does at a high level.
• You can distinguish installed extensions from available packages.
• You can show one setting together with its source/context instead of quoting a guessed default.

11. Summary and bridge to release planning

PostgreSQL is a separately operated client/server DBMS. One server instance manages one PostgreSQL database cluster, that cluster contains multiple databases, and each database contains schemas. Accepted client connections receive separate backend processes, while auxiliary processes perform work outside any single session. System catalogs and statistics views make these boundaries observable, and the extension framework deliberately adds packaged capabilities to a database.

The next lesson adds time to this architecture. You will distinguish major releases from minor maintenance releases, understand the five-year support policy, and learn why “upgrade PostgreSQL” can mean anything from a compatible in-place binary update to a planned migration of the whole database cluster.

Authoritative references

• PostgreSQL 18 Documentation — Architectural Fundamentals
• postgres — PostgreSQL database server
• System Catalogs
• Monitoring Database Activity
• pg_available_extensions
• Packaging Related Objects into an Extension