Schemas

In PostgreSQL, a schema is a namespace inside a database that groups related tables, views, functions, and other objects together. A single database can contain many schemas, and two schemas in the same database can even have tables with the identical name without colliding — they simply live in different namespaces.

A quick terminology clarification

In everyday conversation, people sometimes use “schema” loosely to mean “the overall structure of a database” — its tables, columns, and relationships. In PostgreSQL specifically, schema is also a concrete, literal database object you can create, drop, and grant permissions on. Both meanings coexist in this series; context makes clear which one is meant.

The default public schema

Every new database automatically gets one schema called public. Unless you say otherwise, every table you create lands inside public — which is exactly why beginners can use PostgreSQL for a long time without ever noticing schemas exist at all.

These two statements are equivalent

SQL

CREATE TABLE customers (...);
CREATE TABLE public.customers (...);

Creating a schema

SQL

CREATE SCHEMA sales;

Once created, you can put tables inside it just like any other schema:

SQL

CREATE TABLE sales.orders (
    order_id   SERIAL PRIMARY KEY,
    amount     NUMERIC(10, 2) NOT NULL
);

Fully-qualified names

To refer to a table unambiguously — including from another schema or another session with different settings — prefix it with its schema name:

SQL

SELECT * FROM sales.orders;
SELECT * FROM public.orders;

This is what makes it safe to have, say, both sales.orders and archive.orders in the same database — the schema prefix disambiguates them completely.

The search_path

When you write a bare table name with no schema prefix, PostgreSQL looks it up using the search_path setting — an ordered list of schemas it checks, in order, until it finds a match.

Viewing and setting the search path

SQL

SHOW search_path;
-- typically: "$user", public

SET search_path TO sales, public;

search_path	SELECT * FROM orders resolves to
"$user", public	public.orders (assuming no schema named after the user exists)
sales, public	sales.orders, if it exists — otherwise public.orders

Why schemas are useful

Multi-tenant applications — give each tenant its own schema (e.g. tenant_123.orders) inside one shared database, isolating their data logically
Organizing large databases — group tables by module or team (billing, inventory, analytics) instead of one flat list of hundreds of tables
Permission boundaries — grant a role access to an entire schema at once, rather than table by table
Avoiding name collisions — third-party extensions or generated tooling can live in their own schema without risk of clashing with your application's tables