Sharing a Postgres Database Between a Monolith and New Services

TL;DR — Don’t share tables. Share a Postgres instance with separate schemas per service, locked-down roles per schema, and zero cross-schema foreign keys. Migrate to separate databases (or clusters) once the volume justifies it. Never let two services write the same row.

Microservices purity says: every service owns its database. Reality says: you have a 200 GB Postgres cluster, your DBA budget is one person, and standing up a separate cluster per service is operational suicide for a small team. The pragmatic middle ground is one shared Postgres instance, but with strict per-service boundaries enforced at the schema and role level.

This is the pattern we landed on for the monolith + Go services stack. It’s not the “right” answer in a textbook sense. It is, by some margin, the right answer for our team and budget. Here’s how it’s wired.

The shape: one cluster, many schemas

postgres-cluster
├─ database: app
│   ├─ schema: monolith     (owned by monolith_rw)
│   │   ├─ users
│   │   ├─ orders
│   │   ├─ products
│   │   └─ ...
│   ├─ schema: billing      (owned by billing_rw)
│   │   ├─ subscriptions
│   │   ├─ invoices
│   │   └─ payments
│   └─ schema: notifications (owned by notifications_rw)
│       ├─ message_log
│       └─ delivery_status

One Postgres cluster, one database, three schemas. Each schema is owned by a service-specific role. The monolith cannot SELECT from billing.subscriptions because the role it uses has no privileges there. Same for the other direction.

This is enforced by the database, not by convention. That distinction matters. “We agree not to query each other’s tables” is not a boundary; it’s a wish.

Setting up the roles

-- One role per service. Owns its schema.
CREATE ROLE monolith_rw       LOGIN PASSWORD '...';
CREATE ROLE billing_rw        LOGIN PASSWORD '...';
CREATE ROLE notifications_rw  LOGIN PASSWORD '...';

-- Each service owns its schema.
CREATE SCHEMA monolith      AUTHORIZATION monolith_rw;
CREATE SCHEMA billing       AUTHORIZATION billing_rw;
CREATE SCHEMA notifications AUTHORIZATION notifications_rw;

-- The monolith's connection string sets search_path so it doesn't have
-- to qualify every query.
ALTER ROLE monolith_rw      SET search_path = monolith, public;
ALTER ROLE billing_rw       SET search_path = billing, public;
ALTER ROLE notifications_rw SET search_path = notifications, public;

-- Lock down cross-schema access explicitly.
REVOKE ALL ON SCHEMA monolith      FROM billing_rw, notifications_rw;
REVOKE ALL ON SCHEMA billing       FROM monolith_rw, notifications_rw;
REVOKE ALL ON SCHEMA notifications FROM monolith_rw, billing_rw;

If the billing service ever needs to read user data, it has to ask the monolith via the monolith’s API. It cannot reach across into monolith.users. The DB rejects the query.

This sounds obvious. Three months in, you’ll find yourself extremely glad you set it up this way the first time someone slips a “just this once” cross-schema join into a PR.

Foreign keys do not cross schemas

Postgres lets you create a foreign key from billing.subscriptions.customer_id to monolith.users.id. It works. It will also ruin your day six months in.

Cross-schema (and especially cross-service) foreign keys create coupling that you cannot break without coordinating a database migration across teams. When the monolith team wants to soft-delete users, they discover the billing service’s FK won’t let them. When the billing team wants to delete a stale subscription, they’re blocked by a downstream FK from notifications. The DB starts dictating your release cadence.

Our rule: no foreign keys across schemas, period. A subscription record stores customer_id as a string (or UUID). The application layer is responsible for ensuring that customer exists when needed. The relationship is documented, but not enforced in the database.

It feels unsafe. It is, in fact, the only thing that makes the service boundary mean anything.

Connection management

Each service connects to the same Postgres cluster with its own role:

monolith:       postgres://monolith_rw:****@db:5432/app
billing:        postgres://billing_rw:****@db:5432/app
notifications:  postgres://notifications_rw:****@db:5432/app

A few operational notes:

• Use a connection pooler (PgBouncer or pgpool) in front of the cluster. Each service opens its own pool through it. With three services × 30 connections each, you’d otherwise hit Postgres’s max_connections by lunchtime.
• Set application_name=billing in each service’s connection string. Then pg_stat_activity tells you which service is running which query.
• Use separate read-only roles (billing_ro) for analytics queries that shouldn’t be able to write. Same schema-locked privileges.

When to migrate to separate databases

The shared-cluster model works for our scale (~100 GB total, ~5 services). At some point you’ll outgrow it. The signals:

• One service’s vacuum activity stalls another’s queries.
• A schema migration on one service locks tables that — through some long inheritance chain — affect another service’s performance.
• The cluster’s max_connections ceiling forces you to micro-tune pool sizes constantly.
• One service’s traffic profile (huge bursty writes, e.g.) makes the cluster unstable for others.

When you hit those, the migration is: spin up a new cluster, set up logical replication of the relevant schema to the new cluster, switch the service’s connection string, decommission the old schema. Painful but well-trodden.

The point of the schema-isolated approach is that this migration is possible without a code change. If you’d shared tables, you’d have to refactor both sides first.

Common Pitfalls

Letting two services own the same table. Even with the best intentions, two services writing the same row is a data-correctness nightmare. The subscriptions table belongs to billing. Full stop. The monolith reads subscription data via the billing API, not by SELECTing from the table.

Putting public schema in everyone’s search_path. The public schema is everyone’s by default. If you create tables in it, every service can access them. Either drop the public schema entirely (DROP SCHEMA public CASCADE;) or — at minimum — REVOKE CREATE ON SCHEMA public FROM PUBLIC; so nobody can accidentally create tables there.

Forgetting per-schema migrations. Each service should have its own migration tool config and its own migration history. Don’t let one service’s migrate up run another service’s migrations.

Using pg_dump of the whole database for backups. Fine for small clusters. Once you’re north of 100 GB, you want per-schema or even per-table backups so you can restore one service’s data without restoring everyone’s.

Sharing read replicas across services with the wrong roles. If your replica is read-only for everyone, that’s fine. If you let services use their normal write roles on the replica “for analytics”, you’ll eventually have a service trying to write to the replica and getting cryptic errors.

Trusting the application layer instead of the DB. “We promise not to query each other’s schemas” is not a boundary. Set the roles up. The first PR that violates the boundary should fail at the DB, not at code review.

Wrapping Up

One Postgres cluster with per-service schemas + per-service roles + no cross-schema foreign keys is the pragmatic middle ground between “shared everything” (a distributed monolith) and “one cluster per service” (operational masochism). It defers the per-service-database decision until the volume actually justifies it, while keeping the boundary sharp from day one. Next post: the retro on containerizing eleven services in one month, wrapping up January.