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.
CREATE DISTRIBUTION <distributionID> COLUMN TYPES <types>
where types is a comma-separated list of column types
where type is one of: VARCHAR, INTEGER, INT, UUID
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.
ALTER DISTRIBUTION <distribution_name> ATTACH RELATION <relation_name> [, <relation_name> ...]
DISTRIBUTION KEY <keys> [HASH FUNCTION <hash_function_name>]
where hash_function_name is one of: IDENTITY, MURMUR, CITY
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>
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
CREATE KEY RANGE [ <keyRangeID> ]
FROM <lowerBound>
ROUTE TO <shardID>
FOR DISTRIBUTION <distributionID>
DROP KEY RANGE
This command is used to drop an existing key range.
DROP KEY RANGE <keyRangeID>
MOVE KEY RANGE
This command is not available for installations without SPQR coordinator.
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 databases, routers, pools, shards, backend_connections, key_ranges, clients, status, distributions, version, relations, sequences, task_groups, prepared_statements:
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.
INVALIDATE CACHE
This command is used to invalidate the cache for a specific schema or the entire cache in the SPQR Router.