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.

DROP SEQUENCE t1_id;
   drop sequence   
-------------------
 sequence -> t1_id
(1 row)

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

This command is used to drop an existing key range.

DROP KEY RANGE <keyRangeID>

MOVE KEY RANGE

If you don’t fully understand how SPQR works, you should not use this command in production. Use REDISTRIBUTE instead.

This command is used to move an existing key range to a different shard.

MOVE KEY RANGE <keyRangeID> TO <shardID>

REDISTRIBUTE KEY RANGE

This command is not available for installations without SPQR coordinator.

REDISTRIBUTE KEY RANGE command is similar to MOVE KEY RANGE, but with one key difference: REDISTRIBUTE moves the key range in smaller chunks to minimize downtime for write operations. BATCH SIZE is an integer value that controls the number of keys moved in each batch during redistribution. Default batch size is 500. You can also use optional keywords CHECK and APPLY. By default, command will check for the ability to move keys, then perform the actual movement. With CHECK, you can perform a dry-run. With APPLY, you can skip checks.

REDISTRIBUTE KEY RANGE <keyRangeID> TO <shardID> [ BATCH SIZE <batchSize> ] [ CHECK | APPLY ];

SPLIT KEY RANGE

This command is used to split an existing key range into two separate key ranges.

SPLIT KEY RANGE <keyRangeID> FROM <keyRangeFromID> BY <border>

UNITE KEY RANGE

This command is used to unite two adjacent key ranges into a single key range.

UNITE KEY RANGE <keyRangeIDLeft> WITH <keyRangeIDRight>

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, move_stats, move_task, pools, prepared_statements, reference_relations, relations, routers, sequences, sharding_rules, shards, status, task_group, 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

​CREATE KEY RANGE

​DROP KEY RANGE

​MOVE KEY RANGE

​REDISTRIBUTE KEY RANGE

​SPLIT KEY RANGE

​UNITE 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

CREATE KEY RANGE

DROP KEY RANGE

MOVE KEY RANGE

REDISTRIBUTE KEY RANGE

SPLIT KEY RANGE

UNITE KEY RANGE

SHOW

KILL CLIENT

INVALIDATE CACHE

REGISTER ROUTER

UNREGISTER ROUTER