Your MySQL reports are slow. Here is the fix, one problem at a time.

The problem

You run a monthly revenue report on orders:

SELECT DATE(created_at) AS day, SUM(total) AS revenue
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY day;

On 40 million rows this takes minutes. MySQL reads whole rows from disk. The query competes with live traffic. Users see slow pages.

Why it happens

MySQL is an OLTP database. OLTP means online transaction processing. It stores each row together on disk. This design is good for reading or writing one order by id. It is bad for scanning one column across millions of rows.

ClickHouse is an OLAP database. OLAP means online analytical processing. It stores each column apart on disk. This is called column storage. A revenue query reads only two columns, created_at and total. It skips the rest. This reads far less data.

ClickHouse also sorts and compresses each column. Similar values sit together, so they compress well. ClickHouse claims very high scan speed on analytics queries. Treat all speed numbers here as vendor benchmark claims, not guarantees.

This tutorial targets the ClickHouse 26.x line. ClickHouse uses Year.Month.Patch versions. A minor release ships about once a month. An LTS release ships twice a year. Check the docs for changes after 26.x.

The fix

First, run ClickHouse next to your app with Docker. Create docker-compose.yml:

services:
  app:
    build: .
    depends_on:
      - clickhouse
    environment:
      CLICKHOUSE_HOST: clickhouse
      CLICKHOUSE_PORT: 8123
  clickhouse:
    image: clickhouse/clickhouse-server:26.3
    ports:
      - "8123:8123"   # HTTP interface
      - "9000:9000"   # native TCP
      - "9004:9004"   # MySQL wire protocol
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    volumes:
      - ch_data:/var/lib/clickhouse
volumes:
  ch_data:

Port 8123 is the HTTP interface. Port 9000 is the native TCP interface. Port 9004 speaks the MySQL wire protocol. Most Laravel clients use 8123.

Create your first table. In MySQL you pick InnoDB. In ClickHouse you pick a table engine. The main engine is MergeTree.

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    url        String
)
ENGINE = MergeTree
ORDER BY (event_time);

MergeTree is the base engine for large tables. It sorts data by the ORDER BY key and stores it in parts. A part is one immutable folder of sorted, compressed column files.

Run your first query:

SELECT event_type, count() AS c
FROM events
GROUP BY event_type
ORDER BY c DESC;

┌─event_type─┬─────c─┐
│ page_view  │ 91230 │
│ click      │ 12044 │
│ purchase   │   811 │
└────────────┴───────┘

Now connect Laravel. Install the integration package:

composer require glushkovds/phpclickhouse-laravel

Check Packagist for Laravel 13 and PHP 8.4 support before you pin a version. Set the environment values:

CLICKHOUSE_HOST=clickhouse
CLICKHOUSE_PORT=8123
CLICKHOUSE_DATABASE=default
CLICKHOUSE_USERNAME=default
CLICKHOUSE_PASSWORD=

The package registers a clickhouse connection. You get PhpClickHouseLaravel\BaseModel for models and PhpClickHouseLaravel\Migration for migrations. It wraps the low-level client smi2/phpclickhouse, which uses the HTTP interface with curl.

Define a model:

<?php
namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class Event extends BaseModel
{
    protected $table = 'events';
}

Migrations extend PhpClickHouseLaravel\Migration, live in database/migrations, and run with the normal php artisan migrate. Section 04 shows one.

Run your first query from Laravel:

use App\Models\Clickhouse\Event;

$rows = Event::select(['event_type'])
    ->selectRaw('count() AS c')
    ->groupBy('event_type')
    ->orderBy('c', 'desc')
    ->getRows();

Quiz

The problem

You see this error in your logs:

DB::Exception: Too many parts (300). Merges are processing
significantly slower than inserts. (TOO_MANY_PARTS)

Each insert wrote one part. Thousands of requests wrote thousands of tiny parts. ClickHouse cannot merge them fast enough.

Why it happens

Every insert creates a new part. A part is a sorted, compressed folder on disk. ClickHouse merges small parts into bigger parts in the background. A merge is this background work that combines parts. This is why the engine is called MergeTree.

Merges cost CPU and disk. If you insert faster than ClickHouse can merge, the part count grows. When active parts pass a limit, ClickHouse stops new inserts to protect itself. In MySQL you insert one row at a time with no penalty. In ClickHouse you must insert in batches, because each insert is a part.

The fix

Insert thousands of rows in one statement:

INSERT INTO events (event_time, user_id, event_type, url) VALUES
  ('2026-08-25 10:00:00', 1, 'page_view', '/pricing'),
  ('2026-08-25 10:00:01', 2, 'page_view', '/home');
  -- ... thousands of rows in one statement

In Laravel, never insert one row per request. Buffer events, then flush a batch from a queued job. Push events to a Redis list in the request:

<?php
namespace App\Services;

use Illuminate\Support\Facades\Redis;

class EventBuffer
{
    public const KEY = 'events_buffer';

    public function push(int $userId, string $type, string $url): void
    {
        Redis::rpush(self::KEY, json_encode([
            'event_time' => now('UTC')->format('Y-m-d H:i:s'),
            'user_id'    => $userId,
            'event_type' => $type,
            'url'        => $url,
        ]));
    }
}

Flush in a queued job that runs on a schedule:

<?php
namespace App\Jobs;

use App\Models\Clickhouse\Event;
use App\Services\EventBuffer;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Support\Facades\Redis;

class FlushEventsJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable;

    public function handle(): void
    {
        // Take up to 10,000 items in one round trip, atomically.
        [$raws] = Redis::transaction(function ($tx) {
            $tx->lrange(EventBuffer::KEY, 0, 9999);
            $tx->ltrim(EventBuffer::KEY, 10000, -1);
        });
        if ($raws === []) {
            return;
        }
        $rows = [];
        foreach ($raws as $raw) {
            $row = json_decode($raw, true);
            $rows[] = [$row['event_time'], $row['user_id'], $row['event_type'], $row['url']];
        }
        Event::insertBulk($rows, ['event_time', 'user_id', 'event_type', 'url']);
    }
}

Schedule the job in routes/console.php:

use App\Jobs\FlushEventsJob;
use Illuminate\Support\Facades\Schedule;

