Altinity Quickstart for ClickHouse®: Creating Your First Application

Recorded: Thursday, September 15, 2022
Presenters: Robert Hodges & Altinity Support Engineering

In this popular introductory tutorial, Altinity CEO Robert Hodges delivers a rapid but thorough on-ramp for application developers new to ClickHouse, covering everything from installation to performance tuning in about an hour. He opens by explaining what ClickHouse is—an open-source SQL data warehouse built for analytic queries on very large datasets—and what it isn’t: not a transactional database like MySQL, a managed platform like Snowflake, or a cache like Redis. Using a 109-column airline dataset, he shows why columnar storage matters: reading just three columns instead of all 109 cuts I/O by 97%, and compression reduces a query that would scan 59 GB in a row-based database to only 21 MB.

The middle sections turn practical. Robert distinguishes community builds (monthly, latest features) from Altinity Stable Builds (LTS, three-year support) and covers connecting via clickhouse-client, the web UI, and DBeaver against a live Altinity.Cloud endpoint. He then walks through creating a MergeTree table with PARTITION BY and ORDER BY, stresses inserting data in large blocks to avoid “too many parts” errors, demonstrates four loading methods (SQL VALUES, piped CSV, HTTP POST, and the parallel s3() function), and explains asynchronous mutations via ALTER TABLE plus the lightweight DELETE added in 22.8.

The query sections highlight ClickHouse’s familiar GROUP BY alongside its specialized aggregates—any() as a join replacement, countIf()/avgIf() combinators for multiple entity-specific aggregates in one scan, and argMax() for finding the row tied to a maximum value without a self-join—then explain the hash join model, why the large table goes on the left, and the 10 GB right-side memory limit. He closes with four performance levers: adding threads via max_threads, filtering on indexed columns, fixing table design through PARTITION BY and ORDER BY, and applying codecs (LowCardinality, ZSTD), illustrated by shrinking an uncompressed gigabyte to 202 MB on disk.

If you’d like to receive the PowerPoint presentation, please contact us at marketing@altinity.com.

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:08 – Introduction and housekeeping
• 1:22 – Speaker introduction: Robert Hodges, Altinity CEO
• 1:47 – Altinity overview: enterprise ClickHouse provider, Altinity.Cloud, Kubernetes Operator
• 2:24 – What is ClickHouse? SQL data warehouse, columnar, parallel, open source
• 6:04 – What ClickHouse is NOT: not MySQL, not Snowflake, not Redis
• 7:43 – Why columnar storage matters: rows vs columns, files per column
• 8:31 – Concrete example: 109-column airline dataset, 97% data reduction, 21 MB vs 59 GB
• 10:11 – Installation: community builds vs Altinity Stable Builds
• 11:47 – Installing via apt/RPM packages (~60 seconds)
• 12:01 – Running in Docker: community and Altinity container images
• 12:44 – Running in the cloud: Altinity.Cloud and other hosted options
• 13:55 – Connecting: clickhouse-client syntax, similar to MySQL client
• 14:39 – Built-in web UI demo: connecting to Altinity.Cloud public endpoint
• 15:59 – Altinity.Cloud public playground: demo/demo credentials
• 16:29 – Navigation: databases, tables, show commands, count without star
• 18:31 – Special databases: default, system tables
• 19:40 – Creating your first table: MergeTree engine, PARTITION BY, ORDER BY
• 21:33 – Inserting with SQL VALUES: works but not recommended for large data
• 22:05 – Input formats: ~50 supported formats (CSV, JSON, Parquet, Protobuf, ORC, etc.)
• 22:58 – Loading from CSV via pipe to clickhouse-client
• 23:40 – Loading via HTTP POST with curl
• 24:50 – Loading from S3 with parallel threads (up to 32)
• 26:03 – UPDATE and DELETE: ALTER TABLE mutations, asynchronous
• 27:32 – Lightweight DELETE command (experimental in 22.8)
• 27:53 – Monitoring mutations with system.mutations
• 28:54 – MergeTree internals: parts, partitions, sparse index, background merge
• 30:47 – Table design tips: partition key, insert block size, avoiding “too many parts”
• 32:12 – SELECT syntax: GROUP BY, WHERE, aggregates — mostly familiar SQL
• 34:11 – Time dimensions and date/time data types: Date, DateTime, DateTime64
• 35:11 – Time functions: toStartOfWeek, toYear, toStartOfMonth, etc.
• 35:42 – ClickHouse aggregate functions: standard + specialized
• 36:59 – any() aggregate: correlated column lookup without a join
• 38:32 – countIf / avgIf combinators: multiple entity aggregates in one scan
• 40:27 – argMax: finding the message associated with a maximum value
• 41:33 – Traditional alternative: self-join with IN clause (expensive)
• 42:36 – JOIN syntax: works as expected, left/right/full outer, semi/anti join
• 43:39 – How ClickHouse processes JOINs: hash join, right side in RAM
• 44:29 – Critical join rule: large table must always be on the LEFT
• 45:10 – Performance tips overview: CPU, filter rows, fix table design, codecs
• 45:47 – max_threads setting: controlling CPU core usage
• 47:10 – Reducing rows read: filters using indexed columns
• 47:52 – MergeTree internals: primary.idx, granules, .mrk files, .bin compressed blocks
• 49:06 – Table design for performance: small data types, partition key, ORDER BY cardinality
• 49:54 – system.columns: checking compression ratios
• 50:04 – Codecs: LowCardinality (dictionary encoding), ZSTD compression
• 51:05 – Codec example: 1 GB uncompressed → 453 MB LZ4 → 337 MB with codec → 202 MB ZSTD
• 52:57 – Open-source ecosystem: Kafka, Pulsar, Airflow, RudderStack, Superset, Grafana, Tableau
• 54:44 – Documentation: ClickHouse docs, Altinity blog, Altinity Knowledge Base
• 55:57 – Q&A: hardware planning for 10M rows/sec ingest
• 59:06 – Q&A: ZooKeeper vs ClickHouse Keeper best practices
• 1:00:26 – Q&A: differentiating Altinity from ClickHouse, Inc.
• 1:00:56 – “A Day in the Life of a ClickHouse Query” talk recommendation

