PostgreSQL schemas explained: namespaces and search_path

PostgreSQL Schemas

A schema is a namespace inside a database. It lets you organize tables, avoid name collisions, and implement multi-tenancy.

Create and use a schema

CREATE SCHEMA analytics;

-- Schema-qualified reference
SELECT * FROM analytics.events;

-- Create table in a specific schema
CREATE TABLE analytics.page_views (...);

search_path

-- Show current path
SHOW search_path;
-- "$user", public

-- Change it
SET search_path TO analytics, public;

-- Permanent for a user
ALTER ROLE analyst SET search_path TO analytics, public;

PostgreSQL searches schemas in search_path order when you write an unqualified table name. The default path checks a schema matching your username, then public. Use explicit schema qualification in production code to avoid ambiguity.

Schema per tenant

CREATE SCHEMA tenant_42;
CREATE TABLE tenant_42.users (...);
SET search_path TO tenant_42;

Schema-per-tenant isolation is a common multi-tenancy pattern in PostgreSQL. Each tenant gets identical table structures in their own schema. Maintenance is simpler than row-level isolation but harder to scale past a few hundred tenants.