citus

Overview

PIGSTY packages 14.3.0 / SQL 14.3-1 for PG16-18. Requires shared_preload_libraries=citus; conflicts with Hydra.

Version

Build

You can build the RPM / DEB packages for citus using pig build:

pig build pkg citus         # build RPM / DEB packages

Install

You can install citus directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install citus;          # Install for current active PG version

pig ext install -y citus -v 18  # PG 18
pig ext install -y citus -v 17  # PG 17
pig ext install -y citus -v 16  # PG 16

dnf install -y citus_18       # PG 18
dnf install -y citus_17       # PG 17
dnf install -y citus_16       # PG 16

apt install -y postgresql-18-citus   # PG 18
apt install -y postgresql-17-citus   # PG 17
apt install -y postgresql-16-citus   # PG 16

Preload:

shared_preload_libraries = 'citus';

Create Extension:

CREATE EXTENSION citus;

Usage

Sources:

• Citus v14.3.0 release
• Citus v14.3.0 CHANGELOG
• Citus v14.3.0 control file
• Citus v14.3.0 README
• What is Citus?
• Citus Utility Functions
• Citus upgrade procedure

Citus turns PostgreSQL into a distributed database by sharding tables across worker nodes while keeping PostgreSQL SQL, indexes, extensions, transactions, and operational tooling as the user-facing surface. It is commonly used for multi-tenant SaaS databases, real-time analytics, time-series/event workloads, and distributed microservice schemas.

The extension name is citus. Release 14.3.0 uses SQL/control version 14.3-1 and supports PostgreSQL 16-18. Every node must preload the library before a superuser runs CREATE EXTENSION; the extension is not relocatable.

Enable Citus

shared_preload_libraries = 'citus'

Preserve any other required preload libraries. Restart PostgreSQL on the coordinator and workers, then create the extension in each participating database:

CREATE EXTENSION IF NOT EXISTS citus;
SELECT citus_version();

On a multi-node cluster, register the coordinator and workers from the coordinator:

SELECT citus_set_coordinator_host('coord-1', 5432);
SELECT * FROM citus_add_node('worker-1', 5432);
SELECT * FROM citus_add_node('worker-2', 5432);

SELECT * FROM citus_get_active_worker_nodes();

Distributed Tables

Distribute a table by a shard key. Rows with the same shard-key value are colocated on the same shard, so tenant-scoped joins and point lookups stay local.

CREATE TABLE events (
  tenant_id  bigint,
  event_id   bigserial,
  event_at   timestamptz DEFAULT now(),
  kind       text,
  payload    jsonb,
  PRIMARY KEY (tenant_id, event_id)
);

Distribute the table and tune the shard count and colocation explicitly:

SELECT create_distributed_table(
  'events',
  'tenant_id',
  shard_count  := 64,
  colocate_with := 'none'
);

When choosing an explicit shard count, start a new colocation group with colocate_with := 'none'. To colocate with an existing distributed table, name that table and let its shard count determine the layout.

Queries that filter on the distribution column can route to a single shard:

SELECT *
FROM events
WHERE tenant_id = 42
ORDER BY event_at DESC
LIMIT 50;

Cross-shard queries are planned as distributed tasks and run in parallel on the workers:

SELECT kind, count(*)
FROM events
WHERE event_at >= now() - interval '1 hour'
GROUP BY kind
ORDER BY count DESC;

Reference Tables

Reference tables are fully replicated to all workers. They are useful for small lookup tables that must join with many distributed tables.

CREATE TABLE countries (
  code text PRIMARY KEY,
  name text NOT NULL
);

SELECT create_reference_table('countries');

Schema-Based Sharding

Schema-based sharding is useful when each tenant or service owns its own schema. Citus supports running schema-sharding DDLs from any node, including CREATE SCHEMA, DROP SCHEMA, ALTER SCHEMA RENAME, ALTER SCHEMA OWNER, and table-level DDL on distributed schemas.

