SQL commands

CREATE DISTRIBUTION

This command is used to create a new distribution with the specified name. Optionally, you can specify the column types for the distribution using the COLUMN TYPES clause. Optionally, you can specify a default shard for distribution using the “DEFAULT SHARD” clause. For composite sharding keys (multiple columns), specify multiple types separated by commas. You can append hash after a column type to apply hash-based distribution to that column.

CREATE DISTRIBUTION <distributionID> COLUMN TYPES <types> DEFAULT SHARD <shardId>

where types is a comma-separated list of column types (e.g., "integer, varchar" for composite keys)

where type is one of: VARCHAR, INTEGER, INT, UUID, optionally followed by "hash" keyword

where <shardId> is a default shard identifier. The <shardId> must exist.

Examples:

-- Single column distribution
CREATE DISTRIBUTION ds1 COLUMN TYPES integer;

-- Single column with hash
CREATE DISTRIBUTION ds2 COLUMN TYPES varchar hash;

-- Composite key distribution with two columns
CREATE DISTRIBUTION ds3 COLUMN TYPES integer, integer;

-- Composite key with mixed types
CREATE DISTRIBUTION ds4 COLUMN TYPES integer, varchar;

-- Composite key with hash on second column
CREATE DISTRIBUTION ds5 COLUMN TYPES integer, varchar hash;

DROP DISTRIBUTION

This command is used to drop an existing distribution with the specified name. The CASCADE keyword is optional and indicates that any dependent objects should also be dropped.

DROP DISTRIBUTION <distribution_name> [CASCADE]

ALTER DISTRIBUTION ATTACH RELATION

This command is used to alter an existing distribution. You can attach one or more relations to the distribution using the ATTACH RELATION clause. For composite sharding keys, specify multiple column names separated by commas in the DISTRIBUTION KEY clause.

ALTER DISTRIBUTION <distribution_name> ATTACH RELATION <relation_name> [, <relation_name> ...] 
DISTRIBUTION KEY <keys> [HASH FUNCTION <hash_function_name>]

where keys is a comma-separated list of column names (e.g., "col1, col2" for composite keys)

where hash_function_name is one of: IDENTITY, MURMUR, CITY

Examples:

-- Single column sharding key
ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY order_id;

-- Composite sharding key with two columns
ALTER DISTRIBUTION ds2 ATTACH RELATION users DISTRIBUTION KEY tenant_id, user_id;

-- Composite key with hash function
ALTER DISTRIBUTION ds3 ATTACH RELATION events DISTRIBUTION KEY region_id, event_id HASH FUNCTION MURMUR;

ALTER DISTRIBUTION DETACH RELATION

This command is used to alter an existing distribution. You can detach a relation from the distribution using the DETACH RELATION clause.

ALTER DISTRIBUTION <distribution_name> DETACH RELATION <relation_name>

ALTER DISTRIBUTION DROP DEFAULT SHARD

This command is used to add default shard to an existing distribution.

ALTER DISTRIBUTION <distributionID> DROP DEFAULT SHARD

ALTER DISTRIBUTION DROP DEFAULT SHARD

This command is used to add default shard to an existing distribution.

ALTER DISTRIBUTION <distributionID> DEFAULT SHARD <shardId>
where <shardId> is a default shard identifier. <shardId> must be exist 

CREATE REFERENCE TABLE

This command is used to create a reference table. The AUTO INCREMENT argument creates a sequence.

You may specify initial sequence value with START keyword.

CREATE REFERENCE TABLE <table_name> [AUTO INCREMENT <column_name> [, <column_name> ...] >]
CREATE REFERENCE TABLE <table_name> [AUTO INCREMENT <column_name START start_value> [, <column_name START start_value> ...] >]

DROP SEQUENCE

This command is used to drop a sequence. The CASCADE keyword is optional and indicates that any dependent objects should also be dropped.

DROP SEQUENCE <sequence_name> [CASCADE]

SYNC REFERENCE TABLE

This command is not available for installations without SPQR coordinator.

This command is used to synchronize a reference table to a target shard. It copies all data from the reference table on existing shards to the specified destination shard. This is useful when:

Adding a new shard to the cluster and needing to populate it with reference table data
Exceptional cases (indicating data corruption):
- A shard is out of sync and needs to be updated with the latest reference table data
- Recovering reference table data on a shard after a failure

SYNC REFERENCE TABLE <table_name> ON <shard_id>

Parameters:

<table_name>: The name of the reference table to synchronize
<shard_id>: The identifier of the destination shard where data will be copied

Example:

-- First create a reference table on some shards
CREATE REFERENCE TABLE tax_rates ON sh1, sh2;

-- Later, sync it to another shard (e.g., after adding sh3 to the cluster)
SYNC REFERENCE TABLE tax_rates ON sh3;

Note:

