Geo-Partitioning

The partition key is the critical column (e.g., user_country_code or tenant_id) that determines row placement. A well-chosen key creates deterministic locality—every row with country_code = 'DE' is physically stored in a German data center. Poor key selection causes hotspots where one region handles disproportionate load.

• Composite keys combine multiple columns for granular control
• Hash-based partitioning distributes rows uniformly when geographic affinity isn't required
• List partitioning maps explicit values (e.g., 'US', 'CA') to specific regions

Geo-partitioned databases enforce table-level domicile rules that prevent cross-region row migration. A LOCALITY clause binds a table to a specific region, while regional-by-row tables allow individual rows to declare their own locality.

• Regional tables replicate schema globally but store data in one region
• Global tables maintain a read-only copy in every region for low-latency reference data
• Row-level TTL automatically expires data after a jurisdiction-mandated retention period

To balance consistency with latency, geo-partitioned systems offer follower reads—querying a local replica that may lag behind the leader by milliseconds. This satisfies residency rules while avoiding expensive cross-region round trips.

• Exact staleness bounds guarantee reads are no more than 15 seconds behind
• Bounded staleness is acceptable for dashboards and reporting workloads
• Strong consistency reads route to the partition leader, potentially crossing regions

When a transaction spans multiple partitions in different regions, the database employs two-phase commit (2PC) or a consensus protocol like Raft to maintain atomicity. This introduces latency proportional to inter-region round-trip time.

• Co-located transactions (single region) execute with minimal overhead
• Distributed transactions require a quorum across participating regions
• Transaction contention increases exponentially with geographic distance

Secondary indexes must also respect geographic boundaries. A global index spans all partitions but violates strict residency; a local index is co-located with the partition's primary data, ensuring queries never leave the jurisdiction.

• Partitioned indexes shard the index by the same key as the table
• Covering indexes include all queried columns to avoid cross-region lookups
• Index-only scans satisfy queries entirely from the local index structure

Geo-partitioned backups must respect jurisdictional isolation. Each region's partition is backed up independently to a storage bucket within the same legal boundary. A restore operation reconstructs only the rows belonging to that region.

• Point-in-time recovery (PITR) is scoped to a single partition's timeline
• Cross-region backup replication is explicitly prohibited by residency policy
• Incremental backups capture only row-level changes since the last snapshot