Citus 14.0 is out! Now with PG18 Support. Read all about it in Mehmet’s 14.0 blog post. 💥
Citus 14.0 is out! Now with PG18 Support. Read all about it in Mehmet’s 14.0 blog post. 💥
Welcome to the release notes for Citus 14. The headline for 14 is PostgreSQL 18 support—so you can upgrade to Postgres 18 while keeping Citus distributed SQL working end-to-end across coordinator + workers.
Like prior major releases, once Citus is compatible with the new Postgres major, many upstream improvements flow through automatically, but PG18 also introduces new SQL surface area and behavior changes that require Citus-specific work (parsing/deparsing, DDL propagation, and regression stability). (See prior major release notes for context.)
Citus 14 is primarily about PostgreSQL 18 compatibility so teams can adopt Postgres 18 while keeping the distributed SQL, sharding, and operational model that Citus clusters depend on.
PostgreSQL 18 is a substantial release: asynchronous I/O (AIO), skip-scan for multicolumn B-tree indexes, uuidv7(), virtual generated columns by default, OAuth authentication, RETURNING OLD/NEW, and temporal constraints.
Because Citus is implemented as a Postgres extension, most upstream improvements “just work” once compatibility lands—but major releases also bring SQL surface-area changes and planner/behavior shifts that require Citus-specific work to keep distributed semantics correct across coordinator + workers, and to keep DDL/DML/utility commands working end-to-end.
This page dives deep into many of the changes in Citus 14, including:
uuidv7(), OAuth)uuidv7() for time-ordered UUIDs: reduce index churn with time-ordered UUID generationJSON_TABLE() COLUMNS clause support in distributed queries (PG18 expansion)WITHOUT OVERLAPS in UNIQUE/PRIMARY KEY and PERIOD in FOREIGN KEYLIKE support when creating foreign tablespublish_generated_columns)ONLY to keep maintenance scoped to the parent table in partitioned schemasCHECK/FOREIGN KEY ... NOT ENFORCED (and related partitioned-table additions)RETURNING old/new semantics through distributed execution and result shapingfile_copy_method, new EXPLAIN (WAL) fields, and PG_MODULE_MAGIC_EXT supportcitus_create_restore_point() for consistent restore points in MX (multi-writer) mode clustersPostgres 18 brings performance and UX improvements like AIO, skip-scan, and uuidv7(). Once Citus is compatible, these benefit Citus clusters automatically—especially for shard-heavy scans and maintenance. Citus 14 focuses on the PG18-specific syntax and behavior that need distributed planning, deparsing, and propagation updates so coordinator + workers stay in sync.
Postgres 18 adds an asynchronous I/O subsystem that can improve sequential scans, bitmap heap scans, and vacuuming—workloads that show up constantly in shard-heavy distributed clusters.
Postgres 18 expands when multicolumn B-tree indexes can be used via skip scan, helping common multi-tenant schemas where predicates don’t always constrain the leading index column.
uuidv7() for time-ordered UUIDsTime-ordered UUIDs can reduce index churn and improve locality; Postgres 18 adds uuidv7().
Postgres 18 adds OAuth authentication, making it easier to plug database auth into modern SSO flows—often a practical requirement in multi-node deployments.
Citus tracks the PG18 work as a concrete checklist of syntax, behavior, deparsing/propagation, and regression coverage items required for distributed correctness.
PG18 expands SQL/JSON JSON_TABLE() with a richer COLUMNS clause, making it easy to extract multiple fields from JSON documents in a single, typed table expression. Citus 14 ensures the syntax can be parsed/deparsed and executed consistently in distributed queries.
Example: extract multiple fields (name, age) from a nested JSON object:
CREATE TABLE pg18_json_test (id serial PRIMARY KEY, data JSON);
INSERT INTO pg18_json_test (data) VALUES
('{ "user": {"name": "Alice", "age": 30, "city": "San Diego"} }'),
('{ "user": {"name": "Bob", "age": 25, "city": "Los Angeles"} }'),
('{ "user": {"name": "Charlie", "age": 35, "city": "Los Angeles"} }'),
('{ "user": {"name": "Diana", "age": 28, "city": "Seattle"} }'),
('{ "user": {"name": "Evan", "age": 40, "city": "Portland"} }'),
('{ "user": {"name": "Ethan", "age": 32, "city": "Seattle"} }'),
('{ "user": {"name": "Fiona", "age": 27, "city": "Seattle"} }'),
('{ "user": {"name": "George", "age": 29, "city": "San Francisco"} }'),
('{ "user": {"name": "Hannah", "age": 33, "city": "Seattle"} }'),
('{ "user": {"name": "Ian", "age": 26, "city": "Portland"} }'),
('{ "user": {"name": "Jane", "age": 38, "city": "San Francisco"} }');
SELECT jt.name, jt.age
FROM pg18_json_test,
JSON_TABLE(
data,
'$.user'
COLUMNS (
age INT PATH '$.age',
name TEXT PATH '$.name'
)
) AS jt
WHERE jt.age BETWEEN 25 AND 35
ORDER BY jt.age, jt.name;
Postgres 18 adds temporal constraint syntax that Citus must propagate and preserve correctly:
WITHOUT OVERLAPS for PRIMARY KEY / UNIQUEPERIOD for FOREIGN KEYExample: WITHOUT OVERLAPS in a composite primary key (ranges must not overlap for the same key):
CREATE TABLE temporal_rng (
-- Use an int4range so we don't depend on btree_gist for plain ints
id int4range,
valid_at daterange,
CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
SELECT create_distributed_table('temporal_rng', 'id');
-- ok: non-overlapping ranges for the same id
INSERT INTO temporal_rng (id, valid_at)
VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
INSERT INTO temporal_rng (id, valid_at)
VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
-- should fail: overlaps the existing [2018-01-02,2018-02-03)
INSERT INTO temporal_rng (id, valid_at)
VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05'));
Example: temporal foreign key using PERIOD (child rows must match a parent row over the referenced time period):
-- Parent table (reference table here to allow FK from a distributed table)
CREATE EXTENSION btree_gist;
CREATE TABLE temporal_test (
id integer,
valid_at daterange,
CONSTRAINT temporal_test_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
SELECT create_reference_table('temporal_test');
-- Child table (distributed) with temporal FK
CREATE TABLE temporal_fk_rng2rng (
id integer,
valid_at daterange,
parent_id integer,
CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
SELECT create_distributed_table('temporal_fk_rng2rng', 'id');
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_test (id, PERIOD valid_at);
-- sample data
INSERT INTO temporal_test VALUES
(1, '[2000-01-01,2001-01-01)'),
(1, '[2001-01-01,2002-01-01)');
-- ok: fully covered by parent periods
INSERT INTO temporal_fk_rng2rng VALUES
(1, '[2000-01-01,2001-01-01)', 1);
-- ok: spans two parent periods
INSERT INTO temporal_fk_rng2rng VALUES
(2, '[2000-01-01,2002-01-01)', 1);
-- should fail: missing parent_id=3 for that period
INSERT INTO temporal_fk_rng2rng VALUES
(3, '[2000-01-01,2001-01-01)', 3);
Postgres 18 supports CREATE FOREIGN TABLE ... LIKE, letting you define a foreign table by copying the column layout (and optionally defaults/constraints/indexes) from an existing table. Citus 14 includes coverage so FDW workflows remain compatible in distributed environments.
Example (from the PG18 regression coverage): create a local table with a few modern features, then create foreign tables using LIKE ... EXCLUDING ALL vs LIKE ... INCLUDING ALL.
SET citus.use_citus_managed_tables TO ON;
CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port :'master_port', dbname 'regression');
CREATE USER MAPPING FOR CURRENT_USER
SERVER foreign_server
OPTIONS (user 'postgres');
CREATE TABLE ctl_table(
a int PRIMARY KEY,
b varchar COMPRESSION pglz,
c int GENERATED ALWAYS AS (a * 2) STORED,
d bigint GENERATED ALWAYS AS IDENTITY,
e int DEFAULT 1
);
CREATE INDEX ctl_table_ab_key ON ctl_table(a, b);
EXCLUDING ALL example:
CREATE FOREIGN TABLE ctl_ft1 (LIKE ctl_table EXCLUDING ALL)
SERVER foreign_server
OPTIONS (schema_name 'pg18_nn', table_name 'ctl_table');
INCLUDING ALL example:
CREATE FOREIGN TABLE ctl_ft2 (LIKE ctl_table INCLUDING ALL)
SERVER foreign_server
OPTIONS (schema_name 'pg18_nn', table_name 'ctl_table');
SELECT * FROM ctl_ft2 ORDER BY a;
PostgreSQL 18 changes generated column behavior significantly:
Key changes:
publish_generated_columns publication option for replicating generated valuesCitus 14 ensures these PG18 changes work cleanly in distributed clusters by updating distributed DDL propagation and metadata handling so generated-column definitions remain consistent across coordinator and workers.
Example 1: Virtual generated column (computed on read):
CREATE TABLE events (
id bigint,
payload jsonb,
payload_hash text GENERATED ALWAYS AS (md5(payload::text)) VIRTUAL
);
SELECT create_distributed_table('events', 'id');
INSERT INTO events VALUES
(1, '{"type":"signup","user":"alice"}'),
(2, '{"type":"purchase","user":"bob"}');
-- Computed on read
SELECT id, payload_hash
FROM events
ORDER BY id;
Example 2: Explicit STORED generated column
If you want the value materialized at write time (e.g., heavily filtered/indexed), use STORED explicitly.
CREATE TABLE events_stored (
id bigint,
payload jsonb,
payload_hash text GENERATED ALWAYS AS (md5(payload::text)) STORED
);
SELECT create_distributed_table('events_stored', 'id');
Example 3: Publishing generated columns in logical replication
CREATE PUBLICATION pub_events
FOR TABLE events
WITH (publish_generated_columns = true);
ONLY restores old behaviorPostgres 18 introduces ONLY for VACUUM and ANALYZE so you can explicitly target only the parent of a partitioned/inheritance tree without automatically processing children. This matters in distributed environments because a “parent object” can represent many underlying shard/partition relations, and predictable scoping avoids surprising work during maintenance.
Citus 14 adapts distributed utility-command behavior so ONLY works as intended and maintenance remains predictable across coordinator + workers.
Example: partitioned table + parent-only maintenance
CREATE TABLE metrics (
tenant_id bigint,
ts timestamptz,
value double precision
) PARTITION BY RANGE (ts);
CREATE TABLE metrics_2025_01 PARTITION OF metrics
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE metrics_2025_02 PARTITION OF metrics
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
SELECT create_distributed_table('metrics', 'tenant_id');
-- Parent-only: do not recurse into partitions/children
VACUUM (ANALYZE) ONLY metrics;
ANALYZE ONLY metrics;
Example: (contrast) without ONLY
If you omit ONLY, Postgres may process children as well (depending on the command and table structure), which can be undesirable when you intended a light-touch operation.
VACUUM (ANALYZE) metrics;
ANALYZE metrics;
NOT ENFORCED (Partitioned-Table Additions)Postgres 18 expands constraint syntax and catalog semantics in ways Citus must parse/deparse and propagate correctly across coordinator + workers:
CHECK / FOREIGN KEY constraints can be marked NOT ENFORCED (tracked via pg_constraint.conenforced)Partitioned-table additions exercised in Citus PG18 coverage:
NOT VALID foreign keys on partitioned tablesALTER TABLE ... DROP CONSTRAINT ONLY on partitioned tablesExample 1: NOT ENFORCED constraints
CREATE TABLE customers (id bigint PRIMARY KEY);
SELECT create_reference_table('customers');
CREATE TABLE orders (
id bigint PRIMARY KEY,
customer_id bigint NOT NULL,
amount numeric
);
SELECT create_distributed_table('orders', 'id');
ALTER TABLE orders
ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT ENFORCED;
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id)
NOT ENFORCED;
SELECT conname, conenforced
FROM pg_constraint
WHERE conrelid = 'orders'::regclass
ORDER BY conname;
Example 2: Partitioned-table constraints
CREATE TABLE parent (id bigint PRIMARY KEY);
SELECT create_reference_table('parent');
CREATE TABLE child (
tenant_id bigint,
id bigint,
parent_id bigint,
PRIMARY KEY (tenant_id, id)
) PARTITION BY RANGE (id);
CREATE TABLE child_p0 PARTITION OF child FOR VALUES FROM (0) TO (1000);
SELECT create_distributed_table('child', 'tenant_id');
ALTER TABLE child
ADD CONSTRAINT child_parent_fk
FOREIGN KEY (parent_id) REFERENCES parent(id)
NOT VALID;
ALTER TABLE child DROP CONSTRAINT ONLY child_parent_fk;
RETURNING OLD/NEWPostgres 18 lets RETURNING reference both the previous (old) and new (new) row values in INSERT/UPDATE/DELETE/MERGE. Citus 14 preserves these semantics in distributed execution and ensures results are returned correctly from coordinator + workers.
Example 1: UPDATE with old and new values:
UPDATE t
SET v = v + 1
WHERE id = 42
RETURNING old.v AS old_v, new.v AS new_v;
Example 2: DELETE capturing old values:
DELETE FROM t
WHERE id = 42
RETURNING old.v AS deleted_v;
PostgreSQL 18 introduces several infrastructure improvements that affect tooling and extension development:
Citus 14 adapts to PG18 interface/output changes that affect tooling and extension plumbing:
file_copy_method for CREATE DATABASE ... STRATEGY=FILE_COPY (and related file-copy operations).EXPLAIN (WAL) adds a “WAL buffers full” field; Citus propagates it through distributed EXPLAIN output.PG_MODULE_MAGIC_EXT so extensions can report name/version metadata.Example 1: Enhanced EXPLAIN output:
EXPLAIN (ANALYZE, WAL)
SELECT count(*) FROM dist_table;
Example 2: Configuring file copy method:
SET file_copy_method = 'copy';
The citus_create_restore_point() function has been enhanced to provide consistent restore points in MX (multi-writer) mode clusters. Previously, while this function worked correctly in coordinator-only mode by blocking distributed writes at the coordinator level, MX mode clusters could experience inconsistent restore points because metadata workers could continue making 2PC commit decisions independently.
In coordinator-only mode, all distributed transactions are coordinated through the coordinator, making it sufficient to block writes there. However, in MX mode, any worker node with metadata can initiate distributed transactions. The previous implementation only blocked writes at the coordinator, allowing metadata workers to continue committing distributed transactions. This could result in restore points on different nodes representing different transaction visibility, an inconsistent cluster state that could cause data divergence during recovery.
The enhanced implementation now blocks distributed transaction commit decisions cluster-wide by acquiring ExclusiveLock on pg_dist_transaction on all metadata nodes (coordinator and MX workers). Additionally, on the coordinator only, locks are acquired on pg_dist_node and pg_dist_partition to prevent topology and schema changes.
This selective locking strategy leverages MX mode's architecture:
pg_dist_transaction on remote metadata nodes is sufficient to block all distributed writes they can performExclusiveLock on pg_dist_node, pg_dist_partition, and pg_dist_transaction locally on the coordinatorExclusiveLock on pg_dist_transaction on all remote metadata nodes (executed in parallel)The commit decision in Citus 2PC occurs when LogTransactionRecord() writes to pg_dist_transaction (using RowExclusiveLock), which happens before the writer's local commit. By holding ExclusiveLock on pg_dist_transaction:
This creates a clean cut point for consistency without requiring transaction drainage. The restore point captures the exact state of committed transactions across the cluster.
The maintenance daemon's recovery logic relies on the presence of pg_dist_transaction records to determine whether to COMMIT PREPARED or ROLLBACK PREPARED. The locking ensures that:
Since restore points are created while holding these locks, all nodes capture the same set of commit decisions, ensuring cluster-wide consistency.
This enhancement is fully backward compatible:
pg_lsn)Citus 14 includes valuable contributions from the community. We're grateful for these improvements:
PushActiveSnapshot outside a loop to improve correctness/efficiency, contributed by @ivan-v-kush. (GitHub)With Citus 14, we removed PostgreSQL 15 support, following Citus’s policy of supporting the latest 3 PostgreSQL major releases. We highly encourage you to upgrade to Citus 14 and PostgreSQL 18.