Schemas and Namespaces
In PostgreSQL, a schema is a namespace inside a single database that groups tables, views, functions, and other objects, letting two objects with the same name (e.g. public.orders and archive.orders) coexist without collision. This is a different concept from a 'database' in the MySQL sense -- a PostgreSQL database is a hard isolation boundary you cannot query across without extensions like postgres_fdw or dblink, while schemas within one database can be freely joined together in a single query, making schemas the right tool for organizing related data that still needs to interoperate.
Cricket analogy: A schema is like separate sections of one team's scorebook -- the 'batting' section and 'bowling' section share the same book (database) and can be cross-referenced on one page, unlike two entirely separate scorebooks for two different tournaments.
The search_path and Object Resolution
When a query references an unqualified object name like orders, PostgreSQL resolves it by checking each schema listed in the search_path setting, in order, until it finds a matching object -- the default search_path is typically '"$user", public', meaning it first checks a schema matching the current username, then falls back to public. This resolution order is exactly why schema-based multi-tenancy works: each tenant's session can set search_path to their own schema, and identical application code referencing unqualified table names will transparently operate on that tenant's data without any query changes.
Cricket analogy: The search_path is like a substitute umpire's checklist -- first check the home ground's rulebook, then fall back to the ICC's general rulebook if the home ground has no specific rule, resolving ambiguity in a fixed order.
Multi-Tenant Isolation with Schemas
A common architecture pattern is 'schema-per-tenant', where each customer gets their own schema (tenant_acme, tenant_globex) containing identical table structures, offering stronger data isolation than a shared table with a tenant_id column while still sharing one physical database, connection pool, and backup process. This scales well up to hundreds of tenants but becomes unwieldy at thousands, since each schema multiplies catalog metadata and makes cross-tenant migrations (like adding a column to every tenant's orders table) an operation that must be scripted and run once per schema rather than once globally.
Cricket analogy: Schema-per-tenant is like giving each franchise in the IPL its own separate locker room within one shared stadium -- Mumbai Indians and Chennai Super Kings each keep their gear isolated, but they share the same stadium infrastructure and groundskeeping staff.
-- Create per-tenant schemas and set search_path per session
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
CREATE TABLE tenant_acme.orders (id serial PRIMARY KEY, total numeric(10,2));
CREATE TABLE tenant_globex.orders (id serial PRIMARY KEY, total numeric(10,2));
-- Application connects and sets search_path per tenant session
SET search_path TO tenant_acme, public;
SELECT * FROM orders; -- resolves to tenant_acme.orders
-- Grant scoped access so tenant_acme's role can't see tenant_globex
GRANT USAGE ON SCHEMA tenant_acme TO acme_app_role;
GRANT ALL ON ALL TABLES IN SCHEMA tenant_acme TO acme_app_role;
REVOKE ALL ON SCHEMA tenant_globex FROM acme_app_role;Never rely on search_path alone as a security boundary -- a role with CREATE privileges on any schema earlier in the search_path can shadow objects and hijack queries. Combine schema isolation with proper GRANT/REVOKE privileges, and consider setting search_path explicitly and defensively inside SECURITY DEFINER functions.
You can always fully qualify an object with schema.table (e.g. tenant_acme.orders) to bypass search_path resolution entirely -- this is the safest approach inside functions, views, or any code where ambiguity would be a correctness risk.
- A schema is a namespace within one database; a database is a hard isolation boundary you cannot join across.
- Unqualified object names are resolved by searching each schema in search_path, in order.
- The default search_path is typically "$user", public.
- Schema-per-tenant offers stronger isolation than a shared tenant_id column while sharing one physical database.
- Schema-per-tenant becomes unwieldy at very large tenant counts due to catalog metadata growth and per-schema migrations.
- search_path is not a security boundary by itself -- always pair it with explicit GRANT/REVOKE privileges.
- Fully qualifying object names (schema.table) avoids ambiguity in functions and views.
Practice what you learned
1. What is the key difference between a PostgreSQL schema and a PostgreSQL database?
2. What does the search_path setting control?
3. What is a key advantage of schema-per-tenant over a shared table with a tenant_id column?
4. Why is search_path alone considered an insufficient security boundary?
5. What is the safest way to reference an object inside a function or view to avoid search_path ambiguity?
Was this page helpful?
You May Also Like
Constraints in Depth
A thorough look at PostgreSQL's constraint types -- PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, NOT NULL, and EXCLUDE -- and how they enforce data integrity at the database layer.
PostgreSQL Architecture Overview
A tour of how PostgreSQL is built internally: the process model, shared memory, the write-ahead log, and the MVCC storage engine that together deliver durability and concurrency.
Data Types in Depth
A practical deep dive into PostgreSQL's rich type system, covering numeric precision, text vs. varchar, JSONB, arrays, and enum/domain types.