A Guide to ClickHouse Installation on Ubuntu 22.04 / 24.04 LTS: Step-by-Step Tutorial

15 minutes

Dwijadas Dey

Introduction

ClickHouse, a powerful open-source columnar database management system, has gained popularity among data analysts and engineers for its lightning-fast query performance and ability to handle massive datasets.

This comprehensive guide will walk you through the process of installing ClickHouse database on Ubuntu 22.04/24.04 LTS, ensuring you have a robust analytics platform / OLAP system at your fingertips.

With ClickHouse's ability to process analytical queries on big data in real-time, you'll be well-equipped to tackle complex data challenges. This installation guide aims to setup and get you up and running with ClickHouse database quickly, securing ClickHouse with SSL/TLS and optimizing ClickHouse performance on your Ubuntu 22.04/24.04 LTS system.

Prerequisites for ClickHouse Installation on Ubuntu 22.04

Before we dive into the nitty-gritty of installing ClickHouse on Ubuntu 22.04 or Ubuntu 24.04, let's find out the clickhouse system requirements.

ClickHouse is a beast when it comes to processing data, so you'll want to make sure the system is configured with latest hardware. You'll need at least 4 CPU cores and 16GB of RAM to get a decent performance. But if you plan on crunching through terabytes of data, you might want to consider beefing that up a bit!

Storage is another biggie. ClickHouse loves SSDs and performs way better than a regular HDD, so choose SSDs instead of regular HDDs for ClickHouse storage.

You'll also need an Ubuntu 22.04 LTS or Ubuntu 24.04 LTS running in your environment and have sudo access to install ClickHouse.

Here's a quick checklist to make sure you're ready to roll:

Ubuntu 22.04 LTS or 24.04 LTS. (fully updated)
At least 4 CPU cores.
Minimum 16GB RAM. (more is better!)
SSD storage.
sudo access.

Let's dive in and start installing ClickHouse on Ubuntu 22.04 or Ubuntu 24.04.

Preparing Ubuntu 22.04/24.04 System for ClickHouse

Let's make sure that the Ubuntu system is up to date. Fire up the terminal and run these commands:

$ sudo apt update
$ sudo apt upgrade -y

In Ubuntu, ClickHouse needs a few specific dependencies to run smoothly. Here's what you need:

$ sudo apt install -y apt-transport-https ca-certificates dirmngr

These packages help with secure downloads, managing certificates, and making sure only the right software gets in!

Next, configure hostname for each node where you want to install ClickHouse server. In this guide, we will stick with single node ClickHouse server.

$ sudo hostnamectl hostname clickhouse-node-1
$ sudo vi /etc/hosts
...
...
172.26.8.182 clickhouse-node-1
...
...

If you're running a firewall, make sure it's not going to block ClickHouse. By default, ClickHouse uses port 8123 for HTTP and 9000 for native client connections.

If you're using UFW (Uncomplicated Firewall), you can open these ports with:

$ sudo ufw allow 8123/tcp
$ sudo ufw allow 9000/tcp

Set up a separate user for running ClickHouse. It's not strictly necessary, but it's a good practice for security. You can do this with:

$ sudo adduser clickhouse
$ sudo usermod -aG sudo clickhouse

This creates a new user called "clickhouse" and gives them sudo privileges. If you are installing ClickHouse in a multi node cluster, then repeat the above steps on each node.

This prep work will make the actual installation a breeze. In the next section, we'll dive into the nitty-gritty of installing ClickHouse.

Step-by-Step ClickHouse Installation Process

We've prepped the Ubuntu 22.04/24.04 system, and now it's time to install ClickHouse! First up, we need to add the ClickHouse repository to the system. Firstly, import clickhouse gpg key with the following command.

$ sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 3E4AD4719DDE9A38

This command fetches the GPG key for the ClickHouse repository. Next, we need to add the repository itself:

$ echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list

This line tells Ubuntu where to find the ClickHouse packages.

Now update the package list to include the new repository:

$ sudo apt update

It's time to install ClickHouse! We're going to install both the ClickHouse server and the ClickHouse client (clickhouse-client):

$ sudo apt install -y clickhouse-server clickhouse-client

During the installation, you'll be prompted to set a password for the default user. Choose a strong password here. Once the installation is complete, let's make sure ClickHouse starts automatically when the system boots up:

