ClickHouse From Go for Analytics Workloads at Scale

ClickHouse is a database for questions like "how many events of each kind in the last hour, per country, across forty billion rows". It stores each column separately and compressed, reads only the columns a query names, and spreads work over every core. For logs, metrics, product analytics and event data it is dramatically faster and cheaper than a row store, and it is the wrong tool for the things PostgreSQL is good at: single row updates, transactions and lookups by id. This guide covers using it from Go, with the clickhouse-go driver, and the table and insert habits that decide whether it is fast.

It was not run. No ClickHouse server was available in the lab behind this series. The program below compiled and passed go vet against clickhouse-go v2.43.0, so the calls exist with the shapes shown, and nothing was executed against a server. The newest release of the driver needs Go 1.26, so the lab pins the last version that builds on Go 1.25. Claims about behaviour are taken from the ClickHouse documentation; confirm them for your version.

It belongs to the database cornerstone. The embedded analytics alternative is DuckDB from Go, and the data processing cluster covers the pipelines that feed such stores.

Why it is fast, and what that costs

Three design choices explain nearly everything.

Columnar storage. Each column is stored in its own files. A query that touches kind and value reads those two columns and nothing else, so a table of fifty columns costs the same to query as one of two, if the query names two. Similar values sit together, so compression is excellent: ratios of five to ten times are common.

A sorted, immutable layout. Data is written in parts, each sorted by the table's ORDER BY key, and a background process merges parts into larger ones. A sparse index over the key lets the server skip most of the data when a query filters on it.

Vectorised, parallel execution. Queries process blocks of values, across all cores.

The cost is on the other side. There is no efficient single row update or delete, since parts are immutable, and mutations rewrite data in the background. Transactions across tables are not available in the sense a relational database offers. And point lookups by an arbitrary key are not what the sparse index is for. Use ClickHouse for append mostly data that is read in aggregate.

The program

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/ClickHouse/clickhouse-go/v2"
	"github.com/ClickHouse/clickhouse-go/v2/lib/driver"
)

func connect() (driver.Conn, error) {
	return clickhouse.Open(&clickhouse.Options{
		Addr: []string{"127.0.0.1:9000"},
		Auth: clickhouse.Auth{Database: "default", Username: "default"},
		Settings: clickhouse.Settings{
			"max_execution_time": 60,
		},
		DialTimeout:     5 * time.Second,
		MaxOpenConns:    10,
		MaxIdleConns:    5,
		ConnMaxLifetime: time.Hour,
		Compression:     &clickhouse.Compression{Method: clickhouse.CompressionLZ4},
	})
}

const ddl = `
create table if not exists events (
    ts      DateTime,
    user_id UInt64,
    kind    LowCardinality(String),
    value   Float64
) engine = MergeTree
partition by toYYYYMM(ts)
order by (kind, ts)`

type Event struct {
	TS     time.Time
	UserID uint64
	Kind   string
	Value  float64
}

// insert sends many rows in one batch: ClickHouse is built for few large inserts, not many small ones.
func insert(ctx context.Context, conn driver.Conn, events []Event) error {
	batch, err := conn.PrepareBatch(ctx, "insert into events (ts, user_id, kind, value)")
	if err != nil {
		return err
	}
	for _, e := range events {
		if err := batch.Append(e.TS, e.UserID, e.Kind, e.Value); err != nil {
			return err
		}
	}
	return batch.Send()
}

func perKind(ctx context.Context, conn driver.Conn, since time.Time) error {
	rows, err := conn.Query(ctx, `
		select kind, count() as n, round(avg(value), 2) as avg_value
		from events where ts >= $1 group by kind order by n desc`, since)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var kind string
		var n uint64
		var avg float64
		if err := rows.Scan(&kind, &n, &avg); err != nil {
			return err
		}
		fmt.Println(kind, n, avg)
	}
	return rows.Err()
}

func main() {
	ctx := context.Background()
	conn, err := connect()
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()
	if err := conn.Exec(ctx, ddl); err != nil {
		log.Fatal(err)
	}
	now := time.Now()
	if err := insert(ctx, conn, []Event{{now, 1, "click", 1}, {now, 2, "view", 3}}); err != nil {
		log.Fatal(err)
	}
	if err := perKind(ctx, conn, now.Add(-time.Hour)); err != nil {
		log.Fatal(err)
	}
}

