Citus Distributed PostgreSQL: SQL Reference for Creating and Modifying Distributed Tables

Create Distributed Table

Define the table schema with a regular CREATE TABLE statement, then call the Citus function create_distributed_table() with the table name and the distribution column. The function creates citus.shard_count shards, each replicated according to citus.shard_replication_factor. Each shard appears on workers as a regular PostgreSQL table named tablename_shardid. Metadata about the distribution is stored on the coordinator.

CREATE TABLE github_events (
    event_id bigint,
    event_type text,
    event_public boolean,
    repo_id bigint,
    payload jsonb,
    repo jsonb,
    actor jsonb,
    org jsonb,
    created_at timestamp
);
SELECT create_distributed_table('github_events', 'repo_id');

Reference Tables

A reference table is distributed to a single shard and replicated on every worker. It is useful for small lookup data that is joined frequently with larger distributed tables.

Small tables that join with larger distributed tables.

Tables lacking a tenant‑ID column in a multi‑tenant app.

Tables that need a unique constraint across a small set of rows.

Example (US‑centric tax rates):

CREATE TABLE states (
  code char(2) PRIMARY KEY,
  full_name text NOT NULL,
  general_sales_tax numeric(4,3)
);
SELECT create_reference_table('states');

Reference tables are marked in Citus metadata and participate in two‑phase commit (2PC) for strong consistency. 2PC documentation: https://en.wikipedia.org/wiki/Two-phase_commit_protocol

Distribution Coordinator Data

When converting an existing PostgreSQL database, create_distributed_table can be run on empty or populated tables. For populated tables the function copies data and emits notices such as NOTICE: Copying data from local table…. Writes are blocked during the copy; after the function succeeds, pending writes are processed as distributed queries.

CREATE TABLE series AS SELECT i FROM generate_series(1,1000000) i;
SELECT create_distributed_table('series', 'i');
-- NOTICE messages appear, then:
-- HINT: To remove the local data, run:
-- SELECT truncate_local_data_after_distributing_table($$public.series$$);

If a foreign key is created before the referenced table is distributed, Citus raises an error; the fix is to drop the foreign key, distribute the tables, then recreate the foreign key.

Co‑location (Table Co‑location)

Co‑location groups tables that share the same distribution column type, shard count, and replication factor so they are placed on the same workers, improving join performance and shard‑rebalancing. Use the optional colocate_with parameter of create_distributed_table to assign a table to an existing co‑location group, or set it to 'none' to keep the table isolated.

SELECT create_distributed_table('A', 'some_int_col');
SELECT create_distributed_table('B', 'other_int_col');
-- Explicitly co‑locate with 'stores'
SELECT create_distributed_table('orders', 'store_id', colocate_with => 'stores');
SELECT create_distributed_table('products', 'store_id', colocate_with => 'stores');

Metadata about co‑location groups is stored in pg_dist_colocation; table‑to‑group mapping is in pg_dist_partition. Documentation: https://docs.citusdata.com/en/v11.0-beta/sharding/data_modeling.html#colocation

Upgrade from Citus 5.x

Starting with Citus 6.0, co‑location is a first‑class concept stored in pg_dist_colocation. Tables created in Citus 5.x lack explicit co‑location metadata, so run mark_tables_colocated to associate them with a group without moving data.

SELECT mark_tables_colocated('stores', ARRAY['products','line_items']);

Function updates Citus metadata only. Documentation: https://docs.citusdata.com/en/v11.0-beta/develop/api_udf.html#mark-tables-colocated

Drop Table

Use the standard DROP TABLE command. It removes the table, its indexes, rules, triggers, constraints, and the shards on each worker, cleaning up metadata.

DROP TABLE github_events;

Alter Table (DDL Propagation)

Citus automatically propagates many DDL statements (e.g., ALTER TABLE ADD COLUMN, ALTER TABLE ALTER COLUMN SET DEFAULT) from the coordinator to all shards. Statements that modify the distribution column must be executed manually or will raise an error.

ALTER TABLE products ADD COLUMN description text;
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
-- This will error:
ALTER TABLE products ALTER COLUMN store_id TYPE text;

Automatic propagation can be enabled or disabled via the GUC citus.enable_ddl_propagation. Documentation: https://docs.citusdata.com/en/v11.0-beta/develop/api_guc.html#enable-ddl-prop

Add/Drop Constraints

Citus supports PostgreSQL constraints, but unique constraints and foreign keys must include the distribution column. Reference tables cannot have foreign keys pointing to distributed tables. Local‑to‑reference‑table foreign keys are allowed, but ON DELETE/UPDATE CASCADE is not supported.

-- Primary key including distribution column
ALTER TABLE accounts ADD PRIMARY KEY (id);
ALTER TABLE ads ADD PRIMARY KEY (account_id, id);
-- Foreign key (both tables must be distributed or reference tables)
ALTER TABLE ads ADD CONSTRAINT ads_account_fk FOREIGN KEY (account_id) REFERENCES accounts (id);

Documentation: https://www.postgresql.org/docs/current/static/ddl-constraints.html ; https://docs.citusdata.com/en/v11.0-beta/reference/common_errors.html#non-distribution-uniqueness

NOT VALID Constraints

To add a constraint without validating existing rows, use PostgreSQL’s NOT VALID clause. New rows are checked immediately; existing rows can be validated later with VALIDATE CONSTRAINT.

ALTER TABLE users ADD CONSTRAINT syntactic_email CHECK (
  email ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
) NOT VALID;
INSERT INTO users VALUES ('fake'); -- fails because the new row violates the CHECK
ALTER TABLE users VALIDATE CONSTRAINT syntactic_email;

Documentation: https://www.postgresql.org/docs/current/sql-altertable.html

Add/Drop Indexes

Citus forwards standard CREATE INDEX and DROP INDEX statements. In production, use CREATE INDEX CONCURRENTLY to build the index without blocking writes.

-- Adding an index
CREATE INDEX clicked_at_idx ON clicks USING BRIN (clicked_at);
-- Adding an index without locking writes
CREATE INDEX CONCURRENTLY clicked_at_idx ON clicks USING BRIN (clicked_at);
-- Removing an index
DROP INDEX clicked_at_idx;

Documentation: https://www.postgresql.org/docs/current/static/sql-createindex.html ; https://www.postgresql.org/docs/current/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Manual Propagation

DDL commands that are not automatically propagated can be sent manually to workers using the manual propagation mechanism. Documentation: https://docs.citusdata.com/en/v11.0-beta/develop/reference_propagation.html#manual-prop