How to Install ClickHouse on a Cloud Server and Load Your First Data

In true TL;DR fashion, here is a short sequence of steps for installing ClickHouse, verifying access, creating a table, loading a public dataset, running several queries, taking basic measurements, and testing backup and restore.

1. Install ClickHouse Server and Client on Ubuntu, start the service, and enable it to start automatically:

   sudo systemctl enable --now clickhouse-server

Check the service status:

   sudo systemctl status clickhouse-server

Then test the connection:

   clickhouse-client

2. Make sure the server responds:

   SELECT version();

Next, create a separate database and a MergeTree table:

   CREATE DATABASE demo;
CREATE TABLE demo.trips
(
    pickup_datetime DateTime,
    passenger_count UInt8,
    trip_distance Float32,
    fare_amount Float32
)
ENGINE = MergeTree
ORDER BY pickup_datetime;

3. Prepare a public dataset, check its format and size, and then load the data using INSERT … FORMAT or an appropriate input format.

For example:

   clickhouse-client \
  --query="INSERT INTO demo.trips FORMAT CSV" \
  < trips.csv

4. After importing the data, check the row count:

   SELECT count()
FROM demo.trips;

Also inspect some actual values:

   SELECT *
FROM demo.trips
LIMIT 10;

5. Run several analytical queries and record the results along with their execution times.

For example:

   SELECT
    passenger_count,
    count() AS trips,
    avg(trip_distance) AS avg_distance
FROM demo.trips
GROUP BY passenger_count
ORDER BY trips DESC;

6. Before taking measurements, record the VPS configuration:

   nproc
free -h
lsblk
df -h

Also record:

• ClickHouse version;
• source dataset size;
• number of rows;
• disk type;
• RAM and CPU;
• import time;
• test query execution times.

7. Configure access so that ClickHouse is not unnecessarily exposed to the entire internet. If remote client access is required, use the firewall to allow only a trusted address and create a dedicated user with the necessary permissions.
8. Create a backup of the test database:

   BACKUP DATABASE demo
TO Disk('backups', 'demo-backup');

Then test the restore process using RESTORE on a separate table or after a controlled deletion of the test data.

9. If you encounter import issues, check the following first:
   • number of columns;
   • data types;
   • selected FORMAT;
   • delimiters;
   • dates;
   • NULL values;
   • encoding;
   • whether sufficient RAM and disk space are available.

If ClickHouse starts automatically, the user can connect with the required permissions, the dataset is imported in full, the test queries return the expected results, and the backup can actually be restored, the basic server setup can be considered ready for further use.

What to Expect in This Article

ClickHouse is rarely deployed just to run an empty SELECT 1. Things get interesting when you need to load a substantial volume of data onto the server, calculate aggregates quickly, and monitor CPU, RAM, and disk usage during imports and queries.

That is why we will not stop at installation. We will use a publicly available dataset, create a table for it, load the data, and immediately test several queries with real results. We will also document the VPS configuration and test conditions so that the figures can be interpreted properly rather than presented as a generic measure of “ClickHouse performance.”

We will then cover access, backups, and recovery. Finally, we will examine common import issues, including an incorrect format, schema mismatches, type errors, and insufficient resources.

Now that the plan is clear, we can start building the ClickHouse server step by step.

ClickHouse Server Architecture

Before installation, it is helpful to outline the overall test environment architecture. In our case, the setup will be as simple as possible: a single VPS running Ubuntu and clickhouse-server, with clickhouse-client used to connect to the database. After installation, we will create a separate database and a MergeTree table, load a public dataset, and use it to test data imports, queries, resource usage, and backups.

In other words, we will first set up a minimal standalone server, then gradually add data and perform operational checks.

What We Will Install and Verify

We will need two main components on the VPS:

   clickhouse-server
clickhouse-client

clickhouse-server handles data storage and query execution. clickhouse-client is a command-line client that provides a convenient way to connect to the server, execute SQL statements, and check the results.

Throughout this article, we will verify the following, step by step:

• Starting ClickHouse via systemd;
• Local connectivity;
• A dedicated user and permissions;
• Network access;
• Creating a database and table;
• Importing a public dataset;
• The number and structure of imported rows;
• The execution speed of several queries;
• Disk usage;
• Backup and restore;
• Behavior after a reboot;
• Common import errors.

This way, we will have not just an installed package, but a small test environment where we can verify the entire basic data lifecycle.

How ClickHouse Stores and Processes Data

ClickHouse is a column-oriented DBMS. Unlike a traditional row-oriented database such as MySQL, where values from the same row are physically stored together for efficient access, ClickHouse organizes data by individual columns.

In simplified terms, a conventional table can be represented as follows:

row 1 → time, city, distance, price

row 2 → time, city, distance, price

row 3 → time, city, distance, price

A column-oriented layout can be represented as follows:

This is particularly useful for analytical queries that read only a few required columns from millions of rows.

For example:

   SELECT
    passenger_count,
    avg(trip_distance)
FROM trips
GROUP BY passenger_count;

ClickHouse does not need to process all the other columns in the table simply because they belong to the same row.

We will use the MergeTree family for our table. It provides ClickHouse’s standard storage model, with data parts, data sorting, and efficient reads of large datasets.

Now that we understand the server itself, let’s look at how the client will connect to it.

How a Client Connects to ClickHouse

On the VPS itself, the easiest option is to use:

   clickhouse-client

It connects to ClickHouse Server and opens an interactive SQL session.

Basic setup:

For local testing, the application and ClickHouse can run on the same server.

In production, the architecture often looks different:

In this case, access must be restricted separately using listen settings, user permissions, and a firewall. We will address this after the basic installation.

But first, we need a real dataset; otherwise, there will be virtually nothing to test ClickHouse with.

Which Dataset We Will Load

For this exercise, we will use the publicly available NYC Yellow Taxi Trip Records.

This is a convenient option for an initial ClickHouse test: the dataset contains trip dates, distances, passenger counts, fares, and other fields, so it can be used to demonstrate basic queries, filtering, and several standard analytical aggregations.

To keep the instructions reproducible even on a small VPS, we will use a single fixed monthly file rather than the complete trip history.

The source dataset is available in Parquet format, so we can also demonstrate how ClickHouse works with a modern columnar file format.

Before importing the data, we will make sure to record the following:

• The specific file;
• Its size;
• The number of rows;
• The list of columns used;
• The data format.

This information will be needed not only for the import but also for accurate measurements.

What We Will Measure

For the test server, we are interested not in the abstract claim that ClickHouse is fast, but in specific metrics for a specific configuration.

We will record the CPU, RAM, disk, ClickHouse version, source dataset size, and number of rows.

We will then measure:

• Import time;
• Row ingestion rate;
• Simple SELECT query execution time;
• Aggregate query execution time;
• Repeated execution of the same query;
• Table size on disk.

The final setup can be summarized as follows:

The resulting architecture is modest, but sufficient for an initial introduction to ClickHouse. Now we need to choose a VPS where this setup will not run into resource limits as early as the import stage.

Choosing a ClickHouse Version and VPS Configuration

Resource Requirements for a Small ClickHouse Deployment

For a small test server, you can start with approximately:

2 vCPUs

4 GB RAM

40–60 GB SSD

This configuration is sufficient for getting started with ClickHouse, working with small datasets, and running basic queries.

A more comfortable configuration would be:

4 vCPUs

8 GB RAM

80+ GB SSD/NVMe

This provides more headroom for data imports, background merges, and analytical queries.

It is especially important not to size disk capacity based solely on the final table size. ClickHouse also uses disk space for data parts, merges, temporary files, and backups.

Why CPU, RAM, and Fast Storage Matter for ClickHouse

CPU resources are used for parsing, compression, decompression, filtering, aggregation, sorting, and function execution. Therefore, analytical query performance generally benefits from having more vCPUs available.

RAM is needed for intermediate data, including aggregation states, sorting, buffers, joins, and other operations.

However, the entire dataset does not need to fit in RAM. The primary data resides on disk, while RAM is used for query execution and caching.

Storage performance affects several operations:

• import → writing data
• SELECT → reading columns
• MergeTree → merging parts
• backup → reading and writing

Therefore, SSD or NVMe storage is preferable for ClickHouse.

How Data Volume Affects Configuration

The size of the source file and the size of the ClickHouse table are not the same.

After import, the data is:

• Converted to ClickHouse data types;
• Split into parts;
• Sorted according to ORDER BY;
• Compressed;
• Supplemented with system metadata.

Additional disk space is also required during operation.

As a rule of thumb, the disk should have enough capacity for: data + temporary files + merges + backups + free space

The same applies to RAM. Even if the dataset itself occupies tens of gigabytes, this does not mean that ClickHouse requires the same amount of RAM. However, resource-intensive GROUP BY, ORDER BY, and JOIN operations can consume significantly more memory than a simple SELECT count().

Therefore, the configuration should be chosen based not only on the dataset size but also on the types of queries.

Configuration Used

For this example, the server will have the following configuration:

Ubuntu 24.04 LTS

4 vCPU

8 GB RAM

80 GB SSD/NVMe

ClickHouse: Stable

You can check the ClickHouse version after installation:

   SELECT version();

For a small standalone server, this configuration is sufficient to load one month of the NYC Taxi dataset, run basic analytical queries, and test a backup.

The configurations can be compared as follows:

We can now define the conditions under which the import and queries will be run.

Defining the Benchmark Conditions

ClickHouse results are meaningful only when considered alongside the server specifications and dataset parameters.

For this test, we use the following configuration:

During the benchmark, we will separately record the import time, row ingestion rate, execution time for a simple SELECT query, execution time for an aggregate query, and the table size on disk.

These metrics will later allow us to assess how ClickHouse performs on the selected configuration.

Commands for Checking the VPS

Ubuntu version:

   lsb_release -a

Number of available CPUs:

   nproc

Detailed CPU information:

   lscpu

RAM:

   free -h

Disks:

   lsblk

Free disk space:

   df -h

Block device type:

   lsblk -o NAME,SIZE,TYPE,ROTA,MOUNTPOINTS

If the primary disk shows:

   ROTA=0

the system recognizes the device as non-rotational storage.

It is also useful to check the current system load:

   uptime

The server configuration and conditions for future measurements are now defined. The next step is to install ClickHouse Server and Client, start the service, and test the initial connection.

Installing ClickHouse on Ubuntu

Choosing an Installation Method

For our server, we will use the official ClickHouse APT repository.

The process will be as follows:

This approach is more convenient than manually installing individual binaries: the packages use standard system directories, include a service unit, and provide a straightforward update mechanism.

