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.