If you’re building an application that needs to crunch millions of rows in milliseconds (think analytics dashboards, event tracking, logging pipelines, or time-series reporting) ClickHouse is one of the fastest column-oriented databases available today. In this guide, we’ll cover what ClickHouse is, why it’s different from traditional databases like PostgreSQL or MySQL, and walk through complete, working examples of integrating ClickHouse with FastAPI (Python) and Golang.
By the end of this article, you’ll know how to:
- Set up ClickHouse locally with Docker
- Connect to ClickHouse from a FastAPI application
- Connect to ClickHouse from a Go application
- Insert data efficiently (including batch inserts)
- Query and aggregate data safely using parameterized queries
What Is ClickHouse?
ClickHouse is an open-source, column-oriented database management system (DBMS) developed by Yandex, designed for online analytical processing (OLAP). Unlike row-based databases (PostgreSQL, MySQL), ClickHouse stores data by column, which makes aggregation queries (SUM(), COUNT(), AVG(), GROUP BY) extremely fast, even across billions of rows.
Key Features of ClickHouse
- Columnar storage reads only the columns needed for a query, drastically reducing I/O
- Vectorized query execution processes data in batches using CPU SIMD instructions
- High compression ratios reduce storage costs significantly
- Real-time data ingestion supports high-throughput inserts
- Horizontal scalability via sharding and replication
- SQL-like query language is easy to pick up if you already know SQL
ClickHouse vs Traditional Databases
| Feature | ClickHouse (OLAP) | PostgreSQL/MySQL (OLTP) |
|---|---|---|
| Storage model | Columnar | Row-based |
| Best for | Analytics, aggregations, reporting | Transactions, CRUD apps |
| Insert pattern | Batch inserts preferred | Row-by-row inserts fine |
| Update/Delete | Limited, eventual (mutations) | Native, immediate |
| Query speed on large datasets | Extremely fast | Slower at scale |
| Use case | Logs, metrics, events, analytics | User accounts, orders, transactional data |
Rule of thumb: Use ClickHouse for write-once, read-many analytical workloads, and keep your transactional data in PostgreSQL/MySQL. Many production systems use both side by side.
Setting Up ClickHouse with Docker
The fastest way to get ClickHouse running locally is via Docker Compose.
services:
clickhouse:
image: clickhouse/clickhouse-server:latest
container_name: clickhouse
ports:
- "8123:8123" # HTTP interface
- "9000:9000" # Native TCP interface
environment:
CLICKHOUSE_DB: default
CLICKHOUSE_USER: default
CLICKHOUSE_PASSWORD: ""
volumes:
- clickhouse_data:/var/lib/clickhouse
volumes:
clickhouse_data:
Start it with:
docker compose up -dPort 8123 is the HTTP interface (used by Python clients), and 9000 is the native TCP protocol (used by Go and CLI tools).
We’ll build a simple event-tracking use case throughout this article, using a table that stores page views, clicks, and signups.
Connect to clickhouse server using this command:
docker exec -it clickhouse clickhouse-clientThen run this sql command to create the table:
CREATE TABLE IF NOT EXISTS events (
id UUID DEFAULT generateUUIDv4(),
event_name String,
user_id UInt32,
url String,
created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (created_at, user_id);
Note the ENGINE = MergeTree() and ORDER BY clause. These are ClickHouse-specific and define how data is physically sorted and stored on disk, which directly impacts query performance.
ClickHouse with FastAPI (Python)
For Python, the officially maintained clickhouse-connect library is the recommended client. It communicates over HTTP and has a clean, simple API.
Install Dependencies
pip install fastapi uvicorn clickhouse-connectConnecting and Defining Endpoints
# main.py
from fastapi import FastAPI
from pydantic import BaseModel
import clickhouse_connect
app = FastAPI(title="ClickHouse + FastAPI Demo")
client = clickhouse_connect.get_client(
host="localhost",
port=8123,
username="default",
password="",
database="default",
)
class Event(BaseModel):
event_name: str
user_id: int
url: str
@app.on_event("startup")
def startup():
client.command("""
CREATE TABLE IF NOT EXISTS events (
id UUID DEFAULT generateUUIDv4(),
event_name String,
user_id UInt32,
url String,
created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (created_at, user_id)
""")
clickhouse-connect is a synchronous client. FastAPI automatically runs regular def endpoints (not async def) in a thread pool, so this is the correct approach here rather than forcing an async client.
Inserting a Single Event
@app.post("/events")
def create_event(event: Event):
client.insert(
"events",
[[event.event_name, event.user_id, event.url]],
column_names=["event_name", "user_id", "url"],
)
return {"status": "inserted"}
Batch Inserting Events
ClickHouse performs best with batch inserts rather than one row at a time. Here’s how to insert multiple events in a single request:
class EventBatch(BaseModel):
events: list[Event]
@app.post("/events/batch")
def create_events_batch(payload: EventBatch):
rows = [[e.event_name, e.user_id, e.url] for e in payload.events]
client.insert(
"events",
rows,
column_names=["event_name", "user_id", "url"],
)
return {"status": "inserted", "count": len(rows)}
Querying Data
@app.get("/events")
def list_events(limit: int = 50):
result = client.query(
"SELECT event_name, user_id, url, created_at FROM events ORDER BY created_at DESC LIMIT {limit:UInt32}",
parameters={"limit": limit},
)
return result.result_rows
Aggregation Query (Analytics Example)
@app.get("/events/stats")
def event_stats():
result = client.query("""
SELECT event_name, count() AS total
FROM events
GROUP BY event_name
ORDER BY total DESC
""")
return [{"event_name": r[0], "total": r[1]} for r in result.result_rows]
Safe, Parameterized Filtering
Always use ClickHouse’s parameter binding syntax ({name:Type}) instead of string formatting to avoid SQL injection:
@app.get("/events/search")
def search_events(event_name: str, limit: int = 50):
result = client.query(
"""
SELECT event_name, user_id, url, created_at
FROM events
WHERE event_name = {event_name:String}
ORDER BY created_at DESC
LIMIT {limit:UInt32}
""",
parameters={"event_name": event_name, "limit": limit},
)
return result.result_rows
Run it with:
uvicorn main:app --reloadYou now have a fully working FastAPI service backed by ClickHouse for high-speed event analytics.
ClickHouse with Golang
For Go, the official clickhouse-go driver (v2) is the standard choice. It communicates over the native TCP protocol (port 9000), which is faster than the HTTP interface.
Initiate the Project:
go mod init clickhouse-demoInstall Dependencies
go get github.com/ClickHouse/clickhouse-go/v2
Connecting to ClickHouse
package main
import (
"context"
"fmt"
"log"
"time"
"github.com/ClickHouse/clickhouse-go/v2"
)
func main() {
ctx := context.Background()
conn, err := clickhouse.Open(&clickhouse.Options{
Addr: []string{"localhost:9000"},
Auth: clickhouse.Auth{
Database: "default",
Username: "default",
Password: "",
},
})
if err != nil {
log.Fatal(err)
}
defer conn.Close()
if err := conn.Ping(ctx); err != nil {
log.Fatal(err)
}
fmt.Println("Connected to ClickHouse")
}
Creating the Table
err = conn.Exec(ctx, `
CREATE TABLE IF NOT EXISTS events (
id UUID DEFAULT generateUUIDv4(),
event_name String,
user_id UInt32,
url String,
created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY (created_at, user_id)
`)
if err != nil {
log.Fatal(err)
}Batch Inserting Data
Go’s driver has first-class support for batched inserts, which is the recommended way to write to ClickHouse:
batch, err := conn.PrepareBatch(ctx, "INSERT INTO events (event_name, user_id, url)")
if err != nil {
log.Fatal(err)
}
events := []struct {
EventName string
UserID uint32
URL string
}{
{"page_view", 101, "/home"},
{"click", 102, "/pricing"},
{"signup", 103, "/register"},
}
for _, e := range events {
if err := batch.Append(e.EventName, e.UserID, e.URL); err != nil {
log.Fatal(err)
}
}
if err := batch.Send(); err != nil {
log.Fatal(err)
}Querying Data
rows, err := conn.Query(ctx, "SELECT event_name, user_id, url, created_at FROM events ORDER BY created_at DESC LIMIT 10")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var (
eventName string
userID uint32
url string
createdAt time.Time
)
if err := rows.Scan(&eventName, &userID, &url, &createdAt); err != nil {
log.Fatal(err)
}
fmt.Printf("%s | user=%d | url=%s | at=%s\n", eventName, userID, url, createdAt)
}
Aggregation Query (Analytics Example)
rows, err := conn.Query(ctx, `
SELECT event_name, count() AS total
FROM events
GROUP BY event_name
ORDER BY total DESC
`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var eventName string
var total uint64
if err := rows.Scan(&eventName, &total); err != nil {
log.Fatal(err)
}
fmt.Printf("%s: %d\n", eventName, total)
}
Safe, Parameterized Queries in Go
The v2 driver supports named parameters to prevent SQL injection:
rows, err := conn.Query(
ctx,"SELECT event_name, user_id, url FROM events WHERE event_name = @event_name LIMIT @limit",
clickhouse.Named("event_name", "click"),
clickhouse.Named("limit", 10),
)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
Best Practices When Using ClickHouse
- Always batch inserts. Avoid inserting one row at a time in production. ClickHouse is optimized for bulk writes (hundreds to thousands of rows per insert).
- Choose the right
ORDER BYkey. This determines physical sort order and drastically affects query speed. Sort by the columns you filter or aggregate on most. - Use
MergeTreefamily engines (MergeTree,ReplacingMergeTree,AggregatingMergeTree) depending on your use case (deduplication, pre-aggregation, etc.). - Avoid frequent UPDATE/DELETE. ClickHouse handles mutations as background operations. They are not instant like in OLTP databases.
- Use partitioning (
PARTITION BY toYYYYMM(created_at)) for time-series data to make dropping old data cheap and fast. - Always parameterize queries. Both
clickhouse-connectandclickhouse-gosupport safe parameter binding. Never concatenate raw strings into SQL.
When Should You Use ClickHouse?
ClickHouse shines in:
- Product analytics (page views, click tracking, funnel analysis)
- Application/infrastructure logging at scale
- Real-time dashboards and BI tools
- IoT and sensor data (time-series metrics)
- Ad-tech and clickstream processing
It is generally not a good fit for high-frequency transactional updates (e.g., an e-commerce cart or a banking ledger). For those workloads, stick with PostgreSQL or MySQL, and use ClickHouse alongside it for the analytics layer.
Frequently Asked Questions
Is ClickHouse better than PostgreSQL?
Not “better,” just different. PostgreSQL is optimized for transactional workloads (OLTP); ClickHouse is optimized for analytical workloads (OLAP) on large datasets. Many teams use both together.
Can I use ClickHouse with FastAPI in production?
Yes. Use clickhouse-connect with connection pooling, batch your inserts, and run FastAPI with multiple workers behind a process manager like Gunicorn or Uvicorn workers.
Which is faster for ClickHouse: Go or Python?
Go, using the native TCP protocol via clickhouse-go, generally offers lower latency and higher throughput than Python’s HTTP-based client. This matters most for high-ingestion pipelines.
Conclusion
ClickHouse is a powerful choice when your application needs to analyze massive datasets in real time. Whether you’re building your backend in FastAPI for rapid API development or Golang for high-throughput ingestion services, both ecosystems have mature, well-supported ClickHouse clients. Start with the examples above, apply the batching and schema best practices, and you’ll have a production-ready analytics pipeline in no time.
