Public schema vs custom schemas in PostgreSQL - Trade-offs & Expert Analysis

Overview - Public schema vs custom schemas

What is it?

In PostgreSQL, a schema is like a folder inside a database that holds tables, views, and other objects. The public schema is the default folder created automatically where objects go if no other schema is specified. Custom schemas are additional folders you create to organize your database objects separately. Schemas help keep things tidy and avoid name clashes between objects.

Why it matters

Without schemas, all database objects would live together in one big space, making it hard to manage and causing conflicts when different parts of an application use the same names. Schemas let you separate and organize data logically, improving clarity, security, and collaboration. This separation is crucial in real-world projects where many teams or applications share the same database.

Where it fits

Before learning about schemas, you should understand basic database concepts like tables and databases themselves. After schemas, you can explore advanced topics like roles and permissions, schema search paths, and multi-tenant database design.

Mental Model

Core Idea

Schemas are like labeled folders inside a database that organize and separate database objects to avoid confusion and conflicts.

Think of it like...

Imagine your computer's file system: the database is the main drive, schemas are folders inside it, and tables are files inside those folders. The public schema is like the default 'Documents' folder everyone uses unless they create their own folders.

Database
├── public (default schema)
│   ├── table1
│   └── table2
├── sales (custom schema)
│   ├── customers
│   └── orders
└── hr (custom schema)
    ├── employees
    └── salaries

Build-Up - 6 Steps

1

Foundation

What is a schema in PostgreSQL

Concept: Schemas are containers inside a database that hold tables and other objects.

A PostgreSQL database contains schemas. Each schema groups related tables, views, and functions. By default, PostgreSQL creates a schema named 'public'. When you create a table without specifying a schema, it goes into 'public'.

Result

You understand that schemas organize database objects and that 'public' is the default schema.

Understanding schemas as containers helps you see how databases keep things organized and avoid name conflicts.

2

Foundation

Role of the public schema

Concept: The public schema is the default place for new objects and is accessible to all users unless restricted.

When you create tables without specifying a schema, they go into the public schema. By default, all users can access objects in public, making it a shared space. This is convenient but can cause clutter or security issues if not managed.

Result

You know that public schema is the default and shared by all users unless permissions change.

Knowing the public schema's default openness helps you understand why you might want custom schemas for better control.

3

Intermediate

Creating and using custom schemas

🤔Before reading on: do you think creating a custom schema automatically restricts access to it? Commit to your answer.

Concept: Custom schemas let you organize objects separately and control access independently from public.

You can create a schema with 'CREATE SCHEMA sales;'. Then create tables inside it like 'CREATE TABLE sales.customers (...)'. To use objects in a custom schema, you specify the schema name or adjust the search path. Access rights can be set per schema.

Result

You can organize tables into custom schemas and control who can see or use them.

Understanding custom schemas as separate folders with their own permissions helps manage complex databases and security.

4

Intermediate

Schema search path and object resolution

🤔Before reading on: if two schemas have tables with the same name, which one does PostgreSQL use by default? Commit to your answer.

Concept: PostgreSQL uses a search path to find objects when schema is not specified, checking schemas in order.

The search path is a list of schemas PostgreSQL looks through to find tables or other objects. By default, it includes 'public'. If two schemas have the same table name, the first schema in the search path is used. You can change the search path with 'SET search_path TO sales, public;'.

Result

You know how PostgreSQL decides which object to use when names clash.

Knowing the search path prevents confusion and bugs when multiple schemas have objects with the same names.

5

Advanced

Managing permissions with schemas

🤔Before reading on: do you think revoking access on a schema removes access to all its objects automatically? Commit to your answer.

Concept: Schemas allow grouping permissions, but object-level permissions are separate and must be managed carefully.

You can grant or revoke privileges on schemas using commands like 'GRANT USAGE ON SCHEMA sales TO user1;'. This controls if a user can access objects inside. However, users also need permissions on individual objects like tables. Revoking schema access alone doesn't remove table permissions.

Result

You understand how schema and object permissions work together to secure data.

Understanding the difference between schema-level and object-level permissions helps avoid security gaps.

6

Expert

Schema design for multi-tenant applications

🤔Before reading on: is it better to use one schema per tenant or one big schema with tenant IDs? Commit to your answer.

Concept: Schemas can isolate tenants in multi-tenant apps, but tradeoffs exist between isolation and complexity.

In multi-tenant apps, you can create one schema per tenant to isolate data and permissions. This improves security and simplifies backups per tenant. However, it increases management overhead and can complicate queries across tenants. Alternatively, a single schema with tenant ID columns is simpler but less isolated.

Result

You see the pros and cons of schema-based tenant isolation in real-world apps.

Knowing these tradeoffs helps design scalable, secure multi-tenant databases tailored to needs.

Under the Hood

