Creating and switching schemas in PostgreSQL - Mechanics & Internals

Overview - Creating and switching schemas

What is it?

A schema in PostgreSQL is like a folder inside a database that holds tables and other objects. Creating a schema means making a new folder to organize your data. Switching schemas means telling the database which folder to look in by default when you run commands. This helps keep data organized and avoids name conflicts.

Why it matters

Without schemas, all tables and objects would be in one big space, making it hard to manage and find things. Schemas let you separate data logically, like keeping personal files in different folders on your computer. This organization is crucial for teamwork, security, and managing large databases efficiently.

Where it fits

Before learning schemas, you should understand basic database concepts like tables and databases themselves. After schemas, you can learn about permissions, advanced querying, and database design patterns that use schemas for multi-tenant applications.

Mental Model

Core Idea

Schemas are named containers inside a database that organize and separate database objects to avoid conflicts and improve management.

Think of it like...

Think of a schema as a folder on your computer where you keep related files. Creating a schema is like making a new folder, and switching schemas is like opening that folder to work with its files by default.

Database
├── Schema1 (folder)
│   ├── TableA
│   └── TableB
├── Schema2 (folder)
│   ├── TableC
│   └── TableD
└── Schema3 (folder)
    └── TableE

Switching schema means setting which folder you are currently 'inside' to find tables without typing full paths.

Build-Up - 7 Steps

1

Foundation

What is a schema in PostgreSQL

Concept: Introduce the idea of schemas as containers inside a database.

A schema is a way to group tables and other database objects inside a database. It helps organize data and avoid name clashes. By default, PostgreSQL has a schema called 'public' where tables are created if no schema is specified.

Result

You understand that schemas are like folders inside a database that hold tables and other objects.

Understanding schemas as containers helps you see how databases stay organized and scalable.

2

Foundation

How to create a schema

Concept: Learn the SQL command to create a new schema.

Use the command: CREATE SCHEMA schema_name; For example, CREATE SCHEMA sales; This makes a new schema named 'sales' inside your database.

Result

A new schema named 'sales' is created and ready to hold tables and other objects.

Knowing how to create schemas lets you start organizing your database logically.

3

Intermediate

Creating tables inside a schema

Concept: Learn how to create tables inside a specific schema.

When creating a table, specify the schema name before the table name: CREATE TABLE sales.customers (id SERIAL PRIMARY KEY, name TEXT); This creates the 'customers' table inside the 'sales' schema.

Result

The table 'customers' is created inside the 'sales' schema, not in the default 'public' schema.

Specifying the schema when creating tables helps keep data organized and avoids name conflicts.

4

Intermediate

Switching schemas with search_path

🤔Before reading on: Do you think switching schemas changes the database or just the default schema for commands? Commit to your answer.

Concept: Learn how to change the default schema for your session using search_path.

PostgreSQL uses a setting called search_path to decide which schemas to look in by default. You can change it with: SET search_path TO sales; Now, when you run SELECT * FROM customers;, PostgreSQL looks for 'customers' in the 'sales' schema first.

Result

Your session now uses 'sales' as the default schema, so you don't need to type 'sales.' before table names.

Changing search_path lets you work inside a schema without typing its name every time, making queries simpler and less error-prone.

5

Intermediate

Viewing current search_path and schemas

Concept: Learn how to check which schemas are in your search path and what schemas exist.

Run SHOW search_path; to see your current default schema order. Use \dn in psql to list all schemas in the database. This helps you understand where PostgreSQL looks for tables.

Result

You see the list of schemas PostgreSQL searches by default and all schemas available in the database.

Knowing your search_path and existing schemas helps avoid confusion about which tables you are accessing.

6

Advanced

Schema usage in multi-tenant applications

🤔Before reading on: Do you think using schemas for tenants means separate databases or shared database? Commit your guess.

Concept: Learn how schemas can isolate data for different users or clients in the same database.

In multi-tenant apps, each tenant can have its own schema to keep data separate but still share the same database. For example, tenant1.customers and tenant2.customers are different tables in different schemas. This allows easy data separation and management.

Result

You understand schemas can be used to isolate data for different clients without needing multiple databases.

Using schemas for tenants balances isolation and resource sharing, improving scalability and management.

7

Expert

Search_path pitfalls and schema security

🤔Before reading on: Do you think setting search_path affects only your session or all users? Commit your answer.

Concept: Understand how search_path can cause security risks and unexpected behavior if not managed carefully.

Search_path is session-specific but can be set in user roles or functions. If a malicious user creates a schema with the same name as a system schema and tricks search_path, they can hijack queries. Always set search_path explicitly in secure environments and avoid relying on defaults.

Result

You learn that careless use of search_path can lead to security vulnerabilities and bugs.

Knowing search_path risks helps you write safer database code and avoid subtle security holes.

Under the Hood

