Database per Service on PostgreSQL: Schemas, Roles and Limits

Three ways to isolate services on one PostgreSQL cluster

"Database per service" on a shared PostgreSQL cluster means each service owns its tables exclusively. No other service reads or writes them directly. The cluster that hosts them is still shared. Teams choose this to get clear ownership and independent schema changes without running, patching and monitoring one cluster per service.

There are three ways to draw the boundary.

In a schema-per-service setup, a single SQL query can still join across services if privileges allow it. That makes accidental coupling easy.

A separate database per service blocks cross-database queries unless you add an extension such as postgres_fdw. Even so, roles, max_connections, the WAL stream and shared_buffers are still shared across the whole cluster.

Only a separate cluster isolates resources.

It helps to think in three isolation layers:

• Namespace: where objects live and how they are named.
• Privilege: who can touch which objects.
• Resource: CPU, memory, I/O, connections and WAL throughput.

Schemas and roles cover only the first two. A runaway batch job in one service can still fill the WAL, evict hot pages from the buffer cache or use up the connection slots its neighbours need.

This model fits teams that want enforceable ownership boundaries and accept that resource isolation has to come from guardrails and pooling. The later sections cover both.

CopyA) Schema per service     B) Database per service   C) Cluster per service
+----------------------+  +----------------------+  +---------+ +---------+
| cluster              |  | cluster              |  | cluster | | cluster |
| +------------------+ |  | +-------+ +-------+  |  | db: ord | | db: bil |
| | db: app          | |  | |db:ord | |db:bil |  |  | catalog | | catalog |
| | +-----+ +-----+  | |  | |catalog| |catalog|  |  | roles   | | roles   |
| | |s:ord| |s:bil|  | |  | +-------+ +-------+  |  | conns   | | conns   |
| | +-----+ +-----+  | |  |                      |  | WAL     | | WAL     |
| +------------------+ |  |                      |  | buffers | | buffers |
| shared: roles,       |  | shared: roles,       |  |         | |         |
| connections, WAL,    |  | connections, WAL,    |  |         | |         |
| shared_buffers       |  | shared_buffers       |  |         | |         |
+----------------------+  +----------------------+  +---------+ +---------+

Isolates:
namespace + privilege     + catalog per database    + resources

<-- lower ops cost, shared resources ----- higher ops cost, full isolation -->

Schema per service and database per service separate namespaces, with privileges enforced through roles. Only a cluster per service also isolates connections, WAL and shared_buffers.

Why teams use database per service on a shared cluster

The pattern exists to stop services from coupling through the database. When several services read and write the same tables, every column rename, index change or data-type change needs coordination across teams. A migration in one service can break queries in another without either team noticing until production. Giving each service exclusive ownership of its data fixes this. The owning team can change its schema freely, because the only contract other services depend on is the service's API or the events it publishes.

The shared cluster is the cost compromise. A cluster per service gives the strongest isolation, but each cluster needs its own patching, backups, monitoring, failover and capacity planning. For many teams, especially those with many small services and a small platform team, that overhead outweighs the isolation benefit. Hosting all service-owned schemas or databases on one cluster keeps one set of operational tasks while still giving each service:

• Clear ownership. Every table has exactly one owning role and one owning team.
• Independent migrations. Each service runs its own migrations with its own role, against objects no one else references.
• Enforced access rules. Privileges block other services from reading or writing data they do not own, instead of relying on convention.
• A path to extraction. If the boundaries stay clean, a service that outgrows the shared cluster can be moved to its own cluster without rewriting other services.

How it works

The isolation comes from three PostgreSQL mechanisms working together.

Namespaces. Each service gets either its own schema inside one database or its own database inside the cluster. A schema is a namespace for tables, views, functions and sequences, so orders.customers and billing.customers are separate objects. A database goes further: it has its own system catalog for those objects, and ordinary SQL cannot reference objects in another database.

