SmartClient: Hash-Based Partitioning Meets Locality-Aware Routing

Disclaimer: while this text was prepared with AI assistance, the content has been prepared and approved by the author.

Overview

SmartClient is an application-side routing strategy for geo-distributed databases.

It is particularly useful for workloads that would benefit from geo-partitioning and locality, but do not naturally contain a partitioning attribute such as region, country, tenant, customer location, or any other business key suitable for data placement.

Instead of relying on an existing partitioning attribute, SmartClient creates a synthetic partitioning key by applying a deterministic hash function to a stable identifier. The resulting partition is used both to determine where data is stored and where requests should be routed.

This approach provides the benefits of geo-partitioning even for workloads that would otherwise appear unpartitionable.

Additionally, SmartClient can be used to align workload distribution with available infrastructure capacity. In deployments where regions, zones, data centers, or clusters have different resource allocations, partition ownership can be assigned proportionally to capacity, ensuring balanced utilization across the system.

The approach consists of three steps:

1. Partition the dataset using a deterministic hash function.
2. Assign partitions to locations according to placement requirements and capacity goals.
3. Route client requests to the location responsible for the partition associated with the target data.

The result is a system that combines deterministic routing, locality-aware data placement, and predictable load distribution.

---

When to Use SmartClient

SmartClient is most applicable when:

• The workload benefits from geo-partitioning.
• The application lacks a natural partitioning attribute.
• Reads and writes are typically performed using a stable business identifier.
• Low-latency local operations are preferred.
• Cross-region traffic should be minimized.
• Infrastructure capacity differs across deployment locations.
• The application can participate in routing decisions.

Example workloads include:

• Order processing systems
• Financial transaction systems
• Inventory management systems
• Event processing platforms
• IoT workloads
• Multi-region operational databases
• Legacy applications whose data model lacks geographic attributes

---

Architecture

Capacity-Aware Partition Assignment

When infrastructure capacity differs across locations, distributing requests equally across locations can create an imbalance. For example, consider a 15-node cluster spanning three regions: Site A and Site B each have six nodes, while Site C has three. Assuming comparable capacity per node and similar request costs, an equal split of requests would give Site C twice the workload per node of Site A or Site B.

The objective is therefore to distribute workload in proportion to available capacity: approximately 40% to Site A, 40% to Site B, and 20% to Site C.

SmartClient implements this by dividing the keyspace into a fixed number of partitions, assigning those partitions to locations according to capacity and placement requirements, and routing requests to the corresponding location. With 30 partitions, the assignment could be:

Location	Node Counts	Relative Capacity	Assigned Partitions
Site A	6	40%	0-11
Site B	6	40%	12-23
Site C	3	20%	24-29

This assignment targets the desired workload proportions when partitions carry approximately equal workloads. If some partitions receive more traffic or require more work than others, their assignment should also account for measured demand.

The assignment can be adjusted to match any deployment topology and capacity model.

---

Data Partitioning

Each record is assigned to a partition using a deterministic hash of a stable business identifier.

Examples include:

• Order ID
• Customer ID
• Account ID
• Transaction ID
• Device ID
• Tenant ID

A common algorithm is:

partition = hash(record_id) % N

Where:

• record_id is the application's primary business identifier.
• hash() is a fast deterministic hashing function such as CRC32.
• N is the total number of partitions.

Example SQL:

SELECT mod(crc32ieee('example-record-id'), 30) AS partition_id;

Result:

partition_id
------------
15

The same algorithm can be implemented in virtually any programming language, allowing clients to determine the partition without querying the database.

Here an example in Python and Java

>>> import zlib
>>> zlib.crc32("example-record-id".encode()) % 30
15

jshell> import java.util.zip.CRC32;

jshell> CRC32 crc = new CRC32();
crc ==> java.util.zip.CRC32@1bce4f0a

jshell> crc.update("example-record-id".getBytes());

jshell> crc.getValue() % 30
$4 ==> 15

---

Data Placement

The partition identifier becomes part of the primary key and locality strategy.

Example primary key:

(partition_id, record_id)

Tables and indexes can then be partitioned by partition_id, allowing all data associated with a partition to be colocated within a specific location.

Example:

Location	Partitions
Site A	0-11
Site B	12-23
Site C	24-29

This ensures that all records belonging to a partition are stored and served from the same location.

Below is a sample table and related zone configurations demonstrating this implementation.

