🗂️Schema

A schema is a namespace, not a table

A schema groups related tables, views, functions, and types under one prefix (core.patients, ingest.raw_reads). It is how you separate bounded contexts inside the same database while sharing roles, backups, and connection limits. Permissions are often granted at schema granularity, plan that when onboarding a new team.

🧭What lives in a schema (and what does not)

Indexes belong to tables, but their names still collide globally, pick prefixes per domain.

Base tables & views

First-class objects you reference in FROM. Views are stored queries, still schema-bound and permissioned like tables.

Indexes & constraints

Declared on tables but show up in catalog listings; migrations must create/drop them in an order that respects FK dependencies.

Functions / procedures

Optional but common for shared validation logic. Keep ownership and search_path explicit, security definer routines are footguns when schemas move.

Object	Lives in schema?	Typical naming
Base table	Yes	core.patients
View	Yes	reporting.v_daily_kpis
Index	Yes (scoped to table)	idx_patients_mrn

Relying on implicit search_path

A migration runs fine in dev where search_path=public, then fails or worse hits the wrong object in prod because an extension shadowed your table name.

Fully qualify in migrations + CI

Prefix every DDL/DML object in checked-in SQL with schema.table. Set explicit role defaults (ALTER ROLE … SET search_path) for humans, not for robots.

SQL

List schemas

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema', 'pg_catalog')
ORDER BY schema_name;

✓Illustrative result set

schema_name
core
ingest
reporting

SQL

Qualified table reference

SELECT COUNT(*) AS open_batches
FROM ingest.sequencing_batches
WHERE status = 'OPEN';

✓Illustrative result set

open_batches
14

Multi-tenant hint

Putting each tenant in its own schema is one isolation pattern; another is shared tables with tenant_id plus row-level security. The schema approach trades operational overhead (migrations × N) for harder cross-tenant mistakes, choose consciously.

TL;DR

A schema is a namespace inside a database grouping related tables, views, functions, and grants (CREATE SCHEMA / SET search_path in PostgreSQL, dbo in SQL Server). It organizes ownership and permissions, not physical storage by itself. Use schemas to separate bounded contexts or tenants when operational isolation warrants separate migration streams.