Understanding Primary Key and Order By in ClickHouse MergeTree Tables

  

In ClickHouse, tables that use the MergeTree engine (or any of its family variants) require either a primary key or order keys (i.e., an ORDER BY clause). These keys play an important role in organizing data on disk and optimizing query performance.

 

Unlike traditional relational databases where a primary key enforces uniqueness, in ClickHouse, the primary key is used mainly for data sorting and indexing not for uniqueness. The ORDER BY clause defines how the data is physically stored.

 

This post explains both concepts with examples and clarifies how they work in the MergeTree family of table engines.

 

1. ORDER BY in MergeTree Tables

In ClickHouse, the ORDER BY clause determines how data is sorted inside each data part on disk. It creates a clustered index on the specified column(s). This helps ClickHouse to efficiently locate relevant rows during queries. You must specify ORDER BY when creating a MergeTree table.

CREATE DATABASE IF NOT EXISTS demo_db;

CREATE TABLE demo_db.simple_orders
(
    order_id UInt32,
    user_id UInt32,
    order_date Date,
    total_amount Float64
) 
ENGINE = MergeTree()
ORDER BY order_date;

   

Here, order_date determines the order of data stored on disk.

 

2. PRIMARY KEY in MergeTree Tables

In ClickHouse, a primary key is not used to enforce uniqueness like in traditional RDBMS systems. Instead, it's used for building an index to speed up data reads by allowing the engine to skip unnecessary rows during query execution.

 

The primary key helps ClickHouse identify data ranges efficiently when executing queries with filters, particularly on large datasets.

 

ClickHouse supports two syntaxes for defining the primary key.

 

2.1 Defining PRIMARY KEY Inside the Column List

 

CREATE TABLE demo_db.orders_by_user
(
    order_id UInt32,
    user_id UInt32,
    order_date Date,
    amount Float64,
    PRIMARY KEY(user_id)
)
ENGINE = MergeTree
ORDER BY (user_id, order_date);

   

In this example, the PRIMARY KEY(user_id) is specified inside the column definition. ClickHouse will create a sparse index on user_id.

 

2.2 Defining PRIMARY KEY Outside the Column List

 

CREATE TABLE demo_db.orders_by_user_ex
(
    order_id UInt32,
    user_id UInt32,
    order_date Date,
    amount Float64
)
ENGINE = MergeTree
ORDER BY (user_id, order_date)
PRIMARY KEY(user_id);

   

This is a functionally equivalent form, but the PRIMARY KEY clause is declared after the column definitions and engine configuration. Both styles are valid, you can use whichever suits your code style or team conventions.

 

In ClickHouse, primary keys are used for indexing, not enforcing uniqueness. That means you can insert duplicate values for the primary key columns without any error.

 

INSERT INTO demo_db.orders_by_user VALUES 
(1, 101, '2025-05-10', 150.00),
(2, 101, '2025-05-10', 200.50),
(3, 101, '2025-05-11', 180.75);

krishna :) INSERT INTO demo_db.orders_by_user VALUES 
(1, 101, '2025-05-10', 150.00),
(2, 101, '2025-05-10', 200.50),
(3, 101, '2025-05-11', 180.75);

INSERT INTO demo_db.orders_by_user FORMAT Values

Query id: 6db2f88f-7384-4c75-966c-ad2c6fb1ca7f

Ok.

3 rows in set. Elapsed: 0.025 sec. 

krishna :) ;

Empty query

krishna :) SELECT * FROM demo_db.orders_by_user;

SELECT *
FROM demo_db.orders_by_user

Query id: 34df6974-6019-4774-a1d7-97fe25219440

   ┌─order_id─┬─user_id─┬─order_date─┬─amount─┐
1. │        1 │     101 │ 2025-05-10 │    150 │
2. │        2 │     101 │ 2025-05-10 │  200.5 │
3. │        3 │     101 │ 2025-05-11 │ 180.75 │
   └──────────┴─────────┴────────────┴────────┘

3 rows in set. Elapsed: 0.005 sec. 

  

 

Previous                                                    Next                                                    Home