One Database, Many Schemas: Avoid Cross-Database 2PC in YugabyteDB

Jim Knicely

A common question from teams moving to YugabyteDB is:

  • “Can we run a two-phase commit transaction across two databases in the same YugabyteDB universe?”

The better question is usually:

  • “Why are we splitting this data into two databases if the application needs atomic transactions across both?”

In YugabyteDB, the recommended pattern is simple:

  • If two sets of data need to participate in the same transaction, keep them in the same database and organize them with schemas.

YugabyteDB supports distributed ACID transactions across multiple rows, tables, and shards. A schema is a logical container inside a database that can hold tables, views, functions, indexes, and other objects. That makes schemas a good fit for organizing application domains without splitting the transactional boundary.

Quick takeaway: Do not split data into separate databases just because you are worried the database will get too big. In YugabyteDB, scale comes from distributed storage, sharding, tablets, and adding nodes… not from creating more databases.

The Tempting Design

Imagine an application with two logical domains:

  • ● Customer data
  • ● Billing data

A team may be tempted to create two separate databases:

				
					CREATE DATABASE customer_db;
CREATE DATABASE billing_db;

That can look clean at first.

But then the application needs to do something like this:

  • 1. Insert a customer record.
  • 2. Insert a billing record.
  • 3. Commit both changes together.
  • 4. Roll back both changes if either side fails.

At that point, the application is asking for one atomic transaction across two isolated databases.

That is where the design gets messy.

Why Cross-Database Transactions Are the Wrong Fit

A YugabyteDB universe can contain multiple databases, but a database is an isolation boundary. That isolation is useful when you want separate applications, environments, tenants, or administrative boundaries.

But it is not the right boundary for data that must commit together.

In PostgreSQL-style systems, cross-database atomicity usually leads people toward two-phase commit, prepared transactions, external transaction coordinators, or custom compensation logic.

That is usually not what you want for a high-throughput OLTP application.

In YugabyteDB YSQL, PostgreSQL-style prepared transaction commands such as PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED are not currently supported. YugabyteDB’s own migration documentation shows ERROR: PREPARE TRANSACTION not supported yet as an unsupported PostgreSQL behavior.

				
					BEGIN;

-- Do some work here...

PREPARE TRANSACTION 'demo_split_tx';

Expected result:

				
					ERROR:  PREPARE TRANSACTION not supported yet

That matters because PREPARE TRANSACTION is the phase-one mechanism commonly associated with application-controlled two-phase commit.

Important: YugabyteDB supports distributed ACID transactions, but that is not the same thing as exposing PostgreSQL prepared transactions for application-managed two-phase commit across separate databases.

The Better Design: One Database, Multiple Schemas

Instead of this:

				
					CREATE DATABASE customer_db;
CREATE DATABASE billing_db;

Use this:

				
					CREATE DATABASE appdb;

Then organize the logical domains with schemas:

				
					\c appdb
CREATE SCHEMA customer;
CREATE SCHEMA billing;

Schemas give you logical separation inside the same database. YugabyteDB documents schemas as logical containers that help organize objects into groups and avoid name conflicts.

Example:

				
					CREATE TABLE customer.account (
    account_id   BIGINT PRIMARY KEY,
    account_name TEXT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE billing.invoice (
    invoice_id   BIGINT PRIMARY KEY,
    account_id   BIGINT NOT NULL,
    amount       NUMERIC(12,2) NOT NULL,
    status       TEXT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Now the application can modify both logical areas in one normal transaction:

				
					BEGIN;

INSERT INTO customer.account (
    account_id,
    account_name
)
VALUES (
    1001,
    'Acme Example'
);

INSERT INTO billing.invoice (
    invoice_id,
    account_id,
    amount,
    status
)
VALUES (
    5001,
    1001,
    199.99,
    'OPEN'
);

COMMIT;

That is much simpler than trying to coordinate two separate databases.

But Won’t One Database Get Too Big?

This is where it helps to shift from a single-node database mindset to a distributed database mindset.

In YugabyteDB, tables and indexes are split into tablets and distributed across the nodes in the universe. As data grows, the cluster can spread that data across more nodes and storage. Scale comes from the physical distribution of data, not from creating more database names.

A large database is not automatically a problem. A poorly chosen primary key, missing indexes, hot spots, or too many tiny objects can be problems. But keeping related tables in one database is not, by itself, the issue.

If those tables need to participate in the same transaction, keeping them together in one database and organizing them with schemas is usually the cleaner and safer design.

Design reminder: A database is a logical container. Tables and indexes are physically split into tablets and distributed across the cluster. If related data needs to commit together, keep it in the same database and scale the universe as the data grows.

What About FDWs?

Foreign Data Wrappers can be useful when one database needs to read data from another database, but they are not a clean solution for cross-database atomicity.

FDWs help with data access. They do not replace a well-defined transaction boundary.

If the application needs one atomic unit of work, the better design is usually to keep the related data in one database and organize it with schemas.

Design Comparison

Requirement Avoid Prefer
Atomic changes across related data Separate databases with attempted 2PC One database with multiple schemas
Logical organization Database-per-module when modules transact together Schema-per-module or schema-per-domain
Scale Splitting databases to make each one feel smaller Scale the universe, shard correctly, and design primary keys/indexes well
Cross-domain queries FDW as a substitute for transactional design Normal joins inside one database when the data belongs together

Rule of Thumb

Use separate databases when you want strong isolation between applications, tenants, environments, or administrative boundaries.

Use schemas when you want logical organization inside the same application boundary.

If two pieces of data must commit together, they probably belong in the same database.

Final Takeaway

Two-phase commit across separate databases is usually a sign that the data model is fighting the application.

In YugabyteDB, you do not need to split data into separate databases just because the data is large. Keep transactional data together in one database, organize it with schemas, and let YugabyteDB distribute the physical data across the cluster.

The database can be big.

The transaction boundary should be clean.

Have Fun!

While cleaning out basement boxes for our move to Dallas, I found this old cartoon drawing from my first job at the University of Pittsburgh.

I honestly don’t remember why I drew it, but when I left to become an Oracle DBA at a large financial institution in downtown Pittsburgh, my coworkers got hold of it and turned it into a goodbye card.

Funny that I kept it all these years.

It’s a little beat up now, but it’s a pretty cool time capsule from the early days of my career. Glad I found it. 😊