Monolith Schema per Module: Moving Tables into a PostgreSQL Schema

What a PostgreSQL schema gives a modular monolith, and what it does not

A PostgreSQL schema is a namespace inside a single database. Tables in billing and catalog sit in the same database and share everything below the name: transactions, connection pool, backups, CPU, memory, autovacuum workers and the WAL stream. A query can still join across schemas, and a foreign key can still point from one schema to another.

Teams adopt a monolith schema per module because it makes table ownership visible in two places. The first is the name, so billing.invoice states who owns it. The second is the permission system, because grants can be issued per schema. Moving a table between schemas only changes catalog metadata and does not rewrite data. That keeps the step cheap and easy to undo.

A schema provides no physical or performance isolation. A runaway report in one module still competes for the same buffers, I/O and vacuum capacity as every other module.

A schema per module is the cheapest option that the database itself enforces. It is also the last one that keeps single-transaction consistency between modules.

Who should do this and when to avoid it

The move pays off for a module that already behaves like a unit. That means a team owns it and is unlikely to be reorganized next quarter. Other code reaches it through an identifiable API, such as a service class or an internal interface, rather than through ad hoc queries. And you can list its tables without arguing about them. If two teams both claim invoices, a schema boundary will not settle the dispute. It will only move the argument into grant reviews.

When to avoid it or wait

Splitting too finely. Every schema adds grants to maintain, search_path decisions, and often compatibility views. Twenty schemas for twenty small modules means twenty sets of these, multiplied across environments. Start with the few modules whose boundaries matter most.

Single-schema tooling. ORMs, migration tools and test fixtures often assume everything lives in public. Expect to change model mappings, migration ordering, fixture loaders and database reset scripts. If that work competes with other priorities, delay the split until you can finish it in one pass rather than leaving tooling half-converted.

Load problems. Schemas share the same instance, buffer cache, connections and WAL. A noisy module stays noisy.

False readiness signals. A clean schema split does not mean the module is ready for its own database. Code may still join across schemas inside one transaction or call the module synchronously in hot paths. That coupling survives the move untouched, and it is the hard part of any later extraction.

Inventory what the module owns and who touches it

Start with the catalog, not the code. Query pg_class for the module's tables, sequences and views, and use pg_depend to tie sequences to the columns that own them. List triggers from pg_trigger and enum types from pg_type where typtype = 'e'. Search pg_proc.prosrc for the module's table names, because PL/pgSQL function bodies are stored as text. The dependency graph does not track the tables they reference, so a function in another module that writes to your tables will not appear any other way.

Next, find every reader and writer. Grep the codebase for each table name, including raw SQL strings, ORM model mappings and migration files. Static search misses dynamically built queries. Where the pg_stat_statements extension is installed or query logging is enabled, compare the observed statements against your grep results.

Sort each cross-module access into one of three classes:

Dropping a foreign key gives up a guarantee that the database enforced for free. You have to replace it with application-level checks, a cleanup job that finds orphans, or a documented decision to accept some inconsistency.

Expect most of the project's effort to go into this inventory and the fixes that come out of it. The schema move later is mechanical.

Copy-- Inventory for the orders module (tables prefixed 'order', currently in public).

-- 1. Tables, partitioned tables, views, matviews and sequences
SELECT c.relname, c.relkind
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r','p','v','m','S')
  AND c.relname LIKE 'order%';

-- 2. Sequences tied to columns: 'a' = serial/OWNED BY, 'i' = identity
SELECT s.relname AS seq, t.relname AS tbl, a.attname AS col
FROM pg_depend d
JOIN pg_class s ON s.oid = d.objid AND s.relkind = 'S'
JOIN pg_class t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE d.classid = 'pg_class'::regclass AND d.deptype IN ('a','i')
  AND t.relname LIKE 'order%';

-- 3. User-defined triggers (skip internal FK triggers)
SELECT tgrelid::regclass AS tbl, tgname
FROM pg_trigger
WHERE NOT tgisinternal
  AND tgrelid IN (SELECT oid FROM pg_class WHERE relname LIKE 'order%');

