Postgres Database vs Schema: When to Use Each

In Postgres, a database is a fully separate container that a connection attaches to, while a schema is a namespace inside one database that groups tables, views and functions. A single query can join tables across schemas of the same database, but it cannot reach another database without an extension like postgres_fdw. So use schemas to organize and permission parts of one application, and use separate databases when the data must be isolated: different apps, different customers who need hard separation, or different environments.

#How they nest

Postgres server (cluster)
├── roles (shared by every database on the server)
├── database: shop
│   ├── schema: public     → tables, views, functions
│   ├── schema: billing    → tables, views, functions
│   └── schema: extensions → vector, pg_trgm ...
└── database: analytics
    └── schema: public

A few facts follow from this structure:

• A connection string names exactly one database. To use another, you open a new connection (\c analytics in psql does exactly that).
• Schemas within a database share that database's catalog, so objects in public and billing can reference each other with foreign keys, views and joins.
• Roles belong to the server, not to a database. The same role can have rights in several databases.
• Extensions are installed per database. Enabling pgvector in shop does nothing for analytics.

#Database vs schema in Postgres, side by side

#Working with schemas

Creating and using a schema:

create schema billing;

create table billing.invoices (
  id bigint generated always as identity primary key,
  customer_id bigint not null references public.customers (id),
  total_cents integer not null check (total_cents >= 0),
  issued_at timestamptz not null default now()
);

-- A join across schemas is an ordinary join
select c.email, sum(i.total_cents) as lifetime_cents
from public.customers c
join billing.invoices i on i.customer_id = c.id
group by c.email;

#search_path

When you write a name without a schema, Postgres looks through search_path in order:

show search_path;                         -- usually "$user", public
set search_path = billing, public;        -- this session only
select count(*) from invoices;            -- now finds billing.invoices

Unqualified names are convenient in scripts but a source of bugs in shared code: the same query can hit different tables depending on the caller's setting. In functions, especially security definer ones, pin the path explicitly:

create or replace function billing.customer_total(cid bigint)
returns bigint
language sql stable
set search_path = ''
as $$
  select coalesce(sum(total_cents), 0) from billing.invoices where customer_id = cid
$$;

An empty search_path forces every name inside to be schema-qualified, which closes the trick where a caller creates a look-alike object earlier in the path.

#Permissions per schema

A role needs usage on a schema before it can touch anything in it, then rights on the objects:

create role reporting nologin;

grant usage on schema billing to reporting;
grant select on all tables in schema billing to reporting;

-- Also cover tables created later (by the role that will create them)
alter default privileges in schema billing grant select on tables to reporting;

Since Postgres 15, ordinary users can no longer create objects in public by default; the database owner can. If a migration fails with permission denied for schema public on a fresh Postgres 15 or newer server, that change is why.

#Working with databases

Separate databases are the right tool when you want isolation that a mistake in one query cannot cross:

create database analytics;

psql "postgresql://user:pass@host:5432/analytics"

There is no select ... from shop.public.customers from inside analytics. Postgres accepts a three-part name only when the first part is the current database. To read across, you need postgres_fdw or dblink, which adds a network hop, separate credentials and no shared transaction. That friction is the point: it is what makes a database a real boundary.

#When to use a schema

• Grouping parts of one app. billing, inventory, audit. Same transactions, same backups, joins still work.
• Keeping extensions out of the way. Installing extensions into an extensions schema keeps their functions out of public.
• Separating what an API exposes. Tools that generate an API from a schema (such as PostgREST) serve one or more chosen schemas; internal tables and helper functions go in a schema that is not exposed.
• Light multi-tenancy. A schema per tenant gives name-level separation inside one database. It works for dozens or a few hundred tenants, but every migration must run once per schema, and the catalog grows with each one. See multi-tenant Postgres patterns.

#When to use a separate database

• Unrelated applications. Their data should never join, and you want to back up, restore and delete them independently.
• Hard tenant isolation. Contract, compliance or noisy-neighbor reasons require that one tenant's credentials can never reach another tenant's rows. See multi-tenant vs single-tenant for the trade-offs.
• Environments. Development, staging and production should never share a database. A schema called staging next to production tables is one wrong search_path away from an incident.
• Different extensions or settings. Some database-level settings and extension choices are per database.

#Common mistakes

1. Schemas as environments. Use separate databases or projects instead.
2. Forgetting usage. Granting select on tables without usage on schema still gives permission denied for schema.
3. Relying on search_path in shared code. Qualify names in migrations and functions.
4. Forgetting default privileges. grant select on all tables covers only existing tables; new ones need alter default privileges.
5. A database per microservice that still needs joins. If two services constantly join each other's data, they may belong in one database with two schemas.

#Databases and schemas on StitchDB

On StitchDB each project gets its own Postgres database, named p_<project-ref>, and your connection string points at it (connect). Inside it, a set of schemas has fixed roles (schemas and roles):

You can create schemas of your own for internal tables; the SDKs can target another exposed schema with stitchdb.schema('name'). stitchdb db dump includes public and any schemas you created, never the platform's.

For the "separate database" cases, use separate projects: one per app or per environment, and on paid plans a preview branch per feature, which is its own database built from your migrations. Platforms that need a database per customer can create projects through the Platforms API.

One limit to know: because projects on shared servers run side by side, extensions that reach other databases, such as dblink and postgres_fdw, are not available there (extensions). Cross-project queries are therefore not possible from SQL; move data through your app, an Edge Function or an export.

#FAQ

#Is a schema the same as a database in Postgres?

No. A schema is a namespace inside a database. In MySQL the two words mean the same thing, which is where the confusion comes from; in Postgres they are different levels.

#Can I query across two Postgres databases?

Not directly. You need an extension such as postgres_fdw or dblink, or you copy data between them. Across schemas of the same database, a normal join works.

#Should I use the public schema?

It is fine for application tables, and many tools expect it. Put extensions and internal helpers in their own schemas, and schema-qualify names in migrations.

#Schema per tenant or database per tenant?

Schema per tenant is cheaper and keeps cross-tenant reporting easy, but migrations multiply. Database per tenant gives the strongest isolation and independent backups, at a higher operational cost. Shared tables with a tenant column and Row Level Security is the third, most common option.

Organize your data your way: create a free project and read schemas and roles.