Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 90 ยท Getting Started with PostgreSQL

Clusters, Databases and Schemas

Understand the PostgreSQL hierarchy of cluster, database, schema and table, how search_path resolves names, why public changed in 15, and what templates are.

When you write SELECT * FROM books, PostgreSQL has to decide which books you mean. The answer depends on a hierarchy that is a little deeper than in many other databases: a server holds several databases, each database holds several schemas, and each schema holds tables, views, functions and other objects. If you understand this hierarchy and the search_path setting that navigates it, a whole family of confusing errors ("relation does not exist", "permission denied for schema public") becomes easy to read.

Everything in this lesson about PostgreSQL itself is shown as read-only blocks. SQLite has a smaller version of the same idea (a main schema and a temp schema), and the live examples use it to let you watch name resolution work.

The hierarchy

From server to table

Cluster One running server and its data directory.
Database Fully separate set of data. You connect to one.
Schema A namespace inside a database.
Table Tables, views, functions and types live here.

A full name is database.schema.object, but queries usually use schema.object.

1 / 2

A cluster is what initdb creates: one set of data files managed by one server process, listening on one port. (It has nothing to do with a group of servers.) One cluster serves many databases, and each database is an isolated world with its own tables, schemas and settings.

flowchart TD
    C["Cluster<br/>one server, port 5432"] --> D1["Database shop"]
    C --> D2["Database analytics"]
    C --> D3["Database postgres"]
    D1 --> S1["Schema public"]
    D1 --> S2["Schema sales"]
    S2 --> T1["Table sales.orders"]
    S2 --> T2["Table sales.invoices"]
    S1 --> T3["Table public.users"]

A cluster contains databases, a database contains schemas, a schema contains tables.

Two facts follow from this picture and surprise newcomers:

  • A connection belongs to one database. You cannot join a table from shop with a table from analytics in a single query, and a name such as analytics.public.events fails with cross-database references are not implemented. If you need to combine data, put it in one database (in different schemas if you like) or use a foreign data wrapper, covered in Foreign Data Wrappers.
  • Roles (users and groups) belong to the cluster, not to a database. One role can have privileges in many databases.

You can list the databases and switch between them in psql, or ask SQL:

SQL
-- PostgreSQL
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE NOT datistemplate;

-- in psql
\l
\c analytics

What schemas are for

A schema is a named container for objects. The full name sales.orders means "the table orders in the schema sales". Two schemas can each have a table called orders without clashing. Common reasons to use schemas:

  • Organize a growing database: sales, hr, reporting.
  • Separate permissions: grant a role access to one schema and not another.
  • Keep extensions tidy: install an extension's functions into their own schema.
  • Version an API: expose views in api_v1 while tables live elsewhere.
  • Isolate tenants: some multi-tenant designs use a schema per customer, a pattern weighed in Project: Multi-Tenant SaaS Schema.

Creating and removing them is plain SQL:

SQL
-- PostgreSQL
CREATE SCHEMA sales;
CREATE SCHEMA hr AUTHORIZATION hr_admin;

CREATE TABLE sales.orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  placed_at  timestamptz NOT NULL DEFAULT now()
);

SELECT count(*) FROM sales.orders;

DROP SCHEMA sales CASCADE;   -- removes the schema AND everything inside it

DROP SCHEMA without CASCADE refuses to remove a schema that still contains objects, which is a useful safety net. Every database also contains system schemas: pg_catalog (built-in types, functions and the catalog tables), information_schema (the standard-SQL view of the catalog), and temporary schemas named pg_temp_N for your session's temporary tables.

search_path: how names are resolved

When a name is not qualified, PostgreSQL looks through the schemas in the search_path setting, in order, and uses the first match:

SQL
-- PostgreSQL
SHOW search_path;
--    search_path
-- -----------------
--  "$user", public

SELECT current_schema();

The default "$user", public means: first a schema with the same name as your role (if one exists), then public. Unqualified CREATE TABLE puts the new table in the first schema of the path that exists. You can change the path for a session, a role or a whole database:

SQL
-- PostgreSQL
SET search_path TO sales, public;                 -- this session only
ALTER ROLE app_user SET search_path = sales, public;     -- every future login of app_user
ALTER DATABASE shop SET search_path = sales, public;     -- default for the database

Two details are worth remembering. pg_catalog is always searched first, even if it is not listed, which is why count, integer and friends always mean the built-ins. And your session's temporary schema is searched first for tables, so a temporary table can hide a permanent one with the same name. That is the same shadowing effect you can try in SQLite.

search_path is also a security topic. If an unexpected schema comes early in the path, a malicious user who can create objects there can shadow a function that a privileged routine relies on. Keep the path short and explicit for administrative code, and see SQL Injection and Safe Queries.

The public schema and the change in PostgreSQL 15

Every new database gets a schema called public. For decades every user could create objects in it, which made it convenient for learning and risky in shared databases. Since PostgreSQL 15, ordinary users no longer have CREATE on public; the database owner owns it, and other roles must be granted the privilege explicitly:

SQL
-- PostgreSQL
GRANT USAGE, CREATE ON SCHEMA public TO app_user;

Databases upgraded from an older version keep their old permissions, so you may meet both behaviors. A tidy modern setup is to create a schema for your application, give your application role USAGE and CREATE on it, and set that role's search_path to point there. Details of GRANT come in Roles, Privileges and GRANT.