The next step is to determine exactly what will be added to the system during installation.

Which Packages Are Installed

We will need two main packages:

   clickhouse-server
clickhouse-client

clickhouse-server runs the DBMS itself and handles data storage, query execution, background merges, and other server-side operations.

clickhouse-client installs the command-line client used to connect to ClickHouse and execute SQL queries.

The package manager also installs the required shared ClickHouse components, so there is no need to build the server separately or download the standalone binary. 

After installation, the server runs as a separate systemd service.

How clickhouse-server works

The main process:

   clickhouse-server

runs in the background and accepts client connections.

You can check its status with:

   sudo systemctl status clickhouse-server

If the service is stopped:

   sudo systemctl start clickhouse-server

To enable automatic startup after a reboot:

   sudo systemctl enable clickhouse-server

Most subsequent administrative checks will focus on two levels:

• systemd → whether the process itself is running
• SQL → whether ClickHouse is responding and executing queries

Even if systemd reports the service as active, it is useful to run a separate SQL query to verify that the server is actually accepting connections.

You will need clickhouse-client for this.

What clickhouse-client is used for

clickhouse-client is a command-line SQL client for ClickHouse.

When running ClickHouse locally, simply run:

   clickhouse-client

Once connected, an interactive session opens where you can run:

   SELECT 1;
SHOW DATABASES;
SELECT version();

The client can also execute a single query without entering interactive mode:

   clickhouse-client --query "SELECT 1"

Later, we will also use it to create a database and a table, import data, and verify user permissions.

But first, it is useful to understand where ClickHouse stores its core files.

Configuration, Data, and Log Locations

With a package installation, the main paths are organized by purpose.

Main configuration directory:

   /etc/clickhouse-server/

Main configuration file:

   /etc/clickhouse-server/config.xml

Additional server settings are typically stored in separate files in:

   /etc/clickhouse-server/config.d/

Custom settings can be stored separately from the main configuration file, making them easier to maintain.

ClickHouse data is stored in:

   /var/lib/clickhouse/

The default log directory is:

   /var/log/clickhouse-server/

For example, it may contain:

   clickhouse-server.log
clickhouse-server.err.log

This separation will be useful later for troubleshooting: check systemd and the logs for startup errors, /etc/clickhouse-server for configuration, and /var/lib/clickhouse for the actual data.

You can now install the server.

Hands-on: Installing and Starting ClickHouse

First, install the packages required to use the HTTPS repository and signing key:

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

Add the repository key:

   curl -fsSL \
  https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key \
  | sudo gpg --dearmor \
  -o /usr/share/keyrings/clickhouse-keyring.gpg

Determine the system architecture:

   ARCH=$(dpkg --print-architecture)

Add the stable repository:

   echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" \
  | sudo tee /etc/apt/sources.list.d/clickhouse.list

Update the package metadata:

   sudo apt update

Install the server and client:

   sudo apt install -y clickhouse-server clickhouse-client

During installation, the package may prompt you to set a password for the default user. If you set a password at this stage, use a strong password and store it separately.

Start the server:

   sudo systemctl start clickhouse-server

Then enable it to start automatically:

   sudo systemctl enable clickhouse-server

Check the status:

   sudo systemctl status clickhouse-server

Shorter version:

   sudo systemctl is-active clickhouse-server

Expected output:

   active

Now connect to the server:

   clickhouse-client

If a password is set for the default user:

   clickhouse-client --password

After connecting successfully, run:

   SELECT 1;

Expected result:

   1

Key commands for this stage:

The server is installed and accepting connections.

Verifying Basic ClickHouse Operation

The fact that the systemd service has started does not necessarily mean that the entire SQL layer is working correctly. Therefore, immediately after installation, it is useful to perform a few quick checks: connect to the server, check the version, execute a query, and view the available databases.

How to connect using clickhouse-client

For a local connection:

   clickhouse-client

If a password is required:

   clickhouse-client --password

You can explicitly specify the user:

   clickhouse-client \
  --user default \
  --password

After a successful connection, the ClickHouse prompt will appear, allowing you to execute SQL queries.

If you only need to run a single query, you do not need an interactive session:

   clickhouse-client --query "SELECT 1"

This is useful for scripts, diagnostics, and automated checks.

Now let’s record the actual server version.

Checking the Server Version

You can check the version of the running ClickHouse server with the following SQL query:

   SELECT version();

For testing purposes, the server version is what matters, because the performance and behavior of specific functions may vary between releases.

If necessary, you can check the client version separately:

   clickhouse-client --version

The server and client are usually installed together, but for subsequent checks, we will primarily use the result of SELECT version().

Next, we will test a standard SQL query.

Running the First SELECT Query

The simplest check is:

   SELECT 1;

Expected result:

   1

You can also check several functions at once:

   SELECT
    now() AS current_time,
    hostname() AS server,
    version() AS clickhouse_version;

With a single query, you can verify that ClickHouse executes SQL, accesses the system time, and returns server information.

Next, let’s see which databases are available immediately after installation.

How to View Databases and Tables

List of databases:

   SHOW DATABASES;

The list will include the system databases required by ClickHouse itself.

Tables in the current database:

   SHOW TABLES;

You can also specify the database explicitly:

   SHOW TABLES FROM system;

For more detailed information, use the system tables:

   SELECT
    database,
    name,
    engine
FROM system.tables
ORDER BY database, name;

The system database is particularly useful for diagnostics. ClickHouse uses it to store information about tables, queries, parts, settings, metrics, and other internal objects.

Now all that remains is to check the server status using ClickHouse itself.

Checking Server Status

For a quick SQL check, run:

   clickhouse-client --query "SELECT 1"

If the command returns:

   1

the server is accepting native client connections and executing queries.

At the systemd level:

   sudo systemctl is-active clickhouse-server

To view the latest service messages:

   sudo journalctl \
  -u clickhouse-server \
  -n 50 \
  --no-pager

To check the running processes:

   ps aux | grep '[c]lickhouse-server'

This provides several levels of diagnostics:

ClickHouse is now not only installed but also verified in two ways: systemd confirms that the server process is running, while clickhouse-client successfully executes SQL queries. The next step is to configure networking and authentication: restrict access to the server and create a dedicated user instead of continuing to use the default account.

Configuring Secure Access

Why You Should Not Expose ClickHouse to the Internet Without Careful Consideration

ClickHouse typically handles large volumes of data and can execute resource-intensive queries. If the server is left externally accessible without restrictions, unauthorized access will not be the only risk.

An external client could:

• Attempt to guess credentials;
• Generate load by submitting queries;
• Scan accessible databases and tables;
• Take advantage of mistakenly granted permissions;
• Place unnecessary load on the CPU, RAM, and disk.

Access should therefore be implemented in layers:

Even a strong password does not eliminate the need to restrict network access.

The next step is to determine which ports ClickHouse uses.

Which Ports Does ClickHouse Use?

For normal operation, two main interfaces are most commonly used.

Native protocol:

   9000/TCP

This interface is typically used by:

   clickhouse-client

HTTP interface:

   8123/TCP

It is used by HTTP clients, some integrations, and applications.

ClickHouse can also use additional ports for HTTPS, inter-server communication, and other purposes, but for our standalone server, focusing on ports 9000 and 8123 is sufficient at this stage.

You can check the listening sockets with the following command:

   sudo ss -lntp | grep clickhouse

If the server listens only on the loopback interface, external clients will not be able to reach these ports.

This configuration is suitable if the application is running on the same VPS.

When localhost Is Sufficient

If ClickHouse and the application run on the same server, the simplest option is not to expose the database externally at all.

Setup:

In this case, the external firewall plays almost no role in protecting ClickHouse itself because the service does not accept connections on a public network interface.

This is suitable for:

• Small internal services;
• Test environments;
• Applications running on a single VPS;
• Scenarios where remote SQL access is not required.

However, if the application runs on a separate host, localhost is no longer sufficient.

When Remote Access Is Needed

Remote access is needed when ClickHouse is used by another VPS, a BI tool, an ETL pipeline, or an external application server.

Preferred configuration:

In this configuration, ClickHouse listens on a private IP address, while the firewall allows connections only from the required hosts.

If a private network is unavailable, access can be restricted to specific public source IP addresses.

For example:

Up to €20,000 in EU-Based IaaS Credits

Frankfurt infrastructure, predictable pricing, and expert migration support.

Get

All other addresses must not have access to the port.

Now, configure the firewall.

How to Restrict Connections Using a Firewall

If the VPS uses UFW, first check its status:

   sudo ufw status

For a local ClickHouse instance, you do not need to add any rules for ports 9000 or 8123.

If native access is required from only one trusted IP address:

   sudo ufw allow from 203.0.113.25 to any port 9000 proto tcp

For the HTTP interface:

   sudo ufw allow from 203.0.113.25 to any port 8123 proto tcp

Check the rules:

   sudo ufw status numbered

The output should contain only rules that allow access from specific IP addresses, rather than:

   9000 ALLOW Anywhere
8123 ALLOW Anywhere

However, a firewall is useful only when combined with the correct listen_host setting. Now, configure ClickHouse itself.

Hands-on: Allowing Access Only from a Trusted Address

It is more convenient to store additional settings in a separate XML file without modifying the main config.xml file.

Create the file:

   sudo nano /etc/clickhouse-server/config.d/listen.xml

For localhost:

   <clickhouse>
    <listen_host>127.0.0.1</listen_host>
</clickhouse>

If ClickHouse needs to accept connections over a private network, you can specify the server’s private IP address:

   <clickhouse>
    <listen_host>127.0.0.1</listen_host>
    <listen_host>10.0.0.10</listen_host>
</clickhouse>

After making the change, restart the service:

   sudo systemctl restart clickhouse-server

Check its status:

   sudo systemctl is-active clickhouse-server

Then check the listening ports:

   sudo ss -lntp | grep -E ':9000|:8123'

Now add a UFW rule for the trusted client only:

   sudo ufw allow from 203.0.113.25 to any port 9000 proto tcp

Then test the connection from the trusted host:

   nc -vz CLICKHOUSE_IP 9000

The connection should succeed.

A connection attempt from any other address should time out or be rejected.

For this guide, the access configuration is as follows:

Network access is now restricted.

Creating a Dedicated User

Using the default user for routine ClickHouse operations is inconvenient and insecure. For an application or data import, it is better to create a dedicated user and grant it only the permissions it actually needs.

This makes it easier to control access and update the credentials later without affecting other clients.

