PostgreSQL Databases & Schemas

When you first start working with PostgreSQL, it's easy to think of a database as a single, flat container where all your tables live. But as your project grows — adding user data, logs, analytics, and perhaps a separate staging environment — that flat mental model becomes a trap. You'll hit name collisions, messy permissions, and backups that lump everything together. The pain is real: you want logical separation, but you also need a simple way to manage it. This lesson untangles PostgreSQL's two-level hierarchy — databases and schemas — so you can organize objects cleanly, control access with precision, and move through your development lifecycle with confidence.

The problem this lesson solves

Consider a typical backend service. You start with one database, say app, and within it you create tables like users, orders, and products. It works fine — until a colleague creates a table named users again for a side feature, or you want to run a temporary experiment without polluting your main tables.

More critically, production and testing often need separate environments. You could create multiple databases, but then you have to manage multiple connection strings, backup routines, and permission sets. Alternatively, you might try to keep everything in one database but end up with a mess of prefixes like prod_users and test_users. Neither approach is clean.

The root problem: you lack a way to namespace your database objects. PostgreSQL solves this with schemas, which live inside a database and provide a logical grouping layer. The challenge is knowing when to use a database versus a schema, and how to leverage both effectively.

By the end of this lesson, you'll be able to: - Explain the distinction between a database and a schema. - Create and manage schemas to organize tables and other objects. - Set the search_path to control which schema PostgreSQL uses by default. - Apply best practices for schema design in real projects.

This skill is foundational for every developer who builds data-driven applications — you'll avoid expensive refactors later by designing your namespace from day one.

Core concept / mental model

Think of a PostgreSQL server as an apartment building. The database is a single apartment unit — it has its own front door (connection), its own utilities (permissions), and its own storage space. Inside that apartment, the schemas are the rooms: kitchen, bedroom, home office. Each room has a purpose and holds specific furniture (tables, views, functions).

You wouldn't put the kitchen sink in the bedroom, and similarly, you shouldn't mix user-facing tables with internal audit tables in the same unnamed schema. Schemas give you that room-by-room organization.

Technically, a schema is a namespace that contains named objects like tables, views, indexes, sequences, and functions. PostgreSQL enforces that object names must be unique within a schema, but the same name can exist in different schemas. This means you can have two users tables — one in public, one in analytics — without conflict.

The default schema is public. When you create a table without specifying a schema, it goes into public. But you can create as many schemas as you need, and you can control which schema PostgreSQL looks at first via the search_path setting.

Pro tip: Think of schemas as the primary organizational tool within an application. Use multiple databases sparingly — typically for hard isolation (e.g., separate customer tenants) or for different lifecycle stages (dev vs test).

Key definitions

• Database: A top-level container. Each database is physically isolated — separate system catalogs, separate privileges. Connections are made to a database.
• Schema: A logical namespace within a database. It groups objects and provides a layer for permissions and search order.
• Search path: A list of schemas that PostgreSQL checks when you reference an object without a schema prefix. It determines which object you get when names collide.

How it works step by step

Let's walk through the logic of creating and using databases and schemas.

1. Connect to the PostgreSQL server. You must be connected to a database to run commands. The default installation connects to a database named postgres.

2. Create a new database (if needed). Use CREATE DATABASE. This creates a separate storage container with its own system catalogs. You cannot create schemas at the server level — schemas always belong to a database.

3. Create a schema inside the database. Use CREATE SCHEMA schema_name. This creates an empty namespace. You can then create tables, views, functions, etc., within that schema.

4. Set the search path. To make PostgreSQL use your schema by default, you can set search_path at the session, user, or database level. This affects unqualified object names.

5. Create objects with qualified names. You can always reference an object by schema.table to avoid ambiguity, regardless of the search path.

6. Grant privileges. Each schema can have its own access rights. You can allow some users to read from one schema while writing to another.

This sequence gives you fine-grained control over your data organization.

Understanding the search path

When you run SELECT * FROM users, PostgreSQL looks for a table named users in the schemas listed in search_path, in order. The default search path is "$user", public — meaning it first looks for a schema named exactly like the current user, then falls back to public.

You can view your current search path:

SHOW search_path;

Output example:

 search_path 
--------------
 "$user", public
(1 row)

You can change it to prioritize a custom schema:

SET search_path TO analytics, public;

Now, an unqualified users will resolve to analytics.users if it exists; otherwise, it falls back to public.users.

Pro tip: For a production application, always use qualified names (schema.table) in your SQL to avoid surprises when the search path changes. But for interactive exploration, the search path is your friend.

Hands-on walkthrough