PostgreSQL stores schemas as namespaces inside a database catalog. Each schema has an internal identifier and contains references to its objects. When a query runs, PostgreSQL uses the search path to resolve object names by checking schemas in order. Permissions are checked at both schema and object levels. Schemas do not physically separate data but logically group objects.

Why designed this way?

Schemas were introduced to organize database objects logically without creating multiple databases. This allows sharing resources like connections and configurations while isolating objects. The design balances flexibility, performance, and security. Alternatives like separate databases would be heavier and less flexible for shared environments.

Database
┌─────────────────────────────┐
│          Catalog            │
│ ┌───────────────┐           │
│ │ Schema: public│           │
│ │ ┌───────────┐ │           │
│ │ │ Table A   │ │           │
│ │ └───────────┘ │           │
│ └───────────────┘           │
│ ┌───────────────┐           │
│ │ Schema: sales │           │
│ │ ┌───────────┐ │           │
│ │ │ Table B   │ │           │
│ │ └───────────┘ │           │
│ └───────────────┘           │
└─────────────────────────────┘
Query → Search Path → Find Table → Check Permissions → Execute

Myth Busters - 4 Common Misconceptions

Quick: Does creating a custom schema automatically restrict access to it? Commit to yes or no.

Common Belief: Creating a custom schema automatically makes it private and inaccessible to others.

Reality: By default, custom schemas are accessible to all users unless permissions are explicitly changed.

Why it matters: Assuming schemas are private by default can lead to accidental data exposure if permissions are not set properly.

Quick: If two schemas have tables with the same name, does PostgreSQL randomly pick one? Commit to yes or no.

Common Belief: PostgreSQL randomly chooses which table to use if names clash across schemas.

Reality: PostgreSQL uses the search path order to deterministically pick the first matching table.

Why it matters: Not understanding search path order can cause unexpected query results and bugs.

Quick: Does revoking access on a schema remove access to all its tables automatically? Commit to yes or no.

Common Belief: Revoking schema access removes access to all objects inside it automatically.

Reality: Schema permissions control access to the namespace, but object permissions are separate and must be managed individually.

Why it matters: Mismanaging permissions can leave sensitive data accessible despite schema restrictions.

Quick: Is it always better to use one schema per tenant in multi-tenant apps? Commit to yes or no.

Common Belief: Using one schema per tenant is always the best way to isolate data.

Reality: While schema per tenant offers isolation, it adds complexity and may not scale well; sometimes a shared schema with tenant IDs is better.

Why it matters: Choosing the wrong approach can cause maintenance headaches or security risks in multi-tenant systems.

Expert Zone

1

Schemas do not physically separate data storage; they are logical namespaces, so performance differences are minimal but organization and security differ.

2

Changing the search path affects all unqualified object references, which can lead to subtle bugs if not managed carefully in complex applications.

3

Schema-level permissions control namespace usage but do not override object-level permissions, requiring careful combined management for security.

When NOT to use

Avoid using multiple schemas when your application is simple or when you need very fast cross-object joins, as schema boundaries can complicate queries. Instead, use table naming conventions or a single schema with clear naming. For strict physical isolation, consider separate databases.

Production Patterns

In production, schemas are used to separate environments (dev, test, prod), organize modules or features, and isolate tenants in SaaS apps. Teams often set search paths per user or session to simplify queries. Permissions are layered at schema and object levels for security compliance.

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 naming collisions and organize objects logically.

File System Directories

Schemas function like directories in a file system, grouping files (tables) into folders (schemas) for organization and access control.

Knowing how file systems organize files clarifies how schemas help manage database objects.

Multi-Tenancy in Cloud Computing

Using schemas to isolate tenants in a database parallels how cloud platforms isolate customers' resources for security and management.

Recognizing this connection helps design secure, scalable multi-tenant applications using schemas.

Common Pitfalls

#1 Assuming all objects go into the public schema without specifying schema names.

Wrong approach:

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

Correct approach:

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

Root cause:

Not specifying schema causes objects to default to public, leading to clutter and possible name conflicts.

#2 Not setting the search path, causing queries to fail or use wrong tables.

Wrong approach:

SELECT * FROM customers; -- expects sales.customers but finds public.customers or errors

Correct approach:

SET search_path TO sales, public;
SELECT * FROM customers;

Root cause:

Ignoring search path means PostgreSQL may not find the intended object if multiple schemas have same names.

#3 Granting permissions on schema but forgetting to grant on tables inside.

Wrong approach:

GRANT USAGE ON SCHEMA sales TO user1; -- user1 still cannot SELECT from sales.customers

Correct approach:

GRANT USAGE ON SCHEMA sales TO user1;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO user1;

Root cause:

Confusing schema-level and object-level permissions leads to access errors.

Key Takeaways

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

The public schema is the default shared space, but custom schemas let you separate objects and control access more precisely.

PostgreSQL uses a search path to find objects when schema names are not specified, so managing this path is key to correct query behavior.

Permissions must be managed both at the schema level and the individual object level to ensure proper security.

