Sharding

The router knows that some tables have been split into shards. If possible, the router tries to determine on the first transaction statement to which shard this transaction should be sent.

SPQR supports both single-column and composite (multi-column) sharding keys.

Query Routing

When a query arrives, the router extracts the sharding key from the query and determines which key range (and thus which shard) should handle it.

-- This query works with properly configured sharding rules
-- The router extracts id=10 and routes to the appropriate shard
INSERT INTO test(id, age) VALUES (10, 16);

Explicit Sharding Key

You can explicitly specify a sharding key in a SQL comment when the router cannot determine it automatically:

-- Override automatic routing by specifying the sharding key
INSERT INTO test(id, age) VALUES (10, 16) /*__spqr__sharding_key: 30*/;

For composite sharding keys, specify all key values separated by commas:

-- Explicit composite sharding key
INSERT INTO users(tenant_id, user_id, name) VALUES (1, 100, 'Alice') /*__spqr__sharding_key: 1, 100*/;

Explicit Shard Selection

You can also force a query to execute on a specific shard:

-- Execute on a specific shard regardless of key
SELECT * FROM test /*__spqr__execute_on: sh1*/;