Cloud-Environment DB: Citus 13 Cluster brings Postgresql 17 to distribute the atmosphere

The development team of CITUS data has presented a version 13 of the Cloud Native Database Citus. As an open source extension for postgres, the newly released postgressQuL provides the limit of tasks ranging from 17.2 to distributed database cluster environment. Bugfix is ​​included in the update, which corrects both mistakes and accidents in relation to uncertain catalog access, as well as the partition errors in distributed processes. Important innovations include Query and optimizer function from postgresql 17.

Advertisement

Extended syntax for merge operations

In data synchronization between targets and source tables, SQL statement allows MERGE Efficient combination of several work stages with INSERT, UPDATE And DELETEPostgrade 15 since it has also become possible to define tasks for those lines that are available in the source, but not the target – MERGE-Option WHEN NOT MATCHED BY TARGETPostgresql 17 also opens the opportunity to work with lines in the target table that does not occur in the source to simplify various updates and data charging processes. MERGE-Option WHEN NOT MATCHED BY SOURCE Citus-13 users are now available in distributed environment. From the following list Blog post for new version Shows a simple example of tables managed by Citus:

IX Workshop: Programming Programming with Github Copilot and Chatgpt

-- 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 takes another important innovation from Postgrace 17: JSON’s better handling of data. Celebration JSON_TABLE() JSON converts data into a standard postgresql table, so that developers can use the tackling ideas familiar by SQL instead of JSON format.

More options for distributed tables

Citus 13 provides three new options for working with specially distributed tables. On the one hand, users can now with CREATE TABLE ... USING Define a access method for a division table, which is then using the Citus Signature Function create_distributed_table() Can be distributed. In addition, support for the identity column introduced in Citus 11.2 can now also be used in distributed tables. Third, Citus now enables an exclusion restriction by SQL statement ALTER TABLE distributed_partitioned_table ADD CONSTRAINT ... Passes on all nodes in the cluster.

Other innovations in Citus 13 can be found at Postgrace 17 EXPLAIN-Screy options SERIALIZE And MEMORYMore details and Citus 13 offers more complete observation of all changes Blog post by Citus DataThe company has been a part of Microsoft since the announcement of its end in early 2019.

(Map)