-- 4. Enum types in the schema and their labels
SELECT t.typname, e.enumlabel
FROM pg_type t JOIN pg_enum e ON e.enumtypid = t.oid
JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'e' AND n.nspname = 'public'
ORDER BY t.typname, e.enumsortorder;

-- 5. Function bodies are text, so search them; catches other modules' writers
SELECT p.oid::regprocedure AS func
FROM pg_proc p
WHERE p.prosrc ~* '\morders\M|\morder_lines\M';

-- 6. Observed statements (requires pg_stat_statements); compare with grep
SELECT calls, left(query, 120) AS query
FROM pg_stat_statements
WHERE query ~* '\morders\M|\morder_lines\M'
ORDER BY calls DESC LIMIT 50;

Catalog queries that list an orders module's relations, owned sequences, triggers, enum types and the functions and observed statements that reference its tables.

How the move works: CREATE SCHEMA and ALTER TABLE ... SET SCHEMA in a migration

The schema move itself is the small part. The migration runs in a single transaction: CREATE SCHEMA billing, then SET LOCAL lock_timeout = '3s', then one ALTER TABLE ... SET SCHEMA billing per table. Because it is one transaction, the module moves completely or not at all.

What moves with the table

SET SCHEMA changes catalog metadata and does not rewrite data. Indexes and constraints move with the table, including foreign keys in both directions. Foreign keys point at the table's internal identity, not its name, so references from tables that stay in public keep working. Grants are attached to the table, so they come along. Sequences owned by a column also move, which covers serial and identity columns.

Some objects need their own ALTER ... SET SCHEMA:

• free-standing sequences
• views
• functions
• enum and composite types

Views keep working without the move, because they bind to the table and not its name, but they stay behind in the old schema. Function bodies that refer to tables by unqualified name are resolved at call time, so they break if the new schema is not on the search_path.

The lock

Each statement needs an ACCESS EXCLUSIVE lock. The lock is held only briefly, but acquiring it can be slow. The request waits behind any open transaction that has touched the table, and every query that arrives afterwards waits behind the request. lock_timeout limits that stall. When the timeout fires, the migration fails with SQLSTATE 55P03, and the runner should back off and retry a bounded number of times.

Rollback is the same statements in reverse, ALTER TABLE billing.invoices SET SCHEMA public. It stays safe as long as no deployed code depends only on the new qualified names.

Copy-- up: move the billing module out of public in one transaction
BEGIN;

CREATE SCHEMA IF NOT EXISTS billing;

-- Fail fast instead of queueing behind a long transaction.
-- A timeout raises SQLSTATE 55P03; the runner backs off and retries.
SET LOCAL lock_timeout = '3s';

-- Indexes, constraints, FKs, grants and owned sequences move with each table.
ALTER TABLE public.invoices      SET SCHEMA billing;
ALTER TABLE public.invoice_lines SET SCHEMA billing;
ALTER TABLE public.payments      SET SCHEMA billing;

-- Objects that do not follow the table need their own statement.
ALTER SEQUENCE public.invoice_number_seq SET SCHEMA billing;  -- free-standing
ALTER VIEW public.open_invoices SET SCHEMA billing;
ALTER FUNCTION public.invoice_total(bigint) SET SCHEMA billing;
ALTER TYPE public.invoice_status SET SCHEMA billing;          -- enum

COMMIT;

-- down: the same statements in reverse, also in one transaction
-- BEGIN;
-- SET LOCAL lock_timeout = '3s';
-- ALTER TYPE billing.invoice_status SET SCHEMA public;
-- ALTER FUNCTION billing.invoice_total(bigint) SET SCHEMA public;
-- ALTER VIEW billing.open_invoices SET SCHEMA public;
-- ALTER SEQUENCE billing.invoice_number_seq SET SCHEMA public;
-- ALTER TABLE billing.payments      SET SCHEMA public;
-- ALTER TABLE billing.invoice_lines SET SCHEMA public;
-- ALTER TABLE billing.invoices      SET SCHEMA public;
-- DROP SCHEMA billing;  -- fails unless the schema is empty
-- COMMIT;