$ sudo systemctl enable clickhouse-server

And finally, let's start the ClickHouse server:

$ sudo systemctl start clickhouse-server

You've now got ClickHouse installed on the Ubuntu 22.04/24.04 system. Let's do a quick verification:

$ clickhouse-client --password
ClickHouse client version 24.9.1.3278 (official build).
Password for user (default): 
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.9.1.

Warnings:
 * Linux threads max count is too low. Check /proc/sys/kernel/threads-max
 * Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
 * Available memory at server startup is too low (2GiB).
 * Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.

ip-172-26-8-182.ap-south-1.compute.internal :)

This command will prompt you for the password you set during installation. Once you enter it, you should see a ClickHouse prompt. Try running a simple query:

ip-172-26-8-182.ap-south-1.compute.internal :) SELECT version();

SELECT version()

Query id: 86d02cbd-42f6-4fe6-9fc7-6327bf65ed0d

   ┌─version()───┐
1. │ 24.9.1.3278 │
   └─────────────┘

1 row in set. Elapsed: 0.001 sec.

If you see a version number pop up, congratulations! You've successfully installed ClickHouse!

In the next sections, we'll dive into configuration, optimization, and some advanced features that'll really make ClickHouse data sing!

Post-Installation Configuration

You've got ClickHouse installed on the Ubuntu 22.04/24.04 system. It's time to fine-tune clickhouse server configuration from performance point of view.

Let's take a look at the main configuration file. You'll find it at /etc/clickhouse-server/config.xml. This file is like the control panel for ClickHouse server. Make sure to take a backup before you start tinkering:

$ sudo cp /etc/clickhouse-server/config.xml /etc/clickhouse-server/config.xml.bak

Now, let's open up ClickHouse server config file. This config file is read-only by default. As a root user update the file permission to make it editable.

$ sudo -i
$ chmod 666 /etc/clickhouse-server/config.xml
$ vi /etc/clickhouse-server/config.xml

There's a lot going on in this file, but We're going to focus on a few key areas.

First up, let's look at the <listen_host> tag. By default, ClickHouse only listens on localhost. You can configure ClickHouse to access it from remote machines on any network, you'll need to change this.

<listen_host>::</listen_host>

This tells ClickHouse to listen on all available network interfaces. Make sure the firewall is properly configured!

Revert the permission of ClickHouse server configuration to read-only.

$ chmod 400 /etc/clickhouse-server/config.xml

Next, configure memory usages for ClickHouse. ClickHouse can be a bit of a memory hog if you're not careful.

You can configure memory usages in ClickHouse using max_memory_usage. It is a user-level setting that should be located in users.xml inside <profiles> section for a specific profile.

Before updating users.xml file, update the permission of this file to make it editable. Once you have updated this file, revert the permission of this file to the original.

$ sudo -i
$ chmod 666 /etc/clickhouse-server/users.xml
$ vi /etc/clickhouse-server/users.xml
...
...
<profiles>
    <!-- Default settings. -->
        <default>
                    <max_memory_usage>10000000000</max_memory_usage>
        </default>
<profiles>
...
...

$ chmod 400 /etc/clickhouse-server/users.xml

This sets the maximum memory usage to about 10GB. Adjust this setting based on the system's resources, but be generous - ClickHouse loves memory!

Now, let's set up a new user. It's always a good idea to have separate users for different purposes. Here's how you can add a new user in the users.xml file:

$ sudo -i
$ chmod 666 /etc/clickhouse-server/users.xml
$ sudo vi /etc/clickhouse-server/users.xml

Add something like this under the <clickhouse> section:

<clickhouse>
   <password>your_secure_password_here</password>
   <networks>
       <ip>::/0</ip>
   </networks>
   <profile>default</profile>
   <quota>default</quota>
<clickhouse>

Remember to replace your_secure_password_here with an actual secure password and revert the file permission of users.xml to the original!

$ chmod 400 /etc/clickhouse-server/users.xml

Lastly, let's talk about data storage. By default, ClickHouse stores its data in /var/lib/clickhouse/. If you want to store data in some other folder, you can change this in the clickhouse server configuration file.

$ vi /etc/clickhouse-server/config.xml
...
...
<path>/var/lib/clickhouse/</path>
...
...