PostgreSQL stores schemas as namespaces inside a database. Each schema has its own set of object names. When you run a query, PostgreSQL checks schemas in the order of the search_path to find the object. This allows multiple objects with the same name in different schemas without conflict. The search_path is a list of schema names that PostgreSQL checks one by one.

Why designed this way?

Schemas were designed to organize database objects logically and avoid name collisions. The search_path mechanism allows flexible default schema selection per session or user. This design balances isolation and convenience, letting multiple applications or users share a database without interfering with each other.

Database
┌─────────────────────────────┐
│          Database           │
│ ┌───────────────┐          │
│ │   Schema A    │          │
│ │ ┌───────────┐ │          │
│ │ │ Table X   │ │          │
│ │ └───────────┘ │          │
│ └───────────────┘          │
│ ┌───────────────┐          │
│ │   Schema B    │          │
│ │ ┌───────────┐ │          │
│ │ │ Table X   │ │          │
│ │ └───────────┘ │          │
│ └───────────────┘          │
└─────────────────────────────┘

Query: SELECT * FROM Table X;
Search_path: Schema B, Schema A
Result: Table X from Schema B is used.

Myth Busters - 4 Common Misconceptions

Quick: Does creating a schema automatically switch your session to it? Commit yes or no.

Common Belief: Creating a schema automatically makes it the default schema for your session.

Reality: Creating a schema only creates it; your session's search_path does not change automatically. You must set search_path explicitly to switch.

Why it matters: Assuming automatic switching can cause queries to fail or use wrong tables, leading to bugs and confusion.

Quick: Do you think schemas are the same as databases? Commit your answer.

Common Belief: Schemas and databases are the same things, just different names.

Reality: Schemas are containers inside a single database, not separate databases. Databases are higher-level containers that hold schemas.

Why it matters: Confusing schemas with databases can lead to wrong architecture decisions and misunderstanding of data isolation.

Quick: Does changing search_path affect all users connected to the database? Commit yes or no.

Common Belief: Changing search_path changes it for all users and sessions.

Reality: search_path is session-specific or role-specific; changing it affects only your current session or configured roles, not all users.

Why it matters: Misunderstanding this can cause unexpected behavior when multiple users share the database.

Quick: Can you rely on the default 'public' schema to always be safe and secure? Commit your answer.

Common Belief: The 'public' schema is always secure and should be used for all tables.

Reality: The 'public' schema is accessible by default to all users, which can be a security risk. It's better to create and use specific schemas with controlled permissions.

Why it matters: Ignoring schema permissions can expose sensitive data or allow unauthorized changes.

Expert Zone

1

The order of schemas in search_path matters deeply; the first matching object is used, which can cause subtle bugs if schemas have overlapping object names.

2

Functions and stored procedures can have their own search_path settings, which override session settings and affect object resolution inside them.

3

Schema ownership and permissions are separate; a user can create a schema but must also manage who can create or access objects inside it.

When NOT to use

Schemas are not a substitute for full database isolation when strict security or resource separation is needed. In such cases, use separate databases or even separate servers. Also, avoid using schemas to store unrelated data just to save on database count; it can complicate maintenance.

Production Patterns

In production, schemas are used to separate environments (like dev, test, prod), tenants in SaaS apps, or modules in large applications. Teams often set search_path per user role to simplify queries. Schema migrations and versioning tools manage schema changes carefully to avoid downtime.

Connections

Namespaces in programming

Schemas in databases are like namespaces in programming languages that group related code to avoid name conflicts.

Understanding namespaces helps grasp why schemas prevent object name clashes and organize database objects logically.

File system directories

Schemas function like directories in a file system, organizing files (tables) into folders (schemas) for easier management.

Knowing how file systems organize data helps understand schema purpose and usage in databases.

Access control and security

Schemas are a key part of database security, controlling who can see or modify which objects.

Understanding schemas aids in designing secure databases by limiting access at the schema level.

Common Pitfalls

#1 Assuming queries use the newly created schema without setting search_path.

Wrong approach:

CREATE SCHEMA sales;
CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);

Correct approach:

CREATE SCHEMA sales;
CREATE TABLE sales.customers (id SERIAL PRIMARY KEY, name TEXT);

Root cause:

Not specifying the schema when creating tables causes them to be created in the default 'public' schema, not the intended one.

#2 Setting search_path globally without understanding session scope.

Wrong approach:

ALTER DATABASE mydb SET search_path TO sales;

Correct approach:

SET search_path TO sales; -- for current session
-- or configure per user role for controlled access

Root cause:

Misunderstanding that ALTER DATABASE affects only new sessions and may not apply immediately or to all users.

#3 Using the same table name in multiple schemas without managing search_path order.

Wrong approach:

CREATE TABLE sales.customers (...);
CREATE TABLE marketing.customers (...);
SET search_path TO sales, marketing;
SELECT * FROM customers;

