ClickHouse database engine

ClickHouse engine for Piko's database service. It bundles a dedicated DDL/DML parser, type resolver, builtin function and type catalogues, and a migration dialect into a single EngineConfig, targeting ClickHouse's columnar (OLAP) dialect.

Overview

ClickHouse is a server-based columnar OLAP database. It stores data column-by-column, compresses it hard, and uses a vectorised execution engine to scan billions of rows per second. The Piko engine implements the querier EnginePort for ClickHouse directly instead of aliasing it to another dialect. You get ClickHouse-aware parsing and type resolution without extra configuration. The builtin catalogue covers arrays, tuples and maps, IP and UUID helpers, geo and H3 functions, hashing, URL and string search, and statistical aggregates.

ClickHouse diverges from the SQL-standard family in three places the engine handles for you. It binds parameters in the brace form {name:Type} and encodes nullability inside the type name as Nullable(T) instead of a separate column flag. It also carries MergeTree table DDL (PARTITION BY, ORDER BY, SETTINGS) that codegen captures verbatim instead of discarding. The package docs cover the parsing and type-resolution mechanics. A dedicated emitter (db_emitter_clickhouse) renders the brace placeholders, so the generated code is ClickHouse-aware end to end, not a thin wrapper over a generic dialect.

ClickHouse's character is high-ingest analytics at scale. The workload to reach for it is event, log, metric, or time-series analytics over datasets in the billions of rows. It fits real-time aggregation dashboards and anything that ingests millions of rows per second and answers aggregate queries over them. Prefer DuckDB for the same columnar analytics embedded in-process over local files (Parquet, CSV) instead of a separate server. Prefer TimescaleDB when time-series data lives alongside transactional Postgres data and one system serves both. Prefer PostgreSQL for transactional (OLTP) workloads, high concurrent writers, row-level updates, and point lookups, none of which ClickHouse targets.

Unlike Postgres, ClickHouse has no advisory locks, so the migration runner uses a table-based version tracker. Concurrent migrators must coordinate externally, through a single deployer or a CI lock, instead of relying on the database to arbitrate. Migrations also run non-transactionally, since ClickHouse has no DDL transactions. A migration that fails partway leaves the statements it already applied in place, so write each migration to be safe to re-run.

Requirements

A running ClickHouse server. Unlike the embedded engines (SQLite, DuckDB), ClickHouse is a standalone service you connect to over its native protocol.
The native protocol driver github.com/ClickHouse/clickhouse-go/v2, which registers the clickhouse database/sql driver. It is pure Go, no CGO or C toolchain required.

Configuration

EngineConfig is the bundle. The constructor takes no arguments.

import (
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_clickhouse"
)

engineConfig := db_engine_clickhouse.ClickHouse()

ClickHouse() returns a db.EngineConfig with the same three fields every engine returns. Those fields are a driver name, the codegen engine, and a migration dialect. Wiring ClickHouse is identical to wiring Postgres() or MySQL(). The only ClickHouse-specific choice you make is calling ClickHouse(). Unlike CockroachDB and TimescaleDB, which alias the Postgres engine and dialect, ClickHouse ships a native engine and its own migration dialect.

Bootstrap

import (
    "database/sql"
    "os"

    _ "github.com/ClickHouse/clickhouse-go/v2" // registers the "clickhouse" driver

    "piko.sh/piko"
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_clickhouse"
)

connection, err := sql.Open("clickhouse", os.Getenv("CLICKHOUSE_DSN"))
if err != nil {
    return err
}

ssr := piko.New(
    piko.WithDatabase("analytics", &db.DatabaseRegistration{
        DB:           connection,
        EngineConfig: db_engine_clickhouse.ClickHouse(),
        MigrationFS:  migrationsFS,
        QueryFS:      queriesFS,
    }),
)

MigrationFS enables runtime migration execution. QueryFS enables codegen. When you set QueryFS alongside MigrationFS and EngineConfig, the generate sql command produces typed Go query methods from the SQL files. The generated package lands in GeneratedOutputDirectory (default db/generated) under the name in GeneratedPackageName (default generated). Without QueryFS, the registration wires migrations only and emits no typed queries.

The engine package itself imports no runtime driver. The blank import of github.com/ClickHouse/clickhouse-go/v2 is yours to add to your module, and it is the line that registers the clickhouse database/sql driver that sql.Open looks up.

Tradeoffs

ClickHouse is the wrong answer for transactional workloads. Single-row updates and deletes are asynchronous mutations instead of cheap point operations, there are no foreign keys or per-row constraints, and JOIN semantics differ from a row store. Schema design is also less forgiving. The ORDER BY (sort key) you pick at table-creation time governs read performance and stays fixed in practice. The right shape is "analytical store fed by an ingest pipeline". A transactional Postgres often sits on the write path with ClickHouse on the analytics path, populated by change-data-capture or scheduled extract.