Schedule::job(new FlushEventsJob())->everyMinute()->withoutOverlapping();

Read the list with one LRANGE plus one LTRIM inside a Redis transaction. A loop of 10,000 single LPOP calls is 10,000 network round trips. withoutOverlapping() stops two flushes from running at once if a batch takes longer than a minute.

You can also let ClickHouse batch small inserts with async inserts. Set them per query:

INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES ('2026-08-25 10:00:00', 1, 'page_view', '/pricing');

async_insert = 1 tells the server to hold the insert in a memory buffer, then write one part for many inserts. wait_for_async_insert = 1 makes the client wait until the data reaches disk, so you still get an error if the write fails. Use wait_for_async_insert = 0 only if you accept a small risk of data loss on a crash.

Since 26.3 LTS, async inserts are on by default. Batching in your own job still gives you the most control.

Quiz

The problem

You run the classic mutation:

ALTER TABLE orders_analytics UPDATE status = 'refunded' WHERE order_id = 42;

The statement returns fast, but the row does not change at once. You check system.mutations and see the work is still running in the background:

SELECT mutation_id, command, is_done FROM system.mutations
WHERE table = 'orders_analytics';

┌─mutation_id─┬─command──────────────────────────────────┬─is_done─┐
│ mutation_3  │ UPDATE status = 'refunded' WHERE order_id = 42 │       0 │
└─────────────┴──────────────────────────────────────────┴─────────┘

On a large table this rewrites whole column files and takes a long time.

Why it happens

ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE are mutations. A mutation rewrites every affected part. ClickHouse stores columns in immutable parts, so it cannot change a value in place. It must write new parts. This is heavy and asynchronous.

ClickHouse also has lightweight statements. Lightweight DELETE is generally available (GA). It marks rows as deleted and removes them at the next merge. Lightweight UPDATE (UPDATE ... SET ... WHERE) is Beta. It writes a small patch part instead of whole columns. Test it on your own data before you rely on it.

Even with these tools, ClickHouse is built for append, not for row-by-row edits. The best design avoids updates.

The fix

Prefer append plus ReplacingMergeTree. Instead of editing a row, insert a new version of it. Give the table a version column and let ClickHouse keep the latest version during merges:

CREATE TABLE orders_analytics
(
    order_id   UInt64,
    user_id    UInt64,
    status     LowCardinality(String),
    total      Decimal(12, 2),
    updated_at DateTime,
    version    UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (order_id);

To change the status, append a row with a higher version:

INSERT INTO orders_analytics VALUES
  (42, 7, 'refunded', 19.99, now(), 2);

For a real delete of a few rows, use the lightweight DELETE:

DELETE FROM orders_analytics WHERE order_id = 42;

The Beta lightweight update looks like this:

-- Beta in 26.x. Check the docs before production use.
UPDATE orders_analytics SET status = 'refunded' WHERE order_id = 42;

In Laravel, stop calling update(). Insert a new version instead:

<?php
namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class OrderAnalytics extends BaseModel
{
    protected $table = 'orders_analytics';
}

use App\Models\Clickhouse\OrderAnalytics;

OrderAnalytics::insertBulk([
    [42, 7, 'refunded', '19.99', now('UTC')->format('Y-m-d H:i:s'), 2],
], ['order_id', 'user_id', 'status', 'total', 'updated_at', 'version']);

Quiz

The problem

SELECT order_id, status, version FROM orders_analytics WHERE order_id = 42;

┌─order_id─┬─status───┬─version─┐
│       42 │ paid     │       1 │
│       42 │ refunded │       2 │
└──────────┴──────────┴─────────┘

Both versions are present. In MySQL a PRIMARY KEY (order_id) rejects the second row. ClickHouse kept both.

Why it happens

The ClickHouse primary key is not a uniqueness constraint. It is a sparse index. The index stores one entry per granule, not one entry per row. A granule is a block of 8,192 rows by default. The index maps the first key value of each granule to its position, so ClickHouse can skip granules that cannot match a WHERE filter.

Because the index is sparse and does not enforce uniqueness, ClickHouse never checks for duplicate keys on insert. It accepts every row.

ReplacingMergeTree(version) removes duplicates during background merges. It keeps the row with the highest version for each ORDER BY key. Merges happen on the schedule of ClickHouse. Until a merge runs, both rows are visible. This is eventual, not immediate.

The fix

To get a correct answer before merges finish, force deduplication at read time with FINAL:

SELECT order_id, status, version
FROM orders_analytics FINAL
WHERE order_id = 42;

┌─order_id─┬─status───┬─version─┐
│       42 │ refunded │       2 │
└──────────┴──────────┴─────────┘

FINAL applies merge logic during the query. It is correct but it costs CPU.

For aggregations, argMax is often faster than FINAL. It picks the value tied to the highest version inside a GROUP BY:

SELECT
    order_id,
    argMax(status, version) AS status,
    argMax(total,  version) AS total
FROM orders_analytics
GROUP BY order_id;

argMax(status, version) returns the status from the row with the largest version. It deduplicates and aggregates in one pass.

You can also stop exact duplicate batches at insert time. ClickHouse keeps a hash of each inserted block. If your job retries the same batch, the second insert is dropped:

CREATE TABLE orders_analytics
(
    order_id   UInt64,
    user_id    UInt64,
    status     LowCardinality(String),
    total      Decimal(12, 2),
    updated_at DateTime,
    version    UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (order_id)
SETTINGS non_replicated_deduplication_window = 100;

In Laravel, use FINAL for a single record and argMax for reports:

use App\Models\Clickhouse\OrderAnalytics;

$order = OrderAnalytics::select(['order_id', 'status', 'version'])
    ->from('orders_analytics FINAL')
    ->where('order_id', 42)
    ->getRows();

$report = OrderAnalytics::select(['order_id'])
    ->selectRaw('argMax(status, version) AS status')
    ->selectRaw('argMax(total, version) AS total')
    ->groupBy('order_id')
    ->getRows();

Quiz

The problem

SELECT count() FROM events
WHERE user_id = 7 AND event_time >= '2026-08-01';

1 row in set. Elapsed: 4.812 sec. Processed 210.30 million rows

This scans almost every granule. EXPLAIN indexes = 1 shows why:

EXPLAIN indexes = 1
SELECT count() FROM events WHERE user_id = 7;

PrimaryKey
  Keys: id
  Condition: true
  Parts: 24/24
  Granules: 25672/25672

The primary key id cannot prune granules for a user_id filter.

Why it happens

ClickHouse uses the ORDER BY key to build the sparse primary index. It can skip granules only when your WHERE filter matches the leading columns of that key. If the key is id but you filter on user_id, the index cannot help. ClickHouse reads everything.

Design the key from your WHERE patterns. Put the column that almost every query filters on first. Cardinality means the number of distinct values in a column. When two columns are both filtered in most queries, put the lower-cardinality one first: it groups many rows under each index entry, and the higher-cardinality one then narrows inside those groups. Column order also shapes compression, so rows with equal leading values sit together.

A high-cardinality leading column is fine when nearly every query filters on it. user_id has millions of values, but a filter on it skips almost every granule, which is the point. The mistake is to lead with a column you rarely filter on, such as id. The rule "low cardinality first" applies to the order between filtered columns. It does not mean "put a low-cardinality column first even if you never filter on it".

The fix

Design the key from real queries. Most queries filter by user_id, then narrow by time:

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    url        String
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);

Now a filter on user_id skips all granules outside that user. A range on event_time then narrows further, because rows are sorted by time within each user.

PrimaryKey
  Keys: user_id, event_time
  Parts: 3/24
  Granules: 14/25672

Notice what you gave up. The daily revenue report from section 00 filters on time only. With (user_id, event_time) there is no match on the leading column, so that query still reads every granule. ClickHouse sorts a table one way. That is not a bug in the key. Time-only dashboards get their own fix in section 10 with materialized views and projections, and you will see the full scan again there.

You can set a PRIMARY KEY that is a prefix of the ORDER BY key. This keeps the index small while sorting on more columns:

ENGINE = MergeTree
PRIMARY KEY (user_id)
ORDER BY (user_id, event_time, event_type)

The PRIMARY KEY must be a prefix of the ORDER BY. The extra ORDER BY columns help sorting and compression only.

Check the parts and their granules with system.parts:

SELECT name, rows, marks, round(rows / marks) AS rows_per_granule
FROM system.parts
WHERE table = 'events' AND active;

You cannot change ORDER BY on a table that already holds data. Every later section that changes the events definition uses this recipe. Create the new table, copy the rows, swap the names:

-- 1. New table with the new key. Same columns.
CREATE TABLE events_new
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    url        String
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);