Roles and privileges. PostgreSQL decides access through roles. Each service has an owner role that owns its objects, a migration role that changes them, and an application role that only reads and writes data. Grants give each service's roles access to that service's objects and nothing else. Default privileges make sure tables created later receive the same grants. Because roles are cluster-wide, one role model covers both the schema-per-service and the database-per-service layouts.

Guardrails and pooling. Namespaces and privileges do not limit resource use, so the model adds per-role settings such as connection limits, statement timeouts and idle-in-transaction timeouts, and puts a connection pooler in front of the cluster with a fixed budget per service. These limit how much of the shared cluster one service can consume. They do not fully isolate it.

At runtime, a service connects through the pooler as its application role. Its search_path points at its own schema, its queries can touch only objects it has been granted, and its sessions are bounded by the role's limits. Migrations connect as the migration role, switch to the owner role with SET ROLE, and apply DDL so the new objects belong to the owner. CI checks catalog queries for leftover PUBLIC grants and cross-schema references, so boundaries that erode get caught before they ship. The sections below cover each of these pieces in detail.

How to build a least-privilege role model per service

Each service gets three roles. The owner role (orders_owner) is NOLOGIN and owns the schema and every object in it. Nobody connects as it, so its credentials can't leak. The migration role (orders_migrator) can log in, is a member of the owner role, and runs SET ROLE orders_owner before applying DDL. Objects created that way belong to the owner rather than to whichever person or pipeline ran the migration. The application role (orders_app) can log in and holds only USAGE on the schema plus SELECT, INSERT, UPDATE and DELETE on tables, and USAGE on sequences. It cannot create or alter anything.

Before any of this matters, close the default openings. Run REVOKE CREATE, USAGE ON SCHEMA public FROM PUBLIC and REVOKE CONNECT ON DATABASE ... FROM PUBLIC. Then grant CONNECT explicitly to each role that needs it.

New tables need grants too, which is the job of ALTER DEFAULT PRIVILEGES. A common mistake is that default privileges apply only to objects created by the role named in FOR ROLE. If you omit that clause, they apply to objects created by the role running the statement. If you set them as an admin but migrations create objects as orders_owner, the app role gets nothing on new tables. Always write FOR ROLE orders_owner.

Pin each role's search path with ALTER ROLE orders_app SET search_path = orders. The default path is "$user", public, which causes two problems. Unqualified names can resolve to the wrong schema. Worse, anyone with CREATE on public can plant a same-named table or function that shadows yours.

Two things quietly undo this model: an old grant to PUBLIC, or a shared superuser that runs every service's migrations. Neither shows up in a schema diff. Audit both with catalog queries in CI.

Copy-- Run as an admin in database appdb. Passwords are placeholders.
-- 1. Close the default openings, then grant CONNECT explicitly.
REVOKE CREATE, USAGE ON SCHEMA public FROM PUBLIC;
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;

-- 2. Three roles per service.
CREATE ROLE orders_owner NOLOGIN;  -- owns everything, never connects
CREATE ROLE orders_migrator LOGIN PASSWORD 'change-me-migrator' IN ROLE orders_owner;
CREATE ROLE orders_app LOGIN PASSWORD 'change-me-app';
GRANT CONNECT ON DATABASE appdb TO orders_migrator, orders_app;

-- 3. Schema owned by the NOLOGIN role; app gets USAGE only, no CREATE.
CREATE SCHEMA orders AUTHORIZATION orders_owner;
GRANT USAGE ON SCHEMA orders TO orders_app;

-- 4. DML on objects that already exist.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA orders TO orders_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA orders TO orders_app;

-- 5. DML on future objects. FOR ROLE must name the role that will CREATE
--    them (orders_owner, via SET ROLE). Omit it and these defaults attach
--    to the admin running this script, so new tables get no app grants.
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO orders_app;
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA orders
  GRANT USAGE ON SEQUENCES TO orders_app;

-- 6. Pin search_path so unqualified names cannot resolve to public.
ALTER ROLE orders_app SET search_path = orders;
ALTER ROLE orders_migrator SET search_path = orders;

