Schemas & Search Path

A schema is a namespace within a PostgreSQL database that groups tables, views, functions, and other objects. Schemas let you organize a large database into logical sections, support multiple applications in one database, and control access at a fine-grained level.

What Is a Schema?

Think of a database as a filing cabinet and schemas as the drawers. Each drawer (schema) can hold tables, views, sequences, and functions with the same name as objects in other drawers — they won't conflict.

Database: myapp
├── public (default schema)
│   ├── users
│   ├── orders
│   └── products
├── analytics
│   ├── users (different table, same name — no conflict!)
│   ├── daily_stats
│   └── funnels
└── internal
    ├── audit_log
    └── config

The public Schema

Every new database starts with a public schema. This is where tables go by default if you don't specify a schema name. In PostgreSQL 15+, only the owner has CREATE privilege on public by default (changed from earlier versions).

-- These two are equivalent (public is the default schema)
CREATE TABLE users (...);
CREATE TABLE public.users (...);

Creating and Using Schemas

-- Create a schema
CREATE SCHEMA analytics;

-- Create a schema owned by a specific user
CREATE SCHEMA reporting AUTHORIZATION alice;

-- Create a table in a specific schema
CREATE TABLE analytics.daily_stats (
    date        DATE,
    page_views  INTEGER,
    unique_users INTEGER
);

-- Query a table with its schema-qualified name
SELECT * FROM analytics.daily_stats WHERE date = CURRENT_DATE;

-- Create objects in the schema without full qualification
SET search_path = analytics;
CREATE TABLE funnels (...);  -- goes into analytics schema

The search_path

When you write a table name without a schema prefix, PostgreSQL searches the schemas in search_path order until it finds the table.

-- See the current search_path
SHOW search_path;
-- "$user",public
-- $user means "look for a schema with the current username first"

-- Set search_path for the current session
SET search_path = analytics, public;

-- Now these are equivalent:
SELECT * FROM daily_stats;           -- found in analytics
SELECT * FROM analytics.daily_stats; -- explicit, always works

Setting search_path Permanently

-- For a specific database (affects all users of that database)
ALTER DATABASE myapp SET search_path = analytics, public;

-- For a specific user
ALTER ROLE alice SET search_path = analytics, public;

-- For the current session only (reverts when session ends)
SET search_path = analytics, reporting, public;

Schemas vs Databases

This is a common source of confusion:

-- Cross-schema query (easy — same connection)
SELECT u.name, a.page_views
FROM public.users u
JOIN analytics.daily_stats a ON a.user_id = u.id;

-- Cross-database query (not directly possible without FDW or DBLINK)

Listing and Inspecting Schemas

-- In psql
\dn          -- list schemas
\dn+         -- with permissions

-- In SQL
SELECT schema_name, schema_owner
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
ORDER BY schema_name;

-- List all tables across all schemas
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name;

Permissions on Schemas

Schemas have two key permissions:

• USAGE — can reference objects in the schema (needed to query tables)
• CREATE — can create new objects in the schema

-- Grant usage on a schema (allows seeing and querying tables)
GRANT USAGE ON SCHEMA analytics TO reporting_user;

-- Grant usage + create (developer role)
GRANT USAGE, CREATE ON SCHEMA analytics TO dev_role;

-- Grant access to all current tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO reporting_user;

-- Grant access to future tables too (default privileges)
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
GRANT SELECT ON TABLES TO reporting_user;

-- Revoke create on public for safety (good practice in production)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Schema Design Patterns

Multi-Tenant with Schemas

Each tenant gets their own schema (row-level security is usually better at scale, but schemas work for smaller deployments):

-- Create a schema per tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Each has its own tables
CREATE TABLE tenant_acme.users (...);
CREATE TABLE tenant_globex.users (...);

-- Application sets search_path per connection based on logged-in tenant
SET search_path = tenant_acme;
SELECT * FROM users;  -- automatically uses tenant_acme.users

Environment Separation in One Database

CREATE SCHEMA staging;
CREATE SCHEMA production;

-- Deploy schema changes to staging first, then production
-- Both share the same PostgreSQL instance but are isolated

API Versioning

CREATE SCHEMA api_v1;
CREATE SCHEMA api_v2;

CREATE VIEW api_v1.customers AS SELECT id, name, email FROM public.customers;
CREATE VIEW api_v2.customers AS SELECT id, name, email, phone FROM public.customers;

Dropping Schemas

-- Drop empty schema
DROP SCHEMA analytics;

-- Drop schema and everything in it (DANGEROUS!)
DROP SCHEMA analytics CASCADE;

-- Drop only if it exists (no error if missing)
DROP SCHEMA IF EXISTS analytics CASCADE;

Using pg_catalog and information_schema

PostgreSQL has two built-in system schemas:

• pg_catalog — PostgreSQL system tables (pg_class, pg_attribute, etc.)
• information_schema — SQL-standard views for metadata (tables, columns, constraints)

-- Find all columns of a table using information_schema
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'users'
ORDER BY ordinal_position;

-- Find all indexes on a table using pg_catalog
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename  = 'users';