Webinar Transcript

[0:08] — Introduction and Housekeeping

Robert: Hello everyone and welcome to Altinity Quickstart for ClickHouse: creating your first application. This is our popular education class for people who would like to learn how to use ClickHouse. We’re going to give you all the information you need to get started.

You can send questions in the chat or the Q&A box. Either way I can answer them as we go along and there should be a little time at the end as well. This presentation is being recorded and we’ll send both recording and slides to everyone who signed up within a few hours.

[1:22] — Speaker Introduction

Robert: My name is Robert Hodges. I’m one of a number of database geeks at Altinity. Altogether we have centuries of experience in databases and the applications built on top of them. My day job is CEO of Altinity but I’ve been working with databases in one form or another since 1983. We hope you’re an application developer looking to learn about ClickHouse, and that’s what this presentation is designed to do — give you the tools to build applications.

Altinity is an enterprise service provider for ClickHouse. We offer the cloud platform Altinity.Cloud, which was the first cloud in the Amazon and Google markets to offer hosted managed ClickHouse. We’re also authors of, among other things, the Altinity Kubernetes Operator for ClickHouse.

[2:24] — What Is ClickHouse?

Robert: ClickHouse is a SQL data warehouse — a database designed to handle analytic queries on potentially very large data sets that require scanning enormous amounts of data. It understands SQL; the dialect is not all that different from MySQL. It is extremely portable, literally running on everything from an Android phone up to clusters with hundreds of nodes and many things in between. It uses a shared-nothing architecture, the traditional data warehouse architecture where a node has attached storage and nodes talk to each other over a network. ClickHouse is evolving toward a more modern separated compute and storage architecture like Snowflake, but it’s still a journey.

Most importantly for performance: ClickHouse stores data in columns. Instead of each row being stored in one place, each column is stored in one place. You have a table consisting of a bunch of big arrays, one for each column.

Like many other data warehouses, ClickHouse is extremely good at using compute to parallelize processing. It spawns many threads to run queries; those threads use what’s called vectorized execution, taking pieces of the arrays and processing them independently using SIMD (single-instruction-multiple-data) instructions, allowing operations like sums to be done much more efficiently than processing one number at a time.

It scales enormously. We have companies running 800 to 1,000 ClickHouse nodes. And it’s open source under Apache 2.0, which means you can take ClickHouse and use it for any purpose without restriction — embed it in software, build SaaS services, offer your own hosted ClickHouse.