-- Each migration starts with: SET ROLE orders_owner;

-- 7. CI audit: any leftover PUBLIC table grants should return zero rows.
SELECT table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'PUBLIC'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');

A least-privilege setup for one service: a NOLOGIN owner, a migrator that runs DDL via SET ROLE, a DML-only app role, default privileges scoped with FOR ROLE, a pinned search_path, and a CI query that finds leftover PUBLIC table grants.

Per-role guardrails and what they cannot protect

Privileges control what a service can touch. Guardrails control how much damage it can do. ALTER ROLE orders_svc CONNECTION LIMIT 40 caps one service's sessions, and ALTER DATABASE ... CONNECTION LIMIT caps a whole database. ALTER ROLE orders_svc SET statement_timeout = '5s' applies at login, as do idle_in_transaction_session_timeout and lock_timeout. Together they stop a service from running away with one query, forgetting a COMMIT or waiting forever behind another service's lock. Note that a role can override these with its own SET unless your client configuration or review process prevents it.

None of this is resource isolation. CPU, disk I/O, shared buffers and OS memory are shared. So are WAL volume, autovacuum workers, the cluster-wide max_connections and replication. The transaction ID counter and the xmin horizon are shared too.

The xmin horizon is the hardest to notice. A transaction left open in billing keeps its snapshot, and vacuum cannot remove row versions newer than that snapshot's xmin. With schema-per-service, every service shares one database, so every service's tables bloat. Across databases the leak still happens through shared catalogs, replication slots and hot_standby_feedback from replicas. Meanwhile XIDs keep advancing, and freezing falls behind, so wraparound pressure builds.

To find the offender, query pg_stat_activity for usename, state, xact_start and age(backend_xmin), ordered by that age. Then check pg_replication_slots.xmin.

Bulk writes cause a related problem. One service's backfill generates WAL that every replica must replay, so replication lag rises for all services at once.

Copy-- Guardrails: cap sessions and bound runaway work for one service role
ALTER ROLE orders_svc CONNECTION LIMIT 40;
ALTER DATABASE orders CONNECTION LIMIT 60;
ALTER ROLE orders_svc SET statement_timeout = '5s';
ALTER ROLE orders_svc SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE orders_svc SET lock_timeout = '2s';
-- Role-level settings apply to new sessions only; existing ones keep old values

-- Find sessions holding back the xmin horizon (oldest snapshot first)
SELECT pid, datname, usename, state, xact_start,
       age(backend_xmin) AS xmin_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- Replication slots can pin the horizon too (including hot_standby_feedback
-- from a replica using a physical slot)
SELECT slot_name, slot_type, database, active,
       age(xmin) AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

-- Wraparound pressure: how far each database is from its last full freeze
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Once confirmed, end the offending session (pid from the first query)
-- SELECT pg_terminate_backend(12345);

Sets per-role and per-database guardrails, then uses pg_stat_activity, pg_replication_slots and pg_database to find which session or slot is holding back the shared xmin horizon and building wraparound pressure.

Connection pooling with fixed per-service budgets

With database per service, each service has its own pool, and each replica of that service holds its own connections. Idle connections still take a backend process and count against max_connections. They add up quickly. Ten replicas holding ten idle connections each is 100 slots for a service that may run only a few concurrent queries. Put a pooler in front of the cluster and give each service a fixed server-side budget. All the budgets together must sum to less than max_connections.

Illustrative estimate (assumed numbers, not a benchmark)

Formula: per_service_budget = floor((max_connections − superuser_reserved − migrations − admin) × (1 − headroom) / services)

Assumed inputs:

• max_connections = 300
• superuser_reserved = 3
• migrations = 10
• admin and monitoring = 17
• headroom = 20%
• services = 12

Result: (300 − 3 − 10 − 17) × 0.8 / 12 = 18 server connections per service.