Why an Application Should Not Run with Excessive Privileges

If an application only needs to read and load data in a single database, it does not require administrative privileges for the entire ClickHouse instance.

Excessive privileges amplify the consequences of any application error.

For example, a compromised account with broad privileges could:

• Read other databases;
• Drop tables;
• Modify users;
• Perform administrative operations;
• Run resource-intensive queries outside the required scope.

It is therefore good practice to follow a simple principle: one application → one user → minimum required privileges

For our dataset, a dedicated user with access to the test database is sufficient.

How ClickHouse Users and Permissions Work

In ClickHouse, access control can be configured using SQL.

A user is created with the following command:

   CREATE USER ...

Permissions are granted using:

   GRANT ...

They can be revoked when necessary using:

   REVOKE ...

Permissions can be granted at the following levels:

• the entire server;
• a database;
• a specific table.

For example:

   demo.*

refers to objects in the demo database.

This avoids granting the application access to all databases at once.

Now let’s determine which permissions our user needs.

Permissions Required to Load and Read Data

For our scenario, the user must be able to:

• Connect;
• Read data;
• Insert rows;
• Work with objects in the test database.

Once the database and table have been created, the basic set of permissions for working with data can be limited to:

   SELECT
INSERT

If the same user will create tables while following this guide, they will also temporarily need DDL permissions.

For example:

   CREATE
DROP

However, roles should be separated in production:

deployment/admin user → CREATE / ALTER / DROP

application user → SELECT / INSERT

We will first create a user for the hands-on exercises; the user’s permissions can then be restricted.

Practice: Create a user and verify login

Connect locally using an administrative account:

   clickhouse-client

Create a user:

   CREATE USER app_user
IDENTIFIED WITH sha256_password BY 'StrongPassword123';

Create a database:

   CREATE DATABASE IF NOT EXISTS demo;

Grant permissions:

   GRANT SELECT, INSERT, CREATE TABLE, DROP TABLE
ON demo.*
TO app_user;

Verify the grants:

   SHOW GRANTS FOR app_user;

Now exit the current session and connect as the new user:

   clickhouse-client \
  --user app_user \
  --password

When prompted for the password, enter:

   StrongPassword123

Verify the current user:

   SELECT currentUser();

Expected result:

   app_user

Verify access to the database:

   SHOW TABLES FROM demo;

If the database is still empty, ClickHouse simply returns no tables.

You can also test an allowed SQL statement:

   SELECT 1;

The user is now ready for the next import step.

Testing Authentication Failure with an Incorrect Password

Security should be tested not only with a successful login, but also with credentials that are known to be incorrect.

Run:

   clickhouse-client \
  --user app_user \
  --password

Then enter an incorrect password.

ClickHouse should reject the authentication attempt.

Then reconnect using the correct password:

   clickhouse-client \
  --user app_user \
  --password

Then run:

   SELECT currentUser();

The result should be:

   app_user

It is also useful to verify that the user cannot access anything outside the required database.

For example, if the user’s privileges are limited to demo.*, an attempt to perform an administrative operation outside that scope should fail with a permissions error.

Network access is now restricted, and a dedicated user is available for further work. The next step is to understand databases, tables, and MergeTree so that the structure can be properly prepared for the NYC Taxi dataset.

Understanding Databases, Tables, and MergeTree

What Is a Database in ClickHouse?

A database in ClickHouse is a logical container for tables, views, and other objects.

For example:

Here, demo is the database, and trips is a table within it.

A database is created using a standard SQL command:

   CREATE DATABASE demo;

You can then create a table within it:

   CREATE TABLE demo.trips
(
    ...
)
ENGINE = MergeTree
ORDER BY ...

Separating objects into databases is useful when multiple applications or datasets are hosted on the same server. This makes it easier to keep data separate, grant permissions, and back up individual logical groups.

However, the database itself provides little information about the physical storage structure. A table’s key properties are determined by its engine.

Why We Choose MergeTree for a Regular Table

The MergeTree family is most commonly used for analytical data in ClickHouse.

It is designed to handle large numbers of rows, support fast batch inserts, and efficiently read sorted data.

In simplified terms, the process works as follows:

Each insert creates one or more parts on disk, which ClickHouse then gradually merges in the background.

This avoids rewriting the entire table whenever new data is loaded and is well suited to append-heavy analytical workloads.

In addition to the standard MergeTree engine, there are specialized variants such as ReplacingMergeTree, SummingMergeTree, and others. However, we do not need them for our first dataset—the standard MergeTree engine is sufficient.

The next important parameter for this engine is the sorting key.

Why ORDER BY Is Needed

In MergeTree-family tables, the ORDER BY expression determines the order in which data is organized within data parts.

For example:

   ENGINE = MergeTree
ORDER BY pickup_datetime

means that the data will be physically ordered by pickup_datetime.

This is particularly useful for queries such as:

   SELECT *
FROM demo.trips
WHERE pickup_datetime >= '2026-01-01 00:00:00'
    AND pickup_datetime < '2026-01-02 00:00:00';

ClickHouse can use the sort order to avoid unnecessarily scanning the entire dataset.

Therefore, ORDER BY should be chosen based on the most common query filters and ranges.

However, there is an important distinction: ORDER BY in a table definition is not the same as ORDER BY in a regular SELECT query.

How ORDER BY in ClickHouse Differs from Ordinary Result Sorting

In the following query:

   SELECT *
FROM demo.trips
ORDER BY fare_amount DESC
LIMIT 10;

ORDER BY sorts the result before returning it to the client.

In the table definition:

   ENGINE = MergeTree
ORDER BY pickup_datetime

ORDER BY defines how the data is stored.

In other words, these operate at two different levels:

• CREATE TABLE … ORDER BY → how data is organized on disk
• SELECT ... ORDER BY → how the query result is sorted

You can create a table with:

   ORDER BY pickup_datetime

and then run queries with:

   ORDER BY fare_amount DESC

There is no contradiction here.

After the engine and sorting key, one more important part of the schema remains: the column data types.

How Column Types Affect Storage and Import

ClickHouse strictly enforces data types.

For example:

1. DateTime
2. UInt8
3. UInt16
4. UInt32
5. Float32
6. Float64
7. String
8. Nullable(…)

Choosing the right type affects several aspects:

• Data size on disk;
• RAM usage during processing;
• Compressibility;
• Query performance;
• Whether the import succeeds.

For example, if a value is always between 0 and 255, there is no reason to store it as UInt64.

On the other hand, a type with too narrow a range can cause an import error if the dataset contains a value outside the permitted range.

NULL values require separate consideration.

If the source data may contain missing values, the column can be defined as:

   Nullable(UInt8)

or another representation for missing values can be used.

The main table elements can be summarized as follows:

You can now move on to the dataset itself and design the schema around its actual fields.

Preparing a Public Test Dataset

To test ClickHouse, it is best to use data that can demonstrate not only a basic import but also practical analytical queries. We will therefore use the public New York City taxi trip dataset.

It is sufficiently large, has a clear structure, and is well suited for aggregations by time, distance, passenger count, and trip cost.

Which dataset will we use?

We’ll use the NYC Yellow Taxi Trip Records for January 2024.

Source file: yellow_tripdata_2024-01.parquet

The dataset contains information about Yellow Taxi trips, including:

• Pickup time;
• Trip completion time;
• Number of passengers;
• Distance;
• Pickup and drop-off zones;
• Payment method;
• Fare;
• Tips;
• Total amount.

This is more than enough for the initial import.

Why It Is Suitable for Testing ClickHouse

The dataset has several useful properties.

First, it contains many rows, making it possible to see the real difference between simply viewing data and running analytical aggregations.

Second, it includes fields of various types:

1. timestamps
2. integers
3. floating-point values
4. categorical values

This makes it possible to test the schema and the handling of different column types.

Third, the data is well suited to typical analytical queries:

1. trips by hour
2. average distance
3. average fare
4. distribution by passenger_count
5. top pickup zones
6. total tips

ClickHouse is at its best with workloads like these rather than individual row-by-row operations.

The next step is to determine the import format.

Which Format We Will Import

The NYC Taxi dataset is available in Parquet format.

Parquet is a convenient option for ClickHouse because it is also column-oriented and stores the schema within the file.

This simplifies the initial structure validation and is well suited to large datasets.

Our workflow will be as follows:

Before the actual import, we will inspect the file’s metadata and map its columns to our schema.

This reduces the risk of encountering an error after the import has already started.

Checking the Size and Structure of the Source Data

After downloading the file, check its size:

   ls -lh yellow_tripdata_2024-01.parquet

The file size should be approximately:

   50M

For a more precise size:

   du -h yellow_tripdata_2024-01.parquet

You can inspect the Parquet schema using ClickHouse without importing the file first:

   DESCRIBE file('yellow_tripdata_2024-01.parquet', Parquet);

The result will show the column names and data types that ClickHouse inferred from the file metadata.

You can also preview several rows directly:

   SELECT *
FROM file('yellow_tripdata_2024-01.parquet', Parquet)
LIMIT 5;

Row count:

   SELECT count()
FROM file('yellow_tripdata_2024-01.parquet', Parquet);

The result is:

   2964624

This gives us the dataset size, schema, and row count before we create the main table.

We can now select the fields for the table used in this exercise.

Fields Required in the Table

For this article, there is no need to import every column from the source dataset. It is sufficient to select the fields required for data import and subsequent queries.

For example:

We use Nullable for passenger_count because the real-world dataset may contain missing values.

A logical choice for the sorting key is:

   tpep_pickup_datetime

because many subsequent queries will involve time ranges.

The resulting schema is already close to a real-world analytical workload: it includes timestamps, numeric metrics, and several categorical identifiers.

Creating a Database and Table

The dataset is ready, and its structure is now clear. Next, we will create a separate database and a MergeTree table into which we will load the selected NYC Yellow Taxi fields.

For this initial setup, we will not complicate the schema with partitions or specialized engines. Our goal is to create a straightforward table suitable for data import, time-based filtering, and initial analytical queries.

Create a Separate Database

For the test data, we will use the following database:

   demo

Create it:

   CREATE DATABASE IF NOT EXISTS demo;

Verify that it was created:

   SHOW DATABASES;

The following database should appear in the list:

   demo

You can now keep all NYC Taxi–related objects separate from the system databases.

The next step is to define the structure of the table itself.

Defining the Table Structure

We do not need every field from the source Parquet file—only those required for subsequent queries.

We will use the following schema:

   CREATE TABLE demo.trips
