Postgres Schemas Explained

Or Why Everything in Public Might Not Be the Best Solution

Photo by Sean Robertson on Unsplash

As an old-timer in relational databases, I am a big fan of Postgres. Since I started using it, it has become much more. Using extensions, you can put just about anything on it without having to spin up a special database.

While technically categorized as an object-relational database, Postgres is practically a Swiss-army-knife multi-model database. It allows you to mix structured relational tables, NoSQL JSON documents, geospatial data, and AI vectors all within a single, reliable system.

That said, let us dive into schemas.

What is a Schema

In Postgres, a schema is a logical namespace or container inside a database that organizes database objects — such as tables, views, indexes, data types, functions, and operators.

You use schemas to group related objects together without having to separate them into entirely different databases.

Why Use Them

Small applications often dump everything into the default public schema; production and enterprise applications use custom schemas for several key reasons.

Organizing Database Objects

Just like organizing code into directories, schemas let you group tables by business logic or module. You can create one for billing, one for inventory, and one for reporting.

Multi-Tenancy

You can isolate data for different clients or tenants within the same database. For example, tenant_1.users and tenant_2.users can exist simultaneously without conflict. No need for prefixing table names.

Access Control & Security

You can grant or revoke privileges at the schema level. For instance, you can allow an analytics user to read everything in the reporting schema while completely blocking them from accessing the billing schema.

Avoiding Naming Collisions

Two different applications sharing a database can both have a table named users as long as they reside in different namespaces, e.g., app_one.users and app_two.users. See, no need to prefix table names because you stuffed everything in public.

The Default

When you spin up a Postgres Server, the public schema is the default namespace or container where database objects are created if you do not explicitly specify a different schema.

Creating a New Schema

I am connected to my demo database. The only schema I have at this time is public.

demo=# \dn

List of schemas

Name | Owner
 - - - - + - - - - - - - - - -
public | pg_database_owner

All tables are in the public schema.

demo=# \dt
List of relations
Schema | Name | Type | Owner
 - - - - + - - - - - - - - -+ - - - -+ - - - - - 
public | customers | table | postgres
public | heights_weights | table | postgres
public | medium_demo | table | postgres
public | orders | table | postgres
public | test_data | table | postgres
public | users | table | postgres

To create a new schema in this database:

CREATE SCHEMA sales;

And my new schema is created:

demo=# \dn
List of schemas
Name | Owner
 - - - - + - - - - - - - - - -
public | pg_database_owner
sales | postgres

To create a table in this new schema:

CREATE TABLE sales.invoices (
invoice_id serial PRIMARY KEY,
amount numeric
);

And because we want to check our work:

demo=# \dt
List of relations
Schema | Name | Type | Owner
 - - - - + - - - - - - - - -+ - - - -+ - - - - - 
public | customers | table | postgres
public | heights_weights | table | postgres
public | medium_demo | table | postgres
public | orders | table | postgres
public | test_data | table | postgres
public | users | table | postgres

Where is our new table? By default, running \dt without any arguments shows only tables in our current search_path (which currently includes only the public schema and your username’s schema).

Because our new invoices table lives inside the sales schema, \dt hides it from view to keep the terminal uncluttered.

demo=# show search_path;
search_path
 - - - - - - - - -
"$user", public

We could view all tables in all schemas, but that could be messy. Or, we could just search the new schema.

\dt sales.*
List of relations
Schema | Name | Type | Owner
 - - - - + - - - - - + - - - -+ - - - - - 
sales | invoices | table | postgres

Another option is to add sales to your search_path:

SET search_path TO public, sales;

And to check your work:

SHOW search_path;
search_path
 - - - - - - - -
public, sales

This solution is only for the running session. If you want to make it permanent:

ALTER DATABASE demo SET search_path TO public, sales;

Once you disconnect and reconnect to the demo database, the effect is permanent.

Why Schemas Instead of Separate Databases

Joining tables across different schemas versus different databases in Postgres comes down to a fundamental architectural boundary: shared memory and process space.

Because of how Postgres is engineered, joining schemas is trivial, native, and fast, whereas joining completely separate databases requires external workarounds.

Joining Tables Between Schemas

When you join tables across different schemas within the same database, it behaves exactly like a normal, single-schema join. This is because all schemas inside a database share the same query planner, buffer cache, worker processes, and transaction context.

Joining Tables Between Databases

In PostgreSQL, databases are designed to be strictly isolated from one another. They do not share memory caches, transaction IDs, or execution contexts. A single standard SQL query cannot natively reach outside its connected database to cross-join with another.

To bypass this barrier and join database_a.public.table with database_b.public.table, you have to use a mechanism like Foreign Data Wrappers (FDW), specifically postgres_fdw.

Why Schemas Instead of Multiple Databases

Using multiple schemas within a single database — rather than spinning up entirely separate databases — is the industry standard design pattern for a modular application. This approach strikes the perfect balance between logical separation of your application’s modules and physical efficiency of the underlying database engine.

Unified Connection Pooling and Resource Efficiency

Every database you spin up in PostgreSQL requires its own dedicated system resources.

• The Database Barrier: If your application has 5 modules (e.g., auth, billing, inventory, shipping, analytics) and each uses a separate database, your application backend must open and maintain 5 distinct connection pools. Postgres allocates memory (like work_mem) and system processes per connection. This massively inflates your idle RAM and CPU usage.
• The Schema Advantage: When using schemas, your application connects to exactly one database engine with a single connection pool. Your application can route queries to any module seamlessly over that single pipe, dramatically lowering infrastructure overhead and costs.

High-Performance, Low-Overhead Inter-Module Communication

Modules in a modular application rarely live in total isolation; they frequently need to talk to one another (e.g., the billing module needs to verify a user from the auth module).

• The Schema Advantage: Joining a table in billing with a table in auth is instantaneous and native. The Postgres query planner has access to the statistics of all tables across all schemas simultaneously, choosing optimized execution strategies like index or hash joins completely in-memory.
• The Database Barrier: If they are in separate databases, you cannot natively join them. You are forced to either pull all the data into your application code layer to join it manually, or configure Foreign Data Wrappers (postgres_fdw). This adds network latency, serialization overhead, and breaks query optimization.

ACID Compliance & Atomic Transactions

In a modular application, an event in one module often triggers a dependency in another. For example, when a user buys a subscription, you want to record the invoice (billing schema) and provision their access (auth schema).

• The Schema Advantage: You can wrap operations across multiple schemas in a single, standard database transaction. If either step fails, the entire operation rolls back cleanly. This guarantees strict data consistency across your entire application stack natively.
• The Database Barrier: To achieve this across separate databases, you have to implement complex distributed transaction protocols (like Two-Phase Commits) or build an asynchronous Saga Pattern in your application code. This introduces significant code complexity and edge cases where data can become desynchronized.

Simplified Operations, Backups, and Maintenance

Managing data infrastructure over time requires consistent maintenance, backups, and structural updates.

• Database Backups: With a multi-schema setup, running a single pg_dump gives you a point-in-time, mathematically consistent snapshot of your entire application state. If you have separate databases, backing them up at the same fraction of a second is nearly impossible, making point-in-time recovery across modules a logistical nightmare.
• Schema Migrations: Tools like Liquibase, Flyway, or native ORM migrators can manage the entire database evolutionary state in a single execution pipeline. Keeping 5 separate databases in lockstep regarding migration versions adds massive CI/CD overhead.

Plan Before You Act

Schemas in Postgres are not a gimmick. They are a design tool. Before creating a new database for your modular application, consider schemas instead. Unless you like waking up at 3 A.M. to panicked calls from your boss.