CockroachDB - PostgreSQL That Scales Horizontally

Look, CockroachDB is basically PostgreSQL that got stretched across multiple machines and regions. As of September 2025, the latest available version is v25.3. But here's what nobody mentions upfront: they killed the open source version on November 18, 2024. Now it's all proprietary with a "free tier" that disappears when your company hits $10M revenue and requires sending them telemetry about everything you're doing. So factor that vendor lock-in into your decisions.

How the Distributed Thing Actually Works

CockroachDB splits into five layers that handle different parts of the database:

SQL Layer: Takes your PostgreSQL queries and breaks them down into smaller pieces that can be distributed. About 80% of your existing app will probably work without changes, but expect to hit some edge cases with the PostgreSQL features they don't support yet.

Transaction Layer: Uses timestamps and distributed consensus to make sure your transactions work across multiple machines in different regions. Slower than single-node PostgreSQL (2-10ms writes vs sub-millisecond), but it actually works reliably across continents.

Distribution Layer: Automatically splits your data into ranges and tries to keep related data together. Sometimes it gets this wrong and you'll spend a weekend figuring out why your queries are slow because related data ended up on different continents.

Replication Layer: Uses Raft consensus to keep 3+ copies of everything. When nodes die (and they will), the remaining nodes vote on who's in charge. Pretty solid, though you'll get paged when nodes go down.

Storage Layer: Pebble storage engine underneath handles the actual disk I/O. It's optimized for writes, which is good because distributed systems do a lot of writing to maintain consistency.

What Actually Makes This Worth the Complexity

Strong Consistency

CockroachDB gives you serializable isolation - your transactions either happen or they don't, no weird edge cases where data appears and disappears. The ACID guarantees documentation explains how this works across regions. This is why you'd consider this over just running PostgreSQL read replicas.

Horizontal Scaling That Actually Works

Adding nodes to the cluster actually makes things faster and more reliable. Production clusters can scale to hundreds of nodes across continents. The auto-rebalancing system redistributes data when you add nodes, though "automatically" still means you'll spend time tuning and monitoring. Netflix's case study shows how they scaled to massive workloads.

Multi-Region Without the Headache

The multi-region features are where CockroachDB beats rolling your own solution:

• Regional Tables: Pin tables to specific regions for compliance or latency
• Global Tables: Put read-heavy reference data everywhere for fast local reads
• Regional by Row: Automatically place rows based on content (like user location)

This saves you from building your own sharding logic and dealing with cross-region consistency problems. The multi-region deployment patterns guide shows configurations that work in production.

Self-Healing (Mostly)

When things break, CockroachDB usually fixes itself:

• Node failures: Other nodes take over automatically
• Load rebalancing: Moves data around to optimize performance
• Maintenance tasks: Handles compaction and cleanup without downtime
• Rolling upgrades: Can upgrade without taking the cluster down

That said, "self-healing" doesn't mean zero-ops. You'll still get alerts when nodes die and need to understand what's happening.

PostgreSQL Compatibility (Pretty Good, Not Perfect)

CockroachDB wire protocol compatibility means your existing PostgreSQL tools and drivers work. Most common features are supported:

• Standard SQL (the important stuff from ANSI SQL 2016)
• Common data types (JSON, arrays, UUIDs)
• Indexes (B-tree, partial, expression-based)
• Basic stored procedures and functions
• Foreign keys and constraints
• Views and materialized views

The compatibility is good enough that you can often point your app at CockroachDB and it'll work. But you'll find PostgreSQL features that aren't supported, especially the more exotic ones.

Performance Reality Check

CockroachDB is built for OLTP workloads, not analytics. Here's what you actually get:

• Local reads: Sub-millisecond if data is nearby
• Cross-region transactions: 50-100ms if you're lucky, can be higher
• Write latency: 2-10ms because of consensus overhead
• Throughput: Scales linearly as you add nodes (if you design your schema right)

It works best with normalized schemas where related data stays together. If you need heavy analytics, just use something else or stream the data out to a data warehouse.

Licensing and Pricing (The Expensive Part)

Here's the licensing situation as of November 2024:

CockroachDB Enterprise Free: "Free" for companies under $10M revenue, but they get telemetry on everything you do and you need annual renewal. They'll literally know more about your database usage than you do.

CockroachDB Enterprise: The paid version for actual companies. Pricing is "contact sales" which translates to "we'll charge what we think you can afford" based on how desperate you look during negotiations.

CockroachDB Cloud: Fully managed on AWS, Google Cloud, and Azure. Convenient but prepare for sticker shock - you're paying for their ops team plus markup on cloud resources.

The free tier disappears the moment you hit $10M revenue, so plan your business growth carefully. Once you're locked into their ecosystem, they control the pricing and there's no open source escape hatch anymore.

So you've decided to take the plunge into distributed SQL hell. Here's what actually happens when you try to deploy CockroachDB in the real world, not the marketing demos.

Development vs. Production Reality

Local Development (Actually Works)

Development setup is surprisingly smooth, unlike most distributed systems. The Docker approach works and you can have a three-node cluster running locally:

## This actually works and is useful for testing
docker run -d --name=roach1 --hostname=roach1 --net=roachnet -p 26257:26257 \
  cockroachdb/cockroach:v25.2.0 start --insecure --join=roach1,roach2,roach3