Transaction-mode pooling hands a server connection to a different client after each transaction. A session-level SET search_path therefore stays on that connection, and the next client to receive it inherits the setting. That client may belong to a different service if roles share a pool. Set search_path, statement_timeout and idle_in_transaction_session_timeout with ALTER ROLE instead. Role-level settings are applied when the connection is established, so every connection a role receives starts with the same values.

Keeping service boundaries from eroding

Schema per service makes coupling cheap. Every service lives in the same database, so a developer can write JOIN billing.invoices from the orders service, or add a foreign key from orders.order_lines to catalog.products. The query planner sees one catalog, and the change looks harmless in review. Each shortcut binds two services to the same physical database. One join is easy to undo. Fifty, spread across views and migrations, mean the boundary is gone.

The rule is simple: no cross-schema foreign keys, joins or views. Grants from section 2 enforce part of this at runtime, but migration roles often hold broader privileges, so enforce the rule in CI as well.

Two catalog queries cover most cases. For foreign keys, select from pg_constraint where contype = 'f', and compare the namespace of conrelid with the namespace of confrelid by joining both to pg_class. Any row where they differ is a violation. For views, join pg_depend to pg_rewrite on objid. That gives you the view that owns each rewrite rule, and refobjid gives the referenced relation. Flag any view whose referenced relation sits in a different schema. Keep an explicit allowlist for shared extension schemas, and fail the build on anything else.

A database boundary is stronger. PostgreSQL cannot query across databases without an extension such as postgres_fdw. Adopting it brings back the coupling you split apart, so record every foreign server and foreign table as a dependency.

Clean boundaries save engineering time later. If a schema has no inbound references, moving it to its own cluster is mostly a dump-and-restore job. It does not require months of untangling queries.

Copy-- CI check: every returned row is a cross-schema dependency. The build fails if any row comes back.
WITH allowlist(nspname) AS (
  -- System schemas plus shared extension schemas that every service may reference
  VALUES ('pg_catalog'), ('information_schema'), ('extensions')
)
-- 1. Foreign keys whose referencing and referenced tables are in different schemas
SELECT 'foreign_key' AS kind,
       format('%I.%I', sn.nspname, s.relname) AS source,
       format('%I.%I', tn.nspname, t.relname) AS target,
       c.conname::text AS detail
FROM pg_constraint c
JOIN pg_class s      ON s.oid = c.conrelid
JOIN pg_namespace sn ON sn.oid = s.relnamespace
JOIN pg_class t      ON t.oid = c.confrelid
JOIN pg_namespace tn ON tn.oid = t.relnamespace
WHERE c.contype = 'f'
  AND sn.oid <> tn.oid
  AND tn.nspname NOT IN (SELECT nspname FROM allowlist)
UNION ALL
-- 2. Views (and materialized views) that read relations in another schema.
--    Each view's rewrite rule records a pg_depend row for every relation it references.
SELECT DISTINCT 'view',
       format('%I.%I', vn.nspname, v.relname),
       format('%I.%I', rn.nspname, rel.relname),
       NULL::text
FROM pg_depend d
JOIN pg_rewrite r    ON r.oid = d.objid
JOIN pg_class v      ON v.oid = r.ev_class      -- the view that owns the rule
JOIN pg_namespace vn ON vn.oid = v.relnamespace
JOIN pg_class rel    ON rel.oid = d.refobjid    -- the relation the view references
JOIN pg_namespace rn ON rn.oid = rel.relnamespace
WHERE d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND vn.oid <> rn.oid
  AND vn.nspname NOT IN (SELECT nspname FROM allowlist)
  AND rn.nspname NOT IN (SELECT nspname FROM allowlist)
ORDER BY 1, 2, 3;

A catalog query for CI that lists cross-schema foreign keys from pg_constraint and cross-schema view references from pg_depend joined to pg_rewrite, with an allowlist for system and shared extension schemas, so any returned row can fail the build.

Backup, restore and moving a service to its own cluster

