Schemas in PostgreSQL

In PostgreSQL, a schema is a namespace inside a database that groups tables, views, functions, and other objects together. This is a level of organization that sits between the database itself and individual objects — a single database can contain many schemas, and the same table name (like "users") can exist in two different schemas without conflict.

Schemas are frequently confused with databases by newcomers, but they serve a different purpose: they let you organize objects within one database (for example, separating "app" data from "reporting" views or "audit" logs) rather than isolating entirely separate databases from each other.

Why it matters

As a database grows, a single flat list of hundreds of tables becomes hard to manage. Schemas are namespaces inside a database: they group related tables, let two tables share a name, and are the unit on which permissions are commonly granted. Multi-tenant systems and applications with several modules use them constantly.

The public Schema

Every new PostgreSQL database is created with a default schema named public. When you run CREATE TABLE authors (...) without specifying a schema, PostgreSQL creates it as public.authors. This is why beginners rarely notice schemas at all — everything quietly lives in public until a project grows large enough to need separation.

Creating and Using Schemas

You create a schema with CREATE SCHEMA schema_name; and reference objects inside it with dot notation, such as reporting.monthly_sales. This lets teams logically separate concerns — for example, an auth schema for user/session tables, an app schema for business data, and an audit schema for change logs — all within a single database, with permissions grantable per schema.

search_path and Name Resolution

When you write an unqualified table name like SELECT * FROM orders, PostgreSQL looks through the schemas listed in the search_path setting, in order, until it finds a matching table. The default search_path is typically "$user", public. You can view or change it with SHOW search_path; and SET search_path TO app, public;. Understanding search_path matters because it silently determines which of several same-named tables in different schemas actually gets queried.

Example

CREATE SCHEMA reporting;

CREATE TABLE reporting.monthly_sales (
    month       DATE PRIMARY KEY,
    total_sales NUMERIC(12,2)
);

SHOW search_path;

SET search_path TO reporting, public;
SELECT * FROM monthly_sales;  -- resolves to reporting.monthly_sales

CREATE SCHEMA
CREATE TABLE
   search_path
-----------------
 "$user", public
SET
 month | total_sales
-------+-------------
(0 rows)

Exercise

This exercise uses PostgreSQL schemas and search_path, so it needs a PostgreSQL server. Create a schema named sales and a table orders inside it. Insert a row using the fully qualified name. Then change the search path so that sales is searched first, and select from orders without the schema prefix.

Expected result: the final SELECT finds sales.orders without the prefix and returns the inserted row.

-- TODO: create the schema sales

-- TODO: create sales.orders (id integer primary key, total numeric)

-- TODO: insert a row using the qualified name

-- TODO: put sales first in the search path

-- TODO: select from orders without the prefix

CREATE SCHEMA sales;

CREATE TABLE sales.orders (
  id INTEGER PRIMARY KEY,
  total NUMERIC(10, 2)
);

INSERT INTO sales.orders (id, total) VALUES (1, 250.00);

SET search_path TO sales, public;

SELECT id, total FROM orders;

Quick quiz

Interview questions