The CockroachDB Cloud free tier is decent for development - 10 GiB storage and 250M compute units monthly. Good for testing whether your app will implode when you move to distributed.

Pro tip: Use --logtostderr=WARNING or you'll drown in logs. Found this out after spending 2 hours wondering why my disk was full.

Production Deployment (Where Dreams Go to Die)

Production is where you learn that distributed systems are hard:

Node Placement: You need at least 3 nodes across different failure domains. Cross-region setups need careful latency planning - 100ms+ between regions will hurt performance. Don't put nodes too far apart unless you enjoy 30-second transaction timeouts. The cluster topology guide explains placement strategies.

Hardware Sizing: Don't cheap out. Recommended specs are 16+ GB RAM and SSD storage. CockroachDB needs more resources than PostgreSQL due to consensus overhead. Expect 2-3x the hardware costs, learned this when our "adequate" cluster died under production load. Check the capacity planning guide and performance benchmarks for realistic sizing.

Network: Network partitions will test your system design. Cross-region deployments need dedicated connections if you want consistent performance. We tried saving money with regular internet connections and spent 3 months debugging split-brain scenarios and connection refused errors. The network requirements documentation covers bandwidth and latency requirements.

Common Error You'll See: restart transaction: TransactionRetryWithProtoRefreshError happens more often than you'd expect. Your app needs retry logic or users will see random failures. This isn't PostgreSQL where transactions rarely conflict.

Schema Design (Get This Wrong and Suffer)

Don't Design Like PostgreSQL

CockroachDB punishes bad schema design more than single-node databases:

Primary Keys: Auto-incrementing IDs create hotspots where all writes hit the same node. Use compound keys with a distributed first column (user_id, tenant_id). This can take months to learn through experience. Hash-sharded indexes help but check your version - some earlier versions had issues with them. The primary key design guide covers anti-patterns to avoid.

Table Locality: Use regional tables for region-specific data and global tables for reference data. Get this wrong and your cross-region queries will be painfully slow. The multi-region table patterns documentation shows common configurations.

Indexes: Covering indexes are crucial to avoid extra lookups across nodes. More important than in PostgreSQL because network calls are expensive. Read about index best practices before going to production.

Multi-Tenancy (One Thing CockroachDB Does Well)

Multi-tenancy is where CockroachDB shines compared to PostgreSQL:

Row-level Tenancy: Put tenant_id in your primary key and use row-level security. Works better than trying to shard PostgreSQL manually. Just don't forget the tenant_id in your queries or you'll scan the entire cluster.

Regional by Row: REGIONAL BY ROW automatically puts tenant data in their home region. Brilliant for global SaaS apps, though setting it up correctly takes some trial and error.

Performance Optimization (Required Reading)

Query Optimization (Or How to Not Hate Your Database)

Avoid Cross-Region Joins: These will destroy your performance. Denormalize data if needed to keep related stuff together. Foreign keys across regions are performance killers.

Batch Operations: Single-row operations are slow. Batch your inserts/updates or watch your app crawl. CockroachDB handles large batches well, unlike some distributed databases.

Isolation Levels: Serializable isolation is the default and slower. Use read committed if you don't need the strongest guarantees. Your app will be faster and you'll sleep better.

Monitoring (Set This Up First)

The built-in web UI is actually good and the Prometheus integration works:

Key Metrics: Watch SQL query latency, replica lag, and resource utilization. More metrics than you'll ever need, but focus on the ones that indicate your app is dying.

Query Performance: EXPLAIN ANALYZE works differently than PostgreSQL but shows you where distributed queries are spending time. Essential for debugging slow queries.

Migration Reality Check

From PostgreSQL (Your Most Likely Path)

Compatibility: About 80% of PostgreSQL works. Check the compatibility matrix for the features you actually use. Budget time for rewrites.

Schema Changes: You'll need to redesign your primary keys and think about data locality. This isn't optional if you want good performance.

App Changes: Add retry logic for transaction conflicts. Distributed systems have more contention than single-node databases. Your app will need to handle this gracefully or users will complain.

Migration gotcha: pg_dump output won't work directly. CockroachDB's IMPORT statement is picky about CSV formats and will fail silently on edge cases. I've seen imports missing rows because of embedded newlines in text fields.

From NoSQL (Only If You're Desperate)

Data Modeling: You'll need to transform your documents into normalized tables. CockroachDB has JSON support but you lose the flexibility that made you choose NoSQL originally.

Query Translation: NoSQL query patterns don't translate well to SQL. Plan for significant rewrites if you used complex aggregation pipelines or document-specific features.

Consistency: The one good reason to migrate - you get real ACID transactions instead of eventual consistency disasters. But it's a major project, not a weekend migration.

When You Actually Need This Thing

The Reality Check

Backups: Cross-region backups actually work well, which is more than you can say for most distributed databases.

Security: Encryption, auth, and audit logging don't suck. They actually spent time getting this right instead of shipping it broken.

Operations: The self-healing stuff works most of the time. When it doesn't, you'll need someone who understands distributed systems to figure out what's broken.

Look, CockroachDB makes sense if you actually need global distribution with strong consistency. Most apps don't - PostgreSQL with read replicas is simpler and cheaper. But if you're building a global financial platform or multi-region gaming backend where eventually consistent data would fuck up your business, CockroachDB beats building your own distributed system from scratch. Just know what you're signing up for in terms of complexity and costs.