clickhouse.Open returns a connection that speaks the native protocol on port 9000, faster than the HTTP interface on 8123 and supported by the driver as an alternative (Protocol: clickhouse.HTTP). The options matter: DialTimeout bounds connecting, MaxOpenConns and MaxIdleConns size the pool, ConnMaxLifetime recycles connections, Compression with LZ4 shrinks traffic at little cost, and Settings sends server settings with each query: max_execution_time here ends any query that runs past 60 seconds. The driver also implements database/sql through clickhouse.OpenDB, which is useful with existing libraries, though the native interface gives batch inserts and richer types.

Tables: the ORDER BY decides

The ddl constant creates an events table:

• engine = MergeTree is the standard engine for analytics tables.
• order by (kind, ts) is the sorting key, the most important decision in the table. Data is stored sorted by it, and the primary index is built over it. A query that filters on kind, or on kind and a time range, reads a small slice. A query that filters on ts alone cannot use the leading column and reads far more. Put the column you filter on most, with low cardinality, first, and the time column next.
• partition by toYYYYMM(ts) splits data into monthly partitions, which makes dropping old data a cheap operation (alter table drop partition) and lets queries skip partitions. Do not over partition: hundreds of partitions harm performance, and the guidance in the documentation is to partition for data management, not for query speed.
• LowCardinality(String) for kind stores each distinct value once in a dictionary and the rows as small integers. For columns with up to a few thousand distinct values it saves space and speeds filtering and grouping. For high cardinality columns such as ids it hurts.

Pick types with care, too: UInt64 for ids, DateTime or DateTime64 for times, Float64 for measurements, and Nullable only when you need it, since a nullable column carries an extra mask.

Inserting: big batches, not many small ones

This is the rule that new users break. Every INSERT creates a new part on disk. Thousands of tiny inserts create thousands of parts, the server falls behind on merging them, and it starts rejecting inserts with a "too many parts" error. The documentation recommends inserting at least 1,000 rows at a time and ideally tens of thousands, at a rate of about one insert per second per table.

The insert function does it the right way for the native interface: PrepareBatch opens a batch, each Append adds a row to it in memory with the values in column order, and Send transmits the whole thing as one insert. The driver converts Go types, for example time.Time for DateTime, and a type mismatch comes back as an error from Append. A service that receives events one at a time buffers them: collect in a channel, flush when the batch reaches a size or a time, whichever comes first, and handle failure of a flush by retrying the whole batch.

If you cannot batch on the client, ClickHouse has asynchronous inserts (async_insert=1 in Settings), which let the server buffer small inserts and write them together. It trades a delay in visibility, and a choice of whether the call waits for the flush, for tolerance of many small writers. Prefer client batching where you can.

Inserts are not idempotent by default, so a retry after a timeout can duplicate data. ReplacingMergeTree removes rows with the same sorting key during merges, eventually, and there are insert deduplication settings for replicated tables; design for at least once and plan how duplicates are removed or ignored.

Querying

perKind shows the pattern: conn.Query(ctx, sql, args...) with $1 style parameters, then rows.Next() and rows.Scan into typed variables. The types must match the column types exactly: count() returns UInt64, so scan into a uint64, and a mismatch errors. Close the rows and check rows.Err().

Habits that keep queries fast:

• Name only the columns you need. select * defeats the columnar design.
• Filter on the sorting key, leading columns first.
• Aggregate on the server. Send the grouped result across the network, not the rows.
• Use LIMIT and approximate functions (uniq instead of count(distinct), quantile instead of an exact percentile) when exactness is not needed.
• Avoid joins of big tables. They are supported and memory hungry. Denormalise at insert time, or use dictionaries for small lookup tables.
• Do not use it for lookups by id. A key value store or PostgreSQL answers those in microseconds.

Set a max_execution_time and max_memory_usage per query or per user, so one runaway query cannot take the cluster. Use EXPLAIN and the system.query_log table to see what read how many rows and bytes; the number of rows read against the number returned is the first thing to look at.

Materialised views and rollups

Dashboards ask the same aggregations repeatedly. A materialised view in ClickHouse is an insert trigger: as rows arrive in the source table, a query transforms them and writes the result into a target table, often an AggregatingMergeTree or SummingMergeTree that merges partial aggregates. A per minute rollup of events costs almost nothing to maintain and makes a "last 24 hours" chart read a few thousand rows instead of billions. The view only sees data inserted after it exists, so backfill the target table once from the history.

