What is a schema per module in a modular monolith database?

A schema-per-module layout gives each module in a modular monolith its own Postgres schema, such as billing, catalog or identity. All of them live inside a single database. The module owns every table, sequence and view in its schema, and no other module writes to them directly.

A Postgres schema is a namespace, not a separate database. All schemas share one cluster, one connection pool, one WAL stream and one backup. A single transaction can span tables in several schemas, and a query can join across them. That is why the pattern costs almost nothing to run and why it still needs deliberate enforcement.

When SQL uses an unqualified name like invoices, Postgres walks the search_path in order and uses the first schema that contains a matching object. The default path is "$user", public, so in practice most unqualified names resolve to public. With many modules, that lookup can silently bind a query to the wrong table or let a new table land in public by accident. To avoid this, either:

  • write schema-qualified names everywhere, or
  • give each module its own database role and set that role's search_path to its own schema.

Grants turn this from a convention into a boundary. If the catalog role has no USAGE on the billing schema, a query from catalog code against billing tables fails at runtime. A code-review rule can only ask people not to write that query.

The pattern suits teams who want module boundaries clean enough to extract a service later but do not want to operate several databases today.

-- One login role per module. Placeholder passwords; load real ones from your secret store.
CREATE ROLE billing_svc LOGIN PASSWORD 'change-me-billing';
CREATE ROLE catalog_svc LOGIN PASSWORD 'change-me-catalog';

-- Each schema is owned by its module's role, so only that role can create objects in it.
-- Other roles get no USAGE on a non-public schema unless it is granted explicitly.
CREATE SCHEMA billing AUTHORIZATION billing_svc;
CREATE SCHEMA catalog AUTHORIZATION catalog_svc;

-- Stop unqualified CREATE TABLE from landing in public by accident.
-- (This is already the default for new databases on Postgres 15 and later.)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Unqualified names resolve to the module's own schema first.
-- Role-level settings take effect at login, not after SET ROLE.
ALTER ROLE billing_svc IN DATABASE app_db SET search_path = billing;
ALTER ROLE catalog_svc IN DATABASE app_db SET search_path = catalog;

-- Billing creates and owns its tables.
SET ROLE billing_svc;
CREATE TABLE billing.invoices (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    total_cents bigint NOT NULL
);
RESET ROLE;

-- Catalog code that reaches into billing is rejected by the database, not by code review.
SET ROLE catalog_svc;
SELECT * FROM billing.invoices;
-- ERROR:  permission denied for schema billing
RESET ROLE;

Creates one owned schema and one login role per module, pins each role's search_path to its own schema, and shows Postgres rejecting a cross-module query because the catalog role has no USAGE on the billing schema.

How do you map modules to Postgres schemas, roles and grants?

Each module gets three things: a schema, an owner role that runs migrations, and a runtime role the application connects as. Mark the owner role NOLOGIN and have the migration job assume it via SET ROLE from a deploy login. The runtime role receives USAGE on the schema and only the table privileges it needs, such as SELECT, INSERT, UPDATE with no DELETE on an append-only ledger. On clusters where PUBLIC can still create objects in public, revoke CREATE there. Otherwise any role can create tables outside every module's boundary.

Role Owns objects Can log in Privileges
billing_owner Yes No Full DDL in billing
billing_app No Yes USAGE plus specific DML
reporting_app No Yes USAGE plus SELECT on published views

A GRANT ... ON ALL TABLES IN SCHEMA statement only affects tables that exist when it runs. A table added by next month's migration gets no grants, and the application fails or someone grants by hand, so environments drift apart. To prevent this, run ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing once and include sequences. Default privileges are tied to the role that creates the object. If a migration runs as a different role, the defaults silently do not apply.

Some roles sit outside this model. Superusers bypass all permission checks. Table owners bypass grants, and they also bypass row-level security unless it is forced. Extensions often need elevated rights and install functions into whatever schema is current, so give them a dedicated schema. The runtime role must never be a superuser or the owner. It also must not be a member of the owner role, because inherited membership gives it the owner's rights.

