23 Schemas and search_path

A schema in PostgreSQL is a namespace inside a database — a way to group tables, views, functions, sequences, and types under a name. Every database has at least one (public), and most production deployments use several. Schemas are how you organize a large project, host multiple tenants in one database, and isolate extensions from application code. Once you understand search_path, you understand how PostgreSQL resolves unqualified names.

What you’ll learn

• Creating and dropping schemas
• The schema.table qualified-name syntax
• The search_path and how name resolution works
• Per-role and per-database search_path defaults
• Common patterns: an app schema, multi-tenant schemas, separating extensions

Example

-- Create a schema and a table inside it
CREATE SCHEMA IF NOT EXISTS app;

DROP TABLE IF EXISTS app.events_example;
CREATE TABLE app.events_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind text NOT NULL,
  at   timestamptz NOT NULL DEFAULT current_timestamp
);

INSERT INTO app.events_example (kind) VALUES ('login'), ('logout');

-- Fully qualified vs. unqualified access
SELECT * FROM app.events_example ORDER BY id;

-- Make the schema findable without prefixing every name
SET search_path TO app, public;
SELECT * FROM events_example ORDER BY id;   -- resolved as app.events_example

-- Inspect the current search path
SHOW search_path;

-- List schemas and the tables in each
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename
LIMIT 10;

-- Reset to default
SET search_path TO public;

When you write SELECT * FROM events, PostgreSQL looks up the name in each schema listed in search_path, in order, and uses the first match. The default search_path is "$user", public — meaning “first look in a schema named after the connecting user, then public.” If a schema with the user’s name exists, it wins.

You can change the search_path per session (SET search_path TO ...), per role (ALTER ROLE alice SET search_path TO ...), or per database (ALTER DATABASE app_db SET search_path TO ...). The last two persist across reconnections.

Schema separation is the standard way to keep extension objects (postgis, pg_trgm) out of public, to give each tenant its own namespace, or to logically group audit.*, app.*, staging.* tables.

Run the example

To run:

$ psql -f source/schemas.sql postgres
CREATE SCHEMA
DROP TABLE
CREATE TABLE
INSERT 0 2
 id |  kind  |              at
----+--------+-------------------------------
  1 | login  | 2024-xx-xx xx:xx:xx.xxxxxx+00
  2 | logout | 2024-xx-xx xx:xx:xx.xxxxxx+00
(2 rows)
 ...
 search_path
-------------
 app, public
(1 row)
 ...

Common pitfalls

• Forgetting search_path in a function: a SECURITY DEFINER function uses its owner’s privileges but, without a function-level setting, the caller’s search_path. Set a path containing only trusted schemas, with pg_temp last (for example, SET search_path = pg_catalog, app, pg_temp when app is trusted), and qualify sensitive object names.
• Putting extensions in public: extensions install their objects in whatever schema is current. Many teams CREATE SCHEMA extensions; ALTER ROLE ... SET search_path = "$user", public, extensions; and create extensions there.
• Dropping a schema with dependencies fails. Use DROP SCHEMA name CASCADE to drop the schema and its dependent objects. It can be rolled back inside a transaction; after commit, recovery requires a backup or recreation.
• Forgetting that \dt only shows tables in the current search_path schemas. Use \dt *.* to see everything.

Downloads

SQL file: schemas.sql · All lesson files · Setup guide