SQL commands

Create distributions, key ranges and tables and see cluster info

CREATE DISTRIBUTION

Creates a sharding distribution with a distribution identifier and key column types.

A distribution defines the sharding key type layout used by attached relations and key ranges. Optionally, specify a default shard to route records that do not match any key range.

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> | (<types>)]
  [DEFAULT SHARD <shardID>]

where types is a comma-separated list of column types for the
distribution key, and each type is one of: INTEGER, INT,
VARCHAR, UUID (VARCHAR/INTEGER/UUID may be followed by HASH)

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

Examples:

-- Single integer key distribution
CREATE DISTRIBUTION ds1 COLUMN TYPES integer;
-- Single varchar key distribution with hash
CREATE DISTRIBUTION ds2 COLUMN TYPES varchar hash;
-- Single uuid key distribution with hash
CREATE DISTRIBUTION ds_uuid COLUMN TYPES uuid hash;
-- Composite key distribution with two columns
CREATE DISTRIBUTION ds3 COLUMN TYPES integer, integer;
-- Composite key with mixed types and hash on second column
CREATE DISTRIBUTION ds4 COLUMN TYPES integer, varchar hash;
-- Parenthesized type syntax with default shard
CREATE DISTRIBUTION ds5 (integer, varchar hash) DEFAULT SHARD sh1;

DROP DISTRIBUTION

Removes a distribution from cluster metadata.

Use CASCADE to remove dependent metadata objects (key ranges, attached relations) when required.

Dropping a distribution removes routing metadata and may impact attached relations.

DROP DISTRIBUTION <distributionID> [CASCADE]

Examples:

-- Drop one distribution
DROP DISTRIBUTION ds1;
-- Drop distribution with dependent metadata
DROP DISTRIBUTION ds1 CASCADE;

ALTER DISTRIBUTION ATTACH TABLE

Attaches one or more tables to an existing distribution using the ATTACH TABLE clause.

For composite sharding keys, specify multiple column names separated by commas in the DISTRIBUTION KEY clause.

ALTER DISTRIBUTION <distributionID> ATTACH TABLE <tableName> 
  [, <tableName> ...]
  DISTRIBUTION KEY <keys> [HASH FUNCTION <hashFunction>]

where keys is a comma-separated list of column names
(e.g., "col1, col2" for composite keys)
where hashFunction is one of: IDENTITY, MURMUR, CITY

Examples:

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

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

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

CREATE TABLE

This command is a shorthand for ALTER DISTRIBUTION ATTACH TABLE. It creates a table and attaches it to a distribution in a single statement. The DISTRIBUTED keyword is optional and has no effect on the behavior.

There are two syntax forms for specifying distribution keys:

Parenthesized syntax — column names and optional hash functions are listed inside parentheses:

DISTRIBUTION KEY syntax — uses explicit DISTRIBUTION KEY clause: CREATE [DISTRIBUTED] TABLE [schema.]<table_name> DISTRIBUTION KEY <keys> [IN <distribution_id> | FOR DISTRIBUTION <distribution_id>]

CREATE [DISTRIBUTED] TABLE [schema.]<table_name> (<keys>)
  [IN <distribution_id> | FOR DISTRIBUTION <distribution_id>]

Examples:

-- Simple column with no hash function (uses default distribution)
CREATE TABLE r (i);
-- Column with hash function
CREATE TABLE r (i HASH MURMUR);
-- Attach to a specific distribution
CREATE TABLE t (id) IN ds1;
-- Using FOR DISTRIBUTION syntax
CREATE DISTRIBUTED TABLE table1 (i HASH MURMUR) FOR DISTRIBUTION ds1;
-- Composite key with multiple columns
CREATE TABLE t3 (id HASH MURMUR, id2 HASH MURMUR) IN ds3;
-- Routing expression syntax for composite keys with typed columns
CREATE TABLE tr (MURMUR [id1 INT HASH, id2 VARCHAR HASH]);
-- Schema-qualified table name
CREATE DISTRIBUTED TABLE sh1.r (i) IN d;
-- DISTRIBUTION KEY syntax
CREATE DISTRIBUTED TABLE xx DISTRIBUTION KEY col1, col2
  HASH FUNCTION MURMUR IN ds1;