Correct approach:

SET search_path TO sales;
SELECT * FROM customers; -- clearly uses sales.customers
-- or specify schema explicitly: SELECT * FROM marketing.customers;

Root cause:

Ambiguity in object resolution when multiple schemas have objects with the same name and search_path includes both.

Key Takeaways

Schemas are containers inside a database that organize tables and other objects to avoid name conflicts and improve management.

Creating a schema does not switch your session to it; you must set the search_path to change the default schema for your queries.

The search_path setting controls which schemas PostgreSQL looks in and in what order, affecting how object names are resolved.

Using schemas wisely helps isolate data, manage permissions, and support complex applications like multi-tenant systems.

Mismanaging schemas or search_path can cause security risks, bugs, and confusion, so understanding their behavior is crucial for safe database design.

Practice

1. What is the main purpose of creating schemas in PostgreSQL?

easy

A. To encrypt the database tables

B. To speed up query execution automatically

C. To create backups of the database

D. To group database objects and organize them logically

Solution

1. Step 1: Understand what schemas do

   Schemas help organize tables, views, and other objects into groups.

2. Step 2: Identify the main purpose

   This organization helps keep the database tidy and manageable.

3. Final Answer:

   To group database objects and organize them logically -> Option D

4. Quick Check:

   Schemas = Organize objects [OK]

Hint: Schemas group objects to keep your database tidy [OK]

Common Mistakes:

• Thinking schemas speed up queries automatically
• Confusing schemas with backups
• Assuming schemas encrypt data

2. Which of the following is the correct syntax to create a new schema named sales?

easy

A. NEW SCHEMA sales;

B. CREATE SCHEMA sales;

C. MAKE SCHEMA sales;

D. CREATE DATABASE sales;

Solution

1. Step 1: Recall the command to create schemas

   The correct SQL command is CREATE SCHEMA schema_name;.

2. Step 2: Match the syntax

   CREATE SCHEMA sales; matches this syntax exactly for schema sales.

3. Final Answer:

   CREATE SCHEMA sales; -> Option B

4. Quick Check:

   CREATE SCHEMA = create schema [OK]

Hint: Use CREATE SCHEMA followed by the schema name [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Confusing CREATE SCHEMA with CREATE DATABASE
• Missing the semicolon at the end

3. Given the following commands:

CREATE SCHEMA marketing;
SET search_path TO marketing;
CREATE TABLE campaigns (id SERIAL PRIMARY KEY, name TEXT);

What schema will the table campaigns belong to?

medium

A. marketing

B. None, it will cause an error

C. default

D. public

Solution

1. Step 1: Understand SET search_path effect

   Setting search_path to marketing means new objects are created in that schema by default.

2. Step 2: Check where the table is created

   The CREATE TABLE campaigns command creates the table inside the current schema in search_path, which is marketing.

3. Final Answer:

   marketing -> Option A

4. Quick Check:

   SET search_path = schema for new objects [OK]

Hint: SET search_path sets default schema for new tables [OK]

Common Mistakes:

• Assuming tables go to public schema by default
• Thinking SET search_path only affects queries, not creation
• Believing this causes an error without schema prefix

4. You run the command SET search_path TO sales; but get an error: ERROR: schema "sales" does not exist. What is the most likely cause?

medium

A. The schema sales was not created before setting search_path

B. You need to use USE sales; instead

C. The database connection is closed

D. The table sales does not exist

Solution

1. Step 1: Understand error meaning

   The error says the schema sales does not exist, so PostgreSQL cannot set search_path to it.

2. Step 2: Identify cause

   This usually means the schema was never created with CREATE SCHEMA sales;.

3. Final Answer:

   The schema sales was not created before setting search_path -> Option A

4. Quick Check:

   SET search_path needs existing schema [OK]

Hint: Create schema before setting search_path to it [OK]

Common Mistakes:

• Using USE instead of SET search_path in PostgreSQL
• Confusing tables with schemas
• Ignoring the error and continuing

5. You have two schemas: public and archive. You want to query a table named orders in archive without typing the full name every time. Which command will let you switch your session to use archive schema first, then public?

hard

A. USE archive, public;

B. ALTER SCHEMA archive SET search_path;

C. SET search_path TO archive, public;

D. SET schema TO archive, public;

Solution

1. Step 1: Recall how to set search_path for multiple schemas

   PostgreSQL uses SET search_path TO schema1, schema2; to set schema priority.

2. Step 2: Identify correct syntax

   SET search_path TO archive, public; correctly sets archive first, then public.

3. Final Answer:

   SET search_path TO archive, public; -> Option C

4. Quick Check:

   SET search_path sets schema order [OK]

Hint: Use SET search_path with comma-separated schemas [OK]

Common Mistakes:

• Using USE instead of SET search_path
• Trying ALTER SCHEMA to set search_path
• Using SET schema instead of SET search_path