Let's put this into practice. We'll create a database, add schemas, build tables, and query across them.

Step 1: Create a database

Open psql and connect to your server (usually as the postgres user). Then create a database for an e-commerce application:

CREATE DATABASE shop;

Connect to the new database:

\c shop

Step 2: Create schemas

Inside shop, create schemas for different functional areas:

CREATE SCHEMA sales;
CREATE SCHEMA inventory;

Now we have three schemas: public, sales, and inventory.

Step 3: Create tables in specific schemas

Create a table in sales:

CREATE TABLE sales.orders (
    order_id integer PRIMARY KEY,
    customer_id integer NOT NULL,
    total numeric(10,2)
);

And one in inventory:

CREATE TABLE inventory.products (
    product_id integer PRIMARY KEY,
    name text NOT NULL,
    price numeric(10,2)
);

Step 4: Insert and query with qualified names

Insert some data:

INSERT INTO sales.orders (order_id, customer_id, total) VALUES (1, 101, 250.00);
INSERT INTO inventory.products (product_id, name, price) VALUES (1, 'Monitor', 199.99);

Query using qualified names:

SELECT * FROM sales.orders;
SELECT * FROM inventory.products;

Expected output:

 order_id | customer_id | total  
----------+-------------+--------
        1 |         101 | 250.00
(1 row)

 product_id |  name   |  price  
------------+---------+---------
          1 | Monitor |  199.99
(1 row)

Step 5: Use the search path

Now, instead of typing the schema prefix every time, set search_path to include both schemas:

SET search_path TO sales, inventory, public;

Now you can query orders and products without prefixes:

SELECT * FROM orders;
SELECT * FROM products;

Both queries work because PostgreSQL searches sales first, finds orders, then searches inventory and finds products.

Step 6: List schemas and objects

To see your schemas:

\dn

To list tables in a specific schema:

\dt inventory.*

Pro tip: If you create objects without a schema, they default to public. To avoid accidental pollution, you can revoke CREATE on public from non-owners: REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Compare options / when to choose what

You have several ways to organize your PostgreSQL objects. Here's a quick comparison:

Choosing between databases and schemas:

• Use multiple databases when you need hard security isolation (e.g., multi-tenant SaaS where each customer gets a separate DB), or when you want separate backup/restore streams for different environments.
• Use multiple schemas within a single database for most application designs — you get organization, permissions, and the ability to cross-join data without extra connection overhead.

Variations:

• Schema per tenant: In a multi-tenant app, you can create one schema per customer inside a single database, enabling easy row-level separation while keeping shared migrations.
• Schema for extensions: some extensions (like PostGIS) may require special schemas to avoid conflicts.
• Using CREATE SCHEMA AUTHORIZATION to tie a schema to a specific user, which gives that user ownership and control.

Troubleshooting & edge cases

"Schema does not exist" error

You might run CREATE TABLE mytable ... and get ERROR: schema "public" does not exist. This can happen if you somehow dropped the public schema (not recommended). Fix: create a new schema and set it as the default, or recreate public via CREATE SCHEMA public;.

"relation does not exist" when you just created it

If you create a table in a schema but then query it without a prefix, PostgreSQL might not find it because the schema is not in your search_path. Check your search path with SHOW search_path;. To fix, either qualify the table name or adjust the search path.

Name collisions across schemas

If you have two users tables in different schemas, an unqualified SELECT * FROM users will return the first one in the search path — possibly not what you intended. Always use qualified names when precision matters.

Permissions issues

When you create a schema, only the owner (usually the creator) has access. If another user tries to create a table, they'll get permission denied for schema. Grant USAGE and CREATE privileges:

GRANT USAGE ON SCHEMA sales TO app_user;
GRANT CREATE ON SCHEMA sales TO app_user;

Dropping databases and schemas

DROP DATABASE shop; will fail if there are active connections. Use SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'shop'; to disconnect first. DROP SCHEMA sales; works only if the schema is empty — add CASCADE to drop it with its objects, but use it with caution.

What you learned & what's next

You now understand the two-level hierarchy of PostgreSQL: databases as top-level containers and schemas as logical namespaces within each database. You can create databases, design schemas, set the search_path to control resolution, and use qualified names for clarity. You've seen how to choose between databases and schemas based on isolation needs, and you know how to troubleshoot common pitfalls like missing schemas and permission errors.

In the next lesson, you'll build on this foundation by diving into table design and data types. You'll learn how to choose the right column types, enforce integrity with constraints, and avoid common modeling mistakes. With your new schema skills, you'll be able to design a clean, extensible data model from the start.