Clusters, databases, and schemas

PostgreSQL organizes everything in a strict hierarchy, and half of the confusing errors in this course dissolve once you can place each name on the right level.

One PostgreSQL server instance manages a cluster of databases. The cluster is the data directory plus the running server process: one port, one set of roles, one configuration. Everything Homebrew created when you installed PostgreSQL is one cluster.

A database lives inside the cluster. A connection enters one database at a time. You cannot join a table in notes_app with a table in another database from a plain connection; that needs extensions like postgres_fdw, which is a deliberate extra step, not the default.

Inside a database, schemas group objects such as tables and functions. A schema is a namespace, nothing more. public is the familiar default schema, not a separate database. When you write app.notes, you are naming schema app and table notes inside the current database.

A table belongs to one schema inside one database. The same table name can exist in several schemas without conflict.

Where roles fit

Roles break the nesting, and that surprises people. Roles belong to the cluster, so the same role name is visible from every database. You create a role once, then grant it different permissions in each database.

This is why \du shows the same list no matter which database you are connected to, and why dropping a database does not drop the roles that used it.

Verify each level yourself

The inspection commands map straight onto the hierarchy:

\l          -- databases in the cluster
\dn         -- schemas in the current database
\dt app.*   -- tables in one schema
\du         -- roles, shared by the whole cluster

And in plain SQL:

SELECT current_database(), current_schema(), current_user;

 current_database | current_schema | current_user
------------------+----------------+--------------
 notes_app        | public         | flavio

The classic mistake is creating a table in the wrong container: right database, wrong schema, or wrong database entirely. The table then “does not exist” when the application looks for it. Before you blame the query, run the statement above and check which database and schema you are actually in. We will build this course’s own notes_app database, app schema, and roles in the next lessons.