In PostgreSQL, how do a cluster, a database and a schema relate, and which objects belong to the whole cluster rather than one database?
answer
- one server, many databases
- a connection picks one database
- namespaces inside a database
- roles sit above databases
- pg_global holds the shared catalogs
basics
~20 sA 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 sA **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-- 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
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.
Explain why schemas can be joined in one query but databases cannot, and name the three cluster-wide objects and where they are stored.
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.
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