Two databases sounds like one too many until you look at what each is being asked to do. This is the reasoning, the actual schema decisions, and the numbers from a running instance — including the one that came out lower than the literature would predict.
Two stores, two jobs
Positions and business records have almost nothing in common as workloads.
Positions arrive constantly, are never updated, are read in ranges, and are aggregated far more often than they are fetched individually. Millions of rows, append-only, columnar access.
Vehicles, trips, users, geofences, reports are small, mutable, relational, and read by primary key. Thousands of rows, transactional, joined constantly.
Putting both in Postgres works and is the right first move for a small deployment — but the position table grows without bound and the analytical queries over it start competing with the transactional ones. Putting both in ClickHouse does not work at all, because ClickHouse is append-first and your users table needs updates and foreign keys.
So: Postgres is the system of record, ClickHouse is the telemetry store. Trips and events are derived at ingest and written to Postgres, which means the position history is replaceable detail rather than a business record — a distinction that turns out to matter for retention and for backups.
The schema, column by column
The interesting part is the codecs. Generic compression treats a column of coordinates as bytes; a codec that knows the column is a slowly drifting float does much better.
CREATE TABLE routly.telemetry (
ident LowCardinality(String),
timestamp DateTime64(3) CODEC(DoubleDelta, LZ4),
lat Float64 CODEC(Gorilla, LZ4),
lon Float64 CODEC(Gorilla, LZ4),
altitude Float32 CODEC(Gorilla, LZ4),
speed UInt8,
course UInt16 CODEC(Delta, LZ4),
ignition UInt8,
satellites UInt8,
hdop Float32 CODEC(Gorilla, LZ4),
gsm_signal UInt8,
fuel_level Float32 CODEC(Gorilla, LZ4),
mileage Float64 CODEC(Gorilla, LZ4),
battery_voltage Float32 CODEC(Gorilla, LZ4),
external_voltage Float32 CODEC(Gorilla, LZ4)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (ident, timestamp)
TTL toDateTime(timestamp) + INTERVAL 7 DAYEach choice has a reason:
DoubleDelta on the timestamp. Positions arrive at a near-constant interval, so the first difference between timestamps is nearly constant and the second difference is nearly zero. A column of zeros compresses to almost nothing.
Gorilla on coordinates and sensors. Gorilla XORs each float with the previous one and stores the differing bits. For a value that drifts slowly — latitude, fuel level, voltage — most of the mantissa is unchanged between readings and the XOR is mostly zeros.
Delta on course. Heading changes in small increments, so first differences are small integers.
No codec on speed, ignition, satellites, gsm_signal. They are already UInt8. A byte is a byte, and a codec on top adds CPU for nothing.
LowCardinality(String) for the device identifier. A few hundred distinct values across millions of rows becomes a dictionary and an integer.
ORDER BY (ident, timestamp). The sort key is the query pattern: every read is “this vehicle, this time range”.
PARTITION BY toYYYYMMDD with a matching TTL. This pairing is the part people get wrong. When the TTL expires data inside a partition, ClickHouse rewrites parts; when it expires a whole partition, it drops the directory. Partitioning by day and expiring by day means retention costs nothing.
What we actually measured
On the production instance, 16 August 2026:
| Value | |
|---|---|
| Rows | 49,478 |
| Uncompressed | 2.61 MiB |
| On disk | 908 KiB |
| Ratio | 2.94× |
| Bytes per position | ~19 |
Nineteen bytes a position is the number worth carrying around. Two thousand vehicles reporting every 30 seconds for eight hours a day is roughly 13 GB a year.
The honest caveat
2.94× is lower than it should be, and we are publishing it anyway. The literature puts ClickHouse at 5:1 to 10:1 on numeric time series, and considerably higher on some workloads. Our sample is 49,478 rows in a single day’s partition, almost certainly with parts that have not fully merged — and merging is where a lot of the compression arrives, because larger parts give the codecs longer runs to work with.
So the real figure at scale is probably better than what we quote. We quote the measurement rather than the expectation because a number you took yourself and a number you read in a benchmark are different kinds of claim, and only one of them is yours to stand behind. When the instance holds a few hundred million rows we will measure again and update this.
Where ClickHouse is the wrong answer
Being straight about the limits, because pretending a tool has none is how people get burned:
Updates. ClickHouse is append-oriented. If your workload corrects historical positions row by row, Postgres is the better fit. Ours does not: a position is a fact about a moment and is never edited.
Geospatial depth. ClickHouse has geo functions, but they are not comparable to PostGIS. Complex spatial work — routing, topology, precise polygon operations — belongs in Postgres. Geofence definitions live there for exactly this reason; only the position stream is in ClickHouse.
Transactions and joins. Neither is a strength. Anything that needs them is on the Postgres side by design.
Operational weight. It is a second system to back up, monitor and upgrade. For a fleet under a hundred vehicles that overhead is not obviously worth it, which is why the default compose file runs Postgres-only for telemetry and keeps ClickHouse behind an opt-in profile.
A reasonable alternative worth naming: Postgres with TimescaleDB gives you one database, SQL, PostGIS and relational joins, at 2:1 to 3:1 compression rather than ClickHouse’s. If operational simplicity matters more to you than the storage and scan advantage, that is a defensible choice and we would not argue with it.
The other half: what makes queries fast
Compression is about disk. Two other decisions are about latency.
Latest position is its own table. The fleet screen asks “where is everything right now” on every poll, which as a query over the full history is a scan looking for a maximum per vehicle. A ReplacingMergeTree keyed on the device and versioned by the message timestamp keeps one row per vehicle and answers it directly.
Versioning by the message timestamp rather than by insert time is not a detail. A tracker that spends an hour out of coverage flushes its buffer in one batch when it reconnects, so arrival order says nothing about which position is newest. Keying the replacement on insert time leaves that vehicle showing a position it has already moved on from.
A ten-second cache in front of the fleet list. Twenty people watching a dashboard do not need twenty identical queries a second. This is the only latency number the product sets itself; everything else is the tracker’s reporting rate.
Frequently asked
Can I run without ClickHouse?
Yes. Telemetry defaults to Postgres and ClickHouse sits behind a compose profile. The API is identical either way, so it is a capacity decision rather than a feature one — but switching later does not migrate existing points.
Why a seven-day TTL on raw positions?
Because trips, events and their figures are derived at ingest and live in Postgres, where nothing expires. The raw stream is what you need to redraw a track, and after a week almost nobody does. Change it if your case differs — it is one line, and the partitioning already makes it cheap.
How much RAM does ClickHouse need?
Less than its reputation suggests for this shape of workload. The production instance shares a 2-core, 1.9 GB host with Postgres, Redis, the API and the frontend.
Can I query it directly?
That is rather the point. It is your ClickHouse, and any SQL client will connect to it.
Where do trips get computed?
At ingest, in the backend, written to Postgres. Which is why a position history that expires does not cost you business records — and why replaying old positions produces our interpretation of them rather than the original system’s trips.