-- Composite key without hash using DISTRIBUTION KEY syntax
CREATE DISTRIBUTED TABLE copy_test (id1, id2) IN ds2;

ALTER DISTRIBUTION DETACH TABLE

Detaches a table from the distribution using the DETACH TABLE clause.

ALTER DISTRIBUTION <distributionID> DETACH TABLE <tableName>

ALTER DISTRIBUTION ALTER TABLE DISTRIBUTION KEY

Replaces the entire distribution key of an already attached table. This overwrites all column names and hash functions in one shot — it does not patch individual columns. Use this when you need to change the key structure entirely, or to fix expression-based routing entries (which RENAME DISTRIBUTION COLUMN does not support).

The new key must have the same number of entries as the distribution’s column types. Duplicate column names in the new key are rejected.

This command does not have well-defined behavior with concurrent read-write operations. Use it during setup or maintenance windows only.

ALTER DISTRIBUTION <distributionID> ALTER TABLE <tableName>
  DISTRIBUTION KEY <keys> [HASH FUNCTION <hashFunction>]

where keys is a comma-separated list of column names or expression entries
where hashFunction is one of: IDENTITY, MURMUR, CITY

Examples:

-- Replace the distribution key with a new column name
ALTER DISTRIBUTION ds1 ALTER TABLE orders 
DISTRIBUTION KEY customer_id;

-- Replace with a composite key
ALTER DISTRIBUTION ds1 ALTER TABLE events 
  DISTRIBUTION KEY region_id, event_id;

-- Fix an expression-routing entry (RENAME DISTRIBUTION COLUMN cannot do this)
ALTER DISTRIBUTION ds1 ALTER TABLE tr 
  DISTRIBUTION KEY MURMUR [correct_id INT HASH, id2 VARCHAR HASH];

ALTER DISTRIBUTION ALTER TABLE SCHEMA

Changes the schema associated with an attached table in the distribution metadata.

ALTER DISTRIBUTION <distributionID> ALTER TABLE <tableName>
  SCHEMA <schemaName>
  SCHEMA <schemaName>

Examples:

-- Change table schema
ALTER DISTRIBUTION ds1 ALTER TABLE orders SCHEMA my_schema;

ALTER DISTRIBUTION RENAME DISTRIBUTION COLUMN

A metadata-only fix that renames a column in the distribution key of an attached table. This does not execute ALTER TABLE ... RENAME COLUMN on PostgreSQL — it only updates the column name stored in SPQR’s distribution metadata.

The primary use case is fixing a case mismatch between the column name in SPQR metadata and the actual PostgreSQL column. Because PostgreSQL normalizes unquoted identifiers to lowercase, attaching a table with W_ID when the table column is w_id breaks routing: INSERTs fail and SELECTs scatter to all shards.

For composite (multi-column) distribution keys, call this command once per column that needs renaming.

This command does not have well-defined behavior with concurrent read-write operations. Use it during setup or maintenance windows only.

ALTER DISTRIBUTION <distributionID> ALTER TABLE <tableName>
  RENAME DISTRIBUTION COLUMN <oldColumnName> TO <newColumnName>

Examples:

-- Fix column name casing in metadata
ALTER DISTRIBUTION ds1 ALTER TABLE rc_test 
 RENAME DISTRIBUTION COLUMN W_ID TO w_id;

ALTER DISTRIBUTION ADD DEFAULT SHARD

Adds a default shard to an existing distribution. Records that do not match any key range will be routed to this shard.

ALTER DISTRIBUTION <distributionID> ADD DEFAULT SHARD <shardID>

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

ALTER DISTRIBUTION DROP DEFAULT SHARD

