Clusters, Databases and Schemas
A new developer at Pedal writes a query that joins pedal.public.rentals to a table in the billing database, and PostgreSQL refuses. The query is fine; the mental model is wrong. PostgreSQL arranges objects in a strict nesting, and knowing the layers tells you what can see what.
The rule
A cluster holds databases; a database holds schemas; a schema holds tables.
- Cluster: one running PostgreSQL server and the data directory it manages. Pedal's server
db1is one cluster. Roles (users) belong to the cluster, so one login works for every database in it. - Database: a separate, self-contained collection of objects. A connection always goes to exactly one database, and a normal query cannot reach into another one. A fresh cluster already has
postgres,template0andtemplate1;CREATE DATABASEcopiestemplate1by default. - Schema: a namespace inside a database, like a folder. Every database starts with a schema called
public. Unlike databases, schemas in the same database can be joined freely. - Table (and view, function, sequence, index): lives in exactly one schema.
A fully qualified name reads from the outside in, but only the last two parts are needed once you are connected:
-- connected to database "pedal"
CREATE SCHEMA billing;
CREATE TABLE billing.invoices (invoice_id bigint PRIMARY KEY, rental_id bigint);
SELECT r.rental_id, i.invoice_id
FROM public.rentals AS r
JOIN billing.invoices AS i USING (rental_id); -- two schemas, one database: fineWhen you write a bare name like rentals, PostgreSQL searches the schemas listed in search_path, which defaults to "$user", public: first a schema named after your role, then public.
Inside the cluster, one main server process (the postmaster) listens for connections and starts a backend process for each client, alongside helper processes such as the checkpointer and autovacuum. You will meet those helpers later in the course.
The shortcut
To place any object, walk server → database → schema → object and ask at each step: "Does my connection already fix this layer?"
Server
Both databases are on
db1, the same cluster. So far, so good.
The trap is using databases where schemas belong. Use separate databases for things that must never be joined or that different teams own completely; use schemas to organise related data.
Drills
Answer fast — trust the rule.
A role called analyst is created on cluster db1. Where can it log in?
Can one ordinary query join public.rentals to audit.logins in the same database?
With the default search_path, which table does SELECT * FROM bikes; read if only public.bikes exists?
What does CREATE DATABASE reports; use as its starting point by default?
Remember
- A cluster holds databases; a database holds schemas; a schema holds tables.
- One connection sees one database; schemas inside it join freely.
- Bare names resolve through
search_path, by default"$user", public.