SQL commands

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 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;
-- 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 RELATION

Attaches one or more relations to an existing distribution using the ATTACH RELATION clause. For composite sharding keys, specify multiple column names separated by commas in the DISTRIBUTION KEY clause.

ALTER DISTRIBUTION <distributionID> ATTACH RELATION <relationName> [, <relationName> ...]
  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 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;

CREATE RELATION

This command is a shorthand for ALTER DISTRIBUTION ATTACH RELATION. It creates a relation 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] RELATION [schema.]<relation_name> DISTRIBUTION KEY <keys> [IN <distribution_id> | FOR DISTRIBUTION <distribution_id>]

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

Examples:

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

ALTER DISTRIBUTION DETACH RELATION

Detaches a relation from the distribution using the DETACH RELATION clause.

ALTER DISTRIBUTION <distributionID> DETACH RELATION <relationName>

ALTER DISTRIBUTION ALTER RELATION DISTRIBUTION KEY

Replaces the entire distribution key of an already attached relation. 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 RELATION <relationName>
  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 RELATION orders DISTRIBUTION KEY customer_id;
-- Replace with a composite key
ALTER DISTRIBUTION ds1 ALTER RELATION events DISTRIBUTION KEY region_id, event_id;
-- Fix an expression-routing entry (RENAME DISTRIBUTION COLUMN cannot do this)
ALTER DISTRIBUTION ds1 ALTER RELATION tr DISTRIBUTION KEY MURMUR [correct_id INT HASH, id2 VARCHAR HASH];

ALTER DISTRIBUTION ALTER RELATION SCHEMA

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

ALTER DISTRIBUTION <distributionID> ALTER RELATION <relationName>
  SCHEMA <schemaName>

Examples:

-- Change relation schema
ALTER DISTRIBUTION ds1 ALTER RELATION orders SCHEMA my_schema;

ALTER DISTRIBUTION RENAME DISTRIBUTION COLUMN

A metadata-only fix that renames a column in the distribution key of an attached relation. 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 relation 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 RELATION <relationName>
  RENAME DISTRIBUTION COLUMN <oldColumnName> TO <newColumnName>

Examples:

-- Fix column name casing in metadata
ALTER DISTRIBUTION ds1 ALTER RELATION 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;

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>

SHOW

Displays cluster metadata, topology, configuration, and runtime status. Valid targets: databases, routers, shards, 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;

Documentation

Documentation Index

​CREATE DISTRIBUTION

​DROP DISTRIBUTION

​ALTER DISTRIBUTION ATTACH RELATION

​CREATE RELATION

​ALTER DISTRIBUTION DETACH RELATION

​ALTER DISTRIBUTION ALTER RELATION DISTRIBUTION KEY

​ALTER DISTRIBUTION ALTER RELATION SCHEMA

​ALTER DISTRIBUTION RENAME DISTRIBUTION COLUMN

​ALTER DISTRIBUTION ADD DEFAULT SHARD

​ALTER DISTRIBUTION DROP DEFAULT SHARD

​CREATE REFERENCE TABLE

​DROP REFERENCE TABLE

​ALTER REFERENCE TABLE STORAGE

​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

​KILL BACKEND

​INVALIDATE CACHE

​INVALIDATE BACKENDS

​INVALIDATE STALE CLIENTS

​REGISTER ROUTER

​UNREGISTER ROUTER

CREATE DISTRIBUTION

DROP DISTRIBUTION

ALTER DISTRIBUTION ATTACH RELATION

CREATE RELATION

ALTER DISTRIBUTION DETACH RELATION

ALTER DISTRIBUTION ALTER RELATION DISTRIBUTION KEY

ALTER DISTRIBUTION ALTER RELATION SCHEMA

ALTER DISTRIBUTION RENAME DISTRIBUTION COLUMN

ALTER DISTRIBUTION ADD DEFAULT SHARD

ALTER DISTRIBUTION DROP DEFAULT SHARD

CREATE REFERENCE TABLE

DROP REFERENCE TABLE

ALTER REFERENCE TABLE STORAGE

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

KILL BACKEND

INVALIDATE CACHE

INVALIDATE BACKENDS

INVALIDATE STALE CLIENTS

REGISTER ROUTER

UNREGISTER ROUTER