The latest release of Citus, version 13, enhances its functionality by incorporating features from PostgreSQL 17 into distributed database environments. Citus, an open-source extension for PostgreSQL, aims to improve performance and reliability for users leveraging cloud-native databases.

This update addresses various bugs, including fixes for crashes related to unsafe catalog accesses and segmentation faults in distributed procedures. However, the most significant enhancements are derived from the new query and optimizer capabilities introduced with PostgreSQL 17.

One of the standout features in this release is the enhanced syntax for the MERGE operation, facilitating data synchronization between target and source tables. The MERGE command allows for the efficient combination of multiple operations--INSERT, UPDATE, and DELETE--into a single command. With PostgreSQL 15 and now in Citus 13, users can employ the WHEN NOT MATCHED BY TARGET option to define actions for rows present in the source but absent in the target. Additionally, with PostgreSQL 17, users can now interact with rows in the target table that do not exist in the source, simplifying various update and data loading processes using the WHEN NOT MATCHED BY SOURCE option.

To illustrate this, consider the following example for managing distributed tables:

-- Create and distribute the target and source tables CREATE TABLE target_table (tid integer, balance float, val text); CREATE TABLE source_table (sid integer, delta float); SELECT create_distributed_table('target_table', 'tid'); SELECT create_distributed_table('source_table', 'sid'); -- Populate the tables INSERT INTO target_table SELECT id, id * 100, 'initial' FROM generate_series(1,5,2) AS id; INSERT INTO source_table SELECT id, id * 10 FROM generate_series(1,4) AS id; -- Use WHEN NOT MATCHED BY SOURCE MERGE INTO target_table t USING source_table s ON t.tid = s.sid AND tid = 1 WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (sid, delta, 'inserted by merge') WHEN NOT MATCHED BY SOURCE THEN UPDATE SET val = val || ' not matched by source'; -- See the updated distributed target table SELECT * FROM target_table ORDER BY tid;

This example succinctly showcases how Citus harnesses the power of PostgreSQL 17's capabilities within distributed environments.

Citus 13 also brings improved support for handling JSON data. The JSON_TABLE() function now allows developers to convert JSON data into standard PostgreSQL tables, providing a relational view that is more familiar than working with raw JSON formats.

Moreover, the release introduces three new options for managing distributed partitioned tables. Users can now define an access method for partitioned tables using CREATE TABLE ... USING, which can then be distributed via the create_distributed_table() function. Support for identity columns, introduced in Citus 11.2, is now also available for distributed partitioned tables. Additionally, Citus facilitates the implementation of exclusion constraints across all nodes in the cluster using the ALTER TABLE distributed_partitioned_table ADD CONSTRAINT ... command.

Among the other enhancements in Citus 13 are the EXPLAIN query options SERIALIZE and MEMORY, both of which are derived from PostgreSQL 17. For a comprehensive overview of all changes implemented in Citus 13, users can refer to the detailed blog post from Citus Data, which has been part of Microsoft since its acquisition announcement in early 2019.