-- 2. Copy old rows in date ranges. One INSERT per month keeps memory flat.
INSERT INTO events_new SELECT * FROM events
WHERE event_time >= '2026-07-01' AND event_time < '2026-08-01';
-- ... repeat per month up to a cutoff you choose

-- 3. Swap names in one atomic step, then drop the old table.
EXCHANGE TABLES events AND events_new;
DROP TABLE events_new;

Pause the flush job while you copy, or copy up to a cutoff time, swap, and then copy the rows after the cutoff. Otherwise rows inserted during the copy land in the old table and are lost at the drop. EXCHANGE TABLES needs an Atomic database, which is the default.

In Laravel, write the migration:

<?php
use PhpClickHouseLaravel\Migration;

return new class extends Migration
{
    public function up(): void
    {
        static::write('
            CREATE TABLE events
            (
                event_time DateTime,
                user_id    UInt64,
                event_type LowCardinality(String),
                url        String
            )
            ENGINE = MergeTree
            ORDER BY (user_id, event_time)
        ');
    }

    public function down(): void
    {
        static::write('DROP TABLE events');
    }
};

Quiz

The problem

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    url        String
)
ENGINE = MergeTree
PARTITION BY user_id           -- one partition per user
ORDER BY (user_id, event_time);

DB::Exception: Too many partitions for single INSERT block (more than 100).
The limit is controlled by 'max_partitions_per_insert_block' setting.

With one partition per user, ClickHouse creates a part for each user in every batch. Merges cannot keep up. TOO_MANY_PARTS comes back. PARTITION BY toHour(event_time) also creates far too many partitions.

Why it happens

A partition is a group of parts that share the same value of the partition expression. ClickHouse never merges parts across partitions. A high-cardinality partition key makes millions of small partitions, each with its own parts. This raises merge pressure and metadata cost.

Partitioning is for data management, not for query speed. The sort key gives you speed. Partitions give you cheap bulk delete and data lifecycle control. Partition by month for almost everything and keep the partition count low.

The fix

Partition by month. Apply it with the copy-and-exchange recipe from section 04, because the partition key cannot change in place either:

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    url        String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)   -- one partition per month
ORDER BY (user_id, event_time);

toYYYYMM(event_time) makes one partition per month. Keep active partitions under about 1,000 per table.

Use TTL to expire old data. TTL means time to live. It is a rule that deletes rows after a set age. When the rule matches the partition key, ClickHouse drops whole parts instead of rewriting rows:

ALTER TABLE events MODIFY TTL event_time + INTERVAL 18 MONTH DELETE;
ALTER TABLE events MODIFY SETTING ttl_only_drop_parts = 1;

ttl_only_drop_parts = 1 tells ClickHouse to wait until every row in a part expires, then drop the whole part.

Drop a month by hand in milliseconds:

ALTER TABLE events DROP PARTITION '202501';

List partitions and their sizes:

SELECT partition, sum(rows) AS rows, formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition
ORDER BY partition;

In Laravel, put the partition and TTL in the migration:

<?php
use PhpClickHouseLaravel\Migration;

