Databases, schemas, and connecting

A single PostgreSQL server holds more than one set of tables, and it organizes them in layers. Understanding this nesting — cluster, database, schema, table — clears up where your data lives and how to name it. This lesson walks down through the layers, creates them, and proves how names are resolved.

Run on PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop on a Mac, September 2026). Driver programs: pg 8.23.0 (Node 24.13.0), psycopg 3.3.6, pgx v5.11.0 (Go 1.27.1), sqlx 0.9.0 (Rust 1.98.1).

The layers

One running server is called a cluster. A cluster contains one or more databases, which are fully isolated from each other: a connection talks to exactly one database at a time. Inside each database are schemas, which are named groupings of tables (and other objects). Inside each schema are the tables themselves. The diagram shows the nesting.

flowchart TD
  CL["Cluster: one server"] --> DB1["Database: appdb"]
  CL --> DB2["Database: postgres"]
  DB1 --> S1["Schema: public"]
  DB1 --> S2["Schema: archive"]
  S1 --> T1["Table: authors"]
  S1 --> T2["Table: books"]
  S2 --> T3["Table: books"]

How a cluster nests databases, schemas, and tables

Load the course dataset (same fence as the first lesson) and ask the server where you are:

-- sql/setup.sql
CREATE TABLE authors (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  born int
);
CREATE TABLE books (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  author_id   bigint REFERENCES authors (id),
  published   int,
  copies_sold int NOT NULL DEFAULT 0
);
CREATE TABLE sales (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id bigint REFERENCES books (id),
  region  text NOT NULL,
  copies  int NOT NULL
);
INSERT INTO authors (name, born) VALUES ('Mara Linde', 1968), ('Owen Pryce', 1975), ('Sela Vance', 1990);
INSERT INTO books (title, author_id, published, copies_sold) VALUES
  ('Quiet Harbors', 1, 2003, 450), ('The Glass Orchard', 1, 2018, 1250), ('Tidal Notes', 2, 2011, 600), ('Unlinked Draft', NULL, 2020, 0);
INSERT INTO sales (book_id, region, copies) VALUES
  (2, 'East', 900), (3, 'West', 400), (1, 'West', 400), (3, 'East', 200), (2, 'West', 150), (1, 'East', 50);

-- sql/layers.sql
SELECT current_database() AS database, current_schema() AS schema;
\dn
\dt

              database               | schema
-------------------------------------+--------
 b1_databases_schemas_and_connecting | public
(1 row)

      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

           List of tables
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | authors | table | postgres
 public | books   | table | postgres
 public | sales   | table | postgres
(3 rows)

The new database has exactly one schema, public, owned by the special role pg_database_owner (meaning: whoever owns the database), and the tables landed there.

Creating a database

A database is the unit of isolation. You give each application its own database so their tables never collide. Creating one is a single statement.

psql

-- sql/create-database.sql
DROP DATABASE IF EXISTS shop;  -- lets you re-run the lesson
CREATE DATABASE shop;

Node.js

await pool.query('CREATE DATABASE shop');

Python

conn.autocommit = True
cur.execute("CREATE DATABASE shop")

Go

_, err := conn.Exec(ctx, "CREATE DATABASE shop")

Rust

sqlx::query("CREATE DATABASE shop")
    .execute(&pool)
    .await?;

NOTICE:  database "shop" does not exist, skipping
DROP DATABASE
CREATE DATABASE

To start using the new database you reconnect to it. In psql that is \c shop (below: an empty database, empty public); in a driver you open a fresh connection with shop as the database in the connection string.

\c shop
SELECT current_database();
\dt

You are now connected to database "shop" as user "postgres".
 current_database
------------------
 shop
(1 row)

Did not find any tables.

pg_database is the catalog behind \l:

-- sql/list-databases.sql
SELECT datname, pg_get_userbyid(datdba) AS owner FROM pg_database WHERE datname = 'shop';

 datname |  owner
---------+----------
 shop    | postgres
(1 row)

You cannot run CREATE DATABASE inside a transaction block. That is why the Python snippet turns on autocommit; the driver programs at the end of the lesson prove it, and this is the server’s own answer (SQLSTATE 25001):

-- @expect-error 25001
BEGIN;
CREATE DATABASE tx_demo;

BEGIN
ERROR:  CREATE DATABASE cannot run inside a transaction block

Creating a schema and the public schema

Within a database, schemas let you group related tables and avoid name clashes. Every new database starts with one schema already present, named public, and unless you say otherwise your tables land there. Create a second schema, archive, holding an older copy of books:

psql

-- sql/create-schema.sql
CREATE SCHEMA archive;

Node.js

await pool.query('CREATE SCHEMA archive');

Python

cur.execute("CREATE SCHEMA archive")

Go

_, err := conn.Exec(ctx, "CREATE SCHEMA archive")

Rust

sqlx::query("CREATE SCHEMA archive")
    .execute(&pool)
    .await?;

Two tables with the same name in two schemas are legal, because the qualified names differ:

-- sql/archive-books.sql
CREATE TABLE archive.books (LIKE public.books);
INSERT INTO archive.books (id, title, published, copies_sold) VALUES
  (1, 'Quiet Harbors (1st edition)', 2003, 0), (2, 'Salt Road', 1998, 120);
\dt archive.*

CREATE TABLE
INSERT 0 2
           List of tables
 Schema  | Name  | Type  |  Owner
---------+-------+-------+----------
 archive | books | table | postgres
(1 row)

In pgAdmin: new schemas appear under Schemas in the browser tree once you refresh the database; you can also create one by right-clicking Schemas. DBeaver shows the same grouping in its own navigator.

Qualified names and search_path

A table has a full, qualified name of the form schema.table, such as archive.books. You can always write the qualified name and PostgreSQL will know exactly which table you mean. When you write an unqualified name like books, PostgreSQL looks through the search_path — an ordered list of schemas — and uses the first match. The default is "$user", public: a schema named after the current user if it exists (there is none, so it is skipped), then public. Real output:

-- sql/search-path.sql
SHOW search_path;
SELECT current_schemas(false) AS resolved;
SELECT count(*) AS books_seen FROM books;

   search_path
-----------------
 "$user", public
(1 row)

 resolved
----------
 {public}
(1 row)

 books_seen
------------
          4
(1 row)

current_schemas(false) shows what the path resolves to right now: only public, because "$user" is skipped. You can change the path for your session:

psql

-- sql/set-search-path.sql
SET search_path TO archive, public;
SHOW search_path;
RESET search_path;

Node.js

await pool.query('SET search_path TO archive, public');

Python

cur.execute("SET search_path TO archive, public")

Go

_, err := conn.Exec(ctx, "SET search_path TO archive, public")

Rust

sqlx::query("SET search_path TO archive, public")
    .execute(&pool)
    .await?;

SET
   search_path
-----------------
 archive, public
(1 row)

RESET

With archive first in the path, the bare name books now means a different table. Same word, different data:

-- sql/search-path-resolve.sql
SET search_path TO archive, public;
SELECT count(*) AS books_seen FROM books;
SELECT count(*) AS public_books FROM public.books;
SELECT relname, relnamespace::regnamespace AS schema FROM pg_class WHERE relname = 'books';
RESET search_path;

SET
 books_seen
------------
          2
(1 row)

 public_books
--------------
            4
(1 row)

 relname | schema
---------+---------
 books   | public
 books   | archive
(2 rows)

RESET

Two rows, not four: the unqualified books is archive.books, while public.books still has four. SET lasts for the session — and a session is one connection. Here are all four drivers running exactly the snippets above (wrapped in a small program that connects first):

$ node dsc.mjs        # pg 8.23.0 (pool of 2; the second line is two concurrent SHOW search_path calls)
{ search_path: 'archive, public' }
[ 'archive, public', '"$user", public' ]

$ python dsc.py        # psycopg 3.3.6 (first line: CREATE DATABASE without autocommit)
ActiveSqlTransaction CREATE DATABASE cannot run inside a transaction block
('archive, public',)

$ go run .        # pgx v5.11.0
archive, public

$ cargo run --bin dsc        # sqlx 0.9.0
("archive, public",)

The Node output has a lesson in it. With a connection pool, the second line shows two concurrent SHOW search_path calls running on two different pooled connections: only the connection that ran SET sees archive, public; the other still has the default. Session state (search_path, SET ROLE, temp tables) does not belong to the pool, it belongs to one connection. Use fully qualified names in application SQL, or set the path per role or database with ALTER ROLE ... SET search_path (docs).

Roles versus databases

It is easy to confuse the two main account ideas. A database is a container for your data. A role is an account that connects and is granted permission to act on that data — roles are both users and groups in PostgreSQL. They are independent: a single role can own and connect to many databases, and a database can be used by many roles. You logged in as the postgres role, a superuser, throughout this module. Real systems create limited roles per application. Create one (the password is a placeholder, never a real secret):

-- sql/roles.sql
CREATE ROLE app_user LOGIN PASSWORD '<app-password>';
\du app_user

CREATE ROLE
     List of roles
 Role name | Attributes
-----------+------------
 app_user  |

The public schema changed in PostgreSQL 15

Older tutorials say “everyone can create tables in public”. That stopped being true in PostgreSQL 15: CREATE on public is no longer granted to every role. The new role cannot create a table there (SQLSTATE 42501):

-- @expect-error 42501
BEGIN;
SET ROLE app_user;
CREATE TABLE public.scratch (id int);

BEGIN
SET
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.scratch (id int);
                     ^

\dn+ shows the access privileges behind that. pg_database_owner (the database’s owner) has Usage and Create; =U means every role (PUBLIC) has only Usage:

-- sql/public-acl.sql
\dn+ public

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(1 row)

The clean pattern is a schema of your own for the application role:

-- sql/own-schema.sql
CREATE SCHEMA app AUTHORIZATION app_user;
SET ROLE app_user;
CREATE TABLE app.notes (id int, body text);
INSERT INTO app.notes VALUES (1, 'created by app_user');
SELECT current_user, count(*) AS notes FROM app.notes;
RESET ROLE;

CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 1
 current_user | notes
--------------+-------
 app_user     |     1
(1 row)

RESET

Roles are cluster-wide, so a role that owns objects cannot just be dropped. The server tells you what depends on it, and DROP OWNED BY clears it (also cleaning up the lesson’s shop database):

-- @expect-error 2BP01
DROP ROLE app_user;

ERROR:  role "app_user" cannot be dropped because some objects depend on it
DETAIL:  owner of schema app
owner of table app.notes

-- sql/cleanup.sql
DROP OWNED BY app_user;
DROP ROLE app_user;
DROP DATABASE shop;

DROP OWNED
DROP ROLE
DROP DATABASE

Reading AI-generated code

A maintenance job that “looks in archive first, then public” seems harmless. Read the two lines that matter:

🐛 Spot the bug

-- maintenance job written by an assistant
-- AI comment: "look in archive first, then public"
SET search_path TO archive, public;

-- purge unsold books
DELETE FROM books WHERE copies_sold = 0;

Show the answer

The intent is to purge unsold books from the live catalog. But archive is first in the path, so books resolves to archive.books, and the statement runs without error against the wrong table. Real run inside a rolled-back transaction: it deleted one row from archive.books, and public.books was untouched.

-- sql/shadowed-delete.sql
BEGIN;
SET LOCAL search_path TO archive, public;
DELETE FROM books WHERE copies_sold = 0;
SELECT (SELECT count(*) FROM archive.books) AS archive_left,
       (SELECT count(*) FROM public.books)  AS public_left;
ROLLBACK;

BEGIN
SET
DELETE 1
 archive_left | public_left
--------------+-------------
            1 |           4
(1 row)

ROLLBACK

Nothing failed, so nothing alerted anyone: the archive lost data and the live table kept its unsold rows. The fix is to qualify every name in maintenance SQL (or not to override search_path at all):

-- sql/qualified-delete.sql
BEGIN;
DELETE FROM public.books WHERE copies_sold = 0 RETURNING id, title;
ROLLBACK;

BEGIN
 id |     title
----+----------------
  4 | Unlinked Draft
(1 row)

DELETE 1
ROLLBACK

Mental Model

A name is resolved in two steps: if it has a schema, that is the answer; if not, walk search_path left to right and take the first schema that has it. The path belongs to the connection, not to the application. So the safe habits are all about removing the second step: qualify names, keep the path default, and give each application role its own schema.

Tips / gotchas

• A connection always targets exactly one database; to switch, you reconnect (\c).
• New databases come with a public schema; since PostgreSQL 15 only its owner can create there by default.
• Unqualified names are resolved through search_path; the qualified schema.table form is unambiguous and safest in shared databases.
• CREATE DATABASE cannot run inside a transaction block (25001), so tools must run it on its own with autocommit.
• Roles (accounts) and databases (data containers) are separate concepts; roles are global to the cluster, and DROP ROLE needs DROP OWNED BY first when the role owns objects.
• SET search_path on a pooled connection changes only that one connection.

🐘 Cluster, database, schema, table

Picture the four layers as nested boxes: a server holds databases, a database holds schemas, a schema holds tables. Knowing which box you are in answers most “where is my table?” questions, and writing qualified schema.table names spares you from search_path surprises.

Trade-offs

Option	Benefit	Cost
One database per app	Clean separation, simple mental model	Separate connection pools, more resource use
Several schemas in one database	Shares a connection pool, can join across schemas	Must manage naming and permissions so they do not collide
Qualified names (schema.table)	Unambiguous — instantly clear where the table lives	More to type
Relying on search_path	Short, terse to write	Fragile, since the result changes with the session (proved above)

Common Misconceptions

• “Schemas are just folders.” They are namespaces that participate in name resolution and permissions; two tables named books in two schemas are different tables (proved above).
• “search_path is a database setting.” The active value is per session; defaults can be attached to a role or database, but a pooled connection can still be in a different state.
• “Every role can create tables in public.” True before PostgreSQL 15; today it fails with permission denied for schema public.
• “A superuser role is fine for the app.” Create a role scoped to just what the application needs, to limit the damage if a credential leaks.
• “CREATE DATABASE is like any DDL.” It cannot run in a transaction block, so a migration tool that wraps everything in a transaction fails immediately.

Which schema exists in every new database by default?

billing public system main

What does search_path control?

Which schemas are searched to resolve an unqualified table name The file path where data is stored The order rows are returned in Which database you connect to

How do roles and databases relate?

A role is a database of users They are independent: a role can connect to many databases and a database can be used by many roles Each role owns exactly one database A database is deleted when its role is dropped

A new role runs CREATE TABLE public.scratch (id int) on PostgreSQL 18. What happens?

It works, as in every earlier version ERROR: permission denied for schema public It creates the table in a schema named after the role It asks for confirmation

A job does SET search_path TO archive, public; DELETE FROM books WHERE copies_sold = 0; and both schemas have a books table. Which table is affected?

public.books archive.books Both The statement errors as ambiguous

Submit

Mark as complete