CREATE SCHEMA tenant_42;
SELECT citus_schema_distribute('tenant_42');

CREATE TABLE tenant_42.orders (
  id bigserial PRIMARY KEY,
  amount numeric,
  created_at timestamptz DEFAULT now()
);

Use row-based distribution for shared tables and schema-based sharding for per-tenant schema layouts; do not mix the two models casually without checking colocation and SQL-support implications.

Node and Shard Operations

-- Add or disable nodes.
SELECT * FROM citus_add_node('worker-3', 5432);
SELECT * FROM citus_disable_node('worker-2', 5432);
SELECT * FROM citus_activate_node('worker-2', 5432);

-- Drain and remove a node.
SELECT * FROM citus_drain_node('worker-1', 5432);
SELECT * FROM citus_remove_node('worker-1', 5432);

-- Rebalance shards.
SELECT citus_rebalance_start();
SELECT * FROM citus_rebalance_status();
SELECT rebalance_table_shards('events');

-- Inspect tables and shards.
SELECT * FROM citus_tables;
SELECT * FROM citus_shards;

Backup Coordination

Citus v14.1.0 adds UDFs for blocking distributed 2PC commit decisions and schema/topology changes while taking coordinated disk snapshots. Use them only inside a controlled backup workflow, and always unblock the cluster after the snapshot step.

SELECT citus_cluster_changes_block();
SELECT * FROM citus_cluster_changes_block_status();

-- Take coordinated filesystem or volume snapshots here.

SELECT citus_cluster_changes_unblock();

Pair these functions with regular PostgreSQL backup discipline: consistent checkpoints, WAL archiving, snapshot ordering across nodes, and a tested restore procedure.

Administrative Controls

Citus 14.2 adds the superuser-only citus_internal.distribute_object() repair helper for supported database objects whose metadata was not propagated correctly. Treat it as a targeted recovery operation, not as the normal distribution API.

The release also adds citus.allow_unsafe_insert_select_pushdown for explicitly opting into batched INSERT ... SELECT pushdown on colocated tables, and improves single-shard stored-procedure execution. Keep the unsafe pushdown setting off unless the workload has been checked against the release-note restrictions.

Caveats

• Citus 14 supports PostgreSQL 16-18 and dropped PostgreSQL 15 support.
• shared_preload_libraries = 'citus' must be set before extension creation. A plain CREATE EXTENSION citus is not enough on a fresh server.
• Choose the distribution column carefully. Primary keys and unique constraints on distributed tables generally need to include the distribution column.
• Cross-shard joins, repartition joins, distributed DDL, and multi-shard writes are powerful but have different planning and locking behavior from single-node PostgreSQL.
• The distribution also provides citus_columnar; its storage and maintenance requirements are separate from distributing row tables.
• The cluster-change blocking functions are operational tools for backups. Do not leave a cluster blocked after a failed backup script.

Release 14.3 and Upgrades

Release 14.3.0 merges sorted worker results for multi-shard ORDER BY queries and fetches rows in smaller batches where possible. It adds citus.enable_or_clause_arm_pruning to prune irrelevant OR-clause arms per shard. It also restricts internal metadata cleanup to superusers, uses an empty search path for internal SPI queries, and replays object definitions under their owner’s restricted execution context.

Coordinate the upgrade across all nodes. After installing matching binaries and restarting PostgreSQL, run the SQL update in each database containing Citus:

ALTER EXTENSION citus UPDATE TO '14.3-1';
SELECT extversion FROM pg_extension WHERE extname = 'citus';
SELECT citus_version();

Finish the cluster upgrade on the coordinator:

CALL citus_finish_citus_upgrade();

Use the SQL/control version in ALTER EXTENSION, not the package version. If both Citus and PostgreSQL need upgrading, complete the Citus upgrade first and follow the separate PostgreSQL-major upgrade procedure.