Removes the default shard from an existing distribution.

ALTER DISTRIBUTION <distributionID> DROP DEFAULT SHARD

CREATE REFERENCE TABLE

Creates a reference table — a table replicated across all or specific shards.

The optional AUTO INCREMENT clause creates a sequence for the specified columns. If no shards are specified with ON, the table is created on all shards.

You may specify initial sequence value with the START keyword.

CREATE REFERENCE TABLE <table_name>
    [AUTO INCREMENT <column> [START <value>], ...]
    [ON <shard_id>, ...]

Examples:

-- Create a reference table on all shards
CREATE REFERENCE TABLE tax_rates;
-- Create on specific shards
CREATE REFERENCE TABLE tax_rates ON sh1, sh2;
-- With auto-increment column
CREATE REFERENCE TABLE orders AUTO INCREMENT id;
-- With auto-increment starting at a specific value
CREATE REFERENCE TABLE orders AUTO INCREMENT id START 1000;

DROP REFERENCE TABLE

Removes a reference table from SPQR metadata. This does not drop the actual table from the shards.

DROP REFERENCE TABLE <table_name>

Examples:

-- Drop a reference table
DROP REFERENCE TABLE tax_rates;

ALTER REFERENCE TABLE STORAGE

Changes the set of shards where a reference table is stored.

ALTER REFERENCE TABLE <table_name> STORAGE TO (<shard_id>, ...)

Examples:

-- Change storage to three shards
ALTER REFERENCE TABLE tax_rates STORAGE TO (sh1, sh2, sh3);

DROP SEQUENCE

Drops a sequence used for auto-increment columns in reference tables. The optional CASCADE keyword drops dependent objects as well.

DROP SEQUENCE <sequence_name> [CASCADE]

Examples:

-- Drop a sequence
DROP SEQUENCE orders_id_seq;
-- Drop a sequence and its dependents
DROP SEQUENCE orders_id_seq CASCADE;

SYNC REFERENCE TABLE

Synchronizes a reference table to a target shard by copying data from an existing shard that already has the table.

This is useful when adding a new shard to the cluster that needs reference table data, or recovering reference table data on a shard after a failure.

This command is not available for installations without SPQR coordinator.

The table structure must already exist on the destination shard before running this command.

SYNC REFERENCE TABLE <table_name> ON <shard_id>

Examples:

-- Sync a reference table to a new shard
SYNC REFERENCE TABLE tax_rates ON sh3;

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;

CREATE KEY RANGES FOR DISTRIBUTION

Automatically creates a set of uniformly distributed key ranges for a distribution. Instead of defining each key range manually, this command inspects the distribution’s column type, splits the whole key space (or an explicitly supplied range) into equal parts, and creates one key range per target shard.

The key space is divided evenly across the selected shards, and each resulting key range is assigned to one shard in order. Generated key ranges are named <distributionID>-<index>, where index starts at 0.

By default the command uses the full value range of the distribution column type. Use the optional BETWEEN clause to bound the generated key ranges to a specific interval. When no shard selection is given, key ranges are created for all shards.

This command only supports distributions with a single, hashable (numeric) sharding column. Composite keys and non-hashable types such as varchar or uuid are not supported; use CREATE KEY RANGE to define those ranges manually.

CREATE KEY RANGES FOR DISTRIBUTION <distributionID>
  [ BETWEEN <lowerBound> AND <upperBound> ]
  [ USING ALL SHARDS | USING SHARDS <shardID> [, ...] ]

Examples:

-- Create key ranges for all shards (default)
CREATE KEY RANGES FOR DISTRIBUTION ds1;
-- Create key ranges explicitly for all shards
CREATE KEY RANGES FOR DISTRIBUTION ds1 USING ALL SHARDS;
-- Create key ranges for a subset of shards
CREATE KEY RANGES FOR DISTRIBUTION ds1 USING SHARDS sh1, sh2;
-- Split a bounded interval across all shards
CREATE KEY RANGES FOR DISTRIBUTION ds1 
BETWEEN 0 AND 1000 USING ALL SHARDS;