In complex applications, especially multi-tenant ones, schema design involves tradeoffs between isolation, complexity, and performance.

Practice

1. What is the role of the public schema in PostgreSQL?

easy

A. It is a special schema that cannot contain tables or other objects.

B. It is the default schema where database objects are created if no schema is specified.

C. It is used only for system tables and cannot be modified by users.

D. It automatically encrypts all data stored within it.

Solution

1. Step 1: Understand the default schema concept

   In PostgreSQL, the public schema is created by default in every new database.

2. Step 2: Identify the role of the public schema

   When you create tables or other objects without specifying a schema, they go into the public schema automatically.

3. Final Answer:

   It is the default schema where database objects are created if no schema is specified. -> Option B

4. Quick Check:

   Default schema = public [OK]

Hint: Remember: public schema is default for new objects [OK]

Common Mistakes:

• Thinking public schema is read-only
• Assuming public schema is for system tables only
• Believing public schema encrypts data automatically

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

easy

A. CREATE SCHEMA sales;

B. CREATE DATABASE sales;

C. NEW SCHEMA sales;

D. MAKE SCHEMA sales;

Solution

1. Step 1: Recall the SQL syntax for creating schemas

   The correct SQL command to create a schema is CREATE SCHEMA schema_name;.

2. Step 2: Check each option

   CREATE SCHEMA sales; uses the correct syntax. MAKE SCHEMA sales;, CREATE DATABASE sales;, and NEW SCHEMA sales; are invalid SQL commands for creating schemas.

3. Final Answer:

   CREATE SCHEMA sales; -> Option A

4. Quick Check:

   CREATE SCHEMA = create new schema [OK]

Hint: Use CREATE SCHEMA to make new schemas [OK]

Common Mistakes:

• Using CREATE DATABASE instead of CREATE SCHEMA
• Using non-existent commands like NEW SCHEMA or MAKE SCHEMA
• Forgetting the semicolon at the end

3. Given the following commands:

CREATE SCHEMA marketing;
CREATE TABLE marketing.campaigns (id SERIAL PRIMARY KEY, name TEXT);
SET search_path TO marketing, public;
SELECT * FROM campaigns;

What will be the result of the SELECT query?

medium

A. It will return rows from a campaigns table in the public schema if it exists.

B. It will cause an error because the table name is not schema-qualified.

C. It will return all rows from the marketing.campaigns table.

D. It will return no rows because search_path is set incorrectly.

Solution

1. Step 1: Understand search_path usage

   The search_path is set to look first in marketing schema, then public.

2. Step 2: Identify which table is found

   The table campaigns exists in marketing schema, so the query finds and returns data from marketing.campaigns.

3. Final Answer:

   It will return all rows from the marketing.campaigns table. -> Option C

4. Quick Check:

   search_path order finds marketing first [OK]

Hint: search_path order decides which schema is used first [OK]

Common Mistakes:

• Assuming table must be schema-qualified always
• Thinking query errors without schema prefix
• Ignoring search_path order

4. You run this command:

SELECT * FROM sales.orders;

But get the error: ERROR: schema "sales" does not exist.
What is the most likely cause?

medium

A. The sales schema has not been created yet.

B. The orders table does not exist in the public schema.

C. You forgot to set the search_path to include sales.

D. You need to use double quotes around sales.

Solution

1. Step 1: Analyze the error message

   The error says the schema "sales" does not exist, meaning PostgreSQL cannot find that schema.

2. Step 2: Identify the cause

   This usually means the schema was never created or was dropped, so the database has no knowledge of it.

3. Final Answer:

   The sales schema has not been created yet. -> Option A

4. Quick Check:

   Schema missing causes this error [OK]

Hint: Check if schema exists before querying it [OK]

Common Mistakes:

• Assuming search_path affects schema existence errors
• Thinking table absence causes schema not found error
• Trying to fix with quotes unnecessarily

5. You want to organize your database by creating two schemas: hr and finance. You create tables with the same name employees in both schemas. How can you write a query to select all employees from the finance schema without changing the search_path?

hard

A. SELECT * FROM public.employees WHERE schema = 'finance';

B. SET search_path TO finance; SELECT * FROM employees;

C. SELECT * FROM employees WHERE schema = 'finance';

D. SELECT * FROM finance.employees;

Solution

1. Step 1: Understand schema-qualified table names

   When tables have the same name in different schemas, you can specify the schema explicitly before the table name.

2. Step 2: Identify the correct query

   SELECT * FROM finance.employees; uses finance.employees which directly accesses the table in the finance schema without changing search_path.

3. Final Answer:

   SELECT * FROM finance.employees; -> Option D

4. Quick Check:

   Schema-qualified names access tables directly [OK]

Hint: Use schema.table to access specific schema tables [OK]

Common Mistakes:

• Trying to filter by schema column which doesn't exist
• Changing search_path unnecessarily
• Assuming public schema contains all tables