CREATE TABLE orders (
    partition_id INT2 NOT NULL 
        AS (mod(crc32ieee(order_id), 30)) STORED
        CHECK (
            partition_id IN (
                 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
                10,11,12,13,14,15,16,17,18,19,
                20,21,22,23,24,25,26,27,28,29
            )
        ),
    order_id STRING NOT NULL,
    col1 BOOL NULL,
    col2 DECIMAL NULL,
    col3 STRING NULL,
    CONSTRAINT pk_orders PRIMARY KEY (partition_id, order_id)
);

-- partitioning
ALTER INDEX orders@pk_orders PARTITION BY LIST (partition_id) (
    PARTITION site_a VALUES IN ( 0,  1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11),
    PARTITION site_b VALUES IN (12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23),
    PARTITION site_c VALUES IN (24, 25, 26, 27, 28, 29)
);

-- zone config
ALTER PARTITION site_a OF INDEX orders@pk_orders CONFIGURE ZONE USING 
    num_replicas = 5, 
    constraints = '{+region=A: 1}', 
    lease_preferences = '[ [+region=A] ]';

ALTER PARTITION site_b OF INDEX orders@pk_orders CONFIGURE ZONE USING 
    num_replicas = 5, 
    constraints = '{+region=B: 1}', 
    lease_preferences = '[ [+region=B] ]';

ALTER PARTITION site_c OF INDEX orders@pk_orders CONFIGURE ZONE USING 
    num_replicas = 5, 
    constraints = '{+region=C: 1}', 
    lease_preferences = '[ [+region=C] ]';

---

Request Routing

When a client needs to read or modify a record:

1. The client computes the partition identifier using the record's key.
2. The client consults a partition-to-location mapping.
3. The client selects a connection pool associated with the responsible location.
4. The request is sent directly to that location.
5. The database processes the request locally because the relevant data resides there.

Example Flow

1. A client receives a request for record 240717C6P0LS-483553.

2. The client computes:
   

   partition = crc32(record_id) % 30

1. The resulting partition is 17.

2. The client looks up partition 17 in its routing map.

3. Partition 17 belongs to Site B.

4. The client obtains a connection from the Site B connection pool.

5. The request is sent directly to Site B.

6. Reads and writes execute locally because the data for partition 17 is colocated there.

---

Secondary Indexes

Partitioning Secondary Indexes

To preserve locality for writes and point lookups, secondary indexes should be partitioned using the same partition_id strategy as the primary table.

For example, if a table uses:

PRIMARY KEY (partition_id, order_id)

then secondary indexes should similarly include partition_id as the leading column and be partitioned using the same locality rules.

This ensures that:

• Primary rows and secondary index entries remain colocated.
• Inserts and updates remain local to the owning partition.
• Secondary index maintenance does not generate cross-region traffic.

Real-Time Secondary Index Queries

A common concern is how queries behave when filtering on a secondary index column but not on partition_id.

For example:

SELECT *
FROM orders
WHERE customer_id = '12345';

The application may not know which partition contains the matching rows because the query predicate does not include the partition key.

In this case, CockroachDB must search all partitions of the secondary index.

At first glance this appears similar to a distributed index scan. However, because the partition space is finite and explicitly defined, the optimizer can generate a significantly more efficient plan.

Informing the Optimizer

The partition key should be defined as a stored computed column together with a bounded CHECK constraint.

Example:

CREATE TABLE orders (
    partition_id INT2 NOT NULL
        AS (mod(crc32ieee(order_id), 30:::INT8)) STORED
        CHECK (
            partition_id IN (
                0,1,2,3,4,5,6,7,8,9,
                10,11,12,13,14,15,16,17,18,19,
                20,21,22,23,24,25,26,27,28,29
            )
        ),
    order_id STRING NOT NULL,
    ...
);

The CHECK constraint explicitly informs the optimizer that partition_id can only take one of a finite set of values.

Because the optimizer knows the complete domain of possible partition values, it can transform a query that lacks a partition predicate into a series of targeted lookups.

Lookup-Based Execution

Rather than performing a full secondary index scan, the optimizer can generate a plan equivalent to:

Lookup partition 0
Lookup partition 1
Lookup partition 2
...
Lookup partition 29

The database then:

1. Executes a targeted lookup against each partition.
2. Collects matching results from all locations.
3. Merges and aggregates the results.
4. Returns a complete result set to the client.

