(opens in a new tab)

Postgres Schema vs Database: The Difference Explained

August 29, 2026 by Chat2DB Team

Newcomers to PostgreSQL — especially from MySQL, where CREATE DATABASE and CREATE SCHEMA are literally synonyms — regularly pick the wrong container and pay for it later: analytics queries that can't join "across databases," migrations pointed at the wrong tenant, or a public schema free-for-all. In PostgreSQL the two are genuinely different objects with different boundaries. Here's the mental model, the syntax, and how to choose between them for real designs like multi-tenancy.

The hierarchy

A PostgreSQL server (cluster) contains databases; each database contains schemas; each schema contains tables, views, functions, sequences and types:

cluster (one postgres instance, one port)
├── database: app_prod
│   ├── schema: public
│   │   └── users, orders, ...
│   ├── schema: billing
│   │   └── invoices, payments
│   └── schema: audit
├── database: app_staging
└── database: postgres        (default admin DB)

The rules that follow from this:

• A connection is to one database. You connect to app_prod, never to "the cluster." Inside that connection you can see every schema you have privileges on.
• Queries can join across schemas, not across databases. SELECT ... FROM billing.invoices JOIN public.users ... is normal SQL. Joining to a table in app_staging from app_prod requires an extension (postgres_fdw or dblink) — there is no cross-database query in core.
• Users/roles are cluster-wide. A role exists once per cluster; what varies per database and schema is its privileges.

The syntax side by side

-- Databases: created rarely, usually by an admin
CREATE DATABASE app_prod;
\c app_prod                     -- psql: reconnect to it (new connection!)
 
-- Schemas: created freely inside the current database
CREATE SCHEMA billing;
CREATE SCHEMA IF NOT EXISTS audit AUTHORIZATION audit_owner;
 
CREATE TABLE billing.invoices (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    total  numeric NOT NULL
);
 
SELECT * FROM billing.invoices;          -- qualified reference
DROP SCHEMA audit CASCADE;               -- drops contained objects too

Inspection:

\l      -- psql: list databases        (SELECT datname FROM pg_database;)
\dn     -- psql: list schemas          (SELECT nspname FROM pg_namespace;)
\dt billing.*                           -- tables in one schema
SELECT current_database(), current_schema;

search_path: how unqualified names resolve

When you write SELECT * FROM users without a schema, PostgreSQL walks the search_path:

SHOW search_path;               -- default: "$user", public
SET search_path TO billing, public;             -- session
ALTER ROLE analyst SET search_path = reporting, public;   -- per role
ALTER DATABASE app_prod SET search_path = app, public;    -- per database

The default "$user", public means: first a schema named like your role (if it exists), then public. Two practical consequences:

1. Convenient defaults: everything lands in public unless you say otherwise, which is why small projects never notice schemas exist.
2. A footgun in functions: SECURITY DEFINER functions should always pin SET search_path = ... so an attacker can't inject a shadowing table earlier in the path. (Temp schemas are searched first, implicitly.)

Note that since PostgreSQL 15, ordinary users no longer get CREATE on public by default — a deliberate hardening. Grant it back explicitly only if you mean to: GRANT CREATE ON SCHEMA public TO app_rw;.

What each boundary actually isolates

That last row matters more than people expect: PgBouncer pools are per (database, user) pair. A hundred tenant databases means a hundred pools and poor connection reuse; a hundred tenant schemas share one pool.

Choosing for multi-tenancy

The classic three options, in increasing isolation and cost:

1. Shared schema, tenant_id column + Row-Level Security. One set of tables; RLS policies enforce isolation. Cheapest to operate, migrations run once, and it scales to huge tenant counts. See our row-level security guide.
2. Schema per tenant. Real namespace isolation, per-tenant restore is possible (pg_dump -n tenant_042), joins to shared reference data still work, one connection pool. Cost: migrations × tenants, catalog growth at thousands of schemas, and application logic to set search_path per request — carefully, since a bug leaks across tenants.
3. Database per tenant. Strongest isolation (no query can cross it), independent extensions and collations. Cost: connection-pool fragmentation, migrations × tenants, cross-tenant analytics requires FDW gymnastics or ETL.

Sound defaults: RLS for SaaS with many small tenants; schema-per-tenant for dozens-to-hundreds of mid-size tenants with per-tenant restore requirements; database-per-tenant for a handful of large, compliance-separated customers.

Schemas as code organization

Even single-tenant apps benefit from schemas as modules:

CREATE SCHEMA app;        -- core tables
CREATE SCHEMA audit;      -- triggers write here; app role has INSERT only
CREATE SCHEMA reporting;  -- views only; BI role gets USAGE here and nothing else
 
GRANT USAGE ON SCHEMA reporting TO bi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO bi_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
  GRANT SELECT ON TABLES TO bi_reader;

Privileges become structural: the BI tool physically cannot see app.users, and audit rows can't be updated by the app role. Remember the two-level rule — a role needs USAGE on the schema and privileges on the object; missing USAGE yields "permission denied for schema," which is one of the most-Googled Postgres errors.

Common mistakes

• Creating a database per environment on one cluster (app_dev, app_test next to app_prod) — fine functionally, but they share memory, WAL and crash domain. Environments deserve separate clusters.
• Expecting USE database mid-session like MySQL. There's no such statement; \c in psql opens a new connection.
• Cross-database foreign keys. Impossible, full stop. If two datasets need referential integrity, they belong in one database (different schemas are fine).
• Dumping the whole cluster when you meant one schema. pg_dump -n billing app_prod scopes to a schema; pg_dumpall grabs the world.

Navigating databases and schemas is exactly where a good GUI pays off: Chat2DB (opens in a new tab) shows the cluster → database → schema → table tree explicitly, lets you run cross-schema queries with autocomplete on qualified names, and its AI assistant respects your current search_path when generating SQL. It also runs in the browser at app.chat2db.ai (opens in a new tab).

FAQ

Is a schema the same as a database in MySQL? In MySQL, yes — CREATE SCHEMA is an alias for CREATE DATABASE, and "cross-database" joins work because MySQL databases are really schemas in one namespace. PostgreSQL separates the concepts: databases are hard connection boundaries, schemas are namespaces inside them. Port MySQL's db1.table habits to Postgres schemas, not databases.

Can I move a table between schemas? ALTER TABLE public.users SET SCHEMA app; — instant, it only updates the catalog. Update search_path or qualified references afterward. Moving between databases means dump/restore or logical replication.

How many schemas can a database hold? There's no practical limit for sane designs; deployments run thousands. Catalog bloat and pg_dump duration grow with object count, so tens of thousands of schemas × many tables each is where schema-per-tenant designs start hurting and RLS designs shine.