Once you're done making changes, save the file and restart ClickHouse:

$ sudo systemctl restart clickhouse-server

These tweaks will make experiences with ClickHouse much smoother. Remember, configuration is an ongoing process - as you use ClickHouse more, you'll discover what settings work best for a specific use case.

In the next section, we'll fire up ClickHouse and run some tests to make sure everything's working as it should.

Starting and Testing ClickHouse Installation

We've installed ClickHouse and tweaked its configuration, and now it's time to fire up ClickHouse installation and put it through its paces! Let's make sure ClickHouse is actually running. Here's how you check:

$ sudo systemctl status clickhouse-server

You should see something like "active (running)" in the output. If not, no worries! Just start it up with:

$ sudo systemctl start clickhouse-server

Now, let's connect to ClickHouse using the command-line client.

$ clickhouse-client --password

You'll be prompted for the password you set during installation. Once you're in, you should see a prompt that looks like this:

ClickHouse client version 24.9.1.3278 (official build).
Password for user (default): 
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.9.1.

Now, let's run some basic queries to make sure everything's working. Now that we're connected to the ClickHouse client, let's run some basic queries to ensure everything's working as it should:

Check the version:

SELECT version();

Create a test database:

CREATE DATABASE IF NOT EXISTS test;

Switch to the test database:

USE test;

Create a simple table:

CREATE TABLE test_table (
   id UInt32,
   name String,
   date Date
) ENGINE = MergeTree()
ORDER BY id;

Insert some data:

INSERT INTO test_table (id, name, date) VALUES 
(1, 'John Doe', '2023-09-13'),
(2, 'Jane Smith', '2023-09-14'),
(3, 'Bob Johnson', '2023-09-15');

Query the data:

SELECT * FROM test_table;

If all these commands run without errors and you see the inserted data, congratulations! The ClickHouse installation is up and running smoothly.

Now, let's test some of ClickHouse's more advanced features. Here's a query that demonstrates ClickHouse's impressive aggregation capabilities:

SELECT 
   toYYYYMM(date) AS month,
   COUNT(*) AS count,
   groupArray(name) AS names
FROM test_table
GROUP BY month;

This query groups the data by month, counts the records, and creates an array of names for each month. It's a simple example, but it showcases ClickHouse's ability to handle complex aggregations efficiently.

One last test - let's check the system tables. ClickHouse has a wealth of system tables that provide insights into its operation:

SELECT table, total_rows, total_bytes
FROM system.tables
WHERE database = 'test';

This query shows you the number of rows and total size of the tables in the test database.

If all these queries run successfully, you can be confident that the ClickHouse installation is working correctly. Remember, these are just basic tests - ClickHouse is capable of handling much more complex queries and massive datasets!

Get familiar with the ClickHouse documentation. It's a goldmine of information that'll help you make the most of the new database.

In the next section, we'll dive into securing ClickHouse installation.

Securing ClickHouse Installation with SSL / TLS

Now that ClickHouse is up and running, we need to start pushing all data into it. But before ingesting data, adjust ClickHouse server security settings to make it secured for your precious data.

Let's start with user authentication. ClickHouse comes with a default user that has no password - that's a big no-no. Let's fix that right away:

Open the users config file:

$ sudo -i
$ chmod 666 /etc/clickhouse-server/users.xml
$ vi /etc/clickhouse-server/users.xml

Find the <users> section and the <default> user. Add a strong password like this.

...
...
<users>
    <default>
        <password>#4oX0j8|l2Lz</password>
    </default>
</users>
...
...
$ chmod 400 /etc/clickhouse-server/users.xml

Next up, configure network security for ClickHouse. By default, ClickHouse listens on all network interfaces. That's convenient, but not very secure. Let's limit it to only accept connections from localhost:

Open the clickhouse configuration file:

$ sudo -i
$ chmod 666 /etc/clickhouse-server/config.xml
$ vi /etc/clickhouse-server/config.xml

Find the <listen_host> tag and change it to:

...
...
<listen_host>127.0.0.1</listen_host>
...
...
$ chmod 400 /etc/clickhouse-server/config.xml

If you need to access ClickHouse from other machines, consider using a VPN or SSH tunnel instead of exposing it directly to the network.

Restart ClickHouse server:

$ sudo systemctl restart clickhouse-server

Generate SSL certificates

Now, let's set up SSL/TLS encryption. This will ensure that all data transmitted between the client and server is encrypted. The following section will show you how to configure a ClickHouse server with SSL/TLS settings using OpenSSL and test it by connecting from a ClickHouse client in secured mode.

Create a folder for SSL certificates on each clickhouse node and generate keys to be used as a CA key.

$ sudo -i
$ mkdir /etc/clickhouse-server/certs
$ cd /etc/clickhouse-server/certs
$ openssl genrsa -out localnet_ca.key 2048

Next, Generate a self-signed CA certificate. This certificate will be used to sign other certificates.

$ openssl req -x509 -subj "/CN=localnet CA" -nodes -key localnet_ca.key -days 1095 -out localnet_ca.crt

Verify the certificate.

$ openssl x509 -in localnet_ca.crt -text

Now generate certificate request(CSR) and generate key for the clickhouse node. In case of ClickHouse cluster, you need to generate certificate for each node.

$ openssl req -newkey rsa:2048 -nodes -subj "/CN=chnode1" -addext "subjectAltName = DNS:clickhouse-node-1,IP:172.26.8.182" -keyout chnode1.key -out chnode1.csr

Create new certificate and key pairs for the clickhouse node using previously created CSR and CA.

$ openssl x509 -req -in chnode1.csr -out chnode1.crt -CA localnet_ca.crt -CAkey localnet_ca.key -days 365

Verify the subject and issuer for the certificate.

$ openssl x509 -in chnode1.crt -text -noout

Test the certificate by verifying against the CA cert:

$ openssl verify -CAfile localnet_ca.crt chnode1.crt

Update ownership and permission of all the certificates.

