CockroachDB Is Not a Drop-In Replacement for PostgreSQL. But It's Not Trying to Be.

CockroachDB Is Not a Drop-In Replacement for PostgreSQL. But It's Not Trying to Be.

I'm a fan of CockroachDB. I want to say that up front.

I spent time working with it, learning its architecture, and helping customers run it in production. It is a genuinely impressive piece of engineering — a distributed SQL database that speaks the PostgreSQL wire protocol, survives node failures without data loss, and scales horizontally in ways that traditional relational databases simply cannot.

But I've also watched teams get caught badly by a single assumption:

"It's PostgreSQL compatible, so it works like PostgreSQL."

It doesn't. And that gap between wire compatibility and drop-in replacement is where real production pain lives.

This post is about that gap — what it means, where it bites, and how to close it before it becomes an incident.

Wire Compatible Is Not the Same as Drop-In Replacement

CockroachDB speaks the PostgreSQL wire protocol. That means your PostgreSQL drivers work. Your connection strings work. Your ORM can connect to it. Tools that speak PostgreSQL can talk to CockroachDB.

That's genuinely valuable. It lowers the barrier to adoption significantly.

But wire compatibility is about the connection layer. It says nothing about behavioral compatibility, performance characteristics, or architectural trade-offs. Those are completely different questions — and they matter far more once you're running real workloads.

Wire compatible means your driver can connect. It does not mean your application will behave the same, perform the same, or scale the same. Those are architecture questions, not protocol questions.

The 1.5 TB Table — Where the Assumption Broke

We had a customer running a very large workload. Their primary table was 1.5 terabytes of data. They had evaluated CockroachDB, connected successfully, run some initial queries, and decided to migrate.

Their reasoning was straightforward: it speaks PostgreSQL, our drivers work, the queries run. Let's move.

What they didn't account for was how CockroachDB's distributed architecture changes the rules for large tables.

The first issue was performance. Queries that ran predictably on their existing PostgreSQL instance were inconsistent on CockroachDB. Some fast. Some painfully slow. No obvious pattern at first.

The second issue was their table structure. They had built their primary keys the way most DBAs are taught — auto-incrementing integers. Sequential. Clean. Exactly what you'd do on SQL Server or PostgreSQL.

On CockroachDB, that choice was causing hotspots.

The Hotspot Problem — and Why Primary Keys Work Differently

In a distributed database like CockroachDB, data is split into ranges and distributed across nodes. The goal is to spread both storage and query load evenly across the cluster.

Sequential primary keys — auto-incrementing integers — work against that goal. Because new rows always have the highest key value, they always land on the same range, on the same node. Every insert hits the same place. Instead of distributing write load across the cluster, you funnel it all into one spot.

That's a hotspot. And on a 1.5 TB table with significant write volume, it was the primary driver of their performance problems.

Everything we teach DBAs about primary keys — use auto-incrementing integers, keep them sequential, keep them small — is optimized for single-node databases. CockroachDB's distributed architecture inverts that guidance.

New tables: use UUID from the start

For new tables, the fix is straightforward:

CREATE TABLE orders (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID        NOT NULL,
  status      STRING      NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  total       DECIMAL     NOT NULL
);

UUIDs are random by nature. New rows land on different ranges, on different nodes. Write load distributes across the cluster the way a distributed database is designed to work.

Existing tables: converting from integer to UUID

For tables already running with an integer primary key, the migration takes a few steps. CockroachDB supports online schema changes, so this can be done without taking the table offline.

-- Step 1: Add a UUID column with a default
ALTER TABLE orders ADD COLUMN new_id UUID NOT NULL DEFAULT gen_random_uuid();

-- Step 2: Verify the column is populated
SELECT id, new_id FROM orders LIMIT 10;

-- Step 3: Drop the old primary key constraint
ALTER TABLE orders DROP CONSTRAINT orders_pkey;

-- Step 4: Set the new UUID column as primary key
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY (new_id);

-- Step 5: Rename for clarity (optional but recommended)
ALTER TABLE orders RENAME COLUMN id TO legacy_id;
ALTER TABLE orders RENAME COLUMN new_id TO id;

