Introduction To Table Engines

What Are Table Engines?

When we create a table in ClickHouse we need to choose an engine which is responsible for storing and querying the data behind the scenes.

This decision is fairly unique to ClickHouse, as many databases don't expose this feature directly to users.

In ClickHouse, table engines work in different ways, and different table engines are suitable for different use cases, data patterns and access patterns. It is therefore important to know broadly how they work and how to choose the appropriate one.

Table Engine Families

There are approximately table engines in ClickHouse, but they can be broadly grouped into four categories:

• MergeTree Family - This is the table engine which is most commonly used. These engines work by accepting data inserts, and then performing some operation in the background to merge and optimise the data. Example engines in this family include the ReplacingMergeTree and the SummingMergeTree;
• Log Family - These are suitable for append only log data where we have frequent inserts and reads and require fast parralell access to the data;
• Integration - These engines provide an interface into other endpoints or databases rather than actually storing the data within ClickHouse. Example engines in this family include JDBC, AWS S3 and Kafka;
• Special - These are a set of miscellaneous engines for ad-hoc requirements. Examples engines in this family include a Memory backed table and materialised views.

Specifying Your Table Engine

Table engines are specified at the table creation time with the ENGINE clause. Each table type will have a set of required and optional properties.

create table orders as