skip to content

Engine Anatomy

A PostgreSQL server is a postmaster forking one process per connection around shared buffers, over a data directory of 8 kB pages. Interviewers test whether you reason from these internals.

part ofPostgreSQLread it →
on this pageshow

explore

questions

22

In PostgreSQL, how do a cluster, a database and a schema relate, and which objects belong to the whole cluster rather than one database?

level: juniorimportance: must knowfreq 72%

answer

  1. one server, many databases
  2. a connection picks one database
  3. namespaces inside a database
  4. roles sit above databases
  5. pg_global holds the shared catalogs

basics

~20 s

A PostgreSQL cluster is one server instance and data directory holding many databases; each database holds schemas, which hold tables and functions. Roles, databases and tablespaces are cluster-wide, and every connection is bound to exactly one database.

solid answer

~40 s

A **cluster** is what one running server manages: one data directory, one postmaster, one port, one WAL stream. Inside it are **databases**, created by `CREATE DATABASE` and isolated from each other: each has its own system catalogs, and a connection names one database and stays in it for life (psql's `\c` opens a new connection). Inside a database are **schemas**, which are only namespaces, so a single query can join `raw.samples` to `curated.samples` freely. A few objects live above every database, in shared catalogs in the `pg_global` tablespace: **roles**, the list of **databases**, and **tablespaces**. Schemas, tables, functions and extensions are per database.

code

sql · 9 lines
sql
-- psql session connected to database genomics_lab
SELECT datname FROM pg_database;     -- every database in the cluster
SELECT rolname FROM pg_roles;        -- every role in the cluster
SELECT nspname FROM pg_namespace;    -- schemas of genomics_lab only

-- a join across two schemas of the same database is ordinary SQL
SELECT r.sample_id, c.quality_score
FROM raw.samples AS r
JOIN curated.samples AS c ON c.sample_id = r.sample_id;

go deeper

for a junior

Recall the order cluster, database, schema, table, and that a connection names one database. Be able to say that roles are shared by every database.

for a middle

Explain why schemas can be joined in one query but databases cannot, and name the three cluster-wide objects and where they are stored.

for a senior

Show you can choose between databases, schemas and separate clusters for tenants or teams, citing what databases still share: WAL, buffers, connections, roles and backups.

for a principal

Frame the choice as an isolation ladder with costs at each rung, including restore granularity and upgrade scheduling, and say which business requirement forces a separate cluster.

## The three levels PostgreSQL organises everything in a strict hierarchy: **cluster > database > schema > object**. A **cluster** (the manual also says *database cluster* or instance) is the collection of databases managed by one running server. It has one data directory (`PGDATA`), one postmaster process, one listening port and one write-ahead log. Creating a cluster leaves you with three databases: `postgres` (the default one to connect to), `template1` and `template0` (the templates every later `CREATE DATABASE` copies). | Level | What it is | Created with | Stored as | |---|---|---|---| | Cluster | one server instance and its data directory | `initdb` | the `PGDATA` directory | | Database | an isolated set of schemas with its own system catalogs | `CREATE DATABASE` | a directory named after the database OID, under `base/` or a tablespace | | Schema | a namespace inside one database | `CREATE SCHEMA` | a row in that database's `pg_namespace` | | Table, view, function, type | the objects you query | `CREATE TABLE` and friends | catalog rows plus, for tables and indexes, data files | ## A connection is bound to one database A client's connection request must name a database, and the backend process that serves it reads that database's catalogs for its whole life. Consequences that interviewers probe: - **No statement switches database.** There is no equivalent of a `USE` command. psql's `\c climate_lab` *establishes a new connection* and closes the previous one once the new one succeeds. - **Cross-schema joins are ordinary joins.** `raw.samples` and `curated.samples` are two names in one catalog, so the planner joins them like any two tables, inside one transaction and one snapshot. - **Cross-database names fail.** A three-part name such as `climate_lab.public.stations`, used from another database, is rejected with `cross-database references are not implemented` (SQLSTATE `0A000`). ## Cluster-wide objects A small set of objects is defined once for the whole cluster. They live in **shared catalogs** stored in the `pg_global` tablespace, which is the `PGDATA/global` directory, and every database sees the same rows: 1. **Roles** (`pg_authid`, with memberships in `pg_auth_members`). A role created while connected to `genomics_lab` exists in `climate_lab` too; whether it may connect there is a matter of the CONNECT privilege and `pg_hba.conf`. 2. **Databases** themselves (`pg_database`), which is why `SELECT datname FROM pg_database` lists every database from wherever you run it. 3. **Tablespaces** (`pg_tablespace`). Any database can place tables in any tablespace, given CREATE privilege on it. A few other shared catalogs exist (per-database and per-role settings in `pg_db_role_setting`, shared dependencies, logical-replication subscriptions), but roles, databases and tablespaces are the answer an interviewer wants. ## Per-database objects Everything else belongs to exactly one database: schemas, tables, indexes, sequences, views, functions, types, operators, and **extensions**, because `CREATE EXTENSION` installs its objects into the current database only. Each database also carries its own copy of the non-shared system catalogs, its own `pg_catalog` and `information_schema` schemas, and the `public` schema it inherited from `template1`. How catalogs work in general is the system-catalogs leaf's subject, and who may create objects in `public` is the security-roles leaf's. ## Database or schema? The research cluster Picture a university cluster shared by a genomics lab, a climate lab and a neuroscience lab. - **Separate databases** suit labs that must not see each other: a lab's owner can be given its own database, `DROP DATABASE` removes one lab cleanly, `pg_dump` dumps one lab at a time, and a role without CONNECT on another lab's database cannot even log in to it. - **Schemas in one database** suit labs that share reference data and must join against it: a `ref` schema beside `genomics` and `climate` keeps joins, foreign keys and transactions working across them. - **Separate clusters** are needed when isolation must go deeper. Databases in one cluster still share the WAL, `shared_buffers`, `max_connections`, the role list and every restart; a physical backup and point-in-time recovery cover the whole cluster, so you cannot rewind one lab's database alone. ## Names that mean something else elsewhere The SQL standard calls a database a *catalog*; the PostgreSQL manual notes there is no practical difference. In MySQL, `CREATE SCHEMA` is a synonym for `CREATE DATABASE`, so people arriving from it often say "schema" for what PostgreSQL calls a database. In PostgreSQL they are two different levels, and only the schema level can be crossed inside one query.

  • If you create a role while connected to genomics_lab, can it log in to climate_lab?
    The role exists everywhere, because roles live in the shared `pg_authid` catalog. Logging in to `climate_lab` still needs the CONNECT privilege on that database (granted to PUBLIC by default) and a matching `pg_hba.conf` line. Privileges on tables, schemas and functions are per database, so the role starts with no access to `climate_lab`'s objects.
  • Is an extension installed for the whole cluster or per database?
    Per database. `CREATE EXTENSION` puts the extension's functions, types and tables into the current database's schemas and catalogs, so every database that needs it runs the command itself. Installing it into `template1` makes databases created afterwards inherit it, but databases that already exist are unaffected.

A cluster is an apartment building with one front desk and one residents' register (the roles). Each database is a locked apartment: you enter one at a time and must leave to visit another. Schemas are rooms inside an apartment, and you walk between rooms freely.

  • A schema and a database are the same thing in PostgreSQL
  • Roles must be created separately inside each database
  • psql's \c switches the current connection to another database
  • Two databases on the same server can be joined in one query
  • A tablespace belongs to the database that created it
open as a page

In PostgreSQL, what do initdb, pg_ctl and the postgres server program each do across a cluster's life, from creation to shutdown?

level: juniorimportance: must knowfreq 55%

basics

~20 s

initdb creates a cluster's data directory once; postgres is the server itself, whose first process is the postmaster; pg_ctl is a wrapper that starts, stops, restarts, reloads and reports on that server, tracking it through postmaster.pid.

open as a page

On a PostgreSQL 18 server, ps lists many postgres processes: what is the postmaster, and what do the other processes do?

level: juniorimportance: must knowfreq 72%

basics

~20 s

The postmaster is PostgreSQL's supervisor: it creates shared memory, listens for connections and forks one backend process per client. The other processes are helpers such as the checkpointer, background writer, walwriter and autovacuum launcher, plus background workers.

open as a page

In PostgreSQL, why does a query joining tables from two databases in the same cluster fail, and what can you do instead?

level: middleimportance: must knowfreq 58%

basics

~20 s

Each PostgreSQL backend is attached to one database and reads only its catalogs, so naming another database's table raises "cross-database references are not implemented" (0A000). Put shared data in schemas of one database, or use postgres_fdw, dblink or the application.

open as a page

In PostgreSQL, what do pg_ctl stop's smart, fast and immediate modes do, which is the default, and when would you use each?

level: middleimportance: must knowfreq 62%

basics

~20 s

Smart refuses new connections and waits for every session to end; fast, the default, rolls back open transactions, disconnects everyone and writes a shutdown checkpoint; immediate kills all processes without a checkpoint, so the next start runs crash recovery.

open as a page

In PostgreSQL, why does kill -9 on one backend process disconnect every other session on the server?

level: middleimportance: must knowfreq 55%

basics

~20 s

A backend killed by SIGKILL may have died holding a lock or halfway through changing shared memory, so the postmaster treats it as a crash: it stops every other process, reinitialises shared memory and runs crash recovery.

open as a page

In PostgreSQL, what is a HOT (heap-only tuple) update, which two conditions must an UPDATE meet to get one, and what does it save?

level: middleimportance: must knowfreq 62%

basics

~20 s

A HOT update keeps the new row version on the same heap page with no new index entries. It needs room on that page and no changed column used by a non-BRIN index; it saves index writes and allows pruning without VACUUM.

open as a page

In PostgreSQL, when is a wide column value TOASTed, and how do the PLAIN, MAIN, EXTERNAL and EXTENDED storage strategies differ?

level: middleimportance: must knowfreq 55%

basics

~20 s

When a row exceeds about 2 kB, PostgreSQL compresses and/or moves its wide variable-length values into a TOAST table in roughly 2 kB chunks, leaving an 18-byte pointer. EXTENDED allows both, EXTERNAL only moving out, MAIN mainly compression, PLAIN neither.

open as a page

In PostgreSQL, which files under the data directory hold one table, and what are the _fsm and _vm files beside it?

level: juniorimportance: should knowfreq 34%

basics

~20 s

A PostgreSQL table lives in PGDATA/base/<database OID>/ as a file named after its relfilenode, split into 1 GB segments of 8 kB pages. Beside it sit forks: _fsm (free space map), _vm (visibility map) and, for unlogged tables, _init.

open as a page

What is a PostgreSQL tablespace, how is it laid out under pg_tblspc, and what do default_tablespace and temp_tablespaces control?

level: middleimportance: should knowfreq 38%

basics

~20 s

A PostgreSQL tablespace is a named directory, often on another disk, for storing tables and indexes; PGDATA/pg_tblspc holds a symlink to it per tablespace OID. default_tablespace places new tables and indexes; temp_tablespaces places temporary objects and spill files.

open as a page

In PostgreSQL, what are template0 and template1, and when should CREATE DATABASE use TEMPLATE template0 instead of the default?

level: middleimportance: should knowfreq 46%

basics

~20 s

CREATE DATABASE clones template1 by default, so objects added there reach every new database. template0 is a pristine, connection-disabled original; clone it to restore a plain pg_dump script or to choose an encoding or locale unlike template1's.

open as a page

When you run initdb for a new PostgreSQL 18 cluster, which options deserve a deliberate choice, and what changed about data checksums?

level: middleimportance: should knowfreq 40%

basics

~10 s

Choose authentication (-A, --auth-local, --auth-host; the default is trust), the superuser and password, WAL location and segment size, and group access. Since PostgreSQL 18, initdb enables data checksums by default; --no-data-checksums opts out.

open as a page

In PostgreSQL, how does the buffer manager pick a shared buffer to evict, and how does pg_buffercache show the result?

level: middleimportance: should knowfreq 38%

basics

~20 s

PostgreSQL uses clock-sweep: each shared buffer's usage count rises on every pin, up to 5, and falls as the clock hand passes; the first unpinned buffer at zero is evicted. pg_buffercache shows each buffer's relation, usage count and dirty flag.

open as a page

In PostgreSQL, you must move a 400 GB cold archive table to a cheaper disk with ALTER TABLE ... SET TABLESPACE; what does it lock, what does it move, and how do you do it safely?

level: seniorimportance: should knowfreq 41%

basics

~20 s

PostgreSQL's ALTER TABLE ... SET TABLESPACE copies the table and its TOAST data under ACCESS EXCLUSIVE, blocking even reads until commit; indexes stay put. Move partitions one at a time with lock_timeout, move indexes separately, or use pg_repack --tablespace online.

open as a page

When `pg_ctl stop -m smart` on a self-managed PostgreSQL inventory server reports "server does not shut down" during a maintenance window while idle pooled sessions stay connected, what is happening and how do you finish safely?

level: seniorimportance: should knowfreq 36%

basics

~20 s

Smart mode waits for every session, idle pooled ones included, to disconnect; pg_ctl gave up after its 60-second wait but the server is still shutting down and refusing new connections. Escalate with pg_ctl stop -m fast.

open as a page

After a PostgreSQL server stops uncleanly, through pg_ctl stop -m immediate or a kill -9, what happens at the next start, and what should you check?

level: seniorimportance: should knowfreq 30%

basics

~10 s

Startup first validates postmaster.pid, then runs crash recovery, replaying WAL from the last checkpoint before accepting connections. Synchronously committed data survives, but unlogged tables are emptied and cumulative statistics counters are reset.

open as a page

A nightly full-table report on a PostgreSQL hotel-booking database seems to flush the cache everyone relies on: what do ring buffers protect, and what do they leave exposed?

level: seniorimportance: should knowfreq 40%

basics

~20 s

A sequential scan of a table larger than a quarter of shared_buffers reuses a small ring of buffers, so shared_buffers survives. The OS page cache does not, and index or bitmap heap scans bypass the ring entirely.

open as a page

On a busy PostgreSQL news site, each page view runs UPDATE articles SET view_count = view_count + 1, n_tup_hot_upd stays near zero, and the table and its indexes keep growing; why, and what do you change?

level: seniorimportance: should knowfreq 46%

basics

~20 s

Each increment writes a new row version, and because view_count is indexed or the pages are full, none are HOT, so every index gains an entry too. Drop that index, lower fillfactor and rewrite once, or move batched counts to a narrow table.

open as a page

In PostgreSQL 18, how do CREATE DATABASE's WAL_LOG and FILE_COPY strategies differ, and what does setting file_copy_method to clone change?

level: seniorimportance: nice to knowfreq 17%

basics

~20 s

In PostgreSQL 18, WAL_LOG (the default) copies the template block by block, WAL-logging each block. FILE_COPY copies directories with one WAL record per tablespace but forces two checkpoints; file_copy_method = clone lets that copy share disk blocks.

open as a page

When is starting PostgreSQL in single-user mode with postgres --single the right tool, and what does that mode switch off?

level: seniorimportance: nice to knowfreq 14%

basics

~20 s

Single-user mode runs one standalone backend with no postmaster, no network, no background processes and implicit superuser rights; use it for catalog repair, such as -P with REINDEX SYSTEM, or when no normal superuser login works.

open as a page

After a planned PostgreSQL restart, queries stay slow until shared_buffers warms up: how do pg_prewarm and its autoprewarm worker help?

level: seniorimportance: nice to knowfreq 24%

basics

~10 s

pg_prewarm loads a relation's blocks into shared_buffers or the OS cache on demand. Its autoprewarm worker records which blocks were cached in autoprewarm.blocks and reloads them after a restart, shortening the cold-cache period.

open as a page

In PostgreSQL, why does a table of narrow rows take far more disk than its column widths add up to, and how can column order reduce it?

level: seniorimportance: nice to knowfreq 27%

basics

~20 s

Every PostgreSQL heap row carries a 23-byte header padded to 24 bytes plus a 4-byte line pointer, and each column is padded to its type's alignment. Ordering fixed-width columns from 8-byte down to 1-byte types removes padding and can shrink narrow rows noticeably.

open as a page