Template databases

New databases are created by copying an existing one, the template. Two are always present:

  • template1 is the default model. Anything you add to it (an extension, a schema, a function) appears in every database you create afterwards.
  • template0 is a pristine, never-modified copy. Use it when you need a database with a different encoding or collation than template1.
SQL
-- PostgreSQL
CREATE DATABASE shop_test TEMPLATE shop;       -- a full copy of shop, data included
CREATE DATABASE legacy TEMPLATE template0
  ENCODING 'LATIN1' LC_COLLATE 'C' LC_CTYPE 'C';

Copying an existing database as a template is a very fast way to build a test database for each run of a test suite, provided nobody else is connected to the source database at that moment. A third database, called postgres, exists simply so that tools have something to connect to.

Listing, renaming and dropping databases

Day-to-day database management is a handful of commands. All results below come from PostgreSQL 18.

SQL
-- List the databases (the same information as psql's \l)
SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate, datistemplate
FROM pg_database
WHERE NOT datistemplate
ORDER BY datname;

SELECT pg_size_pretty(pg_database_size('shop'));   -- how much disk a database uses
SELECT current_database();                          -- the one you are connected to

CREATE DATABASE shop2 TEMPLATE shop;                -- copy
ALTER DATABASE shop2 RENAME TO store;               -- rename
ALTER DATABASE store CONNECTION LIMIT 5;            -- per-database settings; datconnlimit now shows 5
DROP DATABASE store;                                -- remove it for good
DROP DATABASE IF EXISTS store;                      -- a notice instead of an error if it is already gone

In psql, \l lists databases (\l+ adds sizes), \c name connects to another one, and \conninfo shows the current connection. From the operating-system shell, createdb shop and dropdb shop do the same as CREATE DATABASE and DROP DATABASE; dropdb --if-exists --force shop adds the two options described next.

Rules you will run into:

  • You cannot rename or drop the database you are connected to. DROP DATABASE fails with "cannot drop the currently open database". Connect to a different one first (usually postgres) and run the command from there. The same applies to ALTER DATABASE ... RENAME TO.
  • Other sessions block both commands. If anyone else is connected, PostgreSQL refuses with "database is being accessed by other users". Since PostgreSQL 13 you can write DROP DATABASE name WITH (FORCE) to terminate those sessions first. Before that you had to call pg_terminate_backend() for each connection yourself. Renaming still needs the other connections gone.
  • Neither command can run inside a transaction block.
  • Dropping is permanent. There is no recycle bin. Only the owner or a superuser can do it, so take a backup first (Backup and Recovery).
  • Dropping a database that does not exist is an error ("database ... does not exist") unless you add IF EXISTS.

Comparing with SQLite

SQLite has far less structure: a database is one file, and that file is a single namespace. But it does have two named schemas, and you can attach more files:

  • main is the database you opened.
  • temp holds temporary tables, visible only to your connection.
  • ATTACH DATABASE 'other.db' AS other adds another file as a schema called other, so other.orders works. (In PostgreSQL, a schema is inside one database; in SQLite, attached files play the role of extra schemas.)

The first live example creates a temporary table that hides a permanent one, then reads both with qualified names, like main.books and temp.books:

SQL Schemas in SQLite: main and temp ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The unqualified books finds the temporary table first, exactly like PostgreSQL's temporary schema, while the qualified names say precisely which table you mean. The lesson is the same in both systems: qualify names when it matters, and keep temporary tables from shadowing real ones by giving them distinct names.

The second example uses the school database, where a temporary students table hides the real one with 36 rows. Counting through each name shows which table you actually reach:

SQL Qualified names keep things unambiguous ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

If the unqualified query surprises you, that is the point: shadowing is silent. In PostgreSQL, you can ask where a name resolves with SELECT to_regclass('books'); (returns the qualified table or NULL), a handy debugging trick for "which one am I hitting?"

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How many databases can a single PostgreSQL connection query at one time?

    A connection is bound to one database. Joining across databases needs a foreign data wrapper or a single combined database.
  2. 2. What does the default search_path of "$user", public mean?

    PostgreSQL walks the path from left to right and uses the first match; a schema matching your role name comes before public.
  3. 3. What changed about the public schema in PostgreSQL 15?

    From 15 on, the database owner owns public and other roles need an explicit GRANT to create objects in it. Upgraded databases keep their older permissions.
  4. 4. When should you create a database from template0 rather than template1?

    template0 is pristine and can be copied with other encoding settings, while template1 may contain your own additions and must match its encoding.

Summary

  • A cluster is one server and its data directory; it holds databases, which hold schemas, which hold tables and other objects.
  • A connection belongs to a single database, but roles are shared across the whole cluster.
  • schema.table names are explicit; unqualified names are resolved through search_path, whose default is "$user", public.
  • CREATE SCHEMA organizes objects and permissions; DROP SCHEMA ... CASCADE removes everything inside.
  • Since PostgreSQL 15, ordinary roles cannot create objects in public unless granted.
  • template1 is the default copy source, template0 is the pristine one; copying a database as a template is a fast way to clone it.
  • SQLite has main, temp and attached files as its rough equivalents, and shadowing works the same way.

Next: A Tour of PostgreSQL SQL, a first look at the features that make PostgreSQL's SQL special.