Exploring Citus 11.x Utility Functions for Distributed PostgreSQL

Table and Shard DDL

create_distributed_table

Defines a distributed table and, for hash‑distributed tables, creates the required shards. The function takes the table name, distribution column, and optional distribution method. It inserts metadata to mark the table as distributed. If no method is supplied, the default is hash . For hash‑distributed tables it creates worker shards based on the configured shard count and replication factor, and automatically distributes any existing rows to the workers.

truncate_local_data_after_distributing_table

Truncates all local rows on the coordinator after a table has been distributed. The operation cascades to tables that have foreign‑key references to the target table. Truncation of a referenced local table is prohibited to protect referential integrity, producing the error:

ERROR:  cannot truncate a table referenced in a foreign key constraint by a local table

Truncating the coordinator’s copy of a distributed table is safe because rows have already been copied to workers during distribution.

undistribute_table

Reverses create_distributed_table or create_reference_table. All data are moved back to a local table on the coordinator (assuming the coordinator can hold the data) and the shards are dropped. The function will not undistribute a table that has foreign‑key constraints or is referenced by foreign keys unless the cascade_via_foreign_keys argument is set to true. When the argument is omitted or false, offending foreign‑key constraints must be removed manually before undistributing.

alter_distributed_table

Changes a distributed table’s distribution column, shard count, or colocated property.

alter_table_set_access_method

Changes a table’s access method (e.g., heap or columnar).

remove_local_tables_from_metadata

Deletes local tables that are no longer needed from Citus metadata. Unnecessary metadata can persist when enable_local_reference_foreign_keys is disabled; manual cleanup may be required.

create_reference_table

Creates a small reference/dimension table. The function creates a single‑shard distributed table and replicates that shard to every worker node.

mark_tables_colocated

Places target tables into the same colocated group as a source distributed table. If the source is not already in a group, the function creates one and assigns both source and targets to it. This is an alternative to using the colocate_with argument of create_distributed_table.

update_distributed_table_colocation

Updates or breaks the colocated relationship of a distributed table without moving any data. Implicit colocation occurs when two tables share the same distribution column type and are expected to be joined; breaking colocation may be necessary when one table lacks a replica identifier.

create_distributed_function

Propagates a function from the coordinator to all workers and marks it for distributed execution. When the function is called on the coordinator, Citus selects workers based on the values of the function’s distribution parameters. The function runs on workers, increasing parallelism and reducing latency by executing close to the data. The search_path is not propagated, so the function body must use fully‑qualified object names. Notifications emitted by the function are not shown to the user.

alter_columnar_table_set

Modifies settings of a columnar table; calling it on a non‑columnar table raises an error. All parameters except the table name are optional. To view current options for all columnar tables: SELECT * FROM columnar.options; Default options for newly created columnar tables can be overridden via GUCs: columnar.compression, columnar.compression_level, columnar.stripe_row_count, and columnar.chunk_row_count.

create_time_partitions

Creates time‑based partitions covering a specified range using a given interval.

drop_old_time_partitions

Drops all partitions whose interval ends before a supplied timestamp. Older partitions can optionally be compressed by converting them to read‑only columnar storage with alter_old_partitions_set_access_method.

alter_old_partitions_set_access_method

Compresses old time partitions into read‑only columnar storage, a common pattern for time‑series workloads.

Metadata / Configuration Functions

citus_add_node

This function requires superuser privileges.

Registers a new node in the Citus metadata table pg_dist_node and copies reference tables to the new node.

When adding a node to a single‑node cluster, citus_set_coordinator_host must be run first.

citus_update_node

This function requires superuser privileges.

Updates the hostname and port of an existing node in pg_dist_node.

citus_set_node_property

Changes properties of a node in pg_dist_node. Currently only the shouldhaveshards property can be modified.

citus_add_inactive_node

This function requires superuser privileges.

Registers a new node as inactive in pg_dist_node. The node receives no shard placements and reference tables are not copied.

citus_activate_node

This function requires superuser privileges.

Marks an inactive node as active in pg_dist_node and copies reference tables to it. Useful for nodes added via citus_add_inactive_node.

citus_disable_node

This function requires superuser privileges.

Marks a node as inactive, removes its reference tables, and temporarily removes it from the cluster. Reactivation is performed with citus_activate_node.

citus_add_secondary_node

This function requires superuser privileges.

