ClickHouse is a columnar OLAP database designed for fast analytical queries over large datasets. Its MergeTree storage engine uses an ORDER BY key to sort data on disk — the sort order determines both query performance and merge efficiency. UUID v7’s embedded millisecond timestamp makes it a natural ORDER BY key for event data, combining unique identity with time-sorted storage. See UUID in Databases for the broader index strategy context.
generateUUIDv7() — ClickHouse 24.6+
-- ClickHouse 24.6+ built-in
SELECT generateUUIDv7();
-- 018fbe3a-4c5d-7b12-8f3e-1a2b3c4d5e6f
-- Extract timestamp from UUID v7
SELECT UUIDv7ToDateTime(generateUUIDv7());
-- 2026-10-15 14:23:01.234
-- Multiple rows in a single statement each get unique UUID v7 values
SELECT generateUUIDv7() FROM numbers(5);
MergeTree table with UUID v7 primary key
CREATE TABLE events
(
id UUID DEFAULT generateUUIDv7(),
user_id UUID NOT NULL,
event_type String NOT NULL,
properties Map(String, String),
ts DateTime64(3) DEFAULT now64()
)
ENGINE = MergeTree()
ORDER BY id -- UUID v7 = time-sorted storage, no separate date partition needed
PARTITION BY toYYYYMM(UUIDv7ToDateTime(id)); -- partition by month from UUID v7
The PARTITION BY clause extracts the month directly from the UUID v7 timestamp — no separate created_at column required for partition pruning. See Time-Ordered UUID for why this works.
Time-range queries using UUID v7 bounds
-- Build UUID v7 lower bound for "last 24 hours"
-- ClickHouse: dateTimeToUUIDv7 is available in 24.8+
SELECT *
FROM events
WHERE id >= dateTimeToUUIDv7(now() - INTERVAL 24 HOUR)
AND id < dateTimeToUUIDv7(now())
ORDER BY id
LIMIT 1000;
-- Alternatively, use the extracted timestamp (works on all versions)
SELECT *
FROM events
WHERE UUIDv7ToDateTime(id) >= now() - INTERVAL 24 HOUR
ORDER BY id
LIMIT 1000;
Because UUID v7 sorts chronologically, WHERE id >= lower_bound uses the MergeTree sparse index efficiently — no full scan needed. See UUID v7 Performance Benchmarks for index scan comparisons.
Replacing a composite (created_at, id) ORDER BY
Many ClickHouse schemas use ORDER BY (toDate(created_at), id) to get time-sorted storage. UUID v7 simplifies this:
-- Before: composite key required for time ordering
CREATE TABLE events_old (
id UUID,
created_at DateTime64(3),
payload String
) ENGINE = MergeTree()
ORDER BY (toDate(created_at), id);
-- After: UUID v7 alone provides time ordering
CREATE TABLE events_new (
id UUID DEFAULT generateUUIDv7(),
payload String
) ENGINE = MergeTree()
ORDER BY id;
-- created_at is derivable: UUIDv7ToDateTime(id)
Deduplication with UUID v7 in ReplicatedMergeTree
CREATE TABLE events_replicated
(
id UUID DEFAULT generateUUIDv7(),
payload String
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY id;
-- Insert with explicit UUID v7 from application for idempotency
INSERT INTO events_replicated (id, payload)
VALUES ('018fbe3a-4c5d-7b12-8f3e-1a2b3c4d5e6f', 'order_placed');
-- Re-inserting the same UUID v7 is a no-op with ReplacingMergeTree
UUID v7 deduplication works well with UUID as Idempotency Keys — the application generates UUID v7 at request time and uses the same value for retries.
Ingestion from Kafka with UUID v7 message keys
-- Kafka engine table — UUID v7 message key becomes the row ID
CREATE TABLE events_kafka
(
id UUID,
payload String
)
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'broker:9092',
kafka_topic_list = 'events',
kafka_group_name = 'clickhouse-consumer',
kafka_format = 'JSONEachRow';
-- Materialized view inserts into MergeTree
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT id, payload FROM events_kafka;
See UUID v7 in Kafka Streams for the producer-side UUID v7 key generation.
Further reading
- UUID in Databases — Storage and Indexing
- UUID v7 Performance Benchmarks
- UUID v7 in Kafka Streams
- UUID as Idempotency Keys
- Time-Ordered UUID — v7 and the Timestamp Field
External references
Frequently asked questions
Does ClickHouse support UUID v7 natively?
Yes. ClickHouse 24.6 (released June 2024) added generateUUIDv7() as a built-in function. Earlier versions required application-side UUID v7 generation. Use SELECT generateUUIDv7() to verify availability. See ClickHouse UUID functions documentation.
Can UUID v7 replace a (created_at, id) composite primary key in ClickHouse?
Yes. ClickHouse MergeTree uses the ORDER BY key as both the sort order and the sparse primary index. A UUID v7 ORDER BY id gives time-sorted storage because UUID v7 embeds a 48-bit Unix millisecond timestamp. For most analytics workloads, this replaces the ORDER BY (toDate(created_at), id) pattern. For very high-granularity time filtering (millisecond range queries), keep a created_at column for human use and rely on UUID v7 for merge ordering.
What is the storage cost of UUID v7 vs UInt64 in ClickHouse?
A UUID is 16 bytes vs 8 bytes for UInt64. With ClickHouse LZ4 compression, UUID columns compress well because UUID v7 shares timestamp prefix bytes across rows inserted in the same millisecond. In practice, the additional 8 bytes per row is negligible for analytics workloads where columns are typically hundreds of bytes wide. The global uniqueness guarantee is usually worth the storage cost.