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.
//
Search across all documentation pages
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.
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.
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:
search_path order determines which object wins for unqualified names| 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 |
Hundreds are fine. Complexity comes from grants and migration order, not OID cost.
Schema names are per database. Same name in different DBs is unrelated.
A placeholder for a schema matching the role name; often empty.
Many installs use public. Some teams dedicate an extensions schema.
Yes for types and functions resolved for unqualified calls.
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+.
Reviewed by Chris St. John·Last updated Jul 18, 2026