Distribute PostgreSQL 18 with Citus 14

The Citus 14.0 release is out and includes PostgreSQL 18 support! We know you've been waiting, and we've been hard at work adding features we believe will take your experience to the next level, focusing on bringing the Postgres 18 exciting improvements to you at distributed scale.

The Citus database is an open-source extension of Postgres that brings the power of Postgres to any scale, from a single node to a distributed database cluster. Since Citus is an extension, using Citus means you're also using Postgres, giving you direct access to the Postgres features. And the latest of such features came with Postgres 18 release!

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. For those of you who are interested in upgrading to Postgres 18 and scaling these new features of Postgres: you can upgrade to Citus 14.0!

Let's take a closer look at what's new in Citus 14.0:

• Postgres 18 support in Citus 14.0
• PostgreSQL 18 highlights that benefit Citus clusters
• What Citus 14 adds for PostgreSQL 18 compatibility

Postgres 18 support in Citus 14.0

Citus 14.0 introduces support for PostgreSQL 18. This means that just by enabling PG18 in Citus 14.0, all the query performance improvements directly reflect on the Citus distributed queries, and several optimizer improvements benefit queries in Citus out of the box! Among the many new features in PG 18, the following capabilities enabled in Citus 14.0 are especially noteworthy for Citus users:

• Faster scans and maintenance via AIO
• Better index usage with skip-scan
• uuidv7() for time-ordered UUIDs
• OAuth authentication support
• JSON_TABLE() COLUMNS
• Temporal constraints
• CREATE FOREIGN TABLE ... LIKE
• Generated columns (Virtual by Default)
• VACUUM/ANALYZE ONLY semantics
• Constraints: NOT ENFORCED + partitioned-table additions
• DML: RETURNING OLD/NEW
• COPY expansions
• MIN()/MAX() on arrays and composite types
• Nondeterministic collations
• sslkeylogfile connection parameter
• Planner fix: enable_self_join_elimination
• Utility/Ops plumbing and observability

To learn more about how you can use Citus 14.0 + PostgreSQL 18, as well as currently unsupported features and future work, you can consult the Citus 14.0 Updates page, which gives you detailed release notes.

PostgreSQL 18 highlights that benefit Citus clusters

Because Citus is implemented as a Postgres extension, the following PG18 improvements benefit your distributed cluster automatically—no Citus-specific changes needed.

Faster scans and maintenance via AIO

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. This means your Citus cluster can benefit from faster table scans and more efficient maintenance operations without any code changes.

You can control the I/O method via the new io_method GUC:

-- Check the current I/O method
SHOW io_method;

Better index usage with skip-scan

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. This is particularly valuable for Citus users with multi-tenant applications where queries often filter by non-leading columns.

-- Multi-tenant index: (tenant_id, created_at)
-- PG18 skip-scan lets this query use the index even without tenant_id
SELECT * FROM events
WHERE created_at > now() - interval '1 day'
ORDER BY created_at DESC
LIMIT 100;

uuidv7() for time-ordered UUIDs

Time-ordered UUIDs can reduce index churn and improve locality; Postgres 18 adds uuidv7(). This is especially useful for distributed tables where you want predictable ordering and better index performance across shards.

-- Use uuidv7() as a time-ordered primary key
CREATE TABLE events (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  tenant_id bigint,
  payload jsonb
);

SELECT create_distributed_table('events', 'tenant_id');

OAuth authentication support

Postgres 18 adds OAuth authentication, making it easier to plug database auth into modern SSO flows—often a practical requirement in multi-node deployments. This simplifies authentication management across your Citus coordinator and worker nodes.

What Citus 14 adds for PostgreSQL 18 compatibility

While the highlights above work out of the box, PG18 also introduces new SQL syntax and behavior changes that require Citus-specific work—parsing/deparsing, DDL propagation across coordinator + workers, and distributed execution correctness. Here's what we built to make these work end-to-end.

JSON_TABLE() COLUMNS

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.

CREATE TABLE pg18_json_test (id serial PRIMARY KEY, data JSON);

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;

Temporal constraints

Postgres 18 adds temporal constraint syntax that Citus must propagate and preserve correctly:

• WITHOUT OVERLAPS for PRIMARY KEY / UNIQUE
• PERIOD for FOREIGN KEY

CREATE TABLE temporal_rng (
  id int4range,
  valid_at daterange,
  CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);

SELECT create_distributed_table('temporal_rng', 'id');

CREATE FOREIGN TABLE ... LIKE

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.

