ClickHouse + Laravel: Real-World Analytics

Tip: Tracking Middleware

class TrackPageView
{
    public function handle(Request $request, Closure $next)
    {
        $response = $next($request);

        DB::connection('clickhouse')->table('page_views')->insert([
            'url' => $request->path(),
            'user_id' => auth()->id(),
            'ip' => $request->ip(),
            'user_agent' => $request->userAgent(),
            'created_at' => now(),
        ]);

        return $response;
    }
}

Track every page view in ClickHouse.

Gotcha: Don't Track in the Request Path

For high-traffic sites, queue the tracking:

TrackPageView::dispatch($request->path(), auth()->id());

Batch insert in a queue worker.

Tip: Dashboard Query

$stats = DB::connection('clickhouse')->select("
    SELECT toStartOfDay(created_at) as day, count() as views, uniq(user_id) as visitors
    FROM page_views
    WHERE created_at >= now() - INTERVAL 30 DAY
    GROUP BY day ORDER BY day
");

30-day traffic overview.

Gotcha: Separate Databases

• PostgreSQL: users, posts, categories (transactional)
• ClickHouse: page views, events, analytics (analytical)

Don't mix them.

Tip: Top Pages Query

$topPages = DB::connection('clickhouse')->select("
    SELECT url, count() as views
    FROM page_views
    WHERE created_at >= today() - 7
    GROUP BY url ORDER BY views DESC LIMIT 20
");

Gotcha: Data Retention

ClickHouse stores data forever by default. Set up partition dropping:

ALTER TABLE page_views DROP PARTITION '202301';

Tip: Unique Visitors

SELECT uniq(user_id) as unique_visitors FROM page_views WHERE created_at >= today();

uniq() is approximate but extremely fast.

Gotcha: Middleware Performance

Tracking middleware adds ~1-5ms per request. For high-traffic sites, use async/batch tracking.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

Building a real-world analytics system with ClickHouse and Laravel taught me that the ETL pipeline is more important than the database. I use Laravel's scheduled tasks to run nightly aggregation jobs, writing results to ClickHouse for dashboard queries. The raw event data stays in ClickHouse for 7 days (TTL-based deletion), while aggregated summaries are kept indefinitely. This balance gives me detailed debugging data for a week and long-term trends forever.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)