(
    pickup_datetime DateTime,
    dropoff_datetime DateTime,
    passenger_count Nullable(UInt8),
    trip_distance Float64,
    pickup_location_id UInt16,
    dropoff_location_id UInt16,
    payment_type Nullable(UInt8),
    fare_amount Float64,
    tip_amount Float64,
    total_amount Float64
)
ENGINE = MergeTree
ORDER BY (pickup_datetime, pickup_location_id);

We also convert source field names such as PULocationID to a more convenient naming convention:

• PULocationID → pickup_location_id
• DOLocationID → dropoff_location_id

We keep passenger_count and payment_type as Nullable because the source data may contain missing values.

We store monetary amounts and distance as Float64, and location IDs as UInt16 because their values fit within that range.

After defining the columns, all that remains is to choose a sorting key.

Choosing the ORDER BY Clause

For this table, we use:

   ORDER BY (pickup_datetime, pickup_location_id)

pickup_datetime comes first because many analytical queries on trip data are naturally limited to a specific time range:

   WHERE pickup_datetime >= '2024-01-01'
    AND pickup_datetime < '2024-01-02'

We add pickup_location_id second. This helps when a query filters data by both time and pickup zone.

In simplified terms, the storage order will be based on:

This sorting key is sufficient for our tutorial table. A more complex production workload might require a different order depending on the actual query patterns.

Now let’s create the complete table.

Creating a MergeTree Table

Connect as the user who was previously granted the CREATE TABLE privilege:

   clickhouse-client \
  --user app_user \
  --password

Create the table:

   CREATE TABLE demo.trips
(
    pickup_datetime DateTime,
    dropoff_datetime DateTime,
    passenger_count Nullable(UInt8),
    trip_distance Float64,
    pickup_location_id UInt16,
    dropoff_location_id UInt16,
    payment_type Nullable(UInt8),
    fare_amount Float64,
    tip_amount Float64,
    total_amount Float64
)
ENGINE = MergeTree
ORDER BY (pickup_datetime, pickup_location_id);

Verify that the table was created:

   SHOW TABLES FROM demo;

Expected output:

   trips

The table must be empty before the import.

Verify this:

   SELECT count()
FROM demo.trips;

The result should be:

   0

Now verify that ClickHouse created the expected schema.

Verify the Created Schema

The quickest way:

   DESCRIBE TABLE demo.trips;

The output should include all ten columns:

1. pickup_datetime
2. dropoff_datetime
3. passenger_count
4. trip_distance
5. pickup_location_id
6. dropoff_location_id
7. payment_type
8. fare_amount
9. tip_amount
10. total_amount

It is easier to check the engine and sorting key separately:

   SHOW CREATE TABLE demo.trips;

The definition should end with:

   ENGINE = MergeTree
ORDER BY (pickup_datetime, pickup_location_id)

Check the row count again:

   SELECT count()
FROM demo.trips;

For now:

   0

The table is ready. Now we can move on to the main part—loading all 2,964,624 trips from Parquet and checking how much time and storage space it takes.

Loading the First Data into ClickHouse

Now that the schema has been created, we can load the data from yellow_tripdata_2024-01.parquet into demo.trips.

Because the source file contains more columns, and some field names differ from those in our table, we will use INSERT … SELECT instead of a direct INSERT … FORMAT Parquet. This allows us to select only the required fields and immediately adapt them to our schema.

How INSERT Works in ClickHouse

ClickHouse uses standard SQL to load data:

   INSERT INTO table ...

If the data is already available from another ClickHouse data source, you can use:

   INSERT INTO target_table
SELECT ...
FROM source;

In our case, the source is a Parquet file:

This approach is also useful because transformations are performed during import.

Now let’s prepare the file for the server to read.

Importing a Public Dataset

The file() table function reads files from an allowed ClickHouse directory. For package installations, the following directory is typically used:

   /var/lib/clickhouse/user_files/

Copy the dataset there:

   sudo cp yellow_tripdata_2024-01.parquet \
  /var/lib/clickhouse/user_files/

Verify the file:

   sudo ls -lh /var/lib/clickhouse/user_files/yellow_tripdata_2024-01.parquet

You can now run the import:

   INSERT INTO demo.trips
SELECT
    tpep_pickup_datetime AS pickup_datetime,
    tpep_dropoff_datetime AS dropoff_datetime,
    CAST(passenger_count, 'Nullable(UInt8)') AS passenger_count,
    trip_distance,
    toUInt16(PULocationID) AS pickup_location_id,
    toUInt16(DOLocationID) AS dropoff_location_id,
    CAST(payment_type, 'Nullable(UInt8)') AS payment_type,
    fare_amount,
    tip_amount,
    total_amount
FROM file(
    'yellow_tripdata_2024-01.parquet',
    Parquet
);

ClickHouse will read the Parquet file, select the required columns, convert their types, and write the result to MergeTree.

Once complete, the client will return information about the executed query, including the import time and processing speed.

These metrics reflect the actual benchmark results for our VPS.

Next, we will verify that the import completed in full.

How to Monitor Import Progress

While the query is running, clickhouse-client displays its progress: the number of rows processed, the amount of data read, and the processing speed.

For additional verification, you can view the running queries from another session:

   SELECT
    query_id,
    elapsed,
    read_rows,
    read_bytes,
    memory_usage,
    query
FROM system.processes;

If the import is still running, its query will appear in system.processes.

Once the import is complete, the query will disappear from the list of active queries.

You can view the history of completed queries in system.query_log if query logging is enabled:

   SELECT
    query_duration_ms,
    read_rows,
    read_bytes,
    written_rows,
    written_bytes,
    memory_usage
FROM system.query_log
WHERE query LIKE 'INSERT INTO demo.trips%'
    AND type = 'QueryFinish'
ORDER BY event_time DESC
LIMIT 1;

These metrics will be useful later when we measure the import speed separately.

But first, we need to check the most important thing: the number of rows in the table.

Verifying the Number of Imported Rows

In the source file:

2,964,624 rows

After the import, run:

   SELECT count()
FROM demo.trips;

Expected result:

   2964624

This is an important checkpoint: the number of rows in demo.trips must match the number of records in the source Parquet file because we selected specific columns during the import but did not filter any rows.

You can also view a few records:

   SELECT *
FROM demo.trips
LIMIT 5;

And check the date range:

   SELECT
    min(pickup_datetime) AS min_time,
    max(pickup_datetime) AS max_time
FROM demo.trips;

This confirms not only that the table contains the correct number of rows, but also that it was populated with data from the NYC Taxi dataset.

Full Control Over Your Kubernetes Infrastructure

Deploy production-ready Kubernetes with full control over nodes, storage, and networking on predictable-cost EU infrastructure with direct access to your resources.

Get

The final step is to check how much space the data occupies after being converted to MergeTree.

Checking disk usage

You can retrieve the table size from the system.parts system table.

Run:

   SELECT
    formatReadableQuantity(sum(rows)) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size
FROM system.parts
WHERE database = 'demo'
    AND table = 'trips'
    AND active;

These sizes depend on the parts actually created, the ClickHouse version, and the compression settings, so it is best to obtain them directly from the test environment.

Number of active parts:

   SELECT count()
FROM system.parts
WHERE database = 'demo'
    AND table = 'trips'
    AND active;

You can also check the ClickHouse directory at the file-system level:

   sudo du -sh /var/lib/clickhouse/

However, system.parts is more convenient and accurate for determining the size of a specific table because ClickHouse reports the size data for the selected object directly.

The first complete dataset is now stored in ClickHouse: all 2,964,624 rows have been loaded into MergeTree, the row count has been verified, and the actual table size can be compared with the source Parquet file.

Validating the Imported Data

The import is complete, but matching row counts alone are not enough. Before running analytical queries, it is useful to inspect the data itself: review a few records, check the ranges of dates and numeric values, identify NULL values, and verify the table’s actual size.

This allows us to immediately confirm that the dataset was imported correctly and identify any characteristics of the source data that need to be taken into account later.

Displaying the First Rows

Let’s start with a basic query:

   SELECT *
FROM demo.trips
LIMIT 5;

The first few records will look something like this:

This view is useful for quickly checking that the columns are aligned correctly: timestamps look like timestamps, location IDs look like numeric identifiers, and monetary amounts and distances have not shifted into adjacent columns.

Next, it is worth examining the ranges of values rather than individual rows.

Checking Value Ranges

For timestamps, run:

   SELECT
    min(pickup_datetime) AS min_pickup,
    max(pickup_datetime) AS max_pickup,
    min(dropoff_datetime) AS min_dropoff,
    max(dropoff_datetime) AS max_dropoff
FROM demo.trips;

The January file has one minor peculiarity: although it is nominally a dataset for January 2024, it contains a handful of timestamps outside that month. The earliest pickup_datetime is 2002-12-31 22:59:39, and the latest is 2024-02-01 00:01:15. There are very few such rows, so for January-specific analysis, we will explicitly restrict the date range using WHERE.

Now let’s examine the ranges of the numeric values:

   SELECT
    min(trip_distance) AS min_distance,
    max(trip_distance) AS max_distance,
    min(fare_amount) AS min_fare,
    max(fare_amount) AS max_fare,
    min(total_amount) AS min_total,
    max(total_amount) AS max_total
FROM demo.trips;

The results may include negative amounts, zero distances, and other outliers. This is normal for the public NYC Taxi dataset: raw records should not be treated as a fully cleaned dataset. This is why analytical queries may sometimes require additional filtering.

Next, we’ll verify the row count.

Counting Rows

After the full import:

   SELECT count()
FROM demo.trips;

we get:

   2964624

This matches the number of records in the original yellow_tripdata_2024-01.parquet. DataKitchen

You can also check the number of trips in January 2024:

   SELECT count()
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00';

Because the source file contains 18 rows with pickup_datetime values outside January, the result is:

   2964606

This filter will be useful later for queries that require statistics specifically for the calendar month of January.

Now let’s assess how clean the dataset is.

Checking for NULLs and Invalid Values

First, let’s check the nullable columns:

   SELECT
    countIf(passenger_count IS NULL) AS passenger_count_null,
    countIf(payment_type IS NULL) AS payment_type_null
FROM demo.trips;

In addition to NULLs, it makes sense to check for obvious anomalies:

   SELECT
    countIf(passenger_count IS NULL OR passenger_count < 1) AS invalid_passenger_count,
    countIf(trip_distance <= 0 OR trip_distance > 1000) AS invalid_distance,
    countIf(fare_amount < 0) AS negative_fare,
    countIf(total_amount < 0) AS negative_total,
    countIf(dropoff_datetime < pickup_datetime) AS invalid_time