return new class extends Migration
{
    public function up(): void
    {
        static::write('
            CREATE TABLE events
            (
                event_time DateTime,
                user_id    UInt64,
                event_type LowCardinality(String),
                url        String
            )
            ENGINE = MergeTree
            PARTITION BY toYYYYMM(event_time)
            ORDER BY (user_id, event_time)
            TTL event_time + INTERVAL 18 MONTH DELETE
            SETTINGS ttl_only_drop_parts = 1
        ');
    }

    public function down(): void
    {
        static::write('DROP TABLE events');
    }
};

Quiz

The problem

SELECT u.country, count() AS events
FROM users_raw AS u
JOIN events AS e ON e.user_id = u.user_id
WHERE e.event_time >= today()
GROUP BY u.country;

DB::Exception: Memory limit (for query) exceeded: would use 9.31 GiB
(attempt to allocate chunk of 4194304 bytes), maximum: 9.31 GiB.
(MEMORY_LIMIT_EXCEEDED)

The events table sits on the right side of the join. The join tries to hold it in memory.

Why it happens

The default join algorithm builds a hash table in memory from the right-hand table. It then streams the left-hand table through it. The size of the right table sets the memory cost. If you put the large table on the right, you use a lot of memory.

Since ClickHouse 24.12 the planner tries to put the smaller table on the right for two-table joins. Still learn the rule, because you can guide the planner and reason about the plan.

For a lookup like user attributes, a dictionary is better than a join. A dictionary is an in-memory key-value store that ClickHouse refreshes from a source table on a schedule. A lookup against a dictionary is very fast.

The fix

First, put the small table on the right when you write a join by hand:

SELECT u.country, count() AS events
FROM events AS e
JOIN users_raw AS u ON e.user_id = u.user_id   -- users_raw is smaller
WHERE e.event_time >= today()
GROUP BY u.country;

Better, create a dictionary for user attributes:

CREATE TABLE users_raw
(
    user_id UInt64,
    country String,
    plan    String
)
ENGINE = MergeTree
ORDER BY (user_id);

CREATE DICTIONARY users_dict
(
    user_id UInt64,
    country String,
    plan    String
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(TABLE 'users_raw'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

Look up values with dictGet:

SELECT
    dictGet('users_dict', 'country', user_id) AS country,
    count() AS events
FROM events
WHERE event_time >= today()
GROUP BY country;

Beware the one-to-many trap. A dictionary returns exactly one value per key. Do not use it where a key maps to many rows, such as one user with many orders.

Or denormalize at insert time. Write country and plan into events in your buffer service, so you never join at read time:

ALTER TABLE events
    ADD COLUMN country LowCardinality(String),
    ADD COLUMN plan    LowCardinality(String);

<?php
namespace App\Services;

use App\Models\User;
use Illuminate\Support\Facades\Redis;

class EventBuffer
{
    public const KEY = 'events_buffer';

    public function push(User $user, string $type, string $url): void
    {
        Redis::rpush(self::KEY, json_encode([
            'event_time' => now('UTC')->format('Y-m-d H:i:s'),
            'user_id'    => $user->id,
            'event_type' => $type,
            'url'        => $url,
            'country'    => $user->country,   // denormalized
            'plan'       => $user->plan,      // denormalized
        ]));
    }
}

If you must join two large tables, pick an algorithm that fits your memory. Set join_algorithm:

SELECT count()
FROM events AS e
JOIN orders_analytics AS o ON e.user_id = o.user_id
SETTINGS join_algorithm = 'grace_hash';

grace_hash spills to disk instead of failing. full_sorting_merge fits when both sides are sorted on the join key.

Quiz

The problem

CREATE TABLE events
(
    event_time DateTime64(9),
    user_id    UInt64,
    event_type Nullable(String),
    url        Nullable(String),
    country    Nullable(String),
    plan       Nullable(String),
    revenue    Nullable(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time);

SELECT formatReadableSize(sum(bytes_on_disk)) FROM system.parts
WHERE table = 'events' AND active;

┌─size──────┐
│ 41.20 GiB │
└───────────┘

DateTime64(9) stores nanoseconds you never use. Every Nullable column adds a hidden mask column. revenue as a string cannot be summed without a cast. Only user_id escaped, because ClickHouse refuses a Nullable column in the sort key unless you set allow_nullable_key = 1. Do not set it.

Why it happens

ClickHouse stores each column apart and compresses it. The type controls the size and the speed. A small, fixed type reads and compresses better than a wide or nullable one.

Nullable adds a separate map that marks null rows. This costs storage and slows reads. Store a default value instead of null where you can.

LowCardinality(String) replaces repeated strings with a small dictionary of ids. It helps below about 10,000 distinct values. Enum stores a fixed set of labels as integers. Decimal stores money exactly. Date stores a day. DateTime stores seconds. DateTime64(3) stores milliseconds. Pick the smallest precision you need.

The JSON type stores flexible objects with typed paths. It is GA since 25.3. The old Object('json') type is removed. Array stores a list of values. Map stores keys and values of one type.

The fix

Choose tight types and add codecs. This is a new table definition, so apply it with the copy-and-exchange recipe from section 04:

CREATE TABLE events
(
    event_time DateTime CODEC(DoubleDelta, ZSTD(1)),
    user_id    UInt64   CODEC(Delta, LZ4),
    event_type Enum8('page_view' = 1, 'click' = 2, 'purchase' = 3),
    url        String,
    country    LowCardinality(String),
    plan       LowCardinality(String),
    revenue    Decimal(12, 2),
    tags       Array(LowCardinality(String)),
    attributes Map(String, String),
    payload    JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time);

A codec transforms a column before compression. Delta and DoubleDelta shrink values that grow in steps, such as ids and timestamps. Gorilla fits floats that change slowly. ZSTD is a strong general compressor. LZ4 is fast to decode. Chain them as CODEC(encoder, compressor).

After the change the same data is much smaller:

┌─size──────┐
│ 6.84 GiB  │
└───────────┘

Read a JSON path by name:

SELECT payload.utm_source AS source, count()
FROM events
GROUP BY source;

In Laravel, cast scalar values before insert so types match. insertBulk is fine for scalars and, with the InsertArray helper, for Array columns:

use PhpClickHouseLaravel\InsertArray;

$rows[] = [
    $row['event_time'],
    (int) $row['user_id'],
    $row['event_type'],              // one of the Enum labels
    $row['url'],
    $row['country'],
    $row['plan'],
    (string) $row['revenue'],        // Decimal sent as string keeps precision
    new InsertArray($row['tags']),   // Array(LowCardinality(String))
];
Event::insertBulk($rows, [
    'event_time', 'user_id', 'event_type', 'url', 'country', 'plan',
    'revenue', 'tags',
]);

For Map and JSON columns, do not rely on the builder to guess a literal. Send the batch as JSONEachRow through the low-level client. ClickHouse parses each line into the column types itself, so a PHP assoc array becomes a Map and a nested object becomes JSON:

use Illuminate\Support\Facades\DB;

$lines = [];
foreach ($rows as $row) {
    $lines[] = json_encode([
        'event_time' => $row['event_time'],
        'user_id'    => (int) $row['user_id'],
        'event_type' => $row['event_type'],
        'url'        => $row['url'],
        'country'    => $row['country'],
        'plan'       => $row['plan'],
        'revenue'    => (string) $row['revenue'],
        'tags'       => $row['tags'],        // ["a","b"]      -> Array
        'attributes' => $row['attributes'],  // {"k":"v"}      -> Map
        'payload'    => $row['payload'],     // nested object  -> JSON
    ]);
}

DB::connection('clickhouse')->getClient()->write(
    "INSERT INTO events FORMAT JSONEachRow\n" . implode("\n", $lines)
);

Send dates as 'Y-m-d H:i:s' strings in UTC and Decimal as strings. Numbers wider than 53 bits, such as UInt64, and all Decimal values come back from the HTTP interface as strings. Cast them in PHP before you do math.

Quiz

The problem

SELECT * FROM events FINAL WHERE user_id = 7;

12 rows in set. Elapsed: 1.902 sec. Processed 3.12 million rows, 2.41 GB

SELECT * reads every column, including payload and attributes that the dashboard ignores. FINAL forces merge logic on a plain MergeTree that has no duplicates to resolve.

Why it happens

ClickHouse reads only the columns you name. This is column pruning. With SELECT * you lose the main benefit of column storage, because you read all columns from disk.

FINAL is right when you must see the deduplicated state of a ReplacingMergeTree now. It is wrong as a default, because it adds merge work to every query. On an append-only MergeTree with no duplicates, FINAL gives no benefit and costs time.

The fix

Name only the columns you use:

SELECT event_time, event_type
FROM events
WHERE user_id = 7 AND event_time >= today();

12 rows in set. Elapsed: 0.014 sec. Processed 16.38 thousand rows, 131 KB

Use FINAL only on a ReplacingMergeTree when you need the current state:

SELECT order_id, status FROM orders_analytics FINAL WHERE order_id = 42;

Tune heavy queries with settings:

SELECT event_type, count()
FROM events
GROUP BY event_type
SETTINGS max_threads = 8, max_memory_usage = 4000000000;

max_threads sets how many threads run the query. max_memory_usage caps memory per query in bytes.

Find slow queries in system.query_log. This table records every finished query:

SELECT query, read_rows, formatReadableSize(read_bytes) AS read, query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

In Laravel, list columns in the builder and pass settings for heavy reports:

use App\Models\Clickhouse\Event;

$rows = Event::select(['event_time', 'event_type'])
    ->where('user_id', 7)
    ->where('event_time', '>=', now('UTC')->startOfDay()->format('Y-m-d H:i:s'))
    ->getRows();

$report = Event::select(['event_type'])
    ->selectRaw('count() AS c')
    ->groupBy('event_type')
    ->settings(['max_threads' => 8, 'max_memory_usage' => 4000000000])
    ->getRows();

Quiz

The problem

SELECT toStartOfDay(event_time) AS day, sum(revenue) AS revenue
FROM events
GROUP BY day;

┌─────────────────day─┬─revenue─┐
│ 2026-08-23 00:00:00 │ 1840.00 │
│ 2026-08-24 00:00:00 │ 2210.50 │   ← finance says 2019.50
└─────────────────────┴─────────┘

Your app wrote event_time in Paris local time. The server groups by its own zone. A sale at 01:00 Paris time counts on the wrong day.

Why it happens

A DateTime value maps to a moment in time. If you store local times without a fixed zone, the same clock value means different moments in different places. Grouping by day then depends on the server zone, which is fragile.

Store every timestamp in UTC. UTC is one fixed zone with no daylight saving. Convert to the zone of the user only at the edge, when you show a value or when you group for a report.

The fix

Type the column as UTC. Changing a column type on an existing table is a mutation that rewrites the column, so apply this with the copy-and-exchange recipe from section 04 as well:

CREATE TABLE events
(
    event_time DateTime('UTC') CODEC(DoubleDelta, ZSTD(1)),
    user_id    UInt64   CODEC(Delta, LZ4),
    event_type Enum8('page_view' = 1, 'click' = 2, 'purchase' = 3),
    url        String,
    country    LowCardinality(String),
    plan       LowCardinality(String),
    revenue    Decimal(12, 2),
    tags       Array(LowCardinality(String)),
    attributes Map(String, String),
    payload    JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time);

Group by day in a chosen zone with the time zone argument:

SELECT
    toStartOfDay(event_time, 'Europe/Paris') AS day,
    sum(revenue) AS revenue
FROM events
GROUP BY day
ORDER BY day;

toStartOfDay(event_time, 'Europe/Paris') converts to Paris time, then truncates to the day. The stored value stays UTC.

In Laravel, set 'timezone' => 'UTC' in config/app.php. Cast to UTC before insert:

'event_time' => now('UTC')->format('Y-m-d H:i:s'),

Convert for display in the view layer:

$day = \Carbon\Carbon::parse($row['day'], 'Europe/Paris');
echo $day->format('d.m.Y');

Pass the zone of the user into the report. The zone is user input, so it must not be pasted into the SQL string. Bind it:

use Illuminate\Support\Facades\DB;

$tz = auth()->user()->timezone ?? 'UTC';
if (! in_array($tz, timezone_identifiers_list(), true)) {
    $tz = 'UTC';
}

$daily = DB::connection('clickhouse')->getClient()->select(
    'SELECT toStartOfDay(event_time, :tz) AS day, sum(revenue) AS revenue
     FROM events
     GROUP BY day
     ORDER BY day',
    ['tz' => $tz]
)->rows();

Pass user input safely

ClickHouse has no prepared statements over HTTP, but it has two safe paths, and the package gives you both.

The query builder escapes values you pass to where(), whereIn(), and friends. Raw fragments are your responsibility. Keep selectRaw, whereRaw, and orderByRaw for fixed SQL only:

// Safe: the builder quotes $userId.
Event::select(['event_time', 'url'])->where('user_id', $userId)->getRows();

// Unsafe: string interpolation into raw SQL.
Event::whereRaw("user_id = {$userId}")->getRows();

For raw SQL, use the client bindings. Each :name is quoted and escaped by the client before the query is sent:

$db = DB::connection('clickhouse')->getClient();

$rows = $db->select(
    'SELECT event_type, count() AS c
     FROM events
     WHERE user_id = :user_id AND event_time >= :since
     GROUP BY event_type',
    ['user_id' => $userId, 'since' => $since->utc()->format('Y-m-d H:i:s')]
)->rows();

ClickHouse also has server-side query parameters written as {name:Type} and sent as param_name on the HTTP request. They are typed and never touch the SQL text. Use them when you talk to the HTTP interface directly without the package:

SELECT event_type, count() AS c
FROM events
WHERE user_id = {user_id:UInt64} AND event_time >= {since:DateTime}
GROUP BY event_type;
-- HTTP: ?param_user_id=7&param_since=2026-08-01%2000:00:00

Identifiers cannot be bound. If a user chooses a column or a table, map the choice to an allow-list in PHP and never place the raw string into SQL.

Quiz

The problem

SELECT toDate(event_time, 'UTC') AS day, count() AS views, uniq(user_id) AS users
FROM events
GROUP BY day;

540 rows in set. Elapsed: 2.310 sec. Processed 412.00 million rows

Every page load scans the raw events table. As the table grows, each load reads more and gets slower.

Why it happens

Raw aggregation repeats the same scan. You can compute the result once as data arrives, then read a small table. This is what materialized views do.

An incremental materialized view is a trigger that runs a query on each insert into the source table and writes the result into a target table. AggregatingMergeTree is an engine that stores partial aggregate states and combines them at merge time. The -State combinator writes the state. The -Merge combinator finalizes it at read time. SummingMergeTree is a simpler engine that sums numeric columns with the same key.

A refreshable materialized view runs a full query on a schedule and replaces its target in one step. It fits daily or hourly summaries and full-scan rollups.

A projection is an alternate ordering or pre-aggregation stored inside the same table. ClickHouse picks it for a matching query without a separate table.

The fix

Create the target and the incremental view:

CREATE TABLE events_daily
(
    day   Date,
    views AggregateFunction(count),
    users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (day);

CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
SELECT
    toDate(event_time, 'UTC') AS day,
    countState()              AS views,
    uniqState(user_id)        AS users
FROM events
GROUP BY day;

uniq is an approximate count. It uses a fixed-size sketch, so its memory stays small, and its error is usually well under 2 percent. That is fine for a page-views chart. It is not fine when finance compares your number to theirs. Use uniqExact for exact numbers and accept that its state grows with the number of distinct values. uniqCombined64 sits between the two. The -State and -Merge combinators work with all of them:

-- exact, larger state
users AggregateFunction(uniqExact, UInt64)
-- write: uniqExactState(user_id)   read: uniqExactMerge(users)

Read with the -Merge combinator and a GROUP BY:

SELECT day, countMerge(views) AS views, uniqMerge(users) AS users
FROM events_daily
GROUP BY day
ORDER BY day;

540 rows in set. Elapsed: 0.009 sec. Processed 1.62 thousand rows

The view only sees rows inserted after it exists. You must backfill the old rows once. A naive INSERT ... SELECT from the whole table double counts every row that arrives while the backfill runs, because the view also sees those rows. Use a cutoff. Create the view so it only handles rows from the cutoff onward, then backfill everything before the cutoff:

-- 1. The view handles rows from the cutoff onward.
CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
SELECT
    toDate(event_time, 'UTC') AS day,
    countState()              AS views,
    uniqState(user_id)        AS users
FROM events
WHERE event_time >= '2026-09-01 00:00:00'   -- cutoff, a little in the future
GROUP BY day;

-- 2. Backfill everything before the cutoff, once.
INSERT INTO events_daily
SELECT toDate(event_time, 'UTC') AS day, countState(), uniqState(user_id)
FROM events
WHERE event_time < '2026-09-01 00:00:00'
GROUP BY day;

Pick a cutoff a few minutes in the future so no row can be on both sides. Once the cutoff has passed, the WHERE in the view costs nothing and you can leave it. If your ClickHouse version supports atomic POPULATE for a view with a TO table, that is the same idea done for you under a short lock. Check the docs for your version before you rely on it.

For a plain sum, SummingMergeTree is enough:

CREATE TABLE revenue_daily
(
    day     Date,
    revenue Decimal(38, 2)
)
ENGINE = SummingMergeTree
ORDER BY (day);

CREATE MATERIALIZED VIEW revenue_daily_mv TO revenue_daily AS
SELECT toDate(event_time, 'UTC') AS day, sum(revenue) AS revenue
FROM events
GROUP BY day;

Always GROUP BY and sum() at read time, because parts may not be merged yet.

For a scheduled full rollup, use a refreshable view:

CREATE MATERIALIZED VIEW events_weekly
REFRESH EVERY 1 DAY OFFSET 1 HOUR
ENGINE = MergeTree ORDER BY (week_start)
AS SELECT toMonday(day) AS week_start, countMerge(views) AS views
FROM events_daily
GROUP BY week_start;

REFRESH EVERY sets the schedule. DEPENDS ON other_view makes one view wait for another. SYSTEM REFRESH VIEW events_weekly forces a refresh now.

Add a projection for a second common sort order:

ALTER TABLE events ADD PROJECTION by_type
(
    SELECT event_type, event_time, user_id
    ORDER BY (event_type, event_time)
);
ALTER TABLE events MATERIALIZE PROJECTION by_type;

In Laravel, read the small rollup table:

<?php
namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class EventDaily extends BaseModel
{
    protected $table = 'events_daily';
}

use App\Models\Clickhouse\EventDaily;

$daily = EventDaily::select(['day'])
    ->selectRaw('countMerge(views) AS views')
    ->selectRaw('uniqMerge(users) AS users')
    ->groupBy('day')
    ->orderBy('day')
    ->getRows();

Quiz

The problem

ALTER TABLE events ADD INDEX idx_url url TYPE bloom_filter GRANULARITY 1;
ALTER TABLE events ADD INDEX idx_country country TYPE set(100) GRANULARITY 1;
ALTER TABLE events ADD INDEX idx_revenue revenue TYPE minmax GRANULARITY 1;
-- ... one index per column

EXPLAIN indexes = 1
SELECT count() FROM events WHERE revenue > 100;

Skip
  Name: idx_revenue
  Type: minmax
  Parts: 24/24
  Granules: 25102/25672

The index skipped almost nothing. Each index still adds write work on every insert.

Why it happens

A skip index stores a small summary per block of granules, such as the lowest and highest value, a set of values, or a bloom filter. At query time ClickHouse reads the summary and skips blocks that cannot match.

A skip index helps only when the column correlates with the primary key. If the values are spread evenly across granules, most blocks can match, so nothing is skipped. revenue is spread across every user and every time. The minmax summary of each block covers the whole range.

Index types serve different filters. minmax fits range filters on columns that change slowly along the sort order. set(N) fits equality on a column with few values per block. bloom_filter fits equality and IN on a column with many distinct values.

The fix

Drop the indexes that do not prune:

ALTER TABLE events DROP INDEX idx_revenue;
ALTER TABLE events DROP INDEX idx_country;

Keep a skip index only where it prunes granules. A filter on url for one user is a good case, because one user visits few pages:

ALTER TABLE events ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 4;
ALTER TABLE events MATERIALIZE INDEX idx_url;

EXPLAIN indexes = 1
SELECT count() FROM events WHERE user_id = 7 AND url = '/pricing';

Skip
  Name: idx_url
  Type: bloom_filter
  Parts: 3/3
  Granules: 2/14

Follow the tool order. Reach for tools in this sequence:

1. ORDER BY design. Fix the sort key first.
2. Projections. Add an alternate ordering or pre-aggregation.
3. Materialized views. Pre-compute rollups.
4. Skip indexes. Use them last, for a column that correlates with the key.

There is no app code change here. Put the index in a migration:

<?php
use PhpClickHouseLaravel\Migration;

return new class extends Migration
{
    public function up(): void
    {
        static::write('ALTER TABLE events ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 4');
        static::write('ALTER TABLE events MATERIALIZE INDEX idx_url');
    }

    public function down(): void
    {
        static::write('ALTER TABLE events DROP INDEX idx_url');
    }
};

Quiz

The problem

-- MySQL
SELECT count() FROM orders WHERE created_at >= '2026-08-01';
-- 184,203

-- ClickHouse
SELECT count() FROM orders_analytics FINAL WHERE updated_at >= '2026-08-01';
-- 181,977

A new order in MySQL sometimes never appears in orders_analytics. A refund updates MySQL but not ClickHouse. Your dashboards fall behind the real data.

Why it happens

Two databases need a defined sync path. There are three common options.

App-level events. Your Laravel code writes to MySQL and also queues a ClickHouse insert. This is simple and you already do it for events. It misses changes that bypass the app, such as manual SQL or admin tools.

ClickPipes MySQL CDC. CDC means change data capture. It reads the MySQL binary log and streams every change. ClickPipes is the managed ingestion service in ClickHouse Cloud. Its MySQL connector is in Beta and is built on PeerDB.

Debezium or PeerDB, self-hosted. These tools read the binlog and write to ClickHouse without ClickHouse Cloud. They cost more to run.

The old MaterializedMySQL database engine was removed in 24.12. Do not plan around it. One CDC gap matters. A cascading delete in MySQL does not appear as row deletes in the binlog, so a CDC stream can miss those deletes.

The fix

For an app that owns all writes, dual-write from an observer. Write to MySQL, then queue the ClickHouse insert after the transaction commits:

<?php
namespace App\Observers;

use App\Jobs\SyncOrderToClickHouse;
use App\Models\Order;

class OrderObserver
{
    public bool $afterCommit = true;

    public function saved(Order $order): void
    {
        SyncOrderToClickHouse::dispatch($order->id);
    }
}

<?php
namespace App\Jobs;

use App\Models\Clickhouse\OrderAnalytics;
use App\Models\Order;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;

class SyncOrderToClickHouse implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable;

    public int $tries = 3;

    public function __construct(public int $orderId) {}

    public function handle(): void
    {
        $o = Order::findOrFail($this->orderId);
        OrderAnalytics::insertBulk([[
            $o->id,
            $o->user_id,
            $o->status,
            (string) $o->total,
            $o->updated_at->utc()->format('Y-m-d H:i:s'),
            (int) $o->updated_at->format('Uu'),   // microseconds, no same-second ties
        ]], ['order_id', 'user_id', 'status', 'total', 'updated_at', 'version']);
    }
}

Model the target as ReplacingMergeTree(version), so retries and reorders resolve to the latest version. Add a nightly reconcile command that compares counts and re-sends missing ids:

<?php
namespace App\Console\Commands;

use App\Jobs\SyncOrderToClickHouse;
use App\Models\Clickhouse\OrderAnalytics;
use App\Models\Order;
use Illuminate\Console\Command;

class ReconcileOrders extends Command
{
    protected $signature = 'clickhouse:reconcile-orders';

    public function handle(): void
    {
        $mysqlIds = Order::where('updated_at', '>=', now()->subDay())->pluck('id');
        $chIds = collect(OrderAnalytics::select(['order_id'])
            ->from('orders_analytics FINAL')
            ->whereIn('order_id', $mysqlIds->all())
            ->getRows())->pluck('order_id');

        foreach ($mysqlIds->diff($chIds) as $id) {
            SyncOrderToClickHouse::dispatch($id);
        }
    }
}

For a hands-off pipeline on ClickHouse Cloud, use ClickPipes MySQL CDC. It handles the first full copy and then continuous replication from RDS, Aurora, Cloud SQL, or your own MySQL. It is Beta today. Enable binlog_format = ROW and GTID mode in MySQL first.

For self-hosted, run Debezium or PeerDB into ClickHouse. Both write into ReplacingMergeTree tables with a version column.

Handle cascading deletes yourself. Because they are not in the binlog, delete child rows in ClickHouse from application logic or the reconcile job.

Quiz

The problem

A single node has no redundancy. If it fails, you lose queries and risk data. You have no backups and no alerts. You cannot ship like this.

Why it happens

Production needs more than a working query. You need copies of data, a coordination service, tested restores, and metrics you watch.

ReplicatedMergeTree is the engine family that keeps identical copies of each part on several servers. Every MergeTree variant has a Replicated form, such as ReplicatedReplacingMergeTree. Replication needs a coordination service. Keeper is the ClickHouse coordination service. It speaks the ZooKeeper protocol and ships with ClickHouse.

The fix

First, choose Cloud or self-hosted. ClickHouse Cloud runs the servers, storage, and Keeper for you and offers ClickPipes. Self-hosted gives full control at the cost of operations. In Cloud, replication is on by default and you use the SharedMergeTree family without extra setup.

For self-hosted redundancy, use ReplicatedMergeTree:

CREATE TABLE events ON CLUSTER my_cluster
(
    event_time DateTime('UTC') CODEC(DoubleDelta, ZSTD(1)),
    user_id    UInt64   CODEC(Delta, LZ4),
    event_type Enum8('page_view' = 1, 'click' = 2, 'purchase' = 3),
    url        String,
    country    LowCardinality(String),
    plan       LowCardinality(String),
    revenue    Decimal(12, 2),
    tags       Array(LowCardinality(String)),
    attributes Map(String, String),
    payload    JSON
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events', '{replica}'
)
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time)
TTL event_time + INTERVAL 18 MONTH DELETE
SETTINGS ttl_only_drop_parts = 1;

Run 3 Keeper nodes for a quorum. Run at least 2 replicas per shard.

Back up to object storage:

BACKUP DATABASE default TO S3(
    'https://s3.amazonaws.com/my-bucket/clickhouse/2026-08-25', 'KEY', 'SECRET'
);

Restore with RESTORE DATABASE default FROM S3(...). The open-source clickhouse-backup tool also works. Run a monthly restore drill on a staging server to confirm your recovery works.

Monitor with system tables:

SELECT table, absolute_delay FROM system.replicas WHERE absolute_delay > 300;
SELECT table, count() AS parts FROM system.parts WHERE active GROUP BY table;
SELECT query, query_duration_ms FROM system.query_log
WHERE type = 'ExceptionWhileProcessing' AND event_time >= now() - INTERVAL 1 HOUR;
SELECT name, formatReadableSize(free_space) FROM system.disks;

Alert on replication delay, part counts, failed queries, and disk use.

Set common server settings in a profile:

CREATE SETTINGS PROFILE laravel_app SETTINGS
    max_memory_usage = 8000000000,
    max_execution_time = 30,
    max_threads = 8;

CREATE USER laravel IDENTIFIED BY 'change-me' SETTINGS PROFILE 'laravel_app';
GRANT SELECT, INSERT ON default.* TO laravel;

In Laravel, set retries, timeouts, and a health check:

CLICKHOUSE_HOST=ch.internal
CLICKHOUSE_PORT=8443
CLICKHOUSE_DATABASE=default
CLICKHOUSE_USERNAME=laravel
CLICKHOUSE_PASSWORD=change-me
CLICKHOUSE_HTTPS=true
CLICKHOUSE_TIMEOUT_CONNECT=2
CLICKHOUSE_TIMEOUT_QUERY=30
CLICKHOUSE_RETRIES=2

CLICKHOUSE_RETRIES=2 retries a failed HTTP request twice. Retrying an insert is safe because ClickHouse deduplicates an identical block (section 03). Retrying a slow read can pile up load, so keep query timeouts short.

<?php
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Route;

Route::get('/health/clickhouse', function () {
    try {
        DB::connection('clickhouse')->getClient()->select('SELECT 1')->rows();
        return response()->json(['ok' => true]);
    } catch (\Throwable $e) {
        return response()->json(['ok' => false], 503);
    }
});

Mark the flush job with $tries = 5 and a backoff, so a short outage does not lose events.

Test it in CI

Confidence comes from tests that hit a real ClickHouse. Mocks cannot tell you that a query scans the wrong granules or that a Map literal failed to parse. Run the same Docker image in CI that you run in production:

# .github/workflows/tests.yml (excerpt)
services:
  clickhouse:
    image: clickhouse/clickhouse-server:26.3
    ports:
      - 8123:8123
    options: >-
      --health-cmd "wget -qO- http://localhost:8123/ping || exit 1"
      --health-interval 5s --health-retries 10
env:
  CLICKHOUSE_HOST: localhost
  CLICKHOUSE_DATABASE: testing

Give tests their own database, run the migrations once, and truncate between tests. ClickHouse has no transactions to roll back, so RefreshDatabase does not apply:

<?php
namespace Tests\Concerns;

use Illuminate\Support\Facades\DB;

trait UsesClickHouse
{
    protected function setUpUsesClickHouse(): void
    {
        $db = DB::connection('clickhouse')->getClient();
        $db->write('CREATE DATABASE IF NOT EXISTS testing');
        foreach (['events', 'orders_analytics', 'events_daily'] as $table) {
            $db->write("TRUNCATE TABLE IF EXISTS testing.{$table}");
        }
    }
}

<?php
namespace Tests\Feature;

use App\Models\Clickhouse\Event;
use App\Reports\DailyReport;
use Tests\Concerns\UsesClickHouse;
use Tests\TestCase;

class DailyReportTest extends TestCase
{
    use UsesClickHouse;

    public function test_it_groups_by_the_users_zone(): void
    {
        Event::insertBulk([
            ['2026-08-23 23:30:00', 7, 'purchase', '/checkout', 'FR', 'pro', '10.00'],
        ], ['event_time', 'user_id', 'event_type', 'url', 'country', 'plan', 'revenue']);

        $rows = app(DailyReport::class)->for(userTz: 'Europe/Paris');

        $this->assertSame('2026-08-24', substr($rows[0]['day'], 0, 10));
        $this->assertSame('10.00', $rows[0]['revenue']);
    }
}

Run php artisan migrate --database=clickhouse once in the CI job before the tests. Keep one test that runs EXPLAIN indexes = 1 on your heaviest dashboard query and asserts the granule ratio, so a bad sort key fails the build instead of the dashboard.

Sharding

Everything in this tutorial runs on one shard with replicas, and that carries most SaaS analytics workloads far. Sharding splits a table across servers with a Distributed table on top. It adds a sharding key, cross-shard joins, and resharding pain. Do not start there. Add shards when one server cannot hold the data or the merges, and read the Distributed engine docs first.

Quiz