Backups follow the same split. pg_dump can scope a logical dump to one service with --schema or by dumping its database. Physical backups and WAL archiving work on the whole cluster, though. Point-in-time recovery and failover therefore apply to every service at once. If one service corrupts its own data, you cannot rewind it alone. You restore the cluster to a scratch instance at the target time, dump that service's schema, and load it back by hand. Plan this procedure before you need it.

Extracting a service

1. Provision the new cluster with matching major version, extensions, encoding and collation settings.
2. Copy roles and schema. pg_dumpall --roles-only exports every role, so filter it down to this service's roles. Load the schema with pg_dump --schema-only.
3. Move the data.
   • A plain dump and restore is the simplest option, and it works if the service is small or can tolerate a write freeze.
   • For minimal downtime, create a publication on the old cluster and a subscription on the new one. Logical replication does not carry DDL, so freeze schema changes until cutover.
4. Sync sequences. Logical replication does not copy sequence values. Before cutover, set each sequence on the new cluster to at least its source value. If you skip this, new inserts will collide with existing rows.
5. Handle large objects separately. Logical replication ignores them. When you select specific schemas, pg_dump omits them unless you request them explicitly.
6. Verify row counts, checksums on key tables and constraints.
7. Cut over by repointing the service's pooler entry. Then drop the subscription.
8. Decommission the old schema. Revoke access first and watch for errors. Drop it once a full release cycle passes cleanly.

Alternatives to database per service on a shared cluster

Database per service on a shared cluster is one point on a range. The main alternatives are:

• Shared database with shared tables. All services read and write the same schema, often the starting point when a monolith is split. It is the cheapest to run and makes cross-service queries and transactions trivial. The cost is the coupling this article is designed to avoid: any schema change can break another service, and no team clearly owns the data.
• Cluster per service. Each service gets its own PostgreSQL cluster, self-run or as a managed instance. This isolates connections, WAL, buffers, autovacuum, backups, failover and major versions. It also multiplies operational work, which is the reason many teams start on a shared cluster.
• Single owning service with an API. One service owns a domain's data in its own schema or database, and other services read it only through that service's API. This works with any of the layouts above. It is how a shared cluster avoids becoming a shared database in practice.
• Event-driven read models. Instead of querying another service's tables, a service subscribes to events the owner publishes and keeps its own local copy of the fields it needs. This removes runtime dependencies between databases at the cost of eventual consistency and extra code to build and maintain the copies.
• Foreign data wrappers. postgres_fdw lets one database query tables in another. It is a pragmatic bridge during a migration, but as a permanent design it reintroduces cross-service coupling at the database level.
• Row-level isolation in shared tables. Row-level security policies can restrict which rows a role sees. This suits multi-tenant data within one service better than service boundaries, because the services would still share table definitions and migrations.

The next section compares the most common of these options on cost and fit.

When to use a shared cluster, when to split, and the cost trade-offs

A shared cluster works well when services have similar durability needs, can run on the same major version, and none of them dominates the load. Split a service off when any of the following becomes true:

• Repeated noisy-neighbor incidents. WAL, checkpoints, autovacuum workers and disk I/O are shared at cluster level, and per-role limits do not cover them.
• Different RPO or durability needs. Replica topology, archiving and backup cadence apply to the whole cluster.
• Divergent major versions or extensions. One team's upgrade or extension install becomes every team's risk.
• A compliance boundary. Auditors may require separate infrastructure, keys and access paths, and a schema boundary may not satisfy that.
• Dominant write volume. One service then sets replication lag and failover time for everyone.

The shared cluster cuts patching and monitoring work, but it adds coordination work. Every major upgrade, extension install or parameter change needs sign-off from all teams, and that coupling grows with each service you add. Extraction is expensive if boundaries have eroded. If schemas and roles have stayed strict, moving a service out is the replication-and-cutover runbook described above, not an application rewrite.

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 database per service postgresql, 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 shared variable is one edit and one review instead of editing every service's .env by hand.

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.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• 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.