Automating Global Data Residency in CockroachDB via Policy-Driven Sharding

Introduction

As digital services expand globally, the complexity of managing data across international borders has transitioned from a logistical challenge to a rigorous legal requirement. Frameworks such as GDPR (EU), LGPD (Brazil), and CCPA (California) mandate that personal data belonging to citizens must reside within specific geographic boundaries. Traditionally, engineers solved this using manual application-level sharding—splitting a single logical database into multiple physical instances, each managed independently. However, manual sharding introduces immense operational overhead, breaks cross-shard consistency, and complicates global reporting.

The technical challenge is to maintain a single logical database—allowing for global ACID transactions and a unified schema—while programmatically enforcing physical data placement based on row-level attributes. CockroachDB addresses this via a multi-region architecture and policy-driven sharding, specifically using the REGIONAL BY ROW locality setting. This post explores the technical implementation of automating these data residency policies, moving from manual infrastructure management to an Infrastructure-as-Code (IaC) and policy-driven paradigm.

---

Technical Overview: The Architecture of Locality

CockroachDB’s ability to automate data residency relies on its distributed consensus mechanism (Raft) and its awareness of the physical topology.

1. The Multi-Region Abstraction

Unlike traditional RDBMS where data placement is opaque to the SQL layer, CockroachDB treats “Region” as a first-class citizen. When a cluster is deployed, each node is started with a --locality flag (e.g., region=us-east-1,az=ad-1). This metadata allows the database to map the logical keyspace to physical hardware.

2. Policy-Driven Sharding vs. Manual Sharding

• Manual Sharding: The application logic determines which database instance (e.g., db-eu-01 or db-us-01) to query. Cross-region joins are impossible without custom middleware.
• Policy-Driven (Regional by Row): The developer defines a column (e.g., country_code) as the partition key. CockroachDB automatically moves the Raft Leaseholder (the replica that handles reads and writes) and its associated replicas to the nodes matching that region.

3. Survival Goals and Consensus

CockroachDB utilizes “Survival Goals” to define the failure domain.
* Zone Survival: Ensures the database stays available if a single data center fails within a region.
* Region Survival: Distributes replicas across three different regions, ensuring availability even if an entire cloud region goes offline.

For data residency, we typically focus on Data Locality, ensuring the “primary” copy of the data never leaves its designated jurisdiction, while still participating in a global consensus for cluster-wide consistency.

---

Implementation Details

The following implementation guide walks through automating a global deployment with data residency requirements using Terraform and SQL locality policies.

Step 1: Infrastructure Provisioning (Terraform)

Automating the cluster deployment requires tagging nodes with the correct regional metadata. Using the CockroachDB Cloud Terraform provider (or self-managed nodes on K8s), we define the topology.

resource "cockroach_cluster" "global_cluster" {
  name           = "global-data-store"
  cloud_provider = "AWS"
  regions {
    name = "us-east-1"
    node_count = 3
  }
  regions {
    name = "eu-central-1"
    node_count = 3
  }
  regions {
    name = "ap-southeast-1"
    node_count = 3
  }
}

Step 2: Defining Database-Level Locality

Once the nodes are active, we must inform the SQL engine which regions are available and which is the “Primary” region (used for global tables).

-- Connect to the cluster and set the regions
ALTER DATABASE global_app ADD REGION "us-east-1";
ALTER DATABASE global_app ADD REGION "eu-central-1";
ALTER DATABASE global_app ADD REGION "ap-southeast-1";

-- Set the primary region for global metadata
ALTER DATABASE global_app SET PRIMARY REGION "us-east-1";

Step 3: Implementing REGIONAL BY ROW

To automate residency at the row level, we use a hidden or explicit column to track the region. Using a computed column based on a country_code is a common pattern for automation.

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING UNIQUE,
    country_code STRING NOT NULL,
    region crdb_internal_region AS (
        CASE
            WHEN country_code IN ('DE', 'FR', 'IT') THEN 'eu-central-1'
            WHEN country_code IN ('US', 'CA') THEN 'us-east-1'
            WHEN country_code IN ('SG', 'JP') THEN 'ap-southeast-1'
            ELSE 'us-east-1'
        END
    ) STORED,
    data JSONB
) LOCALITY REGIONAL BY ROW AS region;

Note: The crdb_internal_region type is an ENUM generated by CockroachDB when regions are added to the database.

Step 4: Automating Schema Migrations in CI/CD

In a production pipeline, residency policies should be versioned. Using a tool like Liquibase or Flyway, you can automate the addition of new regions.

# Example migration snippet
databaseChangeLog:
  - changeSet:
      id: add-brazil-region
      author: platform-team
      changes:
        - sql:
            sql: "ALTER DATABASE global_app ADD REGION 'sa-east-1';"
        - sql:
            sql: "ALTER TABLE users VALIDATE CONSTRAINT check_region;"

---

Best Practices and Considerations

1. Indexing for Locality

When using REGIONAL BY ROW, CockroachDB automatically prefixes unique indexes with the region column. This is critical because it ensures that unique checks (like checking if an email already exists) can be performed locally within a region without a global “round-trip” latency penalty.

2. Improving Read Latency with “Follower Reads”

For data that is read globally but doesn’t need to be strictly up-to-the-millisecond (like product catalogs), use AS OF SYSTEM TIME.

SELECT * FROM products AS OF SYSTEM TIME '-10s' WHERE id = '...';

This allows the query to be served by the nearest replica (Follower) rather than the Leaseholder, significantly reducing latency for global applications.

3. Security and Compliance

• Encryption in Transit: Ensure all inter-node communication is encrypted using TLS certificates. CockroachDB’s nodelocal or cert-manager on Kubernetes can automate this.
• Audit Logging: Enable SQL audit logs specifically for the tables governed by residency policies to prove compliance during regulatory audits.
• IAM Mapping: Use RBAC to ensure that only specific service accounts in the EU region can access the EU partition of the data.

---

Real-World Use Cases and Performance Metrics

Use Case: AI/ML Feature Stores

In modern AI architectures, low-latency access to feature vectors is essential for real-time inference.
* Problem: An inference engine in Frankfurt needs features for an EU user, but the data is stored in a US-centralized database, causing 150ms+ latency.
* Solution: By sharding the feature store REGIONAL BY ROW, the feature data is moved to the Frankfurt data center.
* Impact: Latency drops to <5ms, enabling real-time model scoring while adhering to EU data sovereignty.

Performance Metrics: The “Global Round Trip”

In a standard non-sharded global cluster, a write from London to a leader in New York typically incurs a ~70-90ms RTT (Round Trip Time).
* Without Locality: 3-node Raft consensus across regions = ~150ms-200ms write latency.
* With REGIONAL BY ROW: Raft consensus happens within the local region (3 nodes in EU) = ~2ms-5ms write latency.

---

Conclusion

Automating global data residency is no longer just a database configuration—it is a core architectural requirement for global enterprises. By leveraging CockroachDB’s policy-driven sharding, engineers can move away from the “brittle” nature of manual sharding and embrace a unified global database that is both compliant and performant.

Key Takeaways:

• Abstraction: Use REGIONAL BY ROW to decouple logical data from physical location.
• Automation: Integrate region management into your IaC (Terraform) and CI/CD (Liquibase) pipelines.
• Optimization: Localize Leaseholders to minimize Raft consensus latency and use Follower Reads for global read scaling.
• Compliance: Always back policy-driven sharding with robust encryption and audit logging to satisfy regulatory requirements.

By treating data residency as a policy rather than a manual partitioning task, organizations can scale globally with the confidence that their data remains exactly where it belongs.