-- Split a bounded interval across selected shards
CREATE KEY RANGES FOR DISTRIBUTION ds1 
BETWEEN 0 AND 1000 USING SHARDS sh1, sh2;

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 receive a key range is locked error.

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>

SHOW

Displays cluster metadata, topology, configuration, and runtime status.

Valid targets: databases, routers, shards, shards_extended, distributions, key_ranges, key_ranges_extended, relations, reference_relations, pools, clients, backend_connections, hosts, version, status, instance, sequences, users, task_group, task_groups, task_group_ext, task_groups_ext, move_task, move_tasks, redistribute_tasks, prepared_statements, time_quantiles, unique_indexes, coordinator_address, is_read_only, move_stats, errors, startup_finished, two_phase_tx, dcs_storage, file_settings, tsa_cache.

SHOW <target> [(<columns>)]
  [WHERE <condition>]
  [GROUP BY <column>]
  [ORDER BY <column> [ASC|DESC]]

Examples:

-- Show all shards
SHOW shards;
-- Show key ranges with specific columns
SHOW key_ranges (key_range_id, shard_id);
-- Show backend connections filtered by hostname
SHOW backend_connections WHERE hostname = 'localhost';
-- Show clients ordered by user
SHOW clients ORDER BY user DESC;
-- Show backend connections grouped by shard
SHOW backend_connections GROUP BY shard_key_name;

KILL CLIENT

Terminates a specific client connection by its numeric ID.

Use SHOW CLIENTS to find client IDs.

KILL CLIENT <client_id>

Examples:

-- Kill a client connection
KILL CLIENT 42;

KILL BACKEND

Cancels a backend (shard) connection by its numeric ID.

Use SHOW BACKEND_CONNECTIONS to find backend IDs.

KILL BACKEND <backend_id>

Examples:

-- Cancel a backend connection
KILL BACKEND 17;

INVALIDATE CACHE

Invalidates the router’s schema cache, forcing it to be rebuilt on the next query.

INVALIDATE CACHE
INVALIDATE SCHEMA CACHE

Examples:

-- Invalidate the schema cache
INVALIDATE CACHE;

INVALIDATE BACKENDS

Marks all backend connections as stale, causing them to be re-established.

INVALIDATE BACKENDS

Examples:

-- Invalidate all backend connections
INVALIDATE BACKENDS;

INVALIDATE STALE CLIENTS

Identifies clients with dead TCP connections and signals them to close.

INVALIDATE STALE CLIENTS

Examples:

-- Clean up stale client connections
INVALIDATE STALE CLIENTS;

REGISTER ROUTER

Registers 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. After registration, the coordinator automatically synchronizes metadata (shards, key ranges, distributions) to the router.

This command is not available for installations without SPQR coordinator.

The router ID must be unique across all registered routers.

The address must be unique across all registered routers.

The router must be reachable at the specified address (a ping check is performed).

After registration, metadata is automatically synchronized to the router via gRPC.

REGISTER ROUTER <routerID> ADDRESS <address>

where <routerID> is a unique identifier for the router
(unquoted or quoted string)
where <address> is the network address in the format '[host]:port'

Examples:

-- Register a router on localhost
REGISTER ROUTER r1 ADDRESS '[localhost]:6432';
-- Register a router with a custom address
REGISTER ROUTER r2 ADDRESS '[router-host]:7432';

UNREGISTER ROUTER

Unregisters a router from the SPQR coordinator.

This removes the router from the coordinator’s management. Use ALL to unregister all routers at once.

This command is not available for installations without SPQR coordinator.

UNREGISTER ROUTER <routerID>
UNREGISTER ROUTER ALL

where <routerID> is the identifier of the router to unregister

Examples:

-- Unregister a specific router
UNREGISTER ROUTER r1;
-- Unregister all routers
UNREGISTER ROUTER ALL;