Citus 13 brings PostgreSQL 17 to distributed cluster environments

The Citus Data development team has presented version 13 of the cloud-native database Citus. As an open source extension for Postgres, the new release provides the functional scope of PostgreSQL 17.2 in distributed database cluster environments. The update includes bug fixes that eliminate errors such as crashes in connection with insecure catalog accesses, as well as segmentation errors in distributed procedures. The most important new features include query and optimizer functions from PostgreSQL 17.

Extended syntax for MERGE operations

When synchronizing data between target and source tables, the SQL statement MERGE allows several operations to be efficiently combined with INSERT, UPDATE and DELETE. Since Postgres 15, it is also possible to define actions for rows that are present in the source but not the target – with the MERGE option WHEN NOT MATCHED BY TARGET. PostgreSQL 17 also offers the option of working with rows in the target table that do not exist in the source in order to simplify various update and data loading processes. The MERGE option WHEN NOT MATCHED BY SOURCE is now also available to Citus 13 users in distributed environments. The following listing from the blog post on the new version shows a simple example of tables managed by Citus:

-- 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;

 tid | balance |              val
-----+---------+-------------------------------
   1 |     110 | initial updated by merge
   2 |      20 | inserted by merge
   3 |      30 | inserted by merge
   3 |     300 | initial not matched by source
   4 |      40 | inserted by merge
   5 |     500 | initial not matched by source
(6 rows)

Citus also adopts another important innovation from Postgres 17: improved handling of JSON data. The JSON_TABLE() function converts JSON data into a standard PostgreSQL table so that developers can use the relational views familiar from SQL instead of the JSON format.

More options for distributed partitioned tables

Citus 13 provides three new options specifically for working with distributed partitioned tables. Firstly, users can now use CREATE TABLE ... USING to define an access method for a partitioned table, which can then be distributed using the Citus signature function create_distributed_table(). In addition, the support for identity columns introduced in Citus 11.2 can now also be used in distributed partitioned tables. Thirdly, Citus now allows you to add an exclusion constraint by passing the SQL statement ALTER TABLE distributed_partitioned_table ADD CONSTRAINT ... to all nodes in the cluster.

Read also

Fünf Cloud-native SQL-Datenbanken im Überblick

Other new features in Citus 13 include the EXPLAIN query options SERIALIZE and MEMORY, which are also based on Postgres 17. More details and a more complete overview of all changes in Citus 13 can be found in the Citus Data blog post. The company has been part of Microsoft since the takeover announcement at the beginning of 2019.

(map)