ClickHouse December 2023 Release – v23.12

Introduction

Every new release includes new features, enhancements, and numerous bug fixes, and the ChistaDATA team always stays on top of the latest releases. On December 29, 2023, ClickHouse version 23.12 was released, and this version contains the following;

  • 21 new features,
  • 18 performance optimizations,
  • 37 bug fixes.

For further details, please see the official ClickHouse docs here.

This article will look at the critical features of the ClickHouse 23.12 release.

Key features & improvements

1. ORDER BY ALL

The ORDER BY clause has been enhanced to include the option of specifying ALL. This indicates that ClickHouse will arrange the results based on all columns listed in the SELECT clause. For instance: SELECT col1, col2 FROM tab WHERE […] ORDER BY ALL.

SELECT * FROM github_events ORDER BY ALL

SELECT * FROM github_events ORDER BY ALL DESC

SELECT * FROM github_events ORDER BY ALL DESC COLLATE 'tr'

2. PASTE JOIN

Introducing PASTE JOIN, a feature that enables users to join tables without an ON clause, simply using row numbers. For example: SELECT * FROM (SELECT number AS a FROM numbers(2)) AS t1 PASTE JOIN (SELECT number AS a FROM numbers(2) ORDER BY a DESC) AS t2.

SELECT * FROM
(
    SELECT
        toYear(date),
        medianExact(price) AS m,
        bar(m, 0, max(m) OVER (), 20) AS b
    FROM uk_price_paid
    GROUP BY ALL
    ORDER BY ALL ASC
) AS t
PASTE JOIN
(
    SELECT *
    FROM numbers(100)
) AS u

3. SQID

Introduced a new SQL function, sqid, to generate Sqids (https://sqids.org/). For example: SELECT sqid(125, 126).

:) SELECT sqid(123);

UKk

:) SELECT sqid(123456789);

rDOOFW

:) SELECT sqid(123, 456, 789);

eVH6til6J

4. APPLY DELETED MASK

Implemented a new mutation command: ALTER TABLE <table> APPLY DELETED MASK. This command enables the enforcement of applying a mask generated by lightweight delete, effectively removing rows marked as deleted from the disk.

Batch DELETE:

ALTER TABLE t DELETE WHERE user = 'ChistaDATA'

— a full rewrite of data parts by copying all the records
  except the deleted ones.

Lightweight DELETE:

DELETE FROM t WHERE user = 'ChistaDATA'

— a lazy deletion by creating a mask of records
  to filter out on SELECT and during merges.

ALTER TABLE t APPLY DELETED MASK

— cleans the deleted records after a lightweight delete operation,
  without the need for OPTIMIZE query.

ALTER TABLE t APPLY DELETED MASK

— cleans the deleted records after a lightweight delete operation,
  without the need for OPTIMIZE query.

Essentially transforms lightweight DELETEs to a batch operation.

5. _part_offset Column

The primary key analysis in MergeTree tables has been extended to include predicates that involve the virtual column _part_offset, optionally with _part. This enhancement allows for leveraging this feature as a unique type of secondary index.

SELECT count(), uniq(_part)
   FROM github_events WHERE _part_offset < 100

┌─count()─┬─uniq(_part)─┐
│    1700 │          17 │
└─────────┴─────────────┘

Conclusion

The ClickHouse 23.12 release introduces significant enhancements, including new SQL functions like ORDER BY ALL and SQID, as well as features such as PASTE JOIN and APPLY DELETED MASK for improved query flexibility and data management. Additionally, performance optimizations and bug fixes enhance overall system efficiency and responsiveness, making ClickHouse more adept at handling concurrent activities.

These are the ClickHouse 23.12 features. To find out more details, please visit the official ClickHouse Docs.

To know more about the ClickHouse releases, do visit the following articles:

About Can Sayn 38 Articles
Can Sayın is experienced Database Administrator in open source relational and NoSql databases, working in complicated infrastructures. Over 5 years industry experience, he gain managing database systems. He is working at ChistaDATA Inc. His areas of interest are generally on open source systems.
Contact: Website