A batcher you can reuse

Production code seldom has a slice of events ready to insert. It has a stream of them, from an HTTP handler or a queue consumer, and needs to turn that into large inserts without losing any. The shape that works is small: a buffered channel that producers write to, one goroutine that owns the connection and the batch, and two flush triggers.

The goroutine appends each event to the open batch. When the batch reaches a count, say 10,000 rows, or when a timer fires, say every second, it calls Send and starts a new batch. On shutdown it flushes what it has. If Send fails, it keeps the rows, retries with backoff, and applies backpressure by letting the channel fill, so producers block or shed load instead of the process running out of memory. Count the rows sent, the rows retried and the age of the oldest unsent row, and alert on the last one.

Two details matter. Do not share a driver.Batch between goroutines; one goroutine owns it. And decide what a duplicate does: a retry after an ambiguous failure can insert a batch twice, so either tolerate duplicates in the queries (count distinct on an event id), use a ReplacingMergeTree keyed by the event id, or use the insert deduplication that replicated tables support when you retry the identical batch. The same at least once reasoning appears in the outbox pattern and bulk loading into PostgreSQL, and the answer is the same: make the consumer cope with duplicates.

Go types and ClickHouse types

The driver maps types strictly, and most first day errors come from a mismatch. Integers must match in width and sign (UInt64 scans into uint64, not int), DateTime maps to time.Time with the time zone of the column, Float64 to float64, String to string, Array(String) to []string, Map(String, String) to map[string]string, and Nullable(T) to *T. Decimal maps to decimal.Decimal from a third party package. When a scan fails with a message about a type conversion, print the column types with rows.ColumnTypes() and compare. Prefer DateTime64(3) or (6) for sub second timestamps, and store everything in UTC.

Using database/sql

clickhouse.OpenDB returns a *sql.DB, which is the way to use ClickHouse with tools built on the standard interface, such as migration runners and query builders. The standard interface works, with caveats: batch inserts go through a prepared insert statement inside a transaction, which the driver turns into one batch on Commit, and the pool, Stats() and context handling behave as in any other driver. Use the native interface for the ingest path, where batching and types matter, and the database/sql one for the rest.

Migrations for an analytics database

Schema change is cheap to add and expensive to alter. Adding a column is a metadata change, and a new column is read as its default for old data. Changing the sorting key, the partition expression or an engine is not possible in place: create the new table, copy with insert into new select ... from old in chunks by partition, switch writers and readers, and drop the old one. Keep the DDL in versioned files and run it from one place, as with any migration tool, and write the DDL so that rerunning it is harmless with if not exists. Treat the sorting key as a decision made after looking at your real queries, because it is the one you cannot revise cheaply.

Operating it

• Updates and deletes are mutations, rewritten asynchronously in the background and expensive. Design so you rarely need them; lightweight deletes exist and still do not behave like a transactional store.
• Replication and sharding use ReplicatedMergeTree with Keeper or ZooKeeper, and distributed tables over shards. Start with one replicated shard.
• TTL on a table or column deletes or moves old data automatically: TTL ts + interval 90 day.
• Schema changes such as adding a column are cheap and lazy; changing the sorting key is not, and means creating a new table and copying.
• Monitoring: parts per partition, merge backlog, query duration and memory from the system tables.

Testing and local development

A single ClickHouse server in a container is enough for functional tests of the schema, the inserts and the queries, and starts in seconds. Test the batcher with a table that has a unique sorting key, assert row counts after a flush, and test the failure path by pointing at a closed port. Test the ordering key by running EXPLAIN indexes = 1 on your main queries and checking that the number of granules read is small. Performance conclusions need data of realistic size, since the engine's behaviour at ten thousand rows says little about ten billion.

When to choose it

Choose ClickHouse for event, log and metric data you aggregate, at volumes where a row store struggles, with batch or stream ingestion and mostly append only writes. Do not choose it for transactional data, for frequent updates, or for data small enough that PostgreSQL with good indexes answers in milliseconds; for a local analytical store with no server, DuckDB is simpler. The driver is documented in the ClickHouse Go client guide, and the table design rules in the guide to choosing a primary key.