A single-transaction migration that creates the billing schema, caps lock waits with lock_timeout, moves the tables plus the free-standing sequence, view, function and enum type that do not follow automatically, and shows the reverse statements used for rollback.

Keeping the application running during the cutover

Deployed code depends on the new names only after the application has moved to them, so the cutover is about how code resolves table names.

Qualify names in code

Change ORM mappings to set the schema explicitly, and rewrite raw SQL to say billing.invoices instead of invoices. A qualified name resolves the same way for every role and connection. Search the codebase for raw SQL in reporting queries, background jobs and migrations, because these are the places most often missed.

search_path as a bridge

search_path can be set per role (ALTER ROLE), per database (ALTER DATABASE) or per session (SET). Adding billing to it lets old unqualified queries keep working while the code changes ship. Remove it afterwards, because it hides the boundary you are building and causes three problems:

• Inconsistent resolution. An unqualified name can point to different tables depending on which role or connection runs the query.
• Leaking session settings. A pooler that reuses server connections can carry one client's SET into another client's session. In transaction mode, the setting can also disappear.
• Unsafe SECURITY DEFINER functions. A function without its own SET search_path resolves names using the caller's path. A caller can then plant an object that the function runs with elevated privileges.

Compatibility views

A view such as public.invoices that selects from billing.invoices keeps old queries working without search_path. Views that read a single table, with no joins, aggregates or DISTINCT, are automatically updatable, so inserts and updates through them still work. SELECT * is expanded when the view is created, so recreate the view whenever columns change.

Copy app_legacy role ──► public.invoices (view) ──┐
                                              ▼
 app_billing role ─────────────────► billing.invoices (table)

Copy-- Cutover for one table: move it, leave a compatibility view, bridge briefly.
BEGIN;
CREATE SCHEMA IF NOT EXISTS billing;
ALTER TABLE public.invoices SET SCHEMA billing;

-- Single-table view with no joins, aggregates or DISTINCT, so it is
-- auto-updatable. SELECT * is expanded now: recreate it after column changes.
CREATE VIEW public.invoices AS
  SELECT * FROM billing.invoices;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.invoices TO app_legacy;

-- New code uses qualified names and gets direct access.
GRANT USAGE ON SCHEMA billing TO app_billing;
GRANT SELECT, INSERT, UPDATE, DELETE ON billing.invoices TO app_billing;
COMMIT;

-- Temporary bridge for any other unqualified billing table not yet covered
-- by a view. Takes effect for new sessions of this role only.
ALTER ROLE app_legacy SET search_path = public, billing;

-- SECURITY DEFINER functions pin their own path and qualify names, so a
-- caller's search_path cannot redirect them to planted objects.
CREATE OR REPLACE FUNCTION billing.void_invoice(p_id bigint)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
  UPDATE billing.invoices SET status = 'void' WHERE id = p_id;
$$;
REVOKE ALL ON FUNCTION billing.void_invoice(bigint) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION billing.void_invoice(bigint) TO app_billing;

-- After all code is qualified: remove the bridge, then the view.
-- ALTER ROLE app_legacy RESET search_path;
-- DROP VIEW public.invoices;

Moving public.invoices into the billing schema with an auto-updatable compatibility view, a role-level search_path bridge to remove later, and a SECURITY DEFINER function that pins its own search_path.

Enforcing table ownership with roles and verifying the boundary

Making the boundary visible is not enough. Make it fail loudly when crossed. Create a billing_owner role with NOLOGIN and make it the owner of the billing schema and every table in it. Migrations run as this role. The application connects as app_billing, which receives DML grants. Other modules' roles get USAGE on the schema and SELECT only on the published views. They get no grants on the base tables. Also run REVOKE CREATE ON SCHEMA public FROM PUBLIC so nobody can recreate a shadow table in public. Use ALTER DEFAULT PRIVILEGES so tables added later start out private instead of depending on someone remembering the grant.

