ClickHouse October 2023 Release – v23.10

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 November 02, 2023, ClickHouse version 23.10 was released, and this version contains the following;

• 23 new features,
• 26 performance optimizations,
• 60 bug fixes.

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

• v23.10 Source Code : GitHub Link
• v23.10 Release Webinar : Slides
• Installation: ClickHouse Docs

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

Key features & improvements

1. SHOW MERGES

“show merges” query support added. Now you can run “show merges” command to see same as “select * from from system.merges”.

:) SHOW MERGES

Row 1:
──────
table:             cars
database:          default
estimate_complete: 0.02
elapsed:           0.10
progress:          89.11
is_mutation:       0
size_compressed:   4.14 MiB
memory_usage:      12.86 MiB

2. SHOW SETTING

Added statement SHOW SETTING setting_name which is a simpler version of existing statement SHOW SETTINGS.

87580a26e385 :) SHOW SETTING max_block_size



SHOW SETTING max_block_size

Query id: e5be1b93-1da5-4467-9c72-b6dbfd737c9d

┌─value─┐
│ 65409 │
└───────┘

1 row in set. Elapsed: 0.013 sec.

3. byteSwap

Added function byteSwap which reverses the bytes of unsigned integers. This is particularly useful for reversing values of types which are represented as unsigned integers internally such as IPv4.

:) SELECT byteSwap(00474700474700)

SELECT byteSwap(474700474700)

Query id: 9073e11b-51a9-4573-bf43-1f7aafa5395e

┌─byteSwap(474700474700)─┐
│    5506031552854753280 │
└────────────────────────┘

1 row in set. Elapsed: 0.014 sec.

and

SELECT hex(byteSwap(0xAABBCCDD))

SELECT hex(byteSwap(2864434397))

Query id: 8bfd953b-745b-4a38-a56b-42e58bb833a3

┌─hex(byteSwap(2864434397))─┐
│ DDCCBBAA                  │
└───────────────────────────┘

1 row in set. Elapsed: 0.008 sec.

4. Optimization For Many Concurrent Queries

Fix contention on Context lock, this significantly improves performance for a lot of short-running concurrent queries.

23.9:

QPS: 201.059

50.000% 0.997 sec.
99.990% 75.998 sec.

23.10:

QPS: 961.997

50.000% 0.033 sec.
99.990% 4.153 sec.

5. Optimization For Merges

CREATE TABLE test_table (id UInt64, value UInt64)
ENGINE = MergeTree ORDER BY id;

INSERT INTO test_table
SELECT number % 15, number FROM numbers_mt(500000000);

OPTIMIZE TABLE test_table

23.9: Elapsed: 19.328 sec.

23.10: Elapsed: 12.578 sec.

6. Faster Skip Indices

By reusing the index data structures in memory for different granules.

SELECT count() FROM index_test
WHERE x >= 20000 AND x <= 20100 AND y >= 10000 AND y <= 10100

23.9: Elapsed: 0.083 sec.

23.10: Elapsed: 0.058 sec.

Conclusion

This version has made valuable updates for SQL functions and monitoring. Future releases will allow us to use ClickHouse more efficiently.

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