[6:04] — What ClickHouse Is NOT

Robert: It’s important to know what ClickHouse isn’t so you use it correctly.

It’s not MySQL. MySQL is an OLTP database, excellent at concurrent transaction processing — particularly short transactions. ClickHouse doesn’t have full ACID transactions, updates can be slow because of the column organization, and you can lose consistency in edge cases.

It’s not Snowflake. Snowflake is extremely capable; if you know SQL you can basically use it. ClickHouse values speed over almost anything else, particularly over SQL standards. ClickHouse is backfilling SQL features — window functions appeared over the last couple of years — but some things are still not fully supported. Unlike Snowflake, ClickHouse runs anywhere: your own Kubernetes clusters, hardware appliances, the cloud.

It’s not Redis. Redis is great for caches with hundreds of thousands of concurrent users. ClickHouse is designed to read relatively large blocks of data for fewer users — typically under a thousand, versus Redis which can comfortably serve 100,000 users.

[7:43] — Why Columnar Storage Matters

Robert: ClickHouse stores data in columns, not rows. Every individual row of flight data is broken up into separate columns stored in separate files. All the column values for a given column are stored together, ordered so that the third carrier value correlates with the third flight date value. This explains most of the performance difference between ClickHouse and other database types.

[8:31] — The 97% Data Reduction Example

Robert: Here’s a concrete example. We have the airline on-time dataset with about 109 columns. If you run a query to find the average flight delay in MySQL or any row-based database, you read all 109 columns — 59 GB of data in this particular example.

In a column-oriented database you only need to read the relevant columns. For this query we only needed three columns, so we’ve already eliminated 97% of the data. Moreover, columns compress very well because they contain the same type of value and there are tricks to make the compression even better. In this case the actual data read from storage was only 21 MB. Then because ClickHouse parallelizes the query across eight hardware threads, each thread only had to read 2.6 MB. The result comes back in milliseconds from ClickHouse — cases where MySQL might take many minutes. It’s not uncommon to see query speeds a thousand times faster than MySQL or PostgreSQL.

[10:11] — Installation: Community Builds vs Altinity Stable Builds

Robert: If you want to run ClickHouse locally there are two main sources. Community builds are available from clickhouse.com: a new build every month, with long-term support (LTS) builds every six months that have one year of support. Altinity Stable Builds are focused only on LTS versions, with three years of support, and go through a lengthy certification process. Our most recent certification took about three months after the LTS hit the market to ensure all bugs were flushed out and it’s safe for production use.

Which to use? Community builds are great if you want to see the latest features. Altinity Stable Builds are preferred if you have a production application that you upgrade once a year and need to know it’s supported and stable.

Installing on Ubuntu:

sudo apt-get install -y apt-transport-https ca-certificates curl gnupg

curl -fsSL ‘https://packages.altinity.com/altinity.gpg’ | sudo gpg –dearmor -o /usr/share/keyrings/altinity.gpg

# Add the repo, then:

sudo apt-get install clickhouse-server clickhouse-client

sudo systemctl start clickhouse-server

This takes about 60 seconds with a decent network connection.

[12:01] — Running in Docker

Robert: ClickHouse is great on containers. There are two sources:

# Community build

docker run -d –name clickhouse-server \

  -p 8123:8123 -p 9000:9000 \

  clickhouse/clickhouse-server:latest

# Altinity Stable Build

docker run -d –name clickhouse-server \

  -p 8123:8123 -p 9000:9000 \

  altinity/clickhouse-server:22.8.13.21.altinitystable

The community build takes a few minutes to download — it’s a fairly large container — but you can have this running on a laptop and be ready to go.

[12:44] — Running in the Cloud

Robert: ClickHouse is open source so multiple vendors offer hosted versions. Altinity.Cloud was first to market in Amazon and GCP and also runs in user Kubernetes clusters with full management and tightly integrated support. Other vendors also offer it. Because ClickHouse is open source, you always have choices and you’re never in a corner.

[13:55] — Connecting to ClickHouse

Robert: Connecting is really simple. If you’ve used MySQL or psql it’s basically the same:

# Connect to localhost with defaults

clickhouse-client

# Connect to a remote server

clickhouse-client –host my-server.example.com –port 9000 \

  –user myuser –password mypassword

