Schemas

Organise database objects into namespaces called schemas for better structure and access control.

Overview

A schema is a named namespace inside a database that contains tables, views, functions, sequences, and other objects. Every PostgreSQL database starts with a public schema, and all objects created without specifying a schema land there by default. Schemas let you organise objects logically (e.g., by team or feature area) and control access at the namespace level. The search_path setting controls which schemas PostgreSQL looks in when you reference an object without a schema prefix. By default the search_path is "$user", public so PostgreSQL first looks in a schema matching your username, then in public. You can query objects in any schema by prefixing with the schema name: schema_name.table_name. Schemas are also a common multi-tenancy strategy: give each tenant their own schema with identical table structures, then control access using search_path and privileges on the schema.

Key Points

•CREATE SCHEMA myschema creates a new namespace in the current database
•Default schema is "public" all objects go here unless specified otherwise
•Prefix object names with schema to disambiguate: myschema.users
•SET search_path TO myschema, public; controls schema lookup order
•DROP SCHEMA myschema CASCADE drops the schema and all objects in it
•Schemas enable multi-tenancy: one schema per tenant, shared database

Examples

Create schemas and objects inside them

-- Create a schema for an analytics module
CREATE SCHEMA analytics;

-- Create a table in that schema
CREATE TABLE analytics.events (
  id         BIGSERIAL PRIMARY KEY,
  event_type TEXT NOT NULL,
  occurred_at TIMESTAMPTZ DEFAULT NOW()
);

-- Query with full schema prefix
SELECT * FROM analytics.events;

Set and use search_path

-- Set the search path for this session
SET search_path TO analytics, public;

-- Now we can omit the schema prefix
SELECT * FROM events;

-- Show current search_path
SHOW search_path;

Common Mistakes

Forgetting to grant USAGE on the schema even if a user has table privileges, they need GRANT USAGE ON SCHEMA myschema TO user to look up objects in it.

Schemas

Overview

Key Points

Examples

Common Mistakes

Exercises

Knowledge Check

What is the default schema in PostgreSQL where objects are created unless otherwise specified?