Database, Schema & Search Path
A database is an isolated catalog of schemas. A schema namespaces tables and functions. search_path controls how unqualified names resolve. Production bugs often come from ambiguous paths, not missing tables.
Recipe
SHOW search_path;
ALTER ROLE app_user SET search_path = app, public;
-- Prefer explicit qualification in migrations
CREATE TABLE app.orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES app.customers(id)
);When to reach for this: You design multi-app databases, shared clusters, or role-specific defaults.
Working Example
CREATE SCHEMA billing AUTHORIZATION postgres;
CREATE SCHEMA reporting AUTHORIZATION postgres;
CREATE TABLE billing.invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);
CREATE VIEW reporting.invoice_totals AS
SELECT date_trunc('month', now()) AS month, sum(amount) AS total
FROM billing.invoices;
SET search_path = reporting, billing;
SELECT * FROM invoice_totals;What this demonstrates:
- Multiple schemas coexist in one database with cross-schema references
- Views can live in a different schema than base tables
search_pathorder determines which object wins for unqualified names
Deep Dive
Resolution Order
- PostgreSQL checks schemas listed in search_path left to right.
- Temporary tables live in pg_temp schemas prepended for the session.
- System catalogs are always reachable via pg_catalog.
Role and Database Defaults
- ALTER DATABASE ... SET search_path applies on connect unless role overrides.
- ALTER ROLE ... SET search_path is common for application roles.
- Migrations should set an explicit path at the top: SET search_path = app;
Security Note
- Objects in public are creatable by default on many installs (PUBLIC CREATE privilege).
- Lock down public or use per-app schemas plus least-privilege grants.
Gotchas
- public is a footgun - Any role can create objects that shadow app tables.. Fix: REVOKE CREATE ON SCHEMA public FROM PUBLIC; use dedicated schemas.
- Duplicate unqualified names - app.users and archive.users both named users.. Fix: Schema-qualify in SQL sent by apps.
- search_path in SECURITY DEFINER functions - Definer rights plus mutable path enables hijack.. Fix: SET search_path = pg_temp, pg_catalog in definer functions.
- Dump/restore order - Schemas must exist before objects that reference them.. Fix: Use pg_dump single transaction or ordered DDL tools.
- Case folding - Unquoted identifiers fold to lower case.. Fix: Quote only when you must; prefer snake_case.
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
| One schema per microservice | Clear ownership in shared DB | Many schemas to grant and migrate |
| Database per service | Hard isolation | More connections and ops overhead |
| Row-level security | Shared tables with tenant_id | Policy complexity and perf testing |
FAQs
How many schemas is too many?
Hundreds are fine. Complexity comes from grants and migration order, not OID cost.
Can two databases share a schema name?
Schema names are per database. Same name in different DBs is unrelated.
What is the $user schema?
A placeholder for a schema matching the role name; often empty.
Should extensions live in public?
Many installs use public. Some teams dedicate an extensions schema.
Does search_path affect operators?
Yes for types and functions resolved for unqualified calls.
Related
- PostgreSQL Basics - create schema and table
- Roles Basics - role-level GUC defaults
- SECURITY DEFINER Risks - search_path traps
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.