# Specify a database

clickhouse-client –database mydb

There’s also a built-in web UI that’s super handy. Let me show you instead of just showing the slide.

I’m pointing at Altinity.Cloud. Let me run SELECT version() — and there it is. Very simple. I’m going to put the link to our public endpoint in the chat so you can follow along. If you go to that endpoint, the user is demo and the password is demo — a real live ClickHouse running on Altinity.Cloud. You can enter any command you want. I use this constantly.

There are also many client software options available. One of my favorites is DBeaver, which works great with ClickHouse.

[16:29] — Navigating ClickHouse

Robert: ClickHouse has a lot of similarities to MySQL, so if you’re a MySQL person you’ll feel at home. It has databases and tables. Databases are kind of like folders — they’re literally folders on the filesystem for locally stored data. They’re similar to PostgreSQL schemas. Every connection has a notion of a current database.

Basic navigation commands:

SHOW DATABASES;              — List databases

SHOW TABLES;                 — List tables in current database

DESCRIBE TABLE flights;      — Show table structure

SELECT count() FROM flights; — Count rows (note: no * required)

USE mydb;                    — Set current database

One thing to notice: ClickHouse doesn’t require count(*) — just count() works.

[18:31] — Special Databases

Robert: Two special databases you’ll see in virtually every ClickHouse:

• default: the standard database you end up in if you don’t specify one.
• system: contains incredibly helpful system tables. These are the best system tables of any database I’ve ever used. They give you useful information like compression levels, query logs, distribution of data across clusters. Very easy to use and very intuitive.

[19:40] — Creating Your First MergeTree Table

Robert: ClickHouse uses CREATE TABLE. It has native data types. You write:

CREATE TABLE IF NOT EXISTS sensor_data (

    device_id   UInt32,

    mdate       Date,

    reading     Float64,

    message     String

)

ENGINE = MergeTree()

PARTITION BY toYYYYMM(mdate)

ORDER BY (device_id, mdate);

Where ClickHouse starts to deviate from other databases is the ENGINE parameter — required on every table. MergeTree is the most important table type, the workhorse for large data. It has access methods that allow efficient scanning and efficient partitioning.

PARTITION BY breaks the table up into pieces. In this case we’re partitioning by month: toYYYYMM(mdate) turns the date into a six-digit number and rows go into the matching partition. ORDER BY tells ClickHouse how to sort the data within each part — this also creates a sparse primary key index that can be used to locate ranges of values.

[21:33] — Inserting Data

Robert: For basic inserts, standard SQL works:

INSERT INTO sensor_data VALUES

    (101, ‘2022-09-01’, 23.5, ‘reading’),

    (102, ‘2022-09-01’, 24.1, ‘reading’);

However, in ClickHouse most people don’t insert data this way. ClickHouse has optimizations for very large numbers of rows — potentially millions per second — and inserting a few rows at a time is inefficient because it creates a new part per insert.

[22:05] — Input Formats and Bulk Loading

Robert: ClickHouse has about 50 built-in input formats. VALUES is just one of them. Others include CSV, CSV with names, tab-separated values, JSONEachRow, Protobuf, Avro, Parquet, ORC, and many more. Whatever format your data is in, there’s a good chance ClickHouse can read it directly.

Loading from CSV via pipe:

cat sensor_data.csv | clickhouse-client \

  –query=”INSERT INTO sensor_data FORMAT CSVWithNames”

The first row contains column names. ClickHouse automatically maps names to columns and converts values. The file could have a million rows — ClickHouse handles it.

Loading via HTTP POST with curl:

curl -X POST “http://localhost:8123/?query=INSERT+INTO+sensor_data+FORMAT+CSVWithNames” \

  –data-binary @sensor_data.csv

The difference from the pipe method: with clickhouse-client, parsing happens on the client side before the data is sent. With curl and HTTP, parsing happens on the server.

Loading from S3 with parallel threads:

INSERT INTO sensor_data

SELECT * FROM s3(

    ‘https://s3.amazonaws.com/mybucket/sensor_data_*.csv’,

    ‘access_key’, ‘secret_key’,

    ‘CSVWithNames’

)

SETTINGS max_insert_threads = 32;

ClickHouse is smart: with a wildcard it spawns 32 threads and they all work on separate files in S3 in parallel. Load rates of 5 to 10 million rows per second from a single command are common. We use this constantly for data lakes.