The reference table must already be created in SPQR metadata and the table structure must exist on the destination shard before running this command.
SYNC REFERENCE TABLES (plural) to sync all reference tables at once is currently not supported.

CREATE KEY RANGE

This command is used to create a new key range. Since the key space is an ordered set, it is enough to specify only one end of the range. For composite sharding keys, specify multiple values separated by commas that correspond to each column in the distribution.

CREATE KEY RANGE [ <keyRangeID> ]
  FROM <lowerBound>
  ROUTE TO <shardID> 
  FOR DISTRIBUTION <distributionID>

where lowerBound is a single value or comma-separated values for composite keys 
(e.g., "100, 'value'" for a two-column key)

Examples:

-- Single column key range
CREATE KEY RANGE krid1 FROM 1000 ROUTE TO shard1 FOR DISTRIBUTION ds1;
-- Composite key range with two integers
CREATE KEY RANGE krid2 FROM 0, 100 ROUTE TO shard2 FOR DISTRIBUTION ds2;
-- Composite key range with integer and varchar
CREATE KEY RANGE krid3 FROM 100, 'aaaaa' ROUTE TO shard3 FOR DISTRIBUTION ds3;

DROP KEY RANGE

Removes a key range from the cluster. This deletes the key range metadata but does not affect the actual data stored on the shard. Use DROP KEY RANGE ALL to remove all key ranges at once.

DROP KEY RANGE <keyRangeID>
DROP KEY RANGE ALL

Examples:

-- Drop a specific key range
DROP KEY RANGE krid1;
-- Drop all key ranges
DROP KEY RANGE ALL;

LOCK KEY RANGE

Locks a key range to prevent concurrent modifications. This is typically used before performing operations like SPLIT or UNITE that require exclusive access to the key range. While a key range is locked, queries routed to it will wait until the lock is released.

LOCK KEY RANGE <keyRangeID>

MOVE KEY RANGE

Updates the routing metadata to assign a key range to a different shard. This only changes where new queries for this key range are routed — it does NOT migrate the actual data between shards.

MOVE KEY RANGE only updates metadata. To move both metadata and data, use REDISTRIBUTE KEY RANGE instead.

MOVE KEY RANGE <keyRangeID> TO <shardID>

Examples:

-- Move a key range to a different shard
MOVE KEY RANGE krid1 TO sh2;

REDISTRIBUTE KEY RANGE

Moves a key range to a different shard, including the actual data migration. The operation can be run in check-only mode to validate before applying, and supports batch processing to control the migration pace.

This command is not available for installations without SPQR coordinator.

REDISTRIBUTE KEY RANGE <keyRangeID> TO <shardID>
  [BATCH SIZE <batchSize>]
  [TASK GROUP <taskGroupID>]
  [CHECK | APPLY | NOWAIT]

Examples:

-- Redistribute with default settings
REDISTRIBUTE KEY RANGE krid1 TO sh2;
-- Redistribute with custom batch size
REDISTRIBUTE KEY RANGE krid1 TO sh2 BATCH SIZE 100;
-- Check redistribution without applying
REDISTRIBUTE KEY RANGE krid1 TO sh2 CHECK;
-- Non-blocking redistribution
REDISTRIBUTE KEY RANGE krid1 TO sh2 BATCH SIZE 500 NOWAIT;
-- Redistribute with task group for tracking
REDISTRIBUTE KEY RANGE krid1 TO sh2 TASK GROUP taskgroup1;

SPLIT KEY RANGE

Splits an existing key range into two key ranges at the specified boundary value. The new key range takes the upper portion of the original range (from the split point to the original upper bound), while the source range keeps the lower portion. Both resulting key ranges remain on the same shard. Use REDISTRIBUTE KEY RANGE afterwards to relocate one of them if needed.

SPLIT KEY RANGE <newKeyRangeID> FROM <sourceKeyRangeID> BY <splitBound>

where splitBound is a single value or comma-separated values for composite keys

Examples:

-- Split a key range at integer boundary
SPLIT KEY RANGE krid2 FROM krid1 BY 100;
-- Split a key range at UUID boundary
SPLIT KEY RANGE krid4 FROM krid3 BY 'FF000000-0000-0000-0000-000000000000';

UNITE KEY RANGE

Merges two adjacent key ranges into a single key range. The left key range absorbs the right key range, extending its upper bound to cover both ranges. The two key ranges must be adjacent (share a boundary) and must route to the same shard. After the operation, the right key range is removed.

UNITE KEY RANGE <leftKeyRangeID> WITH <rightKeyRangeID>

Examples:

-- Unite two adjacent key ranges
UNITE KEY RANGE krid1 WITH krid2;

UNLOCK KEY RANGE

Releases the lock on a key range that was previously locked with LOCK KEY RANGE. After unlocking, queries routed to this key range will resume normal processing.