Registers a secondary node for an existing primary node and updates pg_dist_node.

citus_remove_node

This function requires superuser privileges.

Removes a node from pg_dist_node. The operation fails if any shards remain on the node; shards must be moved off first.

citus_get_active_worker_nodes

Returns a list of active worker hostnames and ports.

citus_backend_gpid

Returns the global process identifier (GPID) for the backend serving the current session. The GPID encodes the node and the OS process ID. Citus extends pg_cancel_backend() and pg_terminate_backend() to accept GPIDs, allowing a backend on one node to affect a backend on another node.

citus_check_cluster_node_health (beta)

This function is part of Citus 11‑beta.

Checks all pairwise connections between nodes. For N nodes, it verifies the N² connections.

citus_set_coordinator_host

Required when adding workers to a cluster that was originally created as a single‑node cluster. The coordinator stores its hostname in citus.local_hostname (default localhost). Workers attempt to connect to this hostname; updating it ensures workers can reach the coordinator.

master_get_table_metadata

Returns distribution‑related metadata for a distributed table, including relation ID, storage type, distribution method, distribution column, replication count (deprecated), maximum shard size, and shard placement policy. The function queries Citus metadata tables, assembles the information into a tuple, and returns it.

get_shard_id_for_distribution_column

Provides the shard ID for a given distribution‑column value for hash‑distributed tables and reference tables. This low‑level detail can be useful for manual maintenance or troubleshooting.

column_to_column_name

Converts the partkey column of pg_dist_partition to the textual column name, allowing identification of a table’s distribution column. See the discussion in the “finding_dist_col” documentation.

citus_relation_size

Reports the disk space used by all shards of a distributed table, including the size of the primary branch but excluding visibility maps and free‑space maps.

citus_table_size

Reports the disk space used by all shards of a distributed table, excluding indexes but including TOAST data, visibility maps, and free‑space maps.

citus_total_relation_size

Reports the total disk space used by all shards of a distributed table, including all indexes and TOAST data.

citus_stat_statements_reset

Clears all rows from citus_stat_statements. This function is independent of pg_stat_statements_reset(); both must be called to reset all statistics.

Cluster Management and Repair Functions

citus_move_shard_placement

Moves a specified shard (and any colocated shards) from one node to another. It is typically invoked indirectly by the rebalancer rather than manually.

Two movement modes exist: blocking (writes to the shard are paused) and non‑blocking (uses PostgreSQL 10 logical replication). After a successful move, the source shard is removed; on failure, an error is raised and both source and target remain unchanged.

rebalance_table_shards

Redistributes a table’s shards across workers to achieve balance. The function first computes a move plan that respects a cost model, then moves shards one by one, updating metadata after each move.

The default strategy, by_shard_count, assumes equal shard size, similar traffic, identical worker hardware, and no fixed shard placement. It assigns a cost of 1 to each shard, so balancing cost equates to balancing shard count.

Shard sizes are roughly equal.

Shard traffic is roughly equal.

Worker nodes have similar size/type.

Shards are not fixed to specific workers.

If any assumption is violated, the default plan may be suboptimal; a custom rebalance_strategy can be supplied.

It is recommended to run get_rebalance_table_shards_plan first to review the planned moves.

get_rebalance_table_shards_plan

Outputs the planned shard moves for rebalance_table_shards without executing them. The plan may differ slightly from the one generated by an immediate rebalance_table_shards call because the underlying server state can change between calls.

get_rebalance_progress

During an ongoing rebalance, reports the progress of each shard involved in the operation.

citus_add_rebalance_strategy

Inserts a row into pg_dist_rebalance_strategy to define a new rebalance strategy.

citus_set_default_rebalance_strategy

Updates the default strategy used by the rebalancer by modifying the strategy table.

citus_remote_connection_stats

Shows the number of active connections per remote node.

citus_drain_node

Moves all shards from a node whose shouldhaveshards property is true to other nodes before the physical server is decommissioned.

isolate_tenant_to_new_shard

Creates a new shard that holds rows with a specific distribution‑column value. This is useful for multi‑tenant workloads where a large tenant can be isolated onto its own shard and eventually its own physical node.

citus_create_restore_point

Blocks writes across the entire cluster and creates a named restore point on all nodes, analogous to pg_create_restore_point but coordinated cluster‑wide. This enables point‑in‑time recovery and cluster forking.