[26:03] — UPDATE and DELETE

Robert: ClickHouse originally had no notion of UPDATE or DELETE — analytic databases are mostly insert-and-read. But from time to time you need to change data: security applications marking processed alerts, tenants being dropped and their data needing removal.

The traditional approach:

— Asynchronous update

ALTER TABLE sensor_data UPDATE reading = 0 WHERE device_id = 101;

— Asynchronous delete

ALTER TABLE sensor_data DELETE WHERE device_id = 999;

This is quite different from standard SQL. These run asynchronously in the background. ClickHouse creates a “mutation” — a list of changes — and rewrites the affected parts. Because it rewrites entire parts, it’s expensive. Avoid doing this frequently. A common pattern: collect all the tenants you need to drop and run the delete once on Saturday night, rewriting each affected part only once.

Monitoring mutations:

SELECT command, is_done, latest_fail_reason

FROM system.mutations

WHERE table = ‘sensor_data’;

Lightweight DELETE (experimental in 22.8):

DELETE FROM sensor_data WHERE device_id = 999;

This is the standard SQL syntax, now available as an experimental feature in version 22.8 and expected to stabilize. It’s faster than the ALTER TABLE approach in many cases.

[28:54] — MergeTree Internals: Parts, Merges, and the Primary Index

Robert: To understand why updates are slow, it helps to know what’s going on inside MergeTree. When you INSERT data, ClickHouse creates one or more parts — at least one per insert. All rows in a part belong to the same partition month. The columns are stored sorted by the ORDER BY key. There’s a sparse primary index in a file called primary.idx with an entry roughly every 8,000 rows pointing into the column data.

In the background, ClickHouse continuously merges smaller parts into larger ones. This is why the table type is called MergeTree — it merges data in the background. When you insert data it’s instantly queryable, but scanning is more efficient once the data is merged into larger parts. When you run ALTER TABLE UPDATE or ALTER TABLE DELETE, that also rewrites parts — which is why it’s expensive.

Table design tips:

• Pick a PARTITION BY that gives you large partitions. Keep fewer than 1,000 parts per table; partitioning by month or by day is standard.
• Insert in large blocks. If you insert one row at a time, every insert creates a part and ClickHouse will eventually throw a “too many parts” error. Group rows together — pushing 50,000 rows every second is fine, pushing one row at a time is not.
• Use the input formats described above to make bulk loading easy.

[32:12] — SELECT Syntax

Robert: ClickHouse SELECT syntax is essentially the same SQL you already know:

SELECT

    toStartOfWeek(FlightDate) AS week,

    UniqueCarrier,

    count()                  AS num_flights,

    sum(DepDelay)            AS total_delay

FROM flights

WHERE FlightDate BETWEEN ‘2019-01-01’ AND ‘2019-03-31’

GROUP BY week, UniqueCarrier

ORDER BY week, num_flights DESC

LIMIT 100;

If you’ve used another SQL database this will feel very familiar. Basically try out what you already know — there’s a really good chance it’ll just work.

[34:11] — Time Dimensions and Date/Time Types

Robert: ClickHouse has great DateTime support. There are three types:

• Date: day precision
• DateTime: Unix timestamp, one-second precision — recommended for use with Grafana and most BI tools
• DateTime64: adjustable precision up to nanoseconds (implemented by Altinity)

There’s a large library of time functions: toStartOfWeek(), toStartOfMonth(), toYear(), toYYYYMM(), and many more. Just about any date operation you want to do, ClickHouse already has a function for it.

[35:42] — Specialized Aggregates

Robert: This is where ClickHouse really varies from other databases. Standard aggregates (COUNT, SUM, AVG, MIN, MAX) all work. But there are many more. For details on how these work in practice, see ClickHouse aggregation fun on the Altinity blog.

any(): grab any value from within a group. This is extremely useful as a join replacement. If you’re grouping by Origin airport code, you know all the rows in that group have the same city name and state. Rather than doing a join, just use any() to pick one:

SELECT

    Origin,

    any(OriginCityName) AS city,

    any(OriginState)    AS state,

    count()             AS num_flights

FROM flights

GROUP BY Origin;

countIf() and avgIf() combinators: compute multiple entity-specific aggregates in a single table scan. This is critical for large tables.