UNLOCK KEY RANGE <keyRangeID>

Examples:

-- Unlock a key range after a split operation
UNLOCK KEY RANGE krid1;

SHOW

This command is used to retrieve information about a specific entity of SPQR cluster and simple filtering.

SHOW entity [WHERE where_clause]

where entity is one of backend_connections, clients, coordinator_address, databases, distributions, hosts, instance, is_read_only, key_ranges, key_ranges_extended, move_stats, move_tasks, pools, prepared_statements, redistribute_tasks, reference_relations, relations, routers, sequences, sharding_rules, shards, status, task_groups, time_quantiles, tsa_cache, users, version:

Thewhere_clause is used to filter the results based on specific conditions. It allows you to specify criteria for selecting the information to be shown.

spqr-console=> SHOW backend_connections WHERE hostname='hostname:6432'

 backend_connection_id | router  | shard_key_name |   hostname    |     user     | dbname | sync | tx_served | tx_status
-----------------------+---------+----------------+---------------+--------------+--------+------+-----------+-----------
 824694216064          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 10102     | IDLE
 824646300800          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 7508      | IDLE
 824682931072          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 16623     | IDLE
 824688144384          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 10112     | IDLE
 824686833408          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 21080     | IDLE
 824686834560          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 21069     | IDLE
 824653676928          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 34227     | IDLE
 824686833664          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 21060     | IDLE
 824646300928          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 8003      | IDLE
 824682937984          | no data | app-prod-spqr1 | hostname:6432 | test_app_app | testdb | 0    | 7622      | IDLE

Exists feature - order by col asc/desc for clients.

SHOW clients ORDER BY <column> (ASC/DESC)

The ORDER BY column feature works with clients, not works with other entities.

KILL CLIENT

This command is used to terminate a specific client connection in a SPQR Router.

KILL CLIENT <client_id>

INVALIDATE CACHE

This command is used to invalidate the cache for a specific schema or the entire cache in the SPQR Router.

INVALIDATE CACHE

REGISTER ROUTER

This command is not available for installations without SPQR coordinator.

This command is used to register a router with the SPQR Coordinator. When a router is registered, the coordinator becomes aware of it and can manage it as part of the cluster.

REGISTER ROUTER <router_id> ADDRESS <address>

Parameters:

<router_id>: A unique identifier for the router. Can be a simple name or a quoted string (e.g., r1 or 'r-1').
<address>: The network address of the router in the format host:port (e.g., '[localhost]:6432' or '[router-host]:7432').

Example:

REGISTER ROUTER r1 ADDRESS '[localhost]:6432'

UNREGISTER ROUTER

This command is not available for installations without SPQR coordinator.

This command is used to unregister a router from the SPQR Coordinator. This removes the router from the coordinator’s management.

UNREGISTER ROUTER <router_id>
UNREGISTER ROUTER ALL

Parameters:

<router_id>: The unique identifier of the router to unregister.
ALL: Special keyword to unregister all routers at once.

Examples:

-- Unregister a specific router
UNREGISTER ROUTER r1

-- Unregister all routers
UNREGISTER ROUTER ALL

Documentation

​CREATE DISTRIBUTION

​DROP DISTRIBUTION

​ALTER DISTRIBUTION ATTACH RELATION

​ALTER DISTRIBUTION DETACH RELATION

​ALTER DISTRIBUTION DROP DEFAULT SHARD

​ALTER DISTRIBUTION DROP DEFAULT SHARD

​CREATE REFERENCE TABLE

​DROP SEQUENCE

​SYNC REFERENCE TABLE

​CREATE KEY RANGE

​DROP KEY RANGE

​LOCK KEY RANGE

​MOVE KEY RANGE

​REDISTRIBUTE KEY RANGE

​SPLIT KEY RANGE

​UNITE KEY RANGE

​UNLOCK KEY RANGE

​SHOW

​KILL CLIENT

​INVALIDATE CACHE

​REGISTER ROUTER

​UNREGISTER ROUTER

CREATE DISTRIBUTION

DROP DISTRIBUTION

ALTER DISTRIBUTION ATTACH RELATION

ALTER DISTRIBUTION DETACH RELATION

ALTER DISTRIBUTION DROP DEFAULT SHARD

ALTER DISTRIBUTION DROP DEFAULT SHARD

CREATE REFERENCE TABLE

DROP SEQUENCE

SYNC REFERENCE TABLE

CREATE KEY RANGE

DROP KEY RANGE

LOCK KEY RANGE

MOVE KEY RANGE

REDISTRIBUTE KEY RANGE

SPLIT KEY RANGE

UNITE KEY RANGE

UNLOCK KEY RANGE

SHOW

KILL CLIENT

INVALIDATE CACHE

REGISTER ROUTER

UNREGISTER ROUTER