The dashboard that outgrew Postgres
You built a metrics dashboard that renders real-time aggregates. The queries scan millions of rows per second. Postgres starts choking on the write load and the aggregation latency. You move the hot data to ClickHouse. Now your Go backend needs to fetch those aggregates reliably. The connection setup looks like standard SQL, but ClickHouse has quirks that trip up first-time users. The standard library handles the heavy lifting, but you need to understand what the driver is doing under the hood.
The adapter pattern in Go
Go's standard library includes database/sql. It defines the interface for talking to databases. It does not contain the drivers. You need a driver that implements the interface. The official clickhouse-go driver bridges the gap. It speaks the ClickHouse protocol and translates it into the database/sql interface.
Think of database/sql as a universal power adapter. Your Go code plugs into the adapter. The driver plugs into the wall. The adapter ensures your code does not need to know the shape of the socket. The driver handles the voltage and current. This separation keeps your application code stable even when you swap databases later. The standard library enforces a strict contract: you write queries against an interface, and the driver handles the wire format, type mapping, and connection lifecycle.
Minimal connection setup
The official driver lives at github.com/ClickHouse/clickhouse-go/v2. You need the lib/driver subpackage to register the driver with the standard library. The setup requires a blank import, a DSN string, and a ping to verify the link.
Here is the simplest connection: register the driver, open the handle, ping the server.
package main
import (
"database/sql"
// Blank import registers the driver with database/sql.
// The underscore tells the compiler you want the side effect, not the symbols.
_ "github.com/ClickHouse/clickhouse-go/v2/lib/driver"
)
func main() {
// DSN format: clickhouse://user:password@host:port/database?params
// sql.Open validates the DSN but does not open a connection yet.
db, err := sql.Open("clickhouse", "clickhouse://localhost:9000/default")
if err != nil {
panic(err)
}
// Defer close to release resources when main exits.
defer db.Close()
// Ping forces the driver to establish the first connection.
// This is where network errors surface.
if err := db.Ping(); err != nil {
panic(err)
}
}
How the pieces fit together
sql.Open is lazy. It parses the DSN string. It creates a *sql.DB handle. This handle is a pool, not a single connection. The function returns immediately. It does not contact the server. If you pass a hostname that does not exist, sql.Open returns a valid handle and a nil error. The error surfaces later when you try to use the handle.
db.Ping() forces the driver to establish the first connection. It sends a packet to the server. If the server is down, Ping fails. If the credentials are wrong, Ping fails. This is the correct place to verify connectivity during startup.
The blank import _ "github.com/ClickHouse/clickhouse-go/v2/lib/driver" runs the driver's init() function. This function calls sql.Register("clickhouse", driver). It tells the standard library that the string "clickhouse" maps to this specific driver implementation. If you forget the blank import, the runtime rejects the program with driver not registered when you call sql.Open.
The DSN string follows a URL format. clickhouse://user:password@host:port/database?params. The driver parses this string to configure the connection. You can add query parameters for compression, debugging, and timeouts. For example, ?compress=lz4&debug=true enables LZ4 compression and prints debug logs. ClickHouse supports multiple compression algorithms. The driver handles the negotiation.
sql.Open prepares the pool. Ping proves the link. Verify early.
Realistic query with context
Production code needs context. ClickHouse queries can be heavy. A runaway aggregation can consume CPU and memory on the server. Context carries cancellation and deadlines. The driver respects context for query timeout. If the context expires, the driver cancels the query on the server.
Here is a realistic function: accept context, execute a query, scan the result, return the value.
import (
"context"
"database/sql"
)
// GetServerCount returns the number of active ClickHouse servers.
func GetServerCount(ctx context.Context, db *sql.DB) (int64, error) {
// Context carries cancellation and deadline.
// ClickHouse driver respects context for query timeout.
row := db.QueryRowContext(ctx, "SELECT count() FROM system.servers")
var count int64
// Scan copies the column data from the row into the count variable.
err := row.Scan(&count)
if err != nil {
return 0, err
}
return count, nil
}
The context.Context parameter goes first. This is a Go convention. Functions that take a context should respect cancellation and deadlines. The driver checks the context before sending the query. It checks it while waiting for the result. It checks it while reading the response. If the context is cancelled, the driver stops reading and returns an error.
db.QueryRowContext returns a *sql.Row. This is a single row. row.Scan copies the column data into the variable. You must pass a pointer to the variable. Scan reads the data from the network buffer. If the query returns no rows, Scan returns sql.ErrNoRows. If the column type does not match the variable type, Scan returns a type mismatch error.
Context is plumbing. Run it through every long-lived call site.
Tuning the connection pool
*sql.DB is a pool. It manages multiple connections. ClickHouse handles many connections well. The server is designed for high concurrency. You still need to tune the pool to match your workload.
SetMaxOpenConns limits the total number of open connections. SetMaxIdleConns limits the number of idle connections kept in the pool. SetConnMaxLifetime limits how long a connection stays open before being closed and replaced.
Here is how to configure the pool: set limits, verify with ping, return the handle.
import (
"database/sql"
"time"
)
// NewClickHouseDB initializes a configured connection pool.
func NewClickHouseDB(dsn string) (*sql.DB, error) {
// Open the pool handle.
db, err := sql.Open("clickhouse", dsn)
if err != nil {
return nil, err
}
// Limit total open connections to prevent overwhelming the server.
db.SetMaxOpenConns(10)
// Keep idle connections alive to reduce latency on subsequent queries.
db.SetMaxIdleConns(5)
// Rotate connections every 30 minutes to handle network changes.
db.SetConnMaxLifetime(30 * time.Minute)
// Verify the pool can connect to the server.
if err := db.Ping(); err != nil {
return nil, err
}
return db, nil
}
SetMaxOpenConns prevents your Go service from opening thousands of connections. ClickHouse has a limit on concurrent queries. If you exceed the limit, the server rejects new queries. Setting a reasonable max protects the server.
SetMaxIdleConns keeps connections warm. Opening a TCP connection takes time. TLS handshake takes time. Authentication takes time. Keeping idle connections in the pool reduces latency. The driver reuses these connections for new queries.
SetConnMaxLifetime handles network changes. If the server restarts, the old connections become stale. The pool closes old connections and opens new ones. This prevents errors from dead connections.
Trust the pool. Tune the limits. Do not guess.
Pitfalls and compiler errors
DSN formatting errors are common. sql.Open validates the DSN syntax. It does not validate the host. If you pass a malformed DSN, sql.Open returns an error. If you pass a valid DSN with a bad host, sql.Open succeeds. Ping fails.
The compiler complains with undefined: driver if you forget to import the driver package. The runtime complains with driver not registered if you forget the blank import. These errors are distinct. The first is a compile-time error. The second is a runtime error.
Context cancellation is often ignored. If you pass context.Background() to a long-running query, the query runs until completion. If the client disconnects, the query continues on the server. This wastes resources. Always pass a context with a deadline or cancellation. The driver cancels the query on the server.
Connection leaks happen when you forget to close rows. row.Scan reads the data. It does not close the row. You must call row.Close() or use defer row.Close(). The driver reuses the connection after the row is closed. If you do not close the row, the connection stays busy. The pool runs out of connections.
The compiler rejects the program with loop variable i captured by func literal if you use a loop variable in a goroutine without capturing it. This is a general Go rule, not specific to ClickHouse. Always capture loop variables.
The worst goroutine bug is the one that never logs. Close your rows. Respect the context.
Decision matrix
Use database/sql with the official driver when you want standard Go patterns and automatic connection pooling. Use the native driver API when you need ClickHouse-specific features like batch inserts or custom compression. Use sqlx when you want struct scanning and named queries. Use plain HTTP when you want to bypass the driver and send raw queries.
Use database/sql when you have independent I/O calls that can run while others wait. Use the native driver when you need bounded concurrency to protect a downstream service. Use sqlx when one task feeds another in a pipeline. Use plain sequential code when you do not need concurrency: the simplest thing that works is usually the right thing.