-- Copy column layout from an existing table
CREATE FOREIGN TABLE my_ft (LIKE my_local_table EXCLUDING ALL)
  SERVER foreign_server
  OPTIONS (schema_name 'public', table_name 'my_local_table');

Generated columns (Virtual by Default)

PostgreSQL 18 changes generated column behavior significantly:

1. Virtual by default: Generated columns are now computed on read rather than stored, reducing write amplification
2. Logical replication support: New publish_generated_columns publication option for replicating generated values

CREATE TABLE events (
  id bigint,
  payload jsonb,
  payload_hash text GENERATED ALWAYS AS (md5(payload::text)) VIRTUAL
);

SELECT create_distributed_table('events', 'id');

VACUUM/ANALYZE ONLY semantics

Postgres 18 introduces ONLY for VACUUM and ANALYZE so you can explicitly target only the parent of a partitioned/inheritance tree without automatically processing children. Citus 14 adapts distributed utility-command behavior so ONLY works as intended.

-- Parent-only: do not recurse into partitions/children
VACUUM (ANALYZE) ONLY metrics;
ANALYZE ONLY metrics;

Constraints: NOT ENFORCED + partitioned-table additions

Postgres 18 expands constraint syntax in several ways that Citus must parse/deparse and propagate across coordinator + workers:

• CHECK constraints can be marked NOT ENFORCED
• FOREIGN KEY constraints can be marked NOT ENFORCED
• NOT VALID foreign keys on partitioned tables
• DROP CONSTRAINT ONLY on partitioned tables

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;

DML: RETURNING OLD/NEW

Postgres 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.

UPDATE t
SET v = v + 1
WHERE id = 42
RETURNING old.v AS old_v, new.v AS new_v;

COPY expansions

PG18 adds two useful COPY improvements that Citus 14 supports in distributed queries:

• COPY ... REJECT_LIMIT: set a threshold for how many rows can be rejected before the COPY fails, useful for resilient bulk loading into sharded tables
• COPY table TO from materialized views: export data directly from materialized views

-- Tolerate up to 10 bad rows during bulk load
COPY my_distributed_table FROM '/data/import.csv'
  WITH (FORMAT csv, REJECT_LIMIT 10);

MIN()/MAX() on arrays and composite types

PG18 extends MIN() and MAX() aggregates to work on arrays and composite types. Citus 14 ensures these aggregates work correctly in distributed queries.

CREATE TABLE sensor_data (
  tenant_id bigint,
  readings int[]
);
SELECT create_distributed_table('sensor_data', 'tenant_id');

-- Now works with array columns
SELECT MIN(readings), MAX(readings) FROM sensor_data;

Nondeterministic collations

PG18 extends LIKE and text-position search functions to work with nondeterministic collations. Citus 14 verifies these work correctly across distributed queries.

sslkeylogfile connection parameter

PG18 adds the sslkeylogfile libpq connection parameter for dumping SSL key material, useful for debugging encrypted connections. Citus 14 allows configuring this via citus.node_conn_info so it works across inter-node connections.

Planner fix: enable_self_join_elimination

PG18 introduces the enable_self_join_elimination planner optimization. Citus 14 ensures this works correctly for joins between distributed and local tables, avoiding wrong results that could occur in early PG18 integration.

Utility/Ops plumbing and observability

Citus 14 adapts to PG18 interface/output changes that affect tooling and extension plumbing:

• New GUC file_copy_method for CREATE DATABASE ... STRATEGY=FILE_COPY
• EXPLAIN (WAL) adds a "WAL buffers full" field; Citus propagates it through distributed EXPLAIN output
• New extension macro PG_MODULE_MAGIC_EXT so extensions can report name/version metadata
• New libpq parameter sslkeylogfile support via citus.node_conn_info

Diving deeper into Citus 14.0 and distributed Postgres

To learn more about Citus 14.0, you can:

• Check out the 14.0 Updates page to get the detailed release notes.

You can also stay connected on the Citus Slack and visit the Citus open source GitHub repo to see recent developments as well. If there's something you'd like to see next in Citus, feel free to also open a feature request issue :)

• Citus
• Citus release notes
• COPY
• distributed databases
• distributed Postgres
• JSON
• open source
• Postgres
• vacuum

Written by Mehmet Yilmaz

Senior Software Engineer on the Postgres team at Microsoft. B.Sc. in Computer Engineering from Istanbul Technical University. Contributor to the open-source Citus extension and to DocumentDB, with conference talks at PGDay UK on distributed PostgreSQL.

m3hm3t LinkedIn

• Citus
• Citus release notes
• COPY
• distributed databases
• distributed Postgres
• JSON
• open source
• Postgres
• vacuum