-- Step 6: Once application is updated and legacy_id no longer needed
ALTER TABLE orders DROP COLUMN legacy_id;

A few important notes on this migration:

• Keep the legacy integer column until your application code and any foreign key references are updated
• Update foreign keys in child tables to reference the new UUID column before dropping the old integer primary key
• CockroachDB's online schema changes mean reads and writes continue during the migration — but test in staging first, especially on large tables
• If your application generates IDs externally, update that logic to generate UUIDs before the cutover

For DBAs coming from SQL Server or PostgreSQL, UUIDs as primary keys feels wrong — we've been told for years they fragment indexes and hurt performance. On a single-node database, that's largely true. On CockroachDB, UUIDs are the right answer. The architecture changes the trade-off completely.

The Durability Trade-Off — Performance Has a Cost

The second thing this customer hadn't fully accounted for was CockroachDB's durability model.

CockroachDB replicates every write synchronously across multiple nodes before acknowledging the transaction. By default, it uses a replication factor of three — meaning every write is confirmed on three nodes before your application sees a commit.

This is what makes CockroachDB survive node failures without data loss. It's also what makes it slower on writes than a single-node PostgreSQL instance running on the same hardware.

This isn't a flaw. It's the design. You are getting something real in return — a database that can lose a node and keep running, with no data loss and no manual failover. For the right workloads, that trade-off is worth it many times over.

But if you evaluate CockroachDB by comparing raw write throughput to a single-node PostgreSQL instance and declare it slower — you're comparing the wrong things. You're comparing a distributed, fault-tolerant database to a single point of failure.

CockroachDB's synchronous replication is the source of its durability guarantees. That same mechanism adds write latency compared to a single-node database. You are not paying a tax — you are buying something.

What We Did — and How It Performed

We didn't walk away from this engagement and tell the customer CockroachDB was the wrong choice. We fixed the configuration.

The work came down to a few core changes:

• Replaced auto-incrementing integer primary keys with UUIDs across their high-write tables
• Reviewed and restructured indexes to align with how CockroachDB distributes range scans
• Adjusted query patterns to avoid full-table scans on the distributed storage layer
• Tuned cluster topology to align node placement with read/write traffic patterns

The result: performance got close to what they needed. Not identical to their previous PostgreSQL setup — the durability trade-off is real and doesn't disappear — but well within their operational requirements.

The key insight was that basic DBA principles still apply. Understanding your data distribution, your access patterns, your index design — that knowledge is still the foundation. CockroachDB doesn't change what good database design looks like. It changes where you apply the principles and how the architecture responds to your choices.

The same instincts that make a great PostgreSQL DBA make a great CockroachDB DBA. The architecture just has you applying those principles in slightly different places.

What to Do Before You Commit to CockroachDB

• Audit your primary key strategy — sequential integers will cause hotspots on high-write tables. Plan for UUIDs from the start.
• Understand your write latency tolerance — synchronous multi-node replication is real. Benchmark under your actual workload, not synthetic tests.
• Review your schema for PostgreSQL-specific features — some stored procedures, index types, and extension dependencies may behave differently or be unavailable.
• Test your ORM behavior — wire compatibility means your ORM connects, not that every query it generates is optimized for distributed execution.
• Size for distribution, not single-node equivalence — CockroachDB's value compounds at scale. A small single-node comparison will always make it look worse than it is.

Final Thought

CockroachDB is a serious database built for a specific class of problems. When the fit is right — global distribution, high availability requirements, horizontal scale — it delivers in ways that PostgreSQL simply cannot.

But it is not PostgreSQL. It was never trying to be. Wire compatibility is a convenience feature, not an architectural promise.

Go in with clear eyes about the trade-offs. Rethink your primary key strategy. Understand what synchronous replication costs and what it buys. Apply your DBA instincts to the distributed architecture rather than assuming they transfer directly.

Do that, and CockroachDB can be exactly what it promises.

Skip that step, and a 1.5 TB table will teach you the lesson the hard way.

---

If your team is evaluating CockroachDB, planning a migration from PostgreSQL, or dealing with performance problems on a distributed SQL database — this is exactly the kind of work we do.

sixcolumnsolutions.com