FROM demo.trips;

The public dataset does indeed contain such records: some trips have no passenger count specified, zero or anomalous distances, negative amounts, and isolated cases where dropoff occurs before pickup. DataKitchen

This is an important point: a successful import does not mean that all source values are valid from a business logic perspective.

Therefore, when calculating metrics, we will either explicitly use the entire raw dataset or add conditions such as:

   WHERE trip_distance > 0
    AND fare_amount >= 0

depending on the task.

After checking the data, the last thing to review is the table’s physical size.

Checking the table size

Use system.parts:

   SELECT
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size
FROM system.parts
WHERE database = 'demo'
    AND table = 'trips'
    AND active;

The row count should again be 2964624.

For a quick check, the main queries can be summarized in a single table:

We now know that the table has been fully loaded and understand the limitations of the source dataset. We can move on to what ClickHouse is typically used for: analytical queries.

Running Our First Analytical Queries

We will start with simple aggregates, then gradually add GROUP BY, sorting, filtering, and more computationally intensive aggregation across multiple dimensions.

To ensure the statistics cover January 2024 specifically, our time-based queries will exclude several rows that inadvertently fall outside the month.

Calculating aggregates across the entire dataset

First, let’s look at the key metrics:

   SELECT
    count() AS trips,
    round(avg(trip_distance), 2) AS avg_distance,
    round(avg(fare_amount), 2) AS avg_fare,
    round(avg(tip_amount), 2) AS avg_tip,
    round(avg(total_amount), 2) AS avg_total
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00';

This query scans several numeric columns across nearly three million rows and immediately returns summary statistics for the month.

If you only need trips that are known to be valid, you can also filter out obvious anomalies:

   AND trip_distance > 0
AND fare_amount >= 0
AND total_amount >= 0

For the initial test, however, we’ll use the raw dataset and keep in mind that it contains outliers.

Now let’s group the trips.

Grouping Data

Let’s count the number of trips by passenger count:

   SELECT
    passenger_count,
    count() AS trips
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00'
GROUP BY passenger_count
ORDER BY trips DESC;

This query clearly demonstrates the basic mechanics of analytics in ClickHouse: read rows → group by passenger_count → count() → sort the result

The dataset contains not only typical values such as 1 and 2, but also NULL values and a passenger_count of zero, so grouping also reveals characteristics of the source data’s quality.

Next, we’ll add sorting by the aggregated value.

Sorting the Results

For example, let’s find the pickup zones with the most trips:

   SELECT
    pickup_location_id,
    count() AS trips
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00'
GROUP BY pickup_location_id
ORDER BY trips DESC
LIMIT 10;

Here, ORDER BY trips DESC applies only to the query results and does not affect the table’s sorting key.

We can make the query more complex and also view the average cost:

   SELECT
    pickup_location_id,
    count() AS trips,
    round(avg(total_amount), 2) AS avg_total
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00'
GROUP BY pickup_location_id
ORDER BY trips DESC
LIMIT 10;

Now let’s apply more domain-specific filtering.

Filtering Data

For example, let’s select only long trips:

   SELECT
    pickup_datetime,
    pickup_location_id,
    dropoff_location_id,
    trip_distance,
    fare_amount,
    tip_amount,
    total_amount
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00'
    AND trip_distance >= 20
    AND fare_amount >= 0
ORDER BY trip_distance DESC
LIMIT 20;

This query combines:

• filtering by the sorting key;
• an additional condition on trip_distance;
• sorting the results;
• LIMIT.

For ClickHouse, this is much closer to a typical analytical query than a simple SELECT *.

All that remains is to combine several dimensions in a single, more complex query.

Running a More Resource-Intensive Aggregate Query

Let’s examine trip statistics by hour and payment type:

   SELECT
    toHour(pickup_datetime) AS hour,
    payment_type,
    count() AS trips,
    round(avg(trip_distance), 2) AS avg_distance,
    round(avg(fare_amount), 2) AS avg_fare,
    round(avg(tip_amount), 2) AS avg_tip,
    round(sum(total_amount), 2) AS total_revenue
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00'
GROUP BY
    hour,
    payment_type
ORDER BY
    hour,
    trips DESC;

For this query, ClickHouse must do the following in a single pass:

This is exactly the kind of query that is convenient to use for subsequent performance measurements.

After the query completes, clickhouse-client displays the number of rows processed, the amount of data processed, and the query execution time.

The SQL query and dataset are the same for everyone, but execution time depends on the CPU, RAM, storage, ClickHouse version, and cache state. We will therefore record the actual execution time of this query in the next section, along with the benchmark conditions.

Measuring Import and Query Performance

We now have a fixed dataset and several benchmark SQL queries, so we can begin taking measurements. The key is to evaluate a specific workload on a specific VPS rather than some abstract notion of “ClickHouse performance.”

For this test, we use the same demo.trips table and the same yellow_tripdata_2024-01.parquet file throughout, without running any other resource-intensive tasks concurrently.

Conditions to Record Before Benchmarking

You can check the main system resources using the following commands:

   nproc
free -h
lsblk
df -h
uptime

To check the ClickHouse version:

   SELECT version();

During the test, it is best to run operations sequentially. Otherwise, a background backup, another resource-intensive query, or an active merge may significantly affect the result.

Now that the test conditions are defined, we will first benchmark the most resource-intensive operation performed so far: importing the dataset.

Measuring Dataset Load Time

Before running the test again, clear the table:

   TRUNCATE TABLE demo.trips;

Then run the import again:

   INSERT INTO demo.trips
SELECT
    tpep_pickup_datetime AS pickup_datetime,
    tpep_dropoff_datetime AS dropoff_datetime,
    CAST(passenger_count, 'Nullable(UInt8)') AS passenger_count,
    trip_distance,
    toUInt16(PULocationID) AS pickup_location_id,
    toUInt16(DOLocationID) AS dropoff_location_id,
    CAST(payment_type, 'Nullable(UInt8)') AS payment_type,
    fare_amount,
    tip_amount,
    total_amount
FROM file(
    'yellow_tripdata_2024-01.parquet',
    Parquet
);

When the operation is complete, clickhouse-client displays the number of rows processed, the execution time, and the loading speed. These values can be used as the actual import results for the current VPS.

Additional query details can be obtained from system.query_log:

   SELECT
    query_duration_ms,
    read_rows,
    read_bytes,
    written_rows,
    written_bytes,
    memory_usage
FROM system.query_log
WHERE query LIKE 'INSERT INTO demo.trips%'
    AND type = 'QueryFinish'
ORDER BY event_time DESC
LIMIT 1;

This allows you to retrieve the details of the completed import directly from ClickHouse.

After reloading the data, check the row count:

   SELECT count()
FROM demo.trips;

Expected result:

   2964624

If the row count matches the source dataset, the import is complete.

You can now proceed to measuring data read performance.

Measuring the Performance of a Simple SELECT

We will use the following as a simple benchmark:

   SELECT count()
FROM demo.trips;

The query processes the entire table but performs minimal aggregation. After it completes, clickhouse-client displays the number of rows processed, the query execution time, and the processing rate.

For a more representative test, add a filter on the sorting key:

   SELECT count()
FROM demo.trips
WHERE pickup_datetime >= '2024-01-15 00:00:00'
    AND pickup_datetime < '2024-01-16 00:00:00';

This shows how ClickHouse handles a range filter on pickup_datetime, which appears first in our ORDER BY clause.

A simple count() provides a baseline. Now repeat the test with a query that reads multiple columns and builds aggregation states.

Benchmarking an Aggregate Query

We use the same query we prepared in the previous section:

   SELECT
    toHour(pickup_datetime) AS hour,
    payment_type,
    count() AS trips,
    round(avg(trip_distance), 2) AS avg_distance,
    round(avg(fare_amount), 2) AS avg_fare,
    round(avg(tip_amount), 2) AS avg_tip,
    round(sum(total_amount), 2) AS total_revenue
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00'
GROUP BY
    hour,
    payment_type
ORDER BY
    hour,
    trips DESC;

Here, ClickHouse reads several columns, calculates the hour for each row, groups the data by two dimensions, and applies several aggregate functions.

The query processes 2,964,606 rows—18 fewer than the total number of rows in the table because we limit the data to the calendar month of January 2024.

After the query completes, clickhouse-client displays its execution time and processing speed. We will use these values to compare the first run with subsequent runs.

Comparing cold and repeated runs

We run the same aggregation query several times in succession without making any changes.

The first run can be used as an approximation of a colder read, while subsequent runs indicate performance after some of the required data pages may already have entered the filesystem cache.

This is not a rigorous synthetic cold-cache benchmark: some of the data may already have been cached during import or by previous queries. However, this approach is more useful for practical testing because it shows how ClickHouse behaves under normal operating conditions.

If subsequent runs are faster, this is normal in itself and does not mean that the SQL or data has changed—the cache state has changed.

How to Interpret the Results Correctly

The results apply to our test environment:

• 4 vCPUs;
• 8 GB of RAM;
• SSD/NVMe;
• the version of ClickHouse installed on the server;
• a dataset containing 2,964,624 rows.

The same queries may run faster or slower on another VPS. Results are affected by CPU and storage performance, the amount of RAM, the state of the filesystem cache, ongoing background merges, concurrent queries, the table structure, the ORDER BY clause, and the set of columns being read.

Therefore, the benchmark should be viewed not as a universal guarantee of ClickHouse performance, but as a reproducible reference point for this specific configuration.

Checking how much space the table occupies

The source Parquet file is about 50 MB, but after import, the data is no longer stored in Parquet format. ClickHouse converts it to its native MergeTree format, creates column files, marks, and metadata, and then applies compression.

Therefore, the sizes of yellow_tripdata_2024-01.parquet and demo.trips do not necessarily match.

Why the Source File and ClickHouse Table Differ in Size

Several factors affect the table size:

• We imported only 10 columns from the source dataset;
• The values were converted to the selected ClickHouse data types;
• Each column is stored separately;
• ClickHouse applies compression;
• MergeTree creates marks and metadata;
• The table consists of one or more data parts.

This is particularly important in our case: the source Parquet file contains more fields than demo.trips, so directly comparing the size of the source file with that of the ClickHouse table is not appropriate.

Parquet is already a compressed columnar format, and after import, the data is further converted to MergeTree‘s internal representation. To determine the table’s actual size, it is better to query the ClickHouse system tables directly.

How to view compressed and uncompressed sizes

