TopolearnNamespacing decides what queries see and is the basis of permissions and multi-tenant layouts.
PostgreSQL organises objects in three levels:
- A cluster (instance) is one running server with one data directory. It holds several databases and the roles shared by all of them.
- A database is isolated: a connection belongs to exactly one database and can't query tables in another (you'd need
postgres_fdwor dblink). Use separate databases for separate applications. - A schema is a namespace inside a database, holding tables, views, functions and types. Objects in different schemas of the same database can be joined freely. Every database starts with a
publicschema.
Unqualified names are resolved through the search_path setting, by default "$user", public: first a schema named after your role (if it exists), then public. So SELECT * FROM orders finds public.orders unless you have a yourname.orders. You can always qualify: sales.orders.
Schemas are useful to group an application's areas (billing, audit), to install extensions out of the way, or to give each tenant its own namespace. Since PostgreSQL 15, ordinary users can no longer create objects in public by default — the owner of the database must grant CREATE or you create your own schema. Set search_path per role or per database (ALTER ROLE app SET search_path = app, public) rather than in every session.
Example
CREATE SCHEMA billing;
CREATE TABLE billing.invoice (id int PRIMARY KEY, total numeric);
CREATE TABLE public.invoice (id int PRIMARY KEY, note text);
SHOW search_path; -- "$user", public
SELECT * FROM invoice; -- resolves to public.invoice
SET search_path = billing, public;
SELECT * FROM invoice; -- now billing.invoice
SELECT current_database(), current_schema(); -- e.g. shop | billingRemember
- Cluster > database > schema > table
- Connections are per database; schemas in one database can be joined
- search_path decides which schema an unqualified name means
Watch out for
- Expecting to join tables across two databases in one query.