Test the boundary on purpose. Connect as app_orders and select from billing.invoice_lines directly. You should get a permission denied error. Put that query in an integration test that expects the error, so a careless GRANT breaks the build.

To find existing coupling, look for foreign key constraints whose referencing and referenced schemas differ. Join information_schema.table_constraints with constraint_column_usage, or query pg_constraint directly. The information_schema views only show objects the current role has privileges on, so run this as a superuser. For views, join pg_depend to pg_rewrite on objid. Map ev_class to the view and refobjid to the table, then keep the rows where the two live in different schemas.

Grants only cover runtime roles. A migration or admin connection running as a superuser can still join across schemas. Add a CI step that scans SQL, migrations and ORM mappings for schema.table references outside the module's allowlist. Also add the catalog queries above as a check that fails when the count increases.

Copy-- Run as a superuser. NOLOGIN owner role: migrations run as it, apps never do.
CREATE ROLE billing_owner NOLOGIN;
ALTER SCHEMA billing OWNER TO billing_owner;
ALTER TABLE billing.invoice_lines OWNER TO billing_owner;  -- repeat per table

-- Billing gets DML on its own tables; orders sees only the published view.
GRANT USAGE ON SCHEMA billing TO app_billing, app_orders;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing TO app_billing;
GRANT SELECT ON billing.invoice_summary TO app_orders;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- no shadow tables in public

-- Tables billing_owner creates later grant DML to app_billing only.
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_billing;

-- Boundary test: passes only if app_orders is denied the base table.
DO $$
BEGIN
  SET LOCAL ROLE app_orders;
  PERFORM 1 FROM billing.invoice_lines LIMIT 1;
  RAISE EXCEPTION 'boundary broken: app_orders can read billing.invoice_lines';
EXCEPTION WHEN insufficient_privilege THEN
  RAISE NOTICE 'boundary holds';
END $$;

-- Cross-schema foreign keys.
SELECT c.conname, c.conrelid::regclass AS referencing, c.confrelid::regclass AS referenced
FROM pg_constraint c
JOIN pg_class s ON s.oid = c.conrelid
JOIN pg_class t ON t.oid = c.confrelid
WHERE c.contype = 'f' AND s.relnamespace <> t.relnamespace;

-- Views that read tables or views in another schema.
SELECT DISTINCT r.ev_class::regclass AS view, d.refobjid::regclass AS referenced
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid AND d.classid = 'pg_rewrite'::regclass
JOIN pg_class v ON v.oid = r.ev_class
JOIN pg_class t ON t.oid = d.refobjid AND d.refclassid = 'pg_class'::regclass
WHERE v.relnamespace <> t.relnamespace;

Sets up a NOLOGIN owner role and narrow grants for the billing schema, asserts that app_orders is denied the base table, and lists cross-schema foreign keys and view dependencies so CI can fail when either count grows.

Operational tooling, cost and engineering time

Database tooling usually assumes everything lives in public. When that assumption breaks, the tools rarely raise an error. They skip or misfile the moved tables.

The SQL takes minutes. Most of the cost is the access inventory, rewriting cross-module queries and the tooling churn listed above.

Illustrative estimate (assumed numbers, not a benchmark):

Hours = inventory + (cross-module queries × hours per query) + (tooling touchpoints × hours each)

With assumed inputs of a 16-hour inventory, 12 queries at 2 hours each and 8 touchpoints at 1 hour each:

16 + 24 + 8 = 48 hours

Substitute your own counts from the inventory.

The return comes from four places:

• Visible ownership. Every table name shows its owning module, so reviewers do not need to look up ownership.
• Earlier detection. Grants reject boundary violations at query time instead of depending on code review to catch them.
• Easy reversal. SET SCHEMA is a catalog change, so moving tables back is cheap.
• A head start on extraction. If the module later moves to its own database, the work begins from a verified dependency list instead of an archaeology project.

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 monolith schema per module, 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.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

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.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• 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.