The main information is available in system.parts:

   SELECT
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS disk_size,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size
FROM system.parts
WHERE database = 'demo'
    AND table = 'trips'
    AND active;

ClickHouse will show:

• The number of rows;
• The actual disk space occupied by active parts;
• The size of the compressed column data;
• The approximate size of the same data after decompression.

Here, bytes_on_disk shows the actual disk space occupied by active parts, data_compressed_bytes shows the size of the compressed data, and data_uncompressed_bytes shows its size before compression.

These metrics make it possible to assess the table’s physical footprint and compression efficiency without having to manually insert specific values into the text.

However, the total size is also affected by the number of parts.

How to Check the Number of Parts

Each insert into a MergeTree table creates a data part, and background merges can then combine several small parts into larger ones.

Number of active parts:

   SELECT count() AS active_parts
FROM system.parts
WHERE database = 'demo'
    AND table = 'trips'
    AND active;

To view them in more detail, run:

   SELECT
    name,
    rows,
    formatReadableSize(bytes_on_disk) AS size_on_disk
FROM system.parts
WHERE database = 'demo'
    AND table = 'trips'
    AND active
ORDER BY rows DESC;

After a single large insert, there are usually only a few parts. The situation is very different if an application continuously sends thousands of tiny INSERT statements: the number of parts grows, and ClickHouse must perform background merges more frequently.

After checking the table structure, you can calculate the compression ratio.

How to Evaluate the Compression Ratio

To do this, compare the uncompressed and compressed sizes:

   SELECT
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(
    sum(data_uncompressed_bytes) /
    sum(data_compressed_bytes),
    2
) AS compression_ratio
FROM system.parts
WHERE database = 'demo'
    AND table = 'trips'
    AND active;

For example, a ratio of 4.00 would mean that the logical size of the uncompressed column data is approximately four times the size of its compressed representation.

The higher the ratio, the more effectively the data is compressed. However, you should not compare different tables based on this metric alone. The result depends on column types, the repetition of values, data order, and codecs.

The actual compressed, uncompressed, and compression_ratio values can be retrieved directly from system.parts using the query above. This is sufficient to evaluate compression efficiency without duplicating these metrics in a separate table.

We now know not only how quickly the dataset is processed, but also its physical footprint within ClickHouse. Next, we can validate access by separately testing local and remote connections, verifying the credentials, and confirming that access is denied when permissions are insufficient.

Checking Access to ClickHouse

Testing the Local Connection

Run the following on the ClickHouse VPS:

   clickhouse-client \
  --user app_user \
  --password

After entering the correct password, verify the connection:

   SELECT currentUser();

Expected result:

   app_user

Then run a simple query:

   SELECT count()
FROM demo.trips;

If the connection is established, the user is identified correctly, and the table can be queried, local access is working.

You can now test the same setup from another host.

Testing Remote Connectivity

From a trusted client, connect to ClickHouse at the server address:

   clickhouse-client \
  --host 203.0.113.10 \
  --port 9000 \
  --user app_user \
  --password

After connecting, run the following again:

   SELECT currentUser();

and:

   SELECT count()
FROM demo.trips;

If a private network is configured, use the ClickHouse VPS’s private address instead of the public documentation IP address.

A successful connection confirms several things at once:

• ClickHouse is listening on the correct interface;
• port 9000 is accessible;
• the firewall allows connections from the trusted source;
• the user exists;
• the credentials are valid.

If the connection cannot be established at all, the issue is usually below the SQL layer.

Verifying the username and password

Now deliberately enter an incorrect password:

   clickhouse-client \
  --host 203.0.113.10 \
  --port 9000 \
  --user app_user \
  --password

A network connection to the server may still be established, but ClickHouse should reject the authentication attempt.

Then connect again using the correct password and run:

   SELECT currentUser();

This distinguishes port availability from successful authentication.

The next scenario is slightly different: the user logs in successfully but attempts to perform a prohibited operation.

Testing insufficient privileges

Earlier, app_user was granted privileges only within demo.*.

Let’s try performing an action outside the target database, such as creating an object in another database or performing an administrative operation for which no privileges were granted.

For example:

   CREATE DATABASE forbidden_test;

ClickHouse should return an insufficient privileges error.

Meanwhile, a regular query against the permitted table:

   SELECT count()
FROM demo.trips;

should still execute successfully.

This illustrates an important distinction:

• authentication → who connected with the correct password
• authorization → what that user is allowed to do

If login succeeds but a specific SQL statement fails with a permissions error, the issue is no longer the password or firewall.

How to distinguish a network error from an authentication error

In practice, the symptoms are quite different.

If the client receives:

   Connection refused

the server is either not listening on that address/port, or the connection is being rejected at the network level.

If the connection hangs and eventually times out, check the routing, firewall, and host availability.

If ClickHouse returns an authentication error, the client has reached the server, but the credentials were not accepted.

If the user successfully logs in but receives a privilege error, the network and authentication are working, and the issue is limited to permissions.

This can be summarized as follows:

Access has been verified at all key levels. You can now move on to backups and verify that the database can be restored after an error or data deletion.

Creating a ClickHouse Backup

A backup is useful only if it includes the required data and is stored separately from the primary table. ClickHouse’s built-in backup mechanism lets you back up a database or individual tables and then restore them using RESTORE.

For the test environment, we will back up the entire demo database.

What Exactly Should Be Backed Up

If the application uses demo.trips, the minimum backup must include the table itself and its data.

You can back up:

• A single table;
• Multiple tables;
• The entire database.

For our scenario, it is more convenient to back up:

in a single operation.

However, a data backup is not a substitute for separately backing up the server configuration and access control settings if they are also critical for recovery. Here, we are specifically testing dataset recovery.

The next question is why you should not simply copy /var/lib/clickhouse.

How the Built-in Backup Differs from Copying the Data Directory

Simply copying the following directory:

   /var/lib/clickhouse/

from a running server is a poor general-purpose backup method.

While the files are being copied, ClickHouse continues to modify parts, perform merges, and update metadata. As a result, the copied files may be inconsistent.

The built-in BACKUP command operates at the DBMS level and understands the structure of tables and metadata.

In other words:

• cp /var/lib/clickhouse → copies internal files
• BACKUP TABLE / DATABASE → creates a logical backup using ClickHouse

For routine operations, the second option is significantly safer and more convenient for subsequent RESTORE operations.

Now let us decide where to store it.

Where to Store Backups

ClickHouse supports backup destinations defined in its configuration.

For local testing, you can use a separate disk, such as backups, configured to use the following directory:

   /var/lib/clickhouse/backups/

In production, a local directory should not contain the only copy. If the VPS or disk is lost, the backup will be lost along with the database.

A proper setup therefore looks like this:

The external destination can be Object Storage, a dedicated backup server, or another storage system outside the primary VPS.

For a practical test, we will first create a local backup.

Hands-on: Creating a Backup of a Test Database

First, create the backup disk configuration.

Open the file:

   sudo nano /etc/clickhouse-server/config.d/backup_disk.xml

Add the following:

   <clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/var/lib/clickhouse/backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
    </backups>
</clickhouse>

Create the directory:

   sudo mkdir -p /var/lib/clickhouse/backups

Assign ownership to ClickHouse:

   sudo chown clickhouse:clickhouse /var/lib/clickhouse/backups

Restart the server:

   sudo systemctl restart clickhouse-server

Check its status:

   sudo systemctl is-active clickhouse-server

Now create the backup:

   BACKUP DATABASE demo
TO Disk('backups', 'demo-backup');

If the operation completes successfully, ClickHouse will confirm that the backup has been created.

Verify That the Backup Was Created

First, verify it using ClickHouse:

   SELECT
    id,
    name,
    status,
    error
FROM system.backups
ORDER BY start_time DESC
LIMIT 5;

For a completed operation, the status should indicate success.

You can also check the directory at the system level:

   sudo find /var/lib/clickhouse/backups -maxdepth 2 -type f | head

And check its size:

   sudo du -sh /var/lib/clickhouse/backups/demo-backup

If the backup appears in system.backups and the files are present in the configured storage, the operation completed successfully.

Testing Recovery from a Backup

A successful BACKUP operation alone does not prove that the backup can be restored. An error in the storage configuration, permissions, or backup contents may not be discovered until a RESTORE is required.

It is therefore safer to test the backup immediately without affecting the live demo database: we will restore it under a different name and compare the restored data with the source database.

Why You Need to Test Backup Restoration

A backup is useful only if it can be used to restore functional tables and data.

The test should verify all of the following:

• ClickHouse can read the backup;
• table metadata has been preserved;
• the data is accessible after restoration;
• the row counts match;
• validation queries return the expected results.

For testing, we will use a separate database:

   demo_restore

This keeps the original demo database intact, while allowing us to verify and then safely delete the restored copy.

First, we will record several reference values from the source table.

Record the baseline data

Check the row count:

   SELECT count()
FROM demo.trips;

Expected result:

   2964624

Now let’s check a few aggregate values:

   SELECT
    min(pickup_datetime) AS first_trip,
    max(pickup_datetime) AS last_trip,
    count() AS rows
FROM demo.trips;

You can also retrieve a few rows:

   SELECT
    pickup_datetime,
    passenger_count,
    trip_distance,
    fare_amount,
    total_amount
FROM demo.trips
ORDER BY pickup_datetime
LIMIT 5;

These queries provide a baseline against which we can compare the restored table.

Now let’s prepare a separate location for the RESTORE operation.

Delete the test table or create a separate restore point

You do not need to delete the original demo.trips table to test the backup. It is much safer to restore the database under a different name:

• demo → original database
• demo_restore → restored copy

Before testing, make sure that this database does not already exist:

   DROP DATABASE IF EXISTS demo_restore;

This does not affect the original data and allows you to test the entire recovery process without unnecessary risk.

You can then start the restore process.

Performing a RESTORE

For backup and restore operations, use an administrative user with the required privileges.

Restore the backed-up demo database as demo_restore:

   RESTORE DATABASE demo AS demo_restore
FROM Disk('backups', 'demo-backup');

ClickHouse will restore the database metadata and table data from the backup.

You can check the operation status using:

   SELECT
    id,
    name,
    status,
    error
FROM system.backups
ORDER BY start_time DESC
LIMIT 5;

Once the operation has completed successfully, verify that the database exists:

   SHOW DATABASES;

Then verify that the table exists:

   SHOW TABLES FROM demo_restore;

The list should include:

   trips

However, the presence of the table alone does not guarantee that the data was fully restored. Complete the verification by running validation queries.

