PostgreSQL Schemas: How to Organize a Database
On this page
"relation does not exist", but it's right there
You can see the orders table in your database tool. Yet your query fails with ERROR: relation "orders" does not exist. Nine times out of ten, the table is in a different schema from the one PostgreSQL searched. This lesson explains how schemas work, how PostgreSQL decides where to look, and how to use schemas to keep a growing database organized.
Databases, schemas and objects
PostgreSQL has three levels of containers:
- A cluster (Day 1) holds several databases.
- A database holds several schemas. Each connection is to exactly one database.
- A schema holds tables, views, functions and sequences.
A schema is a namespace, like a folder. sales.orders and archive.orders can live side by side. Unlike separate databases, tables in different schemas can be joined in one query, in one transaction, over one connection.
Every new database comes with a schema called public, and that's where your tables go unless you say otherwise. Two system schemas are always there too: pg_catalog holds PostgreSQL's own catalogs, and information_schema holds the SQL-standard metadata views.
CREATE SCHEMA sales;
CREATE TABLE sales.orders (order_id int PRIMARY KEY, total numeric(10,2));
ALTER SCHEMA sales RENAME TO commerce;
DROP SCHEMA IF EXISTS scratch CASCADE; -- CASCADE drops everything inside itIn psql, \dn lists schemas and \dt commerce.* lists the tables in one.
How search_path finds an unqualified name
When you write orders with no schema, PostgreSQL walks through the schemas listed in the search_path setting, in order, and uses the first match:
SHOW search_path;
-- "$user", public"$user" means a schema with the same name as your role, if one exists. So by default PostgreSQL looks in your personal schema, then in public. Your commerce.orders table isn't on that path, hence the error from the opening. Two fixes:
SELECT * FROM commerce.orders; -- qualify the name
SET search_path = commerce, public; -- or change the path for this session
SELECT * FROM orders; -- now finds commerce.ordersTo make a path stick for a role, use ALTER ROLE app_user SET search_path = commerce, public;. It applies to every new connection by that role.
In application code, migrations and functions, prefer qualified names. They mean the same thing no matter who runs them or how their session is set up. That matters most in SECURITY DEFINER functions (Day 15), which run with their owner's privileges. If such a function uses an unqualified name, a caller can change their search_path and make it find a table they control. It's a known privilege escalation, so pin search_path on those functions.
Schemas as a permission boundary
To touch a table, a role needs two things: USAGE on its schema and a privilege on the table itself. A role with SELECT on a table but no USAGE on the schema still gets "permission denied for schema". That's the most common permissions puzzle people hit.
A read-only reporting role looks like this:
CREATE SCHEMA reporting;
CREATE ROLE reporting_ro;
GRANT USAGE ON SCHEMA reporting TO reporting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO reporting_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO reporting_ro;The last line matters. ON ALL TABLES only covers tables that exist today. DEFAULT PRIVILEGES covers tables created later. Strictly, it covers tables created later by the role that ran the ALTER, which is usually your migration role.
Since PostgreSQL 15, ordinary users can no longer create objects in public by default. On older clusters, or clusters upgraded from them, lock it down yourself:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;Day 21 covers roles and grants in full.
Ways to split a database into schemas
Schemas are cheap, so use them where they make ownership and access clearer:
- By domain:
sales,inventory,billing. Each team owns a schema. - By access level: an app-facing schema, a
reportingschema for BI tools, astagingschema for raw ETL loads. - By lifecycle:
archiveoraudittables kept apart from hot data.
Backups follow the same lines. pg_dump --schema=billing exports just one schema (Day 26).
Schema per tenant: the trade-off
SaaS apps need to keep each customer's data separate. There are three common designs:
| Design | Isolation | Main cost |
|---|---|---|
Shared tables with a tenant_id column | A missed filter leaks data, unless you use row-level security (Day 29) | Every query must filter by tenant |
| A schema per tenant | Strong separation by name, easy per-tenant backups | Every migration runs once per tenant |
| A database per tenant | Full separation | Connections, backups and upgrades multiply |
Schema per tenant is the middle ground. Provisioning a tenant copies a template:
CREATE SCHEMA template;
CREATE TABLE template.customers (id int PRIMARY KEY, name text NOT NULL);
CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.customers (LIKE template.customers INCLUDING ALL);INCLUDING ALL copies columns, defaults, constraints and indexes, but not data. Watch three costs before you commit to it. Migrations must loop over every tenant schema. Thousands of schemas, each with dozens of tables, make the system catalogs large and can slow catalog lookups, so measure at your scale. And SET search_path per tenant doesn't mix well with PgBouncer's transaction pooling, which can hand your next transaction a different server connection. Qualify names, or set the path inside each transaction with SET LOCAL.
Check yourself
USAGE on the schema. Run GRANT USAGE ON SCHEMA reporting TO analyst;. Table privileges don't help until the role can reach the schema.
The migration's session had public first on its search_path, and the CREATE TABLE used an unqualified name. Qualify names in migrations, for example CREATE TABLE commerce.orders.
That grant only covers tables that existed when you ran it. Add ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO reporting_ro, run by the role that creates the tables.
Before PostgreSQL 15, every user could create objects in public by default, so one user could create a function or table that shadowed another's through search_path. Since 15, only the database owner can create there by default. Keep each application's objects in its own schema and grant access explicitly.
It tells PostgreSQL where to look for unqualified names in that session: first commerce, then public. A query for orders then finds commerce.orders. Setting it per role with ALTER ROLE ... SET search_path is safer than relying on each client to set it.