Why We Chose ClickHouse Over Postgres for Analytics

OwlEye repeatedly asks questions such as “which pages were viewed this week?” and “where did those visits come from?” Each answer filters many events and aggregates a few columns. That workload shaped our choice of ClickHouse for analytics facts.

This is an explanation of the architecture in the source, not a claim that we migrated an identical PostgreSQL workload and measured a particular speedup. We have not published that benchmark.

Start with the query shape

An event can carry a timestamp, page, event type, browser, country, and other context. A top-pages report needs only a subset of those values across a time range. A browser breakdown scans a different subset. Most events are appended; reports repeatedly summarize them.

ClickHouse’s column-oriented storage fits those scans and aggregations. Its benefit still depends on table ordering, filtering, cardinality, data volume, and the hardware available. The database name alone does not make an unbounded report cheap.

OwlEye’s reporting queries must also enforce application scope. A fast query that reads another organization’s events would be a serious failure. Authentication and membership checks belong ahead of the storage query, regardless of the engine.

Keep transactional metadata separate

ClickHouse does not hold every piece of OwlEye’s state. SQLite owns control-plane and application metadata. ClickHouse owns high-volume event and performance facts. This keeps account and configuration operations separate from large reporting scans.

That split creates an operational cost for OwlEye: there are two persistence systems to back up, restore, monitor, and upgrade. It is a deliberate tradeoff, not a free optimization, and part of operating the managed service.

Ingestion matters as much as reads

Small synchronous inserts can create excessive MergeTree parts and leave ClickHouse doing unnecessary merge work. ClickHouse recommends batching inserts; its bulk-insert guidance explains the relationship between insert frequency, batch size, and parts.

OwlEye’s current ingestion implementation uses a bounded Tokio channel and a background worker. It flushes when the accumulated batch reaches 5,000 rows, or after a one-second window beginning with pending work. Several small HTTP requests can therefore become one database insert. The API waits for persistence results instead of treating entry into an in-memory channel as durable storage.

Backpressure is essential. A bounded queue limits how much work can wait when the database is slow. It does not create infinite capacity or survive a process crash. Clients still need to interpret unsuccessful ingestion responses honestly.

For transient insert failures, bounded retries reuse the same serialized batch and deduplication token. That reduces duplicate insertion risk within the configured deduplication window. It is not an exactly-once guarantee, and the queue is not a durable outbox.

Why PostgreSQL is still a reasonable choice

PostgreSQL supports declarative partitioning and partition pruning, alongside indexes and SQL aggregation. A modest analytics workload can fit well in an existing PostgreSQL deployment, especially when a team already knows how to operate it.

If one database can satisfy your latency and retention requirements, adding another system may cost more than it saves. Measure representative queries and inserts before taking on the extra operational work. Include backups and restores in that evaluation, not just dashboard response time.

ClickHouse is a good fit for OwlEye’s append-heavy reporting path. PostgreSQL can be a good fit for a product that values shared infrastructure or has different query patterns. Neither choice excuses poor schema design or missing capacity tests.

What we would measure before making a performance claim

Use the same event distribution, retained history, concurrency, and output semantics. Record insert throughput, tail query latency, memory, disk growth, merge pressure, and recovery time. Include both warm and cold queries, and a sustained write load while reports run.

A dashboard that is quick on a tiny dataset has not established production capacity. Our choice is based on the workload fit and the separation of responsibilities; any numerical performance comparison needs its own reproducible evidence.

For you, this architecture supports time-bounded reports over recorded events: which source brought traffic, which page came next, and where an ordered funnel lost visitors. Start with a dashboard-managed event and funnel. The architecture documentation explains the data flow and its limits.