This document gives an overview of the kitchen side of ClickHouse: how various operations work, what tricky migrations we have experience with as well as various settings and tips.
ClickHouse exposes a lot of information about its internals in
Some stand-out tables:
system.processescontain information on queries executed on the server
system.columnscontain metadata about tables and columns
system.mutationscontain information about ongoing operations
system.replication_queuecontain information about data replication
system.crash_logcontain information about errors and crashes respectively
system.distributed_ddl_queueshows information to help diagnose progress of
For examples of usage and tips, check out this ClickHouse blog article
ClickHouse provides daunting amounts of configuration on all levels. This section provides information on the different kind of settings and how to configure them.
Query settings allow to manipulate the behavior of queries, for example setting limits on query execution time and resource usage or toggling specific behaviors on-and-off.
Using query settings is done:
- at query-time via ClickHouse client library arguments (preferred)
- at query-time via explicit
SETTINGSclause in queries
users.xmlfile to apply to all queries
Server settings allow tuning things like global thread or pool sizes, networking and other
You can change server settings via
config.xml file. Note: some settings may require a server restart.
MergeTree table settings
MergeTree settings allow configuring things from primary index granularity to merge behavior to limits of usage of this table.
- MergeTree tables settings
- Undocumented settings can be found in the source code
MergeTree table settings are set either:
- at table creation time
- or via
ALTER TABLE ... SETTINGstatement
Profiles and users
ClickHouse allows creating different profiles and users with their own set of settings. This can be useful to grant read-only access to some users or otherwise limit resource use.
Read more in documentation:
ALTER TABLE ... UPDATE and
ALTER TABLE ... DELETE operations which mutate data require ClickHouse to
rewrite whole data via special merge operations. These are frequently expensive operations and require monitoring.
You can monitor progress of mutations via the following system tables:
When creating mutations, it's often wise to alter the value of
Running mutations can be stopped by issuing a
KILL MUTATION WHERE mutation_id = '...' statement.
Note that this may not stop any currently running merges. To do so, check out section on
SYSTEM STOP MERGES
When necessary to delete user data due to GDPR or otherwise, it's wise to do so in batches and asynchronously.
At PostHog, when deleting user data, we schedule for all deletions to occur once per week to minimize the cost of rewriting data.
In the future, lightweight deletes might simplify this process.
As explained previously, merges are the lifeblood of ClickHouse, responsible for optimizing how data is laid out on disk as well as for deduplicating data.
Merges can be monitored via the following tables:
OPTIMIZE TABLE] statement schedules merges for a table, optimizing the on-disk layout or speeding up queries or
forcing some schema changes into effect.
Note: not all parts are guaranteed to be merged if the size of parts exceeds maximum limits or if data is already in a single part.
In this case adding a
FINAL modifier forces the merge regardless.
SYSTEM STOP MERGES
SYSTEM STOP MERGES statement can stop background merges
from occurring temporarily for a table or the whole database. This can be useful during trickier schema migrations when copying data.
Note unless ingestion is paused during this time, this can easily lead to
too many parts errors.
Merges can be resumed via
SYSTEM START MERGES statement.
Merges have many relevant settings associated to be cognizant about:
parts_to_throw_insertcontrols when ClickHouse starts when parts count gets high.
- max_bytes_to_merge_at_max_space_in_pool controls maximum part size
background_pool_size(and related) server settings control how many merges are executed in parallel
max_replicated_merges_in_queuesettings control how many merges are processed at once
Simple schema changes
As in any other database, schema changes are done via
ALTER TABLE statements.
One area where ClickHouse differs from other databases is that schema changes are generally lazy and apply to only new data or merged parts. This applies to:
- Adding or removing columns, changing default values
- Changing compression of columns
- Updating table settings
You can generally force these changes onto old data by forcing data to be merged via
OPTIMIZE TABLE FINAL
statement, but this can be expensive.
ClickHouse TTLs allow dropping old rows or columns after expiry.
It's suggested to set up your table to partition by timestamp as well, so old files can be dropped completely instead of needing to be rewritten as a result of TTL.
Tricky schema changes
Some schema changes are deceptively hard and frequently requires rewriting the whole table or re-creating the tables.
Make sure to never re-use Zookeeper paths when re-creating replicated table!
The difference often comes down to how data is stored on disk and its implications.
At PostHog, we've developed Async Migrations for executing these long-running operations in the background without affecting availability.
You can learn more about Async Migrations in our blog, handbook, and runbook.
This is frequently a prerequisite of any large-scale schema change as new data may get lost when you are copying data from one place to another.
If you're using Kafka engine tables for ingestion, you can pause ingestion by dropping materialized view(s) attached to Kafka engine tables.
To restart ingestion, recreate the dropped table(s).
Note that you can also detach the materialized views instead of dropping them (
DETACH TABLE my_mv), but be aware that detached views have some weird behaviors, such as being re-attached on node restarts, "existing in a limbo" (they do not show up on
system.tablesand cannot be dropped but
SHOW CREATE TABLE my_mvwill return results), as well as potentially causing naming clashes.
Changing table engines
When changing table engines, you can leverage
commands to move data between tables.
Note: ALTER PARTITION commands only work if the two tables have identical structure: same columns and ORDER BY/PARTITION BY. It works by creating hard links between partitions, so the operation does not require any extra disk space until merges happen.
Thus it's important to stop ingesting new data and merges during this operation.
PostHog needed to implement this kind of operation to move to a sharded schema:
ORDER BY or
ORDER BY and
PARTITION BY affects how data is stored on disk and requires rewriting this data.
In the case of
ORDER BY, you can modify it with
ALTER TABLE my_table MODIFY ORDER BY, but only to add a new column expression. Other changes require using the approaches below.
Suggested procedure if using
- Create a new table with correct
- Create a new materialized view table, writing new data to new table.
- Copy data over from old table via
INSERT INTO SELECT
- Deduplicate via
OPTIMIZE TABLE FINALif feasible.
Note that INSERT-ing data this way may be slow or time out. Consider:
- Dropping any materialized columns temporarily
- Increasing query settings
receive_timeouttimeouts to be large enough
- Finding correct values for query settings
Note that this operation temporarily doubles the amount of disk space you need.
An example (from PostHog) of an async migration: 0005_person_replacing_by_version.py
At PostHog, we've haven't had to reshard data (yet), but the process would look similar to changing
ORDER BY or
PARTITION BY, requiring either to pause data or deduplicate at the end.
Storing/restoring parts of data from backups might also simplify this process.
Denormalizing columns via dictionaries
A powerful tool in the arsenal of performance is de-normalization of data.
At PostHog, we eliminated some JOINs for person data by storing information on person identities and properties directly on events.
Backfilling this data was implemented via
ALTER TABLE UPDATE populating new columns. The column data was pulled in
which allowed to query and store data from other tables in memory during the update.
An alternative approach might have been to create a new table and populate it similar to changing
ORDER BY, but this
would have required expensive deduplication, a lot of extra space and even more memory usage.
Learn more on this:
- Altinity knowledge base: Column backfilling with alter/update using a dictionary
More information for ClickHouse operations can be found in:
Next in the ClickHouse manual: Schema case studies