Conceptually, this is similar to executing multiple point or range lookups rather than performing a full scan of the entire index.

Benefits include:

• Significantly less data read.
• Better use of index structures.
• Reduced storage layer work.
• More predictable query performance.

Trade-Offs

SmartClient intentionally optimizes for operational workloads whose access patterns are driven by a business identifier.

As a result:

Operation Type	Behavior
Insert	Local
Update	Local
Delete	Local
Point lookup by business identifier	Local
Secondary index maintenance	Local
Query by non-partitioned attribute	Distributed lookup across partitions
Global aggregation	Distributed

This trade-off is typically favorable because operational systems are usually dominated by writes and point lookups rather than broad analytical queries.

In other words:

• Writes remain local.
• Index maintenance remains local.
• Transaction processing remains local.
• Certain real-time reporting queries may require distributed reads.

The cost of those distributed reads is mitigated by the optimizer's ability to perform targeted partition lookups instead of full index scans.

---

Benefits

1. Enables Geo-Partitioning Without a Natural Partition Key

Many applications lack a business attribute that can be used for geographic partitioning.

SmartClient creates a synthetic partition key that enables deterministic data placement and routing while preserving an even distribution of data.

This allows organizations to leverage geo-partitioning even when the underlying data model was not originally designed for it.

2. Capacity-Aligned Load Distribution

SmartClient can assign partition ownership according to available resources, allowing workload distribution to reflect infrastructure capacity rather than client origin.

Benefits include:

• Better resource utilization
• Reduced hotspots
• Predictable scaling behavior
• Support for asymmetric deployments

3. Localized Transactions

Because all data belonging to a partition is colocated:

• Reads remain local.
• Writes remain local.
• Index maintenance remains local.
• Cross-region coordination is minimized.

The latency benefit is greater when a transaction contains multiple statements that access data in the same partition. SmartClient routes the client directly to a gateway in the region hosting the relevant leaseholders from the start, avoiding repeated cross-region round trips between a remote gateway node and the leaseholder nodes as the transaction executes.

The same benefit can apply to a statement using common table expressions (CTEs) when its execution requires multiple dependent data accesses. A CTE is part of a single SQL statement, but those accesses can still incur repeated gateway-to-leaseholder round trips if the gateway is in another region. Routing to the leaseholders' region keeps those interactions local when the accessed data is colocated there.

This typically improves:

• Latency
• Throughput
• Transaction efficiency

4. Deterministic Routing

No centralized routing service is required.

Any client can independently determine:

• The partition for a record.
• The responsible location.
• The correct connection pool.

using only the record identifier and a shared routing configuration.

5. Portable Design

The SmartClient pattern is independent of:

• Database vendor
• Cloud provider
• Geographic topology
• Hashing algorithm

It can be applied to any distributed system that supports data partitioning and locality-aware placement.

---

Design Considerations

Choosing the Number of Partitions

Using more partitions provides:

• Greater flexibility in balancing capacity.
• Easier future reallocation.
• Finer-grained scaling.
• Reduced impact when rebalancing ownership.

A common approach is to create significantly more partitions than deployment locations.

Handling Growth

When infrastructure changes:

• New locations can be assigned partitions.
• Existing partitions can be redistributed.
• Capacity ratios can be adjusted without changing application behavior.
• Data placement policies can evolve independently from application logic.

Routing Configuration

Clients maintain a simple mapping between partitions and locations.

Example:

{
  "0-11": "site-a",
  "12-23": "site-b",
  "24-29": "site-c"
}

This mapping can be distributed through:

• Configuration files
• Service discovery systems
• Centralized configuration services
• Application configuration management platforms

---

Summary

SmartClient is a deterministic, application-side routing strategy that enables geo-partitioning for workloads that may not have a natural partitioning key.

By combining:

• Hash-based data partitioning
• Locality-aware data placement
• Capacity-aware partition ownership
• Deterministic client-side routing

SmartClient enables systems to:

• Benefit from geo-partitioning without changing the data model.
• Keep transactional reads and writes local.
• Minimize cross-region traffic for operational workloads.
• Distribute workload according to available resources.
• Scale predictably as infrastructure evolves.

For workloads running on CockroachDB, secondary indexes can be partitioned using the same strategy, preserving local write behavior while still allowing global queries through optimizer-driven partition lookups. This provides a practical balance between locality, performance, and query flexibility in geo-distributed deployments.