Suppose your table has both reading and restart message types mixed together:

SELECT

    sensor_id,

    countIf(message_type = ‘reading’)              AS num_readings,

    countIf(message_type = ‘restart’)              AS num_restarts,

    avgIf(temperature, message_type = ‘reading’)   AS avg_temp

FROM sensor_data

GROUP BY sensor_id;

This scans the table exactly once. The If combinator can be applied to almost any aggregate function. Without this, you’d need multiple queries or a CASE inside each aggregate — inefficient and less readable.

argMax(): find a value correlated with a maximum. Without it, finding the error message associated with the most recent error requires a self-join:

— Traditional approach (expensive: scans the table twice)

SELECT message

FROM sensor_data

WHERE sensor_id = 236

  AND message_type = ‘error’

  AND mdate = (

    SELECT max(mdate)

    FROM sensor_data

    WHERE sensor_id = 236

      AND message_type = ‘error’

  );

With argMax() it’s one scan:

— ClickHouse approach (single scan)

SELECT

    max(mdate)                        AS last_error_time,

    argMax(message, mdate)            AS last_error_message

FROM sensor_data

WHERE sensor_id = 236

  AND message_type = ‘error’;

argMax(value, by) returns the value from the row where by is at its maximum. When the table has billions or trillions of rows, this difference between one scan and two can be enormous.

[42:36] — JOINs

Robert: ClickHouse JOINs work largely as expected. Standard syntax, left/right/full outer join, and even semi-join and anti-join are supported:

SELECT

    f.FlightDate,

    f.UniqueCarrier,

    a.AirportName   AS origin_name

FROM flights AS f

LEFT JOIN airports AS a ON f.Origin = a.Code

WHERE f.FlightDate = ‘2019-01-15’;

How ClickHouse processes JOINs internally: ClickHouse doesn’t have a query optimizer. It always uses a hash join: the right-side table is queried first and the results go into a hash table in RAM. Then ClickHouse scans the left-side table and for each row checks the hash table for a match.

The critical rule: the large table must always be on the LEFT. ClickHouse is not smart enough to flip them if you get it wrong, and it doesn’t always know the sizes in advance. If you put a billion-row table on the right side, the hash table in RAM will be enormous — potentially exceeding the default 10 GB memory limit — and the query will fail.

Merge joins are supported but very slow. For fast results, always use hash joins (which is the default).

[45:10] — Performance Tips

Robert: Four things you can do to boost ClickHouse performance.

[45:47] — 1. Add CPU: max_threads

Robert: ClickHouse by default allocates up to half your CPU cores to a query. If you have a 4-vCPU instance, it uses 2 threads by default:

— Increase threads for this query

SELECT … SETTINGS max_threads = 4;

Going from 2 to 4 threads can cut query time by 50%. At some point adding more threads stops helping (usually when you hit I/O bandwidth limits), but on large instances using 16 or 32 threads can dramatically speed up queries.

[47:10] — 2. Filter Rows: Use Indexed Columns

Robert: Reading fewer rows is a fundamental way to improve performance. In ClickHouse, filtering is linear — query response time directly correlates with the amount of data read from the left side. Filters that use the primary key index are particularly powerful because they allow ClickHouse to skip granules (groups of ~8,000 rows) without reading them at all.

[47:52] — 3. Fix Table Design: Understanding MergeTree Storage

Robert: The MergeTree table has a sparse index (primary.idx) with an entry roughly every 8,000 rows, pointing to granules — groups of rows. For each column there’s a .mrk marks file containing an array of byte offsets into a .bin file of compressed data blocks.

When ClickHouse executes a query, it uses the primary index to identify which granules might contain relevant data, then reads only those. You want to minimize the number of granules read. The way to do that:

• Use small, appropriate data types (don’t use Int64 if Int32 is sufficient)
• Choose a PARTITION BY that creates large, well-organized partitions
• For PRIMARY KEY (ORDER BY): put columns you filter on first, and sort with increasing cardinality (fewer distinct values first, more distinct values later)

[49:54] — 4. Fix Table Design: Codecs and Compression

Robert: ClickHouse uses LZ4 compression by default, which is usually good enough. But applying codecs before compression can reduce storage and I/O significantly. See how to test different compression codecs for a step-by-step approach.

Check current compression ratios with system.columns:

SELECT

    name,

    formatReadableSize(data_compressed_bytes)   AS compressed,

    formatReadableSize(data_uncompressed_bytes)  AS uncompressed,

    round(data_compressed_bytes / data_uncompressed_bytes, 2) AS ratio

FROM system.columns

WHERE table = ‘flights’

ORDER BY data_uncompressed_bytes DESC;

To improve compression, apply a codec:

— LowCardinality: dictionary encoding for low-cardinality strings

— ZSTD: smaller output than LZ4, slightly slower to decompress

ALTER TABLE flights MODIFY COLUMN TailNum LowCardinality(String) CODEC(ZSTD);

LowCardinality encodes each unique string as an integer (dictionary encoding), converting something like “N862AA” from 6 bytes to 2 bytes. Then ZSTD compresses further.

Concrete example from the airline dataset (tail number column):

The on-disk size matters for I/O. The in-memory working size (337 MB) matters for query speed. Both improved significantly. Typical target is 90% compression ratio, which is achievable by tuning codecs.

Standard codecs available: LZ4 (default, fast), ZSTD (smaller but slower), Delta (for monotonically increasing values like timestamps), DoubleDelta (for slowly-changing integers), Gorilla (for slowly-changing floats), LowCardinality (dictionary encoding for strings and low-cardinality values).

[52:57] — Open-Source Ecosystem

Robert: ClickHouse has a huge open-source ecosystem you can use with it:

Event streaming: Kafka, Pulsar (Apache open source), and RedPanda (business source license, extremely fast).

ETL/ELT: Apache Airflow and RudderStack are popular.

Visualization: Apache Superset, CubeJS, and Grafana are the most popular. Metabase also works great. Altinity has a Tableau connector. For Grafana there are two plugins — an older one we maintain (about 6 million downloads, widely used) and a newer one from ClickHouse, Inc. Both are maintained.

Client libraries: virtually every language is covered.

Kubernetes: ClickHouse runs extremely well on Kubernetes, and the Altinity Kubernetes Operator is open source and used in our own cloud.

[54:44] — Documentation and Resources

Robert: Where to find more information:

• ClickHouse official docs at clickhouse.com: the reference documentation
• Altinity blog: tackles topics like “how do you really make this work in applications”
• Altinity Knowledge Base: hundreds of articles from real production experience, all Apache-licensed. If you’re wondering “who ate my memory,” there’s literally a KB article that answers that.
• Altinity YouTube channel: this talk will be published there
• Telegram ClickHouse channel (English and Russian): extremely active
• ClickHouse public Slack workspace and Altinity public Slack workspace

For paid education, Altinity offers an eight-hour, four-session course with labs that goes deeply into ClickHouse. And Altinity offers support for ClickHouse wherever it runs — about 60% of our customers are self-managed or on-prem.

One additional talk I strongly recommend: search for “A Day in the Life of a ClickHouse Query.” Since ClickHouse doesn’t have a query optimizer and is rule-based, you need to know what ClickHouse is doing inside to make the right design choices. That talk explains ClickHouse internals in an accessible way.

[55:57] — Q&A: Hardware Planning for High-Throughput Ingest

Robert: Question: how do you choose hardware for a ClickHouse server when planning for 10 million rows per second?

With ClickHouse it’s less a question of planning hardware and more a question of using the hardware effectively. With an 8-vCPU instance and a table with 10 columns, I would be disappointed if I couldn’t get 2 million rows per second loaded. That’s a standard load rate for a not-too-wide table on Amazon.

To achieve those rates: pick your partition key so data flowing in right now mostly belongs to one partition (e.g., this month’s data always goes into September’s partition). Ensure good column data types. Make sure you’re inserting in large blocks. Balance that against your query patterns.

Something astounding about ClickHouse: it is quite tractable to have a trillion rows on a single 32-vCPU host with NVMe SSD. So start small, measure it, and ask us if you have questions.

[59:06] — Q&A: ZooKeeper vs ClickHouse Keeper

Robert: Question about ZooKeeper notes.

ClickHouse has a new replacement called ClickHouse Keeper — essentially a rewrite of ZooKeeper in C++ so you don’t need a separate Java process. Key best practices regardless of which you use:

• ZooKeeper/Keeper should run by itself on the node — not shared with ClickHouse or other applications.
• Don’t share one ZooKeeper ensemble across multiple ClickHouse clusters. ZooKeeper needs to be very responsive; sharing it is a recipe for problems.
• In production: always use three ZooKeeper/Keeper nodes on separate hosts.

[1:00:26] — Q&A: Differentiating Altinity from ClickHouse, Inc.

Robert: Question about how to differentiate Altinity from ClickHouse, Inc.

There are multiple vendors in this market. We’ve helped hundreds of customers in every environment, from Fortune 10 companies to small startups, all with very large amounts of data. What we are particularly focused on is helping people build and operate production-level real-time analytics. We offer a POC (proof of concept) where we work with you for a limited period to get your application up and running. Come try us out — use the Contact Us form.

Robert: Thank you very much everybody for attending. It’s been a pleasure introducing you to ClickHouse and I wish you the best of luck using it. Have a wonderful day.

FAQ

What is ClickHouse and how does it differ from MySQL and PostgreSQL?

ClickHouse is a SQL data warehouse designed for analytic queries on very large data sets. Unlike MySQL and PostgreSQL, which are row-based OLTP databases optimized for concurrent short transactions, ClickHouse stores data in columns. This allows it to read only the columns needed for a query (sometimes less than 3% of the data that a row-based database would read), compress columns extremely well, and parallelize query execution across CPU cores using vectorized SIMD processing. ClickHouse does not have full ACID transactions and updates are expensive; it is designed for bulk insert and read-heavy analytical workloads.

What is the MergeTree table engine and what do PARTITION BY and ORDER BY do?

MergeTree is ClickHouse’s primary table engine for large data. PARTITION BY tells ClickHouse how to split the table into separate pieces called partitions (typically by month or day using toYYYYMM(date_column)), which enables efficient data management including partition-level drops. ORDER BY defines how rows are sorted within each partition and implicitly creates a sparse primary key index — an index that has an entry roughly every 8,000 rows pointing into the sorted column data. This index allows ClickHouse to skip large portions of data when filtering on indexed columns.

What is the most efficient way to load data into ClickHouse?

Avoid inserting one row at a time — each insert creates a new part and eventually triggers “too many parts” errors. Instead, batch inserts: use FORMAT CSVWithNames piped to clickhouse-client for file-based loading, HTTP POST via curl for API-based loading, or the S3 table function with max_insert_threads = 32 for parallel loading from object storage. All methods use the same underlying INSERT statement; the only difference is whether parsing happens on the client or server side. ClickHouse supports about 50 input formats including CSV, JSON, Parquet, Protobuf, Avro, and ORC.

What are ClickHouse’s specialized aggregate functions and when should I use them?

ClickHouse extends standard SQL aggregates (COUNT, SUM, AVG, MIN, MAX) with several unique ones. any(column) picks any value from within a group, useful as a join replacement for correlated dimension columns. The -If combinator (e.g., countIf(condition), avgIf(value, condition)) computes condition-specific aggregates in a single scan — critical when a table contains multiple entity types and you need separate counts or averages per type. argMax(value, by) returns the value from the row where by is at its maximum, enabling “find the message associated with the most recent event” queries without a self-join.

Why must the large table always be on the left side of a ClickHouse JOIN?

ClickHouse uses a hash join algorithm by default: it runs the right-side table query first and loads the results into an in-memory hash table, then scans the left-side table looking up each row in the hash table. ClickHouse has no query optimizer to detect and correct wrong table placement. If you put a very large table on the right side, the hash table will be too large to fit in RAM (the default memory limit is 10 GB), and the query will fail with an out-of-memory error.

What are codecs and how do they improve ClickHouse performance?

Codecs are data transformations applied to column data before compression that can dramatically reduce both storage size and in-memory data size during queries. LowCardinality applies dictionary encoding to low-cardinality strings, replacing repeated values with small integers. Delta stores the differences between consecutive values (useful for timestamps). DoubleDelta stores differences-of-differences (useful for slowly-changing integers). Gorilla is optimized for slowly-changing floating-point values. These codecs are combined with a compression algorithm (LZ4 by default, ZSTD for smaller but slower output). A typical result: applying LowCardinality + ZSTD to a 1 GB string column can reduce it to 200 MB on disk and 337 MB in working memory, cutting I/O and query time significantly.

© 2022 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.