Schema

a named group of tables inside one database

What it is

A schema is a namespace inside a database: a container for tables, views, data types, functions and operators, so that the same name can be used in two schemas without conflict. PostgreSQL’s manual compares schemas to directories in a file system, except that they cannot be nested: a database holds schemas, and schemas hold objects. The word has a second, looser sense, just as common: the schema of a database is its whole structure, every table, column, key and constraint. Both senses appear below, and the plural, schemas, almost always means the namespaces.

A billing area of an application can live in its own schema, and a session can choose which schemas it looks in:

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total  numeric(12, 2) NOT NULL
);

SHOW search_path;                  -- "$user", public
SET search_path = billing, public;
SELECT count(*) FROM invoices;     -- now resolves to billing.invoices

A name written with its schema, such as billing.invoices, always means the same table. A name written without one is looked up through search_path, a list of schemas the server walks in order until it finds a match. The default list is "$user", public: a schema named after the current role, if one exists, then the public schema every new database starts with. The first existing schema in the path is also where CREATE TABLE puts a table whose name carries no schema, which is why objects land in public unless someone says otherwise. A name found in none of the listed schemas raises an error, even when another schema of the same database holds it.

Why it matters

The first reason is organisation. A database with a few hundred tables needs a level above the table, and schemas provide it: billing in one, accounts in another, a third-party tool’s tables in a third, clear of everyone else’s names. A client connection reaches a single database, but schemas are not rigidly separated: a role can use objects in any schema of the database it is connected to, privileges permitting, and one query can join tables from several of them.

The second reason is access control. USAGE on a schema lets a role look up the objects inside it, and CREATE lets it add new ones. Taking USAGE away blocks access to the objects, although their names can still be read from the system catalogs, so a schema guards the data and not the knowledge of what exists. CREATE SCHEMA name AUTHORIZATION role hands a new schema to a role, and names beginning with pg_ are reserved for the system.

The third reason is security, because schemas take part in name resolution. Most names in queries are written without a schema, so, in the manual’s words, adding a schema to search_path effectively trusts every role that can create objects in it. Such a role can make an ordinary query run functions of its choosing, as though the person running the query had called them. The secure patterns keep schemas that others can write to off a user’s path, for example one private schema per user, created with CREATE SCHEMA alice AUTHORIZATION alice, which the "$user" entry then finds first. PostgreSQL 15 moved new databases in that direction: it removed the right of every role to create objects in public and made the database owner, through the pg_database_owner role, the owner of public.

Portability is the last point. The SQL standard has schemas but no public schema, and the manual notes that some SQL systems might not implement schemas at all. Qualified names travel better than code that leans on search_path.

What it looks like in practice

The data model in Modeled 348 tables across 34 Postgres schemas uses schemas in the first sense, as the level above the table. The maritime domain, with its professionals, companies, ships, jobs and reviews, is laid out as normalized PostgreSQL schemas organised by domain: 348 tables across 34 of them. Table names are plural inside singular-named schemas, without exception, which also sidesteps a lot of reserved-word collisions, and real foreign keys and constraints hold the relationships between them. Adding a feature usually means extending the schema along the existing grain rather than working against it, which the page calls the only reason 34 schemas is a structure rather than a sprawl.

A schema can also be a boundary between two jobs. In Built Stripe and in-app purchase entitlements Stripe card payments and Apple and Google in-app purchases all converge on a payments schema of 9 tables and 34 functions. A separate access schema of 11 tables and 34 functions answers “may this account do this?” without knowing which storefront paid for it, and that answer, re-checked on the server on every request, gates the company directory, search and data export. The cost, as the page puts it, is two schemas where a smaller product would want one, plus an entitlement lookup on requests that would otherwise have been free.

The second sense of the word is the subject of 1,022 migrations behind a schema drift gate. Most projects describe a schema twice, in the initial setup that builds it from scratch and in the migrations that grew it, and the two drift apart unless something checks them. Here the database is versioned as 1,022 migrations, numbered from 036 through 1102, and a CI job builds it twice on every change, once fresh and once with every migration replayed in order, then compares the two: the seeded data as well as the structure. A migration that backfills a lookup table wrongly does as much damage as one that forgets a column, and only one of those shows up in a schema diff. Any disagreement fails the build with the difference printed, so every migration has to work replayed and from cold.

Where it goes wrong

A function planted in a shared schema. The PostgreSQL wiki’s guide to CVE-2018-1058 shows the attack: one user creates lower(varchar) in public. The built-in lower lives in pg_catalog and takes text, so for a varchar column the planted function is the closer match, and a colleague who runs SELECT lower(full_name) FROM a runs the planted code instead. Qualify calls in sensitive code as pg_catalog.lower, take CREATE on public away from the roles that do not need it, and list what is already there with \df public.*.

An upgrade that kept the old permissions. PostgreSQL 15’s safer default applies to new clusters and newly created databases. Upgrading a cluster or restoring a dump preserves the permissions public already had, so a server on version 15 or later can still let every role create objects there. The manual calls the default path with CREATE granted on public never a secure pattern, and notes that it is the default in databases upgraded from 14 or earlier. In such a database, run REVOKE CREATE ON SCHEMA public FROM PUBLIC, then audit public for objects named like those in pg_catalog.

A table created in the wrong place. A migration creates invoices without a schema, and the table goes into the first existing schema on the path of whichever role runs it. The application, connecting with another path, then reads a different table or gets relation "invoices" does not exist. Qualify every name in migrations, and check SELECT current_schema() in the session that runs them.

A drop that reaches past the schema. DROP SCHEMA billing refuses while the schema holds anything, because RESTRICT is the default. With CASCADE it drops every object inside and every object that depends on those, and the manual warns that this can remove objects in schemas other than the one named. Its advice is to run the DROP without CASCADE first and read the DETAIL lines, which list what would go.

A name the system resolves first. pg_catalog is searched before every schema on the path, even when the path leaves it out. A table of your own whose name begins with pg_ can be shadowed when a later version adds a system table of the same name, so the manual advises against such names. Reserved words such as user and order cause a milder version of the trouble: a table named that way needs double quotes in every statement.

Schema

What it is

Why it matters

What it looks like in practice

Where it goes wrong

Further reading