Operations

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.

System tables

ClickHouse exposes a lot of information about its internals in system tables.

Some stand-out tables:

• system.query_log and system.processes contain information on queries executed on the server
• system.tables and system.columns contain metadata about tables and columns
• system.merges and system.mutations contain information about ongoing operations
• system.replicated_fetches and system.replication_queue contain information about data replication
• system.errors and system.crash_log contain information about errors and crashes respectively
• system.distributed_ddl_queue shows information to help diagnose progress of ON CLUSTER commands

For examples of usage and tips, check out this ClickHouse blog article

Settings

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

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.

Documentation:

• Settings
• Restrictions on Query Complexity

Using query settings is done:

• at query-time via ClickHouse client library arguments (preferred)
• at query-time via explicit SETTINGS clause in queries
• via users.xml file to apply to all queries

Server settings

Server settings allow tuning things like global thread or pool sizes, networking and other clickhouse-server-level configuration.

Documentation:

• Server Settings

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.

Documentation:

• 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 ... SETTING statement

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:

• Settings Profiles
• Settings

Mutations

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:

• system.mutations
• system.merges - see is_mutation column
• system.replication_queue

When creating mutations, it's often wise to alter the value of mutations_sync setting.

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

GDPR

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.

Merges

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:

• system.merges
• system.replication_queue

OPTIMIZE TABLE

[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.

Important settings

Merges have many relevant settings associated to be cognizant about:

• parts_to_throw_insert controls 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
• Undocumented max_replicated_mutations_in_queue and max_replicated_merges_in_queue settings 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.

TTLs

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.

Async migrations

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.

Pausing ingestion

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.tables and cannot be dropped but SHOW CREATE TABLE my_mv will return results), as well as potentially causing naming clashes.

Changing table engines

When changing table engines, you can leverage ALTER PARTITION 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: 0004_replicated_schema.py.

Changing ORDER BY or PARTITION BY

Changing 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 ReplacingMergeTree:

1. Create a new table with correct ORDER BY
2. Create a new materialized view table, writing new data to new table.
3. Copy data over from old table via INSERT INTO SELECT
4. Deduplicate via OPTIMIZE TABLE FINAL if feasible.

Note that INSERT-ing data this way may be slow or time out. Consider:

• Dropping any materialized columns temporarily
• Increasing query settings max_execution_time, send_timeout, receive_timeout timeouts to be large enough
• Finding correct values for query settings max_block_size, max_insert_block_size, max_threads, max_insert_threads
• Setting optimize_on_insert setting to 0

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

Resharding

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 using dictionaries 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:

• 0007_persons_and_groups_on_events_backfill.py
• Altinity knowledge base: Column backfilling with alter/update using a dictionary

Learn more

More information for ClickHouse operations can be found in:

• Altinity knowledgebase
• Tinybird knowledgebase

Next in the ClickHouse manual: Schema case studies