Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-pro· GoogleHugging Face LMArena Human Preference 140K

What does ORDER BY tuple() do in clickhouse exactly?

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-sonnet-4-20250514’s answer.
  1. User

    What does ORDER BY tuple() do in clickhouse exactly?

  2. Gemini Assistant

    Excellent question. The ORDER BY tuple() syntax in ClickHouse is a specific and important construct used when creating tables with the MergeTree family of engines.

    In short, ORDER BY tuple() explicitly tells ClickHouse that the table should have no primary key and the data should not be sorted by any specific columns.

    To understand this fully, you need to understand the role of ORDER BY in a CREATE TABLE statement.

    The Role of ORDER BY in MergeTree Tables

    In most SQL databases, ORDER BY is a clause used in SELECT statements to sort the result set. While ClickHouse supports this, the ORDER BY clause in a CREATE TABLE statement for a MergeTree engine has a completely different and more fundamental meaning:

    1. It Defines the Primary Key: The columns listed in ORDER BY form the table's sorting key, which acts as its primary key.
    2. It Dictates Physical Storage: ClickHouse physically stores the data on disk sorted by this key within each data part.
    3. It Creates a Sparse Primary Index: ClickHouse builds a sparse index based on this primary key. When you query the table with a WHERE clause that filters on the primary key columns, ClickHouse can use this index to quickly skip over large blocks (granules) of data that don't match the filter, dramatically speeding up queries.

    Example of a table with a standard primary key:

    CREATE TABLE hits (
        CounterID UInt32,
        EventDate Date,
        UserID UInt64,
        ...
    ) ENGINE = MergeTree()
    PARTITION BY toYYYYMM(EventDate)
    ORDER BY (CounterID, EventDate, UserID); -- This is the primary key
    

    In this table, queries filtering by CounterID and EventDate will be extremely fast.


    What ORDER BY tuple() Does Exactly

    When you define a table with ORDER BY tuple(), you are making a deliberate choice with specific consequences:

    1. No Primary Key: The table has no primary key.
    2. No Sorting: Data is not sorted on disk. It is stored in the order it was inserted (within each data part).
    3. No Sparse Index: Since there is no primary key, no sparse index is created.

    Example of a table with ORDER BY tuple():

    CREATE TABLE logs_staging (
        timestamp DateTime,
        level String,
        message String
    ) ENGINE = MergeTree()
    PARTITION BY toDate(timestamp)
    ORDER BY tuple(); -- No primary key, no sorting
    

    Why and When Would You Use ORDER BY tuple()?

    This might seem like you're giving up the main performance benefit of MergeTree, so why do it?

    1. Faster Inserts: The biggest reason. Sorting data during ingestion requires CPU and memory. By specifying ORDER BY tuple(), you skip the sorting step entirely, which can make data insertion significantly faster.

    2. Staging Tables: This is the most common use case. You have a stream of raw data (e.g., logs, events) that you need to ingest as quickly as possible. You can dump it into a staging table with ORDER BY tuple(). Later, a background process can read from this staging table, clean/transform the data, and insert it into a properly sorted and optimized "production" table.

    3. Data Without a Natural Key: If your data has no logical columns that you would ever filter on for range scans, and every query is expected to be a full scan, then defining a primary key provides no benefit and only adds overhead to inserts.

    Consequences and Downsides of ORDER BY tuple()

    Using ORDER BY tuple() is a trade-off. You gain insert speed at the cost of query performance.

    1. Terrible Query Performance: Any SELECT query with a WHERE clause will result in a full table scan (within the relevant partitions). ClickHouse cannot skip any data blocks because there is no index to guide it. This can be extremely slow on large tables.

    2. No Data De-duplication: Engines like ReplacingMergeTree and CollapsingMergeTree rely on the ORDER BY key to identify and collapse/replace duplicate rows during merges. With ORDER BY tuple(), these engines lose their special de-duplication capabilities and behave like a regular MergeTree.

    3. Potentially Worse Compression: Sorted data often has repeating values close to each other, which can lead to better compression ratios. Unsorted data may not compress as well.

    Summary

    FeatureORDER BY (col1, col2)ORDER BY tuple()
    Primary KeyYes, defined by (col1, col2)No
    Data SortingData is physically sorted by col1, then col2No, stored in insertion order
    Sparse IndexYes, created on the primary keyNo
    Insert SpeedSlower (due to sorting overhead)Faster
    Query SpeedVery fast for queries filtering on key columnsSlow (full scan) for all queries
    Best Use CaseFinal, optimized analytical tablesFast ingestion, temporary staging tables

    Conclusion: ORDER BY tuple() is a powerful tool for specific scenarios, primarily for high-throughput ingestion into staging tables. It is a conscious decision to sacrifice query performance for insert performance, and should generally be avoided for final, long-term analytical tables where query speed is critical.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.