Verifying Row Counts and Query Results

Count the rows:

   SELECT count()
FROM demo_restore.trips;

Expected result:

High-Performance Virtual Machines

Deploy in seconds with full root access, reserved resources, built-in high availability, and predictable monthly pricing.

Get

   2964624

Now run the aggregate query again:

   SELECT
    min(pickup_datetime) AS first_trip,
    max(pickup_datetime) AS last_trip,
    count() AS rows
FROM demo_restore.trips;

The results should match those from the original demo.trips table.

You can check both tables with a single query:

   SELECT
    'original' AS source,
    count() AS rows
FROM demo.trips
UNION ALL
SELECT
    'restored' AS source,
    count() AS rows
FROM demo_restore.trips;

This returns identical row counts:

   original    2964624
restored    2964624

After verification, you can delete the test copy:

   DROP DATABASE demo_restore;

The original demo database remains unchanged.

The backup has now been verified by performing a full restore, rather than merely confirming that it was created successfully. The next important scenario is import errors: we will examine why ClickHouse sometimes rejects incoming data and how to quickly identify the cause.

Troubleshooting Common Import Errors

Even a correctly structured table does not guarantee a successful import. The issue may lie in the file format, number of columns, data types, or the VPS’s own resources.

The most convenient way to diagnose these errors is step by step: first check the source format and schema, then verify that the columns match, and only after that examine memory and server status.

What to Do When the Number of Columns Does Not Match

This issue is particularly common when importing CSV, TSV, and other text-based formats.

For example, the table expects four values:

   pickup_datetime, passenger_count, trip_distance, fare_amount

but a row contains only three values or, conversely, five.

For a direct import:

   INSERT INTO demo.trips
FORMAT CSV;

ClickHouse must know which columns the input fields correspond to. If the structure does not match, the import fails.

Before loading the data, it is useful to explicitly list the target columns:

   INSERT INTO demo.trips
(
    pickup_datetime,
    passenger_count,
    trip_distance,
    fare_amount
)
FORMAT CSV;

Parquet makes this easier: the file stores its schema, and in our case, we also use INSERT … SELECT to map the source fields to the target columns ourselves:

   SELECT
    tpep_pickup_datetime AS pickup_datetime,
    ...
FROM file('yellow_tripdata_2024-01.parquet', Parquet);

If the source schema has changed, first inspect it using:

   DESCRIBE file('yellow_tripdata_2024-01.parquet', Parquet);

After checking the structure, verify the values themselves.

Why “Cannot parse input” Occurs

Parsing errors occur primarily with text-based formats when ClickHouse cannot convert an input value to the expected type or parse it according to the expected syntax.

For example, a column expects a numeric value:

   trip_distance Float64

but the input data contains:

   unknown

A similar issue can occur with dates:

   2024-01-15 13:45:00

can be converted to DateTime successfully, whereas an arbitrary string cannot.

When a parsing error occurs, first inspect several rows of the source data and make sure the correct input format is selected.

To inspect a file using ClickHouse:

   SELECT *
FROM file('yellow_tripdata_2024-01.parquet', Parquet)
LIMIT 10;

For a text file, it is also useful to inspect its first few lines using standard system tools:

   head -n 10 data.csv

If the structure looks correct, proceed to checking the data types.

What to Do When Data Types Are Incorrect

The source type must either match the target column type or be convertible to it without errors.

For example:

   toUInt16(PULocationID)

works as long as the values are within the UInt16 range and can be converted to numbers.

You can check the range before importing:

   SELECT
    min(PULocationID),
    max(PULocationID)
FROM file('yellow_tripdata_2024-01.parquet', Parquet);

For a nullable field, check separately for NULL values:

   SELECT countIf(passenger_count IS NULL)
FROM file('yellow_tripdata_2024-01.parquet', Parquet);

If the source allows NULL values but the target column does not, either use Nullable(…) or determine in advance which value should replace missing values.

For example:

   coalesce(passenger_count, 0)

However, this replacement should be used only when 0 has a valid meaning in the dataset.

If the types appear correct, another common cause is an incorrect format.

How to Diagnose Format Errors

If you try to read a CSV file as TSV or specify Parquet for a different file type, ClickHouse will be unable to interpret the contents correctly.

It is therefore best to verify the format before importing.

For our file:

   DESCRIBE file('yellow_tripdata_2024-01.parquet', Parquet);

If ClickHouse displays the schema successfully, the Parquet file has been recognized correctly.

You can also run a small sample query:

   SELECT *
FROM file('yellow_tripdata_2024-01.parquet', Parquet)
LIMIT 1;

If an error occurs at this stage, the issue lies with the source file, its format, or access to it—not with demo.trips.

For CSV files, you should also check:

• delimiter;
• whether a header is present;
• quoting;
• encoding;
• the number of fields;
• how NULL values are represented.

After structural and format errors, the next issue is more systemic: insufficient resources.

What to Do When an Import Runs Out of Memory

A large import can consume RAM during parsing, decompression, type conversion, sorting, and the creation of MergeTree parts.

Check the VPS memory:

   free -h

Current ClickHouse queries:

   SELECT
    query_id,
    elapsed,
    read_rows,
    memory_usage,
    query
FROM system.processes
ORDER BY memory_usage DESC;

If the import consistently reaches the RAM limit, there are several options:

• Load the dataset in smaller batches;
• Reduce concurrency;
• Check the ClickHouse memory limits;
• Eliminate unnecessary transformations;
• Increase the VPS RAM.

However, heavy swap usage is not a good solution to persistent memory shortages. For an analytical database, this can significantly increase latency.

If sufficient resources are available but the connection was interrupted, determine whether some of the data had already been inserted into the table.

What to Do If an Import Is Interrupted

After a failed INSERT, first check:

   SELECT count()
FROM demo.trips;

Do not automatically run the same import again without checking the result of the first attempt. Otherwise, you may create duplicates.

You can check completed queries using:

   SELECT
    type,
    query_duration_ms,
    exception,
    written_rows,
    query
FROM system.query_log
WHERE query LIKE 'INSERT INTO demo.trips%'
ORDER BY event_time DESC
LIMIT 10;

For a fully reproducible test import, the simplest approach is to clear the table:

   TRUNCATE TABLE demo.trips;

and run the import again.

This approach is not always suitable for production datasets. In production, imports should be designed to be idempotent: divide the data into batches or partitions and determine in advance which parts have already been written successfully.

The main errors can be summarized as follows:

Import troubleshooting therefore usually begins not with changing server settings, but with the basics: the schema, format, data types, and a few rows from the source file. If these are correct, the next step is to investigate memory usage, logs, and the state of ClickHouse itself.

Troubleshooting Connection Issues

What Does “Connection Refused” Mean?

An error such as:

   Connection refused

usually means that the TCP connection to the specified address and port was refused.

The most common causes are:

• clickhouse-server is not running;
• ClickHouse is not listening on the required interface;
• the wrong port is being used;
• the server is listening only on 127.0.0.1;
• a firewall or another network setting is preventing the connection.

For example, if the client attempts to connect to the native port:

   clickhouse-client \
  --host 203.0.113.10 \
  --port 9000 \
  --user app_user \
  --password

and the connection cannot be established at all, check the infrastructure first, not the password.

The next step is to make sure that ClickHouse itself is running.

How to check clickhouse-server

Run the following on the server:

   sudo systemctl status clickhouse-server

Quick check:

   sudo systemctl is-active clickhouse-server

If the service is running, the output will be:

   active

If the service is stopped, start it:

   sudo systemctl start clickhouse-server

After that, it is a good idea to immediately test a local SQL query:

   clickhouse-client --query "SELECT 1"

If the local query succeeds, the server process is running, so the issue is in the network configuration.

First, check which ports ClickHouse is actually listening on.

How to Check Ports

Run the following on the ClickHouse VPS:

   sudo ss -lntp | grep clickhouse

Or check specific ports:

   sudo ss -lntp | grep -E ':9000|:8123'

For the native protocol, check:

   9000/TCP

For the HTTP interface:

   8123/TCP

It is important to check not only the port number but also the address.

For example:

   127.0.0.1:9000

means that the native interface is accessible only from the local host.

If remote access is required, ClickHouse must listen on the appropriate private interface or another permitted interface.

Then check the listen_host setting.

How to Check listen_host

Additional server settings are stored in:

   /etc/clickhouse-server/config.d/

For example:

   sudo cat /etc/clickhouse-server/config.d/listen.xml

For local access, the file may contain:

   <clickhouse>
    <listen_host>127.0.0.1</listen_host>
</clickhouse>

If access from a private network is required:

   <clickhouse>
    <listen_host>127.0.0.1</listen_host>
    <listen_host>10.0.0.10</listen_host>
</clickhouse>

After changing the server configuration, restart ClickHouse:

   sudo systemctl restart clickhouse-server

Then check again:

   sudo ss -lntp | grep -E ':9000|:8123'

If the required address appears, proceed to the firewall.

How to check the firewall

For UFW:

   sudo ufw status numbered

If only one trusted client is allowed, the native protocol rule might look like this: 203.0.113.25 → 9000/tcp

Add the rule with the following command:

   sudo ufw allow from 203.0.113.25 to any port 9000 proto tcp

You can test the TCP connection from the client itself:

   nc -vz 203.0.113.10 9000

If the port is accessible, nc reports a successful connection.

If the connection hangs until it times out, check the following:

• source IP;
• routing;
• UFW;
• the cloud provider’s firewall;
• whether the server address is correct.

If TCP works but clickhouse-client returns an authentication error, the network is no longer the primary issue.

The final source of information is the logs.

Where to Find ClickHouse Logs

Latest systemd messages:

   sudo journalctl \
  -u clickhouse-server \
  -n 100 \
  --no-pager

Logs for the current boot:

   sudo journalctl \
  -u clickhouse-server \
  -b \
  --no-pager

For a standard package-based installation, also check:

   /var/log/clickhouse-server/

For example:

   sudo tail -n 100 /var/log/clickhouse-server/clickhouse-server.log

And the error log:

   sudo tail -n 100 /var/log/clickhouse-server/clickhouse-server.err.log

The following table helps you quickly determine where to focus your troubleshooting:

Checking the connection in this order makes it fairly easy to identify the layer at which the problem occurred.

The final step is to make sure that all settings persist after a full VPS reboot.

Verifying ClickHouse After a Reboot

We previously verified the installation, access, dataset, and backup separately. The final check is to reboot the entire VPS and ensure that ClickHouse automatically returns to an operational state.

