ClickHouse Fundamentals

This article covers enough ClickHouse to create a database, define a table, load some rows, and read them back. It also introduces the one idea that surprises people coming from other databases: in ClickHouse, ORDER BY is part of how a table is stored, not just how a result is sorted.

Mental model#

Client (HTTP :8123 or native :9000)   ↓Database   ↓Table  ─  engine decides how rows are stored   ↓Columns on disk, compressed, sorted by the table's ORDER BY

A ClickHouse server holds databases. Each database holds tables. Each table has an engine that decides how its data is physically stored and maintained. For analytical tables the engine is almost always MergeTree or one of its variants.

Column-oriented storage#

ClickHouse stores each column separately and compressed. A query reads only the columns it names, so selecting three columns from a hundred-column table reads roughly three columns' worth of data.

Two consequences follow from this:

• SELECT * on a wide table is expensive; name the columns you need.
• Adding a rarely used column to a table is cheap, because queries that ignore it never read it.

Creating a database and a table#

Create a database with CREATE DATABASE:

CREATE DATABASE IF NOT EXISTS analytics;

Create a table with CREATE TABLE, listing each column with its type, choosing an engine, and giving an ORDER BY:

CREATE TABLE analytics.orders(    order_id     UInt64,    customer_id  UInt64,    created_at   DateTime,    amount       Float64,    status       String)ENGINE = MergeTreeORDER BY (created_at, order_id);

IF NOT EXISTS makes the statement safe to run again:

CREATE TABLE IF NOT EXISTS analytics.orders(    order_id     UInt64,    customer_id  UInt64,    created_at   DateTime,    amount       Float64,    status       String)ENGINE = MergeTreeORDER BY (created_at, order_id);

Core data types#

ClickHouse types are explicit about size and signedness.

A column can have a DEFAULT expression that fills in a value when an insert omits it:

CREATE TABLE analytics.events(    event_id   UInt64,    user_id    UInt64,    happened_at DateTime DEFAULT now())ENGINE = MergeTreeORDER BY (happened_at, event_id);

Prefer non-nullable columns where the data allows it; Nullable adds storage and query overhead. Use the smallest integer type that will always fit the range of the data.

Inserting data#

Insert literal rows with INSERT INTO ... VALUES:

INSERT INTO analytics.orders (order_id, customer_id, created_at, amount, status)VALUES    (101, 1, '2026-01-04 09:12:00', 1200, 'completed'),    (102, 1, '2026-01-04 10:40:00', 850,  'completed'),    (103, 2, '2026-01-05 14:05:00', 640,  'pending');

Insert the result of a query with INSERT INTO ... SELECT. This is how one table is built from another and is the backbone of building marts:

INSERT INTO analytics.completed_ordersSELECT order_id, customer_id, created_at, amountFROM analytics.ordersWHERE status = 'completed';

ClickHouse is built for inserts that arrive in batches. Many single-row inserts create many tiny storage parts and force extra background work. Insert thousands of rows at a time where possible.

Reading data#

A basic read names its columns, its table, and an optional filter:

SELECT order_id, amountFROM analytics.ordersWHERE status = 'completed'LIMIT 10;

LIMIT caps the number of returned rows and is useful while exploring a table. The full set of query clauses is covered in SQL Querying and Aggregations.

Where ClickHouse fits#

ClickHouse is a strong choice when:

• queries scan and aggregate large tables
• data is written in batches and rarely changed afterward
• you need history and fast reporting over it

It is the wrong choice when:

• you need frequent single-row updates or deletes
• you need enforced foreign keys and transactions across tables
• the main access pattern is fetching individual records by id

For those needs, an OLTP database such as PostgreSQL is the right tool, and a pipeline loads its data into ClickHouse for analysis.

Common mistakes#

Treating ORDER BY in CREATE TABLE as cosmetic#

It defines physical storage order and the primary index. Getting it wrong makes later queries slow in ways that are hard to fix without rebuilding the table.

Using SELECT * on wide tables#

It reads every column. Name only the columns the query needs.

Many small inserts#

Each insert creates a storage part. Batch rows before inserting.

Reaching for Nullable by default#

Nullable has a cost. Use a sensible default value or a non-nullable type when the data permits it.

Oversized integer types#

UInt64 everywhere wastes space and compresses worse. Choose the smallest type that covers the real range.

Quick reference#

See also#