$ chown clickhouse:clickhouse -R /etc/clickhouse-server/certs
$ chmod 600 /etc/clickhouse-server/certs/*
$ chmod 755 /etc/clickhouse-server/certs
$ ls -l /etc/clickhouse-server/certs

Configure SSL/TLS settings in ClickHouse config file

Edit the ClickHouse server config file for SSL/TLS settings by configuring the https port and disabling the http port.

$ sudo -i
$ chmod 666 /etc/clickhouse-server/config.xml
$ vi /etc/clickhouse-server/config.xml
...
...
<https_port>8443</https_port>
<!-- <http_port>8123</http_port> --> 
...
...

Next Configure the ClickHouse server native secure TCP port and simultaneously disable the default non-secure port.

$ vi /etc/clickhouse-server/config.xml
...
...
<tcp_port_secure>9440</tcp_port_secure>
<!--<tcp_port>9000</tcp_port>-->
...
...

Disable the default non-secure interserver port and configure the interserver https port.

$ sudo vi /etc/clickhouse-server/config.xml
...
...
<interserver_https_port>9010</interserver_https_port>
<!--<interserver_http_port>9009</interserver_http_port>-->
...
...

Now configure SSL/TLS settings for clickhouse server by providing with OpenSSL certificates and paths. For a ClickHouse cluster, the certificate file and private key file for each node will be different.

$ sudo vi /etc/clickhouse-server/config.xml
...
...
    <openSSL>
       <server> 
        <certificateFile>/etc/clickhouse-server/certs/chnode1.crt</certificateFile>
           <privateKeyFile>/etc/clickhouse-server/certs/chnode1.key</privateKeyFile>
           <verificationMode>none</verificationMode>
           <loadDefaultCAFile>true</loadDefaultCAFile>
           <cacheSessions>true</cacheSessions>
           <disableProtocols>sslv2,sslv3</disableProtocols>
           <preferServerCiphers>true</preferServerCiphers>
           <invalidCertificateHandler>
               <name>RejectCertificateHandler</name>
           </invalidCertificateHandler>
       </server>
       <client> 
        <loadDefaultCAFile>true</loadDefaultCAFile>
        <caConfig>/etc/clickhouse-server/certs/localnet_ca.crt</caConfig>
           <cacheSessions>true</cacheSessions>
           <disableProtocols>sslv2,sslv3</disableProtocols>
        <preferServerCiphers>true</preferServerCiphers>
        <verificationMode>relaxed</verificationMode>           
           <invalidCertificateHandler>                
               <name>RejectCertificateHandler</name>
           </invalidCertificateHandler>
       </client>
   </openSSL>
...
...

Configure gRPC for SSL. For a ClickHouse cluster, do it for each node.

$ vi /etc/clickhouse-server/config.xml
...
...
<grpc>
   <enable_ssl>1</enable_ssl>
   <ssl_cert_file>/etc/clickhouse-server/certs/chnode1.crt</ssl_cert_file>
   <ssl_key_file>/etc/clickhouse-server/certs/chnode1.key</ssl_key_file>
   <ssl_require_client_auth>true</ssl_require_client_auth>
   <ssl_ca_cert_file>/etc/clickhouse-server/certs/marsnet_ca.crt</ssl_ca_cert_file>
   <transport_compression_type>none</transport_compression_type>
   <transport_compression_level>0</transport_compression_level>
   <max_send_message_size>-1</max_send_message_size>
   <max_receive_message_size>-1</max_receive_message_size>
   <verbose_logs>false</verbose_logs>
</grpc>
...
...
$ chmod 400 /etc/clickhouse-server/config.xml

To connect ClickHouse server using https, Configure ClickHouse client on at least one of the node using SSL in the ClickHouse client configuration file(config.xml).

$ sudo -i
$ chmod 666 /etc/clickhouse-client/config.xml
$ vi /etc/clickhouse-client/config.xml
 ...
 ...
       <openSSL>
       <client> <!-- Used for connection to server's secure tcp port -->
           <loadDefaultCAFile>true</loadDefaultCAFile>
           <caConfig>/etc/clickhouse-server/certs/localnet_ca.crt</caConfig>
           <cacheSessions>true</cacheSessions>
           <disableProtocols>sslv2,sslv3</disableProtocols>
           <preferServerCiphers>true</preferServerCiphers>
           <!-- Use for self-signed: <verificationMode>none</verificationMode> -->
           <invalidCertificateHandler>
               <!-- Use for self-signed: <name>AcceptCertificateHandler</name> -->
               <name>RejectCertificateHandler</name>
           </invalidCertificateHandler>
       </client>
   </openSSL>
   ...
   ...

Disable the MySQL/PostgreSQL emulation port and revert the file permission to read-only.

...
...
<!--mysql_port>9004</mysql_port-->
<!--postgresql_port>9005</postgresql_port-->
...
...
$ chmod 400 /etc/clickhouse-client/config.xml

Restart ClickHouse server.

$  sudo systemctl restart clickhouse-server

Verify if the SSL ports are up and listening:

$ sudo  netstat -ano | grep tcp
tcp        0      0 127.0.0.54:53           0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp6       0      0 :::9010                 :::*                    LISTEN      off (0.00/0/0)
tcp6       0      0 :::22                   :::*                    LISTEN      off (0.00/0/0)
tcp6       0      0 :::8443                 :::*                    LISTEN      off (0.00/0/0)
tcp6       0      0 :::9440                 :::*                    LISTEN      off (0.00/0/0)
tcp6       0    156 172.26.9.254:22         152.58.144.184:49022    ESTABLISHED on (1.62/0/0)

Adjust the firewall settings and access ClickHouse server in secure mode using ClickHouse client.

$ sudo ufw allow 8443/tcp
$ sudo ufw allow 9440/tcp
$ sudo ufw allow 9010/tcp
$ sudo clickhouse-client --user default --password PassWord@123 --port 9440 --secure --host clickhouse-node-1

If you are getting "Code: 210. DB::NetException: Connection refused" error then edit the configuration file and adjust the value of <listen_host> </listen_host> tag to :: .

<listen_host>::</listen_host>

You are now securely connected to ClickHouse server using SSL.

File permission

Lastly, configure file permissions for ClickHouse databse. ClickHouse should be run as a non-root user, and its data directory should have restricted permissions. Run the following commands to set restricted file permission and non-root ownership to the ClickHouse data directory.

$ sudo chown -R clickhouse:clickhouse /var/lib/clickhouse
$ sudo chmod 700 /var/lib/clickhouse

This ensures that only the ClickHouse user can access the data files.

DNS cache

Finally, enable ClickHouse's internal DNS cache to prevent DNS rebinding attacks:

$ sudo -i
$ chmod 666 /etc/clickhouse-server/config.xml
$ vi /etc/clickhouse-server/config.xml
...
...
<disable_internal_dns_cache>1</disable_internal_dns_cache>
...
...
$ chmod 400 /etc/clickhouse-server/config.xml

After making all these changes, don't forget to restart ClickHouse:

$ sudo systemctl restart clickhouse-server

These security measures are crucial for clickhouse installation.

In the next section, we'll look at some performance optimization techniques to make ClickHouse installation runs faster.

Optimizing ClickHouse Performance on Ubuntu

Once ClickHouse is installed and secured, ClickHouse query optimization is an important step! Firstly, configure system parameters starting with tweaking Ubuntu's default settings.

Increase the maximum number of open files. This will improve I/O performance especially when ClickHouse is handling with a large number of files.

$ sudo vi /etc/security/limits.conf
...
...
clickhouse soft nofile 262144
clickhouse hard nofile 262144
...
...

Optimize the kernel for ClickHouse:

$ sudo vi /etc/sysctl.conf
...
...
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_max_tw_buckets = 200000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fastopen = 3
...
...

Reload sysctl settings.

$ sudo sysctl -p

Now, let's dive into ClickHouse-specific optimizations. Open up ClickHouse users config file:

$ sudo -i
$ chmod 666 /etc/clickhouse-server/users.xml
$ vi /etc/clickhouse-server/users.xml

Here are some tweaks that'll improve the performance of ClickHouse:

Increase the maximum memory usage:

<profiles>
   <default>
        <max_memory_usage>10000000000</max_memory_usage>
         ...
         ...
   </default>
 </profiles>

Adjust this value as needed, but don't be stingy - ClickHouse loves memory!

Enable uncompressed cache:

<profiles>
   <default>
      <use_uncompressed_cache>1</use_uncompressed_cache>
      ...
      ...
   </default>
 </profiles>

This trades some disk space for speed and it's worth it!

Adjust the number of processing threads:

<profiles>
   <default>
      <max_threads>number_of_cpu_cores * 2</max_threads>
      ...
      ...
   </default>
 </profiles>

Replace number_of_cpu_cores with the number of CPU cores you have.

Enable background merges:

<profiles>
   <default>
      <background_pool_size>16</background_pool_size>
      ...
      ...
   </default>
</profiles>
$ chmod 400 /etc/clickhouse-server/users.xml

This helps keep data organized without slowing down queries.

About data compression, ClickHouse offers several compression methods, but LZ4 offers the best balance between speed and compression ratio:

$ sudo -i
$ chmod 666 /etc/clickhouse-server/config.xml
$ vi /etc/clickhouse-server/config.xml
...
...
<compression>
   <case>
       <method>lz4</method>
   </case>
</compression>
...
...
$ chmod 400 /etc/clickhouse-server/config.xml

Restart ClickHouse to reload new settings.

$ sudo systemctl restart clickhouse-server

Last but not least, let's optimize ClickHouse table engine. For most use cases, it is recommended to use the MergeTree engine. Here's an example of how to create an optimized table:

$ sudo clickhouse-client --user default --password PassWord@123 --port 9440 --secure --host clickhouse-node-1
clickhouse-node-1 :) CREATE TABLE optimized_table
(
   id UInt32,
   timestamp DateTime,
   name String,
   value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, id)
SETTINGS index_granularity = 8192

The PARTITION BY clause helps ClickHouse manage data more efficiently, while ORDER BY optimizes for common query patterns. The index_granularity setting controls the trade-off between query speed and index size.

Remember, ClickHouse performance optimization is an ongoing process. Monitor ClickHouse performance using system tables like system.query_log and system.metrics, and don't be afraid to experiment with different settings. If you're dealing with really big data, consider using the Distributed engine to spread data across multiple servers.

Conclusion

With ClickHouse now running on Ubuntu 22.04 / 24.04 LTS system, you're well-equipped to handle large-scale data analytics tasks efficiently. The columnar storage engine and powerful query capabilities make it an excellent choice for organizations dealing with massive datasets.

By following this comprehensive guide, you've learned how to properly install, configure, and secure ClickHouse instance on the latest Ubuntu LTS release.

NGINX Rewrite Rules Explained: From Basics to Advanced Techniques

Comments