This check covers not only the systemd service but also the user, table, data, and a standard analytical query.

What Should Start Automatically

After installing ClickHouse, we enabled it to start automatically:

   sudo systemctl enable clickhouse-server

Therefore, after running:

   sudo reboot

systemd should automatically start clickhouse-server.

The following should also take effect:

• server configuration;
• listen_host;
• users and permissions;
• storage configuration;
• access to existing tables;
• backup disk settings.

After reconnecting via SSH, we start with the service.

Check the service

Run:

   sudo systemctl is-active clickhouse-server

Expected result:

   active

Check whether the service is enabled at startup:

   sudo systemctl is-enabled clickhouse-server

Expected result:

   enabled

If the service failed to start:

   sudo journalctl \
  -u clickhouse-server \
  -b \
  --no-pager

This immediately shows errors from the current boot.

If the server is running, check the user.

Verifying the User

Connect:

   clickhouse-client \
  --user app_user \
  --password

After logging in:

   SELECT currentUser();

The result is:

   app_user

This confirms that the access configuration persisted after the reboot.

Next, check the permitted database:

   SHOW TABLES FROM demo;

Only the following table should remain in the list:

   trips

Now we can proceed to the data itself.

Verify the table and data

Count the rows:

   SELECT count()
FROM demo.trips;

Expected result:

   2964624

You can also check the data range:

   SELECT
    min(pickup_datetime) AS min_pickup,
    max(pickup_datetime) AS max_pickup
FROM demo.trips;

If the table is accessible and the row count has not changed, the data successfully survived the VPS reboot.

All that remains is to run the usual workload.

Repeat the verification query

Use one of the queries introduced earlier:

   SELECT
    pickup_location_id,
    count() AS trips,
    round(avg(total_amount), 2) AS avg_total
FROM demo.trips
WHERE pickup_datetime >= '2024-01-01 00:00:00'
    AND pickup_datetime < '2024-02-01 00:00:00'
GROUP BY pickup_location_id
ORDER BY trips DESC
LIMIT 10;

If ClickHouse returns a result, the final verification sequence is as follows:

For a quick check, the main steps can be summarized in a table as usual:

If all five checks pass after a full reboot, ClickHouse automatically returns to an operational state: the service starts, the access configuration is retained, the table remains available, and the data and queries work without reconfiguration.

How to Use ClickHouse Safely in Production

Before going into production, establish a few operational rules.

They do not require a complex cluster, but they significantly reduce the risk of a network or disk issue, or a resource-intensive query, suddenly bringing down the entire server.

Do Not Expose Ports to the Entire Internet

Native port 9000 and HTTP port 8123 should not be accessible from all IP addresses.

The best approach is to use a private network, a firewall, and a restricted listen_host setting.

If remote access is necessary, allow connections only from specific source IP addresses.

For example:

   sudo ufw allow from 203.0.113.25 to any port 9000 proto tcp

This keeps ClickHouse accessible to the intended client without making the database publicly accessible.

Separate Users and Privileges

Use separate accounts for the application, ETL processes, and administrative tasks.

For example:

• admin_user → user and schema management
• etl_user → INSERT / SELECT
• app_user → only the required SELECT privileges

You can review privileges by running:

   SHOW GRANTS FOR app_user;

This makes access easier to control and limits the impact of an error or the compromise of a single account.

Monitor Free Disk Space

Disk space is a critical resource for ClickHouse.

It is used not only for the data itself, but also for:

• merges;
• temporary files;
• backups;
• logs;
• new parts.

Therefore, checking only the table size is not enough.

At the system level:

   df -h

ClickHouse directory size:

   sudo du -sh /var/lib/clickhouse

You can view the size of individual tables in system.parts.

When free disk space is running low, problems can occur in several areas at once: INSERT operations, merges, backups, and even routine background operations.

Leave RAM headroom for resource-intensive queries

ClickHouse uses RAM extensively for:

• GROUP BY;
• ORDER BY;
• JOIN;
• large aggregations;
• parsing and decompression;
• creating intermediate states.

The server therefore needs memory headroom beyond its normal baseline usage.

Check system RAM:

   free -h

Active queries:

   SELECT
    query_id,
    elapsed,
    memory_usage,
    query
FROM system.processes
ORDER BY memory_usage DESC;

If a single query consistently consumes nearly all the VPS memory, it is better to limit the workload or add more RAM than to rely on swap.

Control the growth of parts

MergeTree stores data in parts, which are then combined by background merges.

If the application sends too many small INSERTs, the number of parts can grow rapidly.

You can check them as follows:

   SELECT
    database,
    table,
    count() AS parts
FROM system.parts
WHERE active
GROUP BY
    database,
    table
ORDER BY parts DESC;

If the number of parts becomes too high, consider revising the ingestion strategy and sending data in larger batches.

For ClickHouse, fewer, larger INSERTs are generally better than thousands of tiny inserts.

Store backups outside the ClickHouse VPS

A backup on the same disk is useful for quickly testing recovery, but it does not protect against the loss of the VPS itself.

A production setup should look roughly like this:

This could be object storage, another VPS, or dedicated backup storage.

If the backup is stored only alongside /var/lib/clickhouse, a storage failure will destroy both the data and the backup.

Test RESTORE

Creating a backup is not enough.

You must periodically verify that it can actually be restored.

We have already used the following safe procedure:

This test should be repeated regularly, especially after changes to the storage configuration or ClickHouse upgrades.

It is better to identify a backup issue in advance than during an incident.

Monitor the query log and errors

ClickHouse stores a wealth of useful information in its system tables.

For example, you can view the query history with:

   SELECT
    event_time,
    query_duration_ms,
    read_rows,
    written_rows,
    memory_usage,
    exception,
    query
FROM system.query_log
ORDER BY event_time DESC
LIMIT 20;

For currently running queries:

   SELECT *
FROM system.processes;

The following system-level commands are also useful:

   sudo journalctl -u clickhouse-server -n 100 --no-pager

and:

   sudo tail -n 100 /var/log/clickhouse-server/clickhouse-server.err.log

This allows you to detect memory errors, failed queries, disk issues, and configuration errors more quickly.

Upgrade ClickHouse systematically

It is best not to upgrade ClickHouse blindly.

Before upgrading, check:

• the current version;
• the changelog for the target version;
• compatibility;
• the backup;
• available disk space;
• the status of the tables;
• the ability to roll back.

To check the server version:

   SELECT version();

After the upgrade, repeat the basic checks:

   sudo systemctl status clickhouse-server
SELECT 1;
SHOW DATABASES;
SELECT count()
FROM demo.trips;

Separately, verify the user, backup configuration, and a test analytical query.

What to Check Before Going into Production

Before running a production workload, it is useful to go through a short checklist.

Network and Access

• ports are not open to the entire internet;
• listen_host is configured deliberately;
• the firewall allows connections only from the required source IP addresses;
• applications use separate user accounts;
• permissions are limited to the minimum required.

Resources

• there is sufficient disk space available;
• RAM is not fully utilized;
• resource-intensive queries do not exhaust the server’s memory;
• the number of active parts remains reasonable;
• there is no persistent pressure on storage.

Data and Recovery

• backups are created without errors;
• backups are stored outside the primary VPS;
• RESTORE has been tested in practice;
• the test dataset is readable after the restore;
• rebooting does not disrupt access to tables.

Operations

• clickhouse-server starts automatically;
• the query log is accessible;
• server logs are reviewed;
• upgrades are performed with a backup and post-upgrade checks;
• backups are created regularly in accordance with the recovery policy;
• benchmark results are evaluated in the context of the VPS configuration, not in isolation.

Once these items have been addressed, a standalone ClickHouse instance can be used as a predictable production component for analytical workloads without requiring an immediate move to a cluster architecture.

Conclusion

ClickHouse on a single cloud server is well suited to small analytics workloads, logs, events, and other use cases that require fast aggregation across large datasets. In our case, a standalone server was sufficient to load several million NYC Taxi rows, run analytical queries, verify access, and examine how MergeTree stores data.

For production use, installation alone is not enough: access should be restricted by a firewall, users should be granted the minimum required permissions, RAM and disk usage and the number of parts should be monitored, and backups should be stored outside the primary VPS and periodically tested using RESTORE.

Thank you!

FAQ

Can ClickHouse replace PostgreSQL or MySQL?

Not always. ClickHouse is designed primarily for analytical workloads (OLAP): large queries, aggregations, filtering, and processing millions of rows. For traditional OLTP workloads involving frequent point UPDATE operations, transactions, and many small operations, PostgreSQL or MySQL are generally better suited.

Is using MergeTree mandatory?

For most standard ClickHouse tables, the MergeTree family is the primary choice. It supports storing data in parts, performing background merges, and reading data efficiently using the sorting key. Other engines are needed for more specialized use cases.

Why Does the Table Size Change After Importing Parquet Data?

Parquet and MergeTree use different storage formats and compression methods. In addition, in our case, only some of the source columns are imported into ClickHouse. Therefore, the source file and the table cannot be directly compared as identical representations of the same dataset.

What happens if you frequently run small INSERT operations?

Each insert can create a new data part. If there are too many small inserts, ClickHouse must perform background merges more frequently, and the number of parts begins to grow. Therefore, it is generally more efficient to load data in larger batches.

Can I connect to ClickHouse over HTTP?

Yes. In addition to the native protocol on port 9000, ClickHouse supports an HTTP interface, which typically runs on port 8123. It is used by applications, scripts, and integrations that prefer to send queries over HTTP.

Does the entire dataset need to be stored in RAM?

No. ClickHouse stores its primary data on disk. RAM is used for query execution, aggregation states, sorting, joins, buffers, and caching. Therefore, a large dataset does not need to fit entirely in memory, but resource-intensive queries still require sufficient RAM.

Why might a subsequent query run faster than the first?

After the initial read, some data pages may remain in the filesystem cache, allowing a subsequent query to retrieve the required data more quickly. Background merges, concurrent workloads, and the state of the VPS itself can also affect the result, so a single measurement should not be treated as a universal indicator of performance.

Is the built-in BACKUP sufficient if it is stored on the same VPS?

Yes, for testing restores, but it is not sufficient for production. A disk failure or loss can destroy both the primary database and the local backup at the same time. Therefore, backups should also be copied to external Object Storage or a separate backup server.

Sources

1. ClickHouse Docs — Installation
2. ClickHouse Docs — MergeTree
3. ClickHouse Docs — BACKUP and RESTORE
4. ClickHouse Docs — Access Rights and Account Management
5. ClickHouse Docs — system.parts