What is a schema per module in Postgres, and why do naming conventions fail?
A schema per module means each module in a modular monolith gets its own Postgres schema. Each schema is paired with roles that can only reach that schema's tables. The goal is that the database, not your team's discipline, decides which code can touch which data.
Most monoliths start with conventions instead: a billing_ prefix on table names, a folder structure, a rule in the review checklist. None of these stop anything. The application usually connects as a single role that can read and write every table, so a join from orders into billing_invoices is a valid query. It passes tests and ships. Reviewers catch some of these joins. They miss the ones hidden in an ORM relation, a reporting query, or a hurried fix.
A Postgres schema is a namespace and a permission boundary inside one database. Tables in different schemas still share:
- transactions and connections
- backups and WAL
- CPU and memory
- the server's
max_connectionslimit
A schema is not a separate database, and it is not a resource boundary. A module running a heavy batch job will still slow down every other module.
The core idea is simple. Each module gets one schema, one owner role that holds DDL rights, and one runtime role with only the data privileges the application needs. When a module's runtime role queries another module's tables, Postgres rejects it with a permission error.
This pays off most for teams with several module owners and a realistic chance of extracting a module into its own service later. Boundary violations surface at query time, in development and CI, instead of depending on someone noticing them in review.
orders module code billing module code
| |
| connects as | connects as
v v
+----------------+ +-----------------+
| orders_runtime | | billing_runtime |
| DML only | | DML only |
+----------------+ +-----------------+
| | |
| OK +--> billing.invoices | OK
| ERROR: permission denied |
v v
+------------------- Postgres database (one server) -------------------+
| +-------- schema: orders --------+ +----- schema: billing ------+ |
| | owner: orders_owner (DDL) | | owner: billing_owner (DDL) | |
| | table: orders.orders | | table: billing.invoices | |
| | table: orders.line_items | | table: billing.payments | |
| +--------------------------------+ +----------------------------+ |
| |
| Shared by every schema: transactions, connections, backups, WAL, |
| CPU, memory, max_connections. Not a resource boundary. |
+----------------------------------------------------------------------+
Each module gets a schema, a DDL owner role and a DML-only runtime role, so Postgres rejects cross-module queries while all schemas still share one database's resources.
How do you map modules to schemas and roles?
Each module gets two roles. The first is an owner role, created NOLOGIN, that owns the schema through CREATE SCHEMA billing AUTHORIZATION billing_owner. Nobody connects as it directly. Migrations run as a login role that is a member of it and switch with SET ROLE billing_owner, so every table the module creates is owned by the same identity. The second is a runtime login role, such as billing_app. It receives USAGE on its own schema and SELECT, INSERT, UPDATE, DELETE on its tables. It also needs USAGE on its sequences if it inserts into serial columns. It gets nothing anywhere else.
Grants on existing tables do not cover tables created later. Use ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing to grant the runtime privileges on future tables and sequences. Default privileges only apply to objects that role creates, which is another reason migrations must run as the owner.
Close the shared space with REVOKE CREATE ON SCHEMA public FROM PUBLIC. If nothing should live in public, revoke all access to it as well. Otherwise it becomes a place where unowned tables accumulate.
For name resolution, either pin it with ALTER ROLE billing_app SET search_path = billing or schema-qualify every name. Qualifying every name is more verbose, but it removes ambiguity. ORMs and migration tools often assume public. That produces "relation does not exist" errors, or worse, queries that silently resolve to a same-named table in another schema.
None of this applies to superusers or roles with broad grants. If developers, admin consoles, or CI routinely connect as superuser, the boundary only exists on paper.
-- One-time bootstrap, run by a privileged admin role.
-- Owner role: owns the schema and every object in it. Nobody logs in as it.
CREATE ROLE billing_owner NOLOGIN;
CREATE SCHEMA billing AUTHORIZATION billing_owner;
-- Migration login role: a member of the owner role, so it can SET ROLE to it.
CREATE ROLE billing_migrator LOGIN PASSWORD 'placeholder-change-me';
GRANT billing_owner TO billing_migrator;
-- Runtime login role: DML on its own schema only, nothing anywhere else.
CREATE ROLE billing_app LOGIN PASSWORD 'placeholder-change-me';
GRANT USAGE ON SCHEMA billing TO billing_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing TO billing_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA billing TO billing_app;
-- Cover tables and sequences created later. This only applies to objects
-- created BY billing_owner, so migrations must SET ROLE before running DDL.
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO billing_app;
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
GRANT USAGE ON SEQUENCES TO billing_app;
-- Close the shared space so unowned tables cannot accumulate in public.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC; -- only if nothing should live there
-- Unqualified names from the app resolve to billing, never to public.
ALTER ROLE billing_app SET search_path = billing;
-- Inside a migration, connected as billing_migrator:
SET ROLE billing_owner;
CREATE TABLE billing.invoices (
id bigserial PRIMARY KEY,
amount numeric(12,2) NOT NULL
);
RESET ROLE;
Bootstrap SQL for one module: a NOLOGIN owner role that owns the schema, a migration role that switches to the owner, a runtime role with default privileges covering future tables, a locked-down public schema, and a pinned search_path.
How should one module read another module's data?
Cross-module reads go through a contract the owning module publishes, never through a direct join into its tables. There are three common forms.
| Option | Coupling | Consistency | Query convenience | Extraction difficulty |
|---|---|---|---|---|
Views in an api schema |
Medium: SQL shape is the contract | Strong, same transaction | High: joinable in SQL | Medium: views become API endpoints or replicas |
| Calls through the module's public interface | Low: code interface only | Strong in-process; becomes eventual after extraction | Low: joins happen in application code | Low: swap the in-process call for a network call |
| Replicated read models | Lowest at read time | Eventual | High: consumer owns the table | Low: replication already exists |
For views, the owner creates billing_api.invoice_summary over its private tables. It then runs GRANT USAGE ON SCHEMA billing_api TO orders_rw and GRANT SELECT ON billing_api.invoice_summary TO orders_rw. The consumer needs no grant on billing.invoices, because a view checks privileges on its underlying tables as the view's owner by default.
Functions behave differently. A function without SECURITY DEFINER runs with the caller's privileges, so it is useful for wrapping logic over the published views, not for hiding the underlying tables.
Treat published views as a versioned contract. Add columns freely. For breaking changes, publish invoice_summary_v2 alongside the old view and retire the old one once consumers have moved.
Postgres also enforces this contract. It tracks view dependencies, so dropping or retyping a column a view references fails until the view is updated or dropped. That protects consumers, but it also means owner migrations must handle the api schema first.
-- Run as the billing module's owner role (billing_owner), which owns billing.*
-- Assumes roles billing_owner and orders_rw already exist.
CREATE SCHEMA IF NOT EXISTS billing_api AUTHORIZATION billing_owner;
-- Publish a stable read shape over private tables. Only expose what consumers need.
CREATE VIEW billing_api.invoice_summary AS
SELECT i.id AS invoice_id,
i.order_id,
i.status,
i.total_cents,
i.issued_at
FROM billing.invoices AS i;
COMMENT ON VIEW billing_api.invoice_summary IS
'Published contract v1. Add columns only; breaking changes go in invoice_summary_v2.';
-- Consumer gets the api schema and the view, nothing in billing itself.
GRANT USAGE ON SCHEMA billing_api TO orders_rw;
GRANT SELECT ON billing_api.invoice_summary TO orders_rw;
-- Make the boundary explicit: no direct access to private tables.
REVOKE ALL ON SCHEMA billing FROM orders_rw;
REVOKE ALL ON ALL TABLES IN SCHEMA billing FROM orders_rw;
-- Works for orders_rw: the view reads billing.invoices with the view owner's privileges.
-- SELECT invoice_id, status FROM billing_api.invoice_summary WHERE order_id = 42;
-- Fails for orders_rw with a permission error:
-- SELECT * FROM billing.invoices;
-- Dependency tracking: this owner migration fails while the view references the column.
-- ALTER TABLE billing.invoices DROP COLUMN total_cents;
-- ERROR: cannot drop column total_cents of table billing.invoices
-- because other objects depend on it
-- Fix: ship billing_api.invoice_summary_v2 first, move consumers, drop v1, then alter.
The billing module publishes a read-only view in a billing_api schema, grants a consumer role access to only that view, and shows how Postgres view dependencies block dropping a referenced column.
Should you keep foreign keys and transactions across schemas?
Postgres does not treat schemas as a transactional or referential boundary. A foreign key from billing.invoices to accounts.customers works exactly like one inside a single schema. It only needs the REFERENCES privilege on the target table. A single transaction can write to both modules and commit atomically. The database will happily erase the boundary you just drew.
Keeping these links is legitimate when the modules share a lifecycle, such as an order and its line items split only for code organization. It is also legitimate when a dangling reference would be a correctness bug rather than a cosmetic one, or when you have no intention of ever extracting the module. In those cases the foreign key is cheap, enforced insurance.
The cost appears when extraction is a real possibility. A cross-schema foreign key means the owning module cannot delete, archive, or move rows without coordinating with its consumers. A cross-module transaction means some business operation assumes both writes commit together. Neither survives a network split, and both tend to be discovered late.
The alternative is to store the other module's ID as a plain column with no constraint. Consistency then comes from application checks or eventual reconciliation. To propagate changes reliably, give each schema its own outbox table. The module writes its state change and an event row in one local transaction, and a relay publishes the events to other modules.
Be honest about the price. Integrity now lives in code and tests, not the catalog. Orphaned rows become possible. Reports and admin queries that used to be simple joins now need read models or a separate reporting role.
-- Billing stores the customer ID as a plain column: no cross-schema FK,
-- so accounts can archive or move customers without coordinating with billing.
CREATE TABLE billing.invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL, -- references accounts.customers by convention only
amount_cents bigint NOT NULL CHECK (amount_cents >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
-- Each schema owns its outbox; only this module writes to it.
CREATE TABLE billing.outbox (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_type text NOT NULL,
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
published_at timestamptz -- set by the relay after delivery
);
-- Partial index keeps the relay's "what is unsent?" scan cheap.
CREATE INDEX outbox_unpublished_idx ON billing.outbox (id) WHERE published_at IS NULL;
-- State change and event commit together in one LOCAL transaction.
-- No other module's tables are touched here.
BEGIN;
WITH inv AS (
INSERT INTO billing.invoices (customer_id, amount_cents)
VALUES (4211, 15900)
RETURNING id, customer_id, amount_cents
)
INSERT INTO billing.outbox (event_type, payload)
SELECT 'invoice.created',
jsonb_build_object('invoice_id', id, 'customer_id', customer_id, 'amount_cents', amount_cents)
FROM inv;
COMMIT;
-- Relay: claim a batch without blocking other relay workers.
-- It publishes the rows, then sets published_at for those IDs in the same transaction.
SELECT id, event_type, payload
FROM billing.outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;
A billing module keeps the customer ID as an unconstrained column and writes each invoice together with its event to a per-schema outbox in one local transaction, which a relay then claims and publishes to other modules.
How do migrations and connection pools work with per-module roles?
Give each module its own migration directory and its own history table inside its own schema. Run those migrations as the module's owner role. The database then refuses any migration that tries to alter another module's tables, which is a stronger guarantee than code review. Cross-module dependencies need explicit ordering. If billing builds a view on orders' published views, orders must migrate first. Declare that dependency in the deploy pipeline instead of relying on alphabetical directory order. Treat a change to a published view as a contract change, and deprecate the old shape before you drop it.
At runtime, separate login roles mean separate connection pools. The alternative is one pool that issues SET ROLE per checkout. That needs disciplined resets, because a connection returned with the wrong role leaks privileges to the next borrower. Separate pools are simpler to reason about, but they multiply.
Illustrative estimate (assumed inputs): 6 modules, 8 app instances, and a pool size of 10 per module per instance gives 6 × 8 × 10 = 480 potential connections. That is far above a typical max_connections setting. One shared pool of 20 per instance would need only 160. You also have to reserve headroom for superuser slots, migrations, replication and ad-hoc admin sessions.
Per-module pools fail sooner because idle connections in a quiet module can't serve a busy one. Size each pool from that module's measured concurrency, not a uniform default. Cap the total explicitly, or put a server-side pooler in front of Postgres.
How do you verify the boundary in CI and adopt it on an existing monolith?
Treat the boundary as a property of the database that CI checks on every build, the same way you check a migration applies cleanly.
Catalog assertions
Run three queries against a freshly migrated database and fail the build if any of them returns rows:
- Grants outside the role's schema. Join
information_schema.role_table_grantsongranteeagainst your module-to-schema mapping, and return any row wheretable_schemais not that role's own schema. This view only lists grants involving roles the querying session is a member of, so run it as the database owner or a superuser. - New objects in
public. Joinpg_classtopg_namespaceonrelnamespaceand return any relation inpublicthat isn't on a shrinking allowlist. - Unexpected roles. Scan
pg_rolesfor login roles that don't appear in the mapping.
Runtime denial test
For each runtime role, open a connection and attempt a read on another module's table. Assert that it fails with SQLSTATE 42501 (insufficient_privilege). A grant can look correct in the catalog and still be bypassed through role membership or a leftover default privilege. This test catches that case because it exercises the real login path.
Adopting it incrementally
- Move one module's tables with
ALTER TABLE ... SET SCHEMA. Indexes, constraints and owned sequences move with each table. - Leave a view under the old name in
public. A simple single-table view is auto-updatable, so existing writes keep working. - Revoke the other modules' privileges on the moved tables, then add those tables to the CI mapping.
- Repeat for the next module.
- Once the logs show no other callers using a view, drop it and remove it from the allowlist.
The payoff is that violations show up as a failing build, so reviewers don't have to catch them. If you later extract a module into its own service, the CI mapping already records exactly which data that module depends on.
When should you avoid schema per module, and what are the alternatives?
Skip the pattern when the team is small enough that everyone already knows who owns which table. The roles, grants, view contracts and per-role pool budgets are real upfront work, and every change to a published view needs coordination between the owning module and its consumers. Skip it when two modules genuinely share data, such as a ledger that both billing and accounting write to. Forcing an owner onto that data produces a stream of views and write APIs that exist only to work around the split. Most importantly, skip it while boundaries are still unclear. Grants and contracts harden whatever lines you draw, and moving a table between schemas later means rewriting both permissions and consumers.
| Option | Isolation strength | Resource isolation | Cross-module queries and transactions | Operational overhead | Extraction effort |
|---|---|---|---|---|---|
| Single schema, conventions | Social only | None | Unrestricted | Lowest | Highest |
| Schema per module | Enforced by grants | Shared cluster | Joins via views; one transaction | Moderate | Moderate |
| Database per module, same cluster | Strong | Shared CPU, memory, WAL | Needs postgres_fdw or app code; no atomic transaction |
Higher | Lower |
| Separate instances | Strongest | Full | App-level only | Highest | Lowest |
Enforced boundaries pay off when several teams change the same codebase and accidental coupling would otherwise surface late. Extraction is still not free. Shared transactions must become sagas or outbox flows, sequences and IDs must move with their owner, and every remaining view consumer has to switch to an API.
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 schema per module postgres, 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.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
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.
- Consumers learn about a breaking change at review time rather than from production errors.
- 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.