skip to content

Databases, Schemas & Tablespaces

A PostgreSQL cluster holds databases cloned from template1, each holding schemas, and one query cannot join across databases. Tablespaces put data on other disks. Interviewers probe this hierarchy.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

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, 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

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

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

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