-- Owner role: owns billing objects, cannot log in; migrations SET ROLE to it
CREATE ROLE billing_owner NOLOGIN;
-- Runtime role: the app connects as this; not superuser, not a member of billing_owner
CREATE ROLE billing_app LOGIN PASSWORD 'change-me-placeholder' NOSUPERUSER NOINHERIT;
-- Deploy login that the migration job uses; it may assume the owner role
CREATE ROLE deploy LOGIN PASSWORD 'change-me-placeholder';
GRANT billing_owner TO deploy;

CREATE SCHEMA billing AUTHORIZATION billing_owner;

-- Keep any role from creating objects outside module boundaries
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Runtime access: USAGE on the schema plus only the DML it needs
GRANT USAGE ON SCHEMA billing TO billing_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA billing TO billing_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA billing TO billing_app;

-- Future objects: applies only to objects created BY billing_owner in billing
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
  GRANT SELECT, INSERT, UPDATE ON TABLES TO billing_app;
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
  GRANT USAGE, SELECT ON SEQUENCES TO billing_app;

-- Migration session: create objects as the owner so the defaults above fire
-- (connected as deploy)
SET ROLE billing_owner;
CREATE TABLE billing.ledger_entries (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount_cents bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
RESET ROLE;

-- Check: billing_app should get SELECT/INSERT/UPDATE but not DELETE
-- SELECT has_table_privilege('billing_app', 'billing.ledger_entries', 'DELETE');

Sets up a billing schema owned by a NOLOGIN migration role, a separate runtime role with limited DML, and default privileges so tables created later by the owner get the same grants.

How should one module read another module's data?

Grants decide who can read a table. They don't decide who should. The patterns below run from tightest to loosest coupling.

Pattern Coupling Consistency Extraction cost
Direct SELECT on another module's tables Highest: callers depend on physical layout Immediate Rewrite every caller
Published views granted SELECT Medium: callers depend on a declared shape Immediate Replace views with an API
In-process API of the owning module Low: callers depend on a function signature Immediate Swap the call for a network client
Events via an outbox table Lowest: callers keep their own copy Eventual Move the consumer as is

Avoid direct reads. If a reporting or query path truly needs SQL, publish a small set of views in the owning schema and treat them as a versioned contract. Call the in-process API by default. Use an outbox when the consumer can tolerate lag. The owner then writes events in the same transaction as its state change, so publication cannot drift from the data.

Cross-schema foreign keys are hidden coupling. The owner can't restructure or move its table without breaking a constraint it doesn't control. Store a bare ID instead, and validate it through the owner's API at write time. Accept that a referenced row may later disappear, and handle that case explicitly.

A transaction spanning two modules' tables is easy to write and works inside one database. It quietly turns two modules into one, so design module writes to stand alone.

Finally, a view that exposes every column of the underlying table passes the grant check and couples callers just as tightly as a direct read.

-- Assumes schemas billing and orders and role orders_module already exist.
-- Owner: billing. The base table is private to the billing module.
CREATE TABLE billing.invoices (
  id             uuid PRIMARY KEY,
  customer_id    uuid NOT NULL,
  amount_cents   bigint NOT NULL,
  status         text NOT NULL,
  internal_notes text  -- never part of any contract
);

-- Contract: a narrow, versioned view. Only these two columns are promised.
CREATE VIEW billing.invoice_status_v1 AS
  SELECT id AS invoice_id, status FROM billing.invoices;

REVOKE ALL ON billing.invoices FROM orders_module;
GRANT USAGE ON SCHEMA billing TO orders_module;
GRANT SELECT ON billing.invoice_status_v1 TO orders_module;

-- Consumer: orders keeps a bare ID with no REFERENCES clause, so billing
-- can restructure or move its table. The ID is validated through billing's
-- API at write time, and the code must handle the invoice disappearing later.
CREATE TABLE orders.orders (
  id         uuid PRIMARY KEY,
  invoice_id uuid
);

-- Outbox: billing records events alongside its own state changes.
CREATE TABLE billing.outbox (
  id           bigserial PRIMARY KEY,
  topic        text NOT NULL,
  payload      jsonb NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz  -- set by the relay after delivery
);

-- One transaction, one module: the state change and its event commit together.
BEGIN;
UPDATE billing.invoices SET status = 'paid'
  WHERE id = '6f1c2a9e-0000-4000-8000-000000000001';
INSERT INTO billing.outbox (topic, payload)
  VALUES ('invoice.paid', jsonb_build_object('invoice_id', '6f1c2a9e-0000-4000-8000-000000000001'));
COMMIT;

Billing exposes a narrow versioned view as its only SQL contract, orders stores a bare invoice ID instead of a cross-schema foreign key, and billing writes outbox events in the same transaction as its own state change.

Per-module migrations and application wiring

Each module keeps its own migration directory and its own history table inside its schema, such as billing.schema_migrations. Separate histories mean one module's version numbers never collide with another's, and dropping or rebuilding a module does not rewrite a shared ledger. At deploy time, run modules in a fixed, declared order (for example, alphabetical or an explicit list in config), so every environment applies changes the same way.

Run each module's migrations as a role that owns only that module's schema and has no CREATE elsewhere. Postgres requires ownership to ALTER or DROP a table and the CREATE privilege to add objects to a schema. A migration that reaches into orders from the billing directory fails with permission denied the first time it runs in CI. A human reviewer no longer has to spot it in a diff. That is where the time saving comes from: the database enforces ownership mechanically, and reviewers can focus on design.

Cross-module changes ship as several releases. First the owner adds the new column or view. Then consumers switch to it. Then the owner removes the old shape.

Application wiring has two common options:

Concern Pool per module Shared pool + SET ROLE
Connection pressure Multiplies idle connections against max_connections One pool sized once
Role leakage None; each pool logs in as its role A session returned without RESET ROLE carries the wrong role
Transaction-mode poolers Works unchanged Session SET ROLE breaks; use SET LOCAL ROLE inside each transaction
-- One-time setup, run as a superuser or admin role.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- no shared dumping ground

-- Migration role: owns billing, and nothing else.
CREATE ROLE billing_migrator LOGIN PASSWORD 'change-me-in-secrets-store';
CREATE SCHEMA billing AUTHORIZATION billing_migrator;

-- Runtime role: reads and writes data, cannot change the schema.
CREATE ROLE billing_app NOLOGIN;
GRANT USAGE ON SCHEMA billing TO billing_app;
ALTER DEFAULT PRIVILEGES FOR ROLE billing_migrator IN SCHEMA billing
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO billing_app;

-- Shared-pool login. NOINHERIT means it gets no module privileges
-- unless it explicitly switches role.
CREATE ROLE app_pool LOGIN NOINHERIT PASSWORD 'change-me-in-secrets-store';
GRANT billing_app TO app_pool;

-- Each module's history table lives in that module's schema.
-- Run this as billing_migrator.
CREATE TABLE billing.schema_migrations (
  version    text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now()
);

-- Fails in CI as billing_migrator. It does not own orders.orders
-- (and has no USAGE on the orders schema), so Postgres rejects it.
-- ALTER TABLE orders.orders ADD COLUMN invoice_id bigint;

-- Request path on a transaction-mode pooler. SET LOCAL ROLE lasts
-- only until COMMIT or ROLLBACK, so the role cannot leak to the
-- next client that gets this server connection.
BEGIN;
SET LOCAL ROLE billing_app;
INSERT INTO billing.invoices (customer_id, amount_cents) VALUES (42, 1999);
COMMIT;

Sets up a migration role that owns only the billing schema, a per-schema migration history table, and a shared-pool login that switches to the module role with SET LOCAL ROLE inside each transaction.

How do you detect and enforce schema boundary violations in CI?

The cheapest enforcement is to let Postgres do it. Run integration tests as the same login roles production uses, never as the schema owner or a superuser. A module that queries another module's tables then fails with a permission-denied error in CI, which is the same failure it would hit in production. Tests that connect as a privileged role pass while hiding every boundary violation.

Tests only cover the paths they exercise, though. Grant drift, such as a manual GRANT applied during an incident or a migration that revokes too much, tends to surface in rarely run paths like a monthly report job or an admin endpoint. Those paths throw permission errors weeks later. Catalog checks catch this before deploy because they inspect the database's structure rather than relying on test coverage.

Catalog checks to run after migrations

  • Cross-schema foreign keys: query pg_constraint for contype = 'f'. Join conrelid and confrelid to pg_class and pg_namespace, and fail when the two namespaces differ and the pair is not on an allowlist.
  • Cross-schema views: join pg_depend through pg_rewrite to find which relations each view's rewrite rule depends on. Avoid information_schema.view_table_usage here, because it only lists views owned by a currently enabled role.
  • Grant drift: compare information_schema.role_table_grants against a grants manifest checked into each module's directory, and fail on any difference in either direction.

Static check on SQL

Scan each module's SQL files and query strings for schema-qualified identifiers such as billing.invoices. Flag any qualifier that is neither the module's own schema nor an approved read interface. This is a lint, not a parser. It misses dynamic SQL, but it catches most violations at review time, before they reach CI.

How do you migrate an existing public schema to per-module schemas?

Start with an ownership map, not DDL. List every table in public, assign each to exactly one module, and record disputed tables explicitly. Tables that two modules both write to are the real migration work. Settle who owns them before anything moves, or you will move the coupling along with the table.

Move one module at a time

Begin with a leaf module that few others reference. ALTER TABLE ... SET SCHEMA is a catalog change. It carries the table's indexes, constraints and owned sequences with it, and existing views and foreign keys follow because they reference the table by OID. It still takes a brief exclusive lock, so run it with a lock_timeout set.

Old unqualified queries will break unless you bridge them. You have two options:

  • Compatibility views. Create a simple view in public with the old table name. Single-table views are automatically updatable, so writes keep working.
  • Search path entries. Add the module schema to the application role's search_path. This resolves unqualified names, including those inside function bodies, which views do not help with.

Rollback is the reverse statement, SET SCHEMA public, plus dropping the shim. Keep each step that small.

Tighten grants last

Once query logs show no traffic through the shims, drop them. Then revoke cross-module grants role by role, and run the CI lint from the previous section in blocking mode.

Each step is a short, reversible pull request, which keeps the development cost small. A big-bang rewrite needs every query fixed and tested at once, and its only rollback is a full revert.

When should you avoid schema-per-module, and what are the alternatives?

Schemas are a namespace and permission boundary, not a resource boundary. Every module still shares one cluster's CPU, memory, max_connections, autovacuum workers, WAL stream, replication lag and major-version upgrade window. A runaway query or a long transaction holding back vacuum in billing hurts catalog too. If modules need different durability, scaling or upgrade schedules, schemas cannot provide that.

The isolation has ongoing costs. Each module needs roles, grants and default privileges, which must be kept correct as tables are added. Per-module pools multiply connections. Cross-module reporting needs read-only roles, views or a replica or warehouse, and that reporting path often becomes the backdoor that couples modules again. Migrations also need ordering across modules that share a deploy.

Option Boundary enforced by Resource isolation Cross-module joins
Shared schema with conventions Code review only None Trivial
Table-name prefixes Naming and lint rules None Trivial
Schema per module Grants and search_path None Possible with grants
Separate databases, one instance Connection target Partial (connections, not CPU or I/O) Not native (needs postgres_fdw or the app)
Separate instances Network Full Not native (needs postgres_fdw or the app)

For a small team with a few modules, prefixes plus a lint rule may be enough. Choose schemas when you want the database to reject violations.

When a module is extracted later, schema-per-module pays off. The move is mostly a data copy and a connection string change. Any cross-module transactions, joins and foreign keys that survived still need rewriting as API calls, events or eventual consistency.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to modular monolith database schema, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.