Hexagons, Hypertables, and 240 Dead Tags: Migrating a Maritime Data Platform to TimescaleDB

Every ship in the world is constantly shouting its name into the void — position, heading, speed, destination, broadcast every few seconds over radio. At VesselAPI, we run one of the databases that tries to make sense of it all. For the first year of our existence, we ran it on MongoDB. This is the story of why we stopped.

It's also a story about hexagons and a single mismatched struct tag that quietly broke an entire nightly job.

The Shape of the Problem

AIS — the Automatic Identification System — is the backbone of maritime surveillance. Most commercial vessels are legally required to carry a transponder that broadcasts their identity and position. The result is a firehose: at peak hours the position reports arrive faster than any single-row insert loop could keep up with. Each one is a point in space and time — latitude, longitude, timestamp, vessel identifier, plus speed and heading. Position-in-time is the core of it.

If you squint, this data looks like a document. It has fields, you can serialize it as JSON, and MongoDB will happily store it. For the first few months that was fine: we were building fast, the schema changed daily, and MongoDB's flexibility was genuinely useful. Hard to have migration problems when there's nothing to migrate.

But vessel positions aren't documents. They're measurements. They have a timestamp and a location, and those aren't metadata — they're the whole point. The questions you ask are about time and space: Where was this ship two hours ago? What vessels are within 50 kilometers of Rotterdam right now? Show me everything that passed through the English Channel since Tuesday.

At the time, MongoDB had no native concept of any of this. (It has since added time-series collections, though they remain limited next to purpose-built engines.) It didn't understand that timestamps partition naturally into chunks, that old data expires, or that latitude and longitude define a point on a sphere where "within 50 kilometers" has real mathematical structure. You can bolt on 2dsphere indexes and TTL policies, but you're fighting the grain of the database — and at this volume, that gets expensive.

What We Needed (and What Exists)

I wrote the requirements on a whiteboard one afternoon and stood back. Time-series ingestion at sustained throughput. Automatic partitioning by time. Compression of old data. Retention that doesn't involve cron. Spatial queries on a sphere. Full-text search. Relational joins. And ideally something I could operate without a dedicated DBA. Looking at the list, I thought: this is either one very specific database, or three separate ones duct-taped together.

I spent a week on alternatives. InfluxDB handles time-series beautifully, but its spatial support was experimental, living in Flux — now being deprecated in InfluxDB 3.0 along with the geo package. ClickHouse kept coming up in benchmarks but the operational overhead scared me, and PostGIS isn't an option there.

TimescaleDB is PostgreSQL with a time-series engine bolted on deep enough that it feels native. And because it is PostgreSQL, you get PostGIS for spatial queries, H3 for hexagonal indexing, GIN indexes for full-text search, and nearly thirty years of battle-tested relational engineering. Turns out we didn't need three databases duct-taped together. We needed one.

Data With a Shelf Life

The central abstraction in TimescaleDB is the hypertable. From the outside it looks like a regular PostgreSQL table — you INSERT, you SELECT, you index it. Underneath, the data is automatically partitioned into chunks: contiguous slices of time, each stored as a separate physical table. I didn't appreciate how much this changes until I stopped thinking about storage and started thinking about expiry.

Our vessel_positions hypertable uses 1-hour chunks, so every hour of AIS data lives in its own partition. When we set a retention policy measured in weeks, TimescaleDB doesn't scan for old records to delete — it drops the chunks that have aged out. The entire partition disappears. It takes milliseconds.

One-hour chunks, retention measured in weeks. The table holds a fixed, recent window of data and stays bounded — no matter how long the system runs.

Compression works the same way. Once a chunk is two hours old — meaning we're no longer writing to it — TimescaleDB compresses it automatically. We segment by MMSI (the vessel's radio identifier, in every AIS message) and order by timestamp descending, so "give me the latest position for vessel X" can be answered from compressed data without decompressing the whole chunk. The storage savings are substantial; the win for time-range queries is bigger, because the planner knows which chunks to skip.

ALTER TABLE vessel_positions SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'mmsi',
    timescaledb.compress_orderby = 'timestamp DESC'
);
SELECT add_compression_policy('vessel_positions', INTERVAL '2 hours');

In MongoDB I had a cron job that ran a cleanup script every few hours. It failed silently for a week once and nobody noticed until disk usage alerted. In TimescaleDB we declare the retention policy and the database handles expiry itself. One fewer thing running at 3 AM that I have to worry about.

The Hexagon Problem

Here's a question that sounds simple: find all vessels within 100 kilometers of a given point.

PostGIS can answer this. You create a GIST spatial index on your geometry column, and ST_DWithin finds every point within a given distance — using the index to eliminate obvious non-candidates via bounding box checks, then computing exact distances for the rest. It works. It's well-engineered.

But when your table is very large and new rows arrive under constant load, "it works" isn't quite enough. The GIST index still has to traverse a tree of geometry — bounding boxes nested inside bounding boxes — and for high-volume tables with constant inserts, that gets heavy.

So we added a layer in front of it. That layer is made of hexagons.

H3 is a spatial indexing system originally built at Uber for matching riders to drivers. It tiles the entire surface of the Earth with hexagons at multiple resolutions — coarser at low resolutions, finer at high ones. Every point on the planet falls inside exactly one hexagon at each level, and each hexagon has a unique integer identifier.

We use resolution 5, where each hexagon has an edge length of roughly 9 kilometers and an area of roughly 253 square kilometers — about 2 million cells cover the whole Earth. Every vessel position, on insert, gets a computed H3 cell stored alongside it:

h3_cell_res5 H3INDEX GENERATED ALWAYS AS (
    h3_lat_lng_to_cell(location::point, 5)
) STORED

That STORED keyword matters. The H3 cell is computed once, at insert time, and written to disk as an integer. No recalculation at query time. And because it's an integer, we can slap a plain B-tree index on it — the simplest, fastest index PostgreSQL knows how to build.

Now, when someone asks for vessels within 100 kilometers of a point, the query doesn't go straight to the spatial index. First we compute which H3 cells overlap the search area — a quick geometric calculation returning a handful of integer IDs — then filter the table to only rows matching those cells, using the B-tree. Integer equality. Blazing fast. This collapses the candidate set from the entire table down to a few thousand rows. Then PostGIS takes over, running ST_DWithin on the survivors for exact distances. A few thousand rows through a precise spatial filter is trivial.

-- Stage 1: H3 pre-filter (integer comparison, B-tree)
h3_cell_res5 = ANY(ARRAY(
    SELECT h3_grid_disk(h3_lat_lng_to_cell(ST_MakePoint($1,$2)::point, 5), $6)
))
-- Stage 2: PostGIS exact filter (geometry, GIST)
AND ST_DWithin(location, ST_SetSRID(ST_MakePoint($1,$2), 4326)::geography, $3)
-- Stage 3: TimescaleDB chunk pruning (time range)
AND timestamp BETWEEN $4 AND $5

Three layers of filtering, each narrowing the set for the next: H3 knocks it from millions to thousands, PostGIS from thousands to hundreds, and chunk pruning keeps you from scanning data outside the time window at all.

Why hexagons? Because hexagons are the only regular polygon that tiles a plane with uniform adjacency — every neighbor shares an edge, and center-to-center distance is the same in every direction. Squares have diagonal neighbors that sit farther away than edge neighbors, which distorts distance calculations. For proximity queries, hexagons give the least distortion. Anyone who's looked at a honeycomb has seen the insight; Uber just built a production-grade library around it.

We tried resolution 4 first — the cells were too big, a single one covering so much ocean that the pre-filter barely filtered. Resolution 6 was better spatially but generated too many cells per query for the B-tree to check. Resolution 5 was the sweet spot: a 100-kilometer radius overlaps a manageable number of cells while still meaningfully shrinking the candidate set. We benchmarked it and moved on.

Feeding the Beast

Sustained high-volume ingest isn't a terrifying load for PostgreSQL on its own, but the naive approach still hurts. Individual INSERT statements, each a separate network round trip, spend more time in protocol overhead than actual writing. The database is fast; the network between your app and the database is not.

The obvious answer is PostgreSQL's COPY protocol, which streams raw row data and bypasses the SQL parser. We use it for vessel_eta and cache_ais_messages, where every column is a literal value we already hold in the application. But for vessel_positions we can't, and the reason is our own schema: COPY streams literal column values — it can't call a function mid-stream. Our location column is built server-side with ST_SetSRID(ST_MakePoint($lon, $lat), 4326), so to feed it through COPY we'd have to precompute the geometry as EWKB in the application first. Since we were already batching, reaching for COPY meant taking on that precompute for no clear win.

So we use pgx.Batch with SendBatch instead — the extended query protocol. It packs hundreds of parameterized INSERTs into a single round trip, and PostgreSQL executes them server-side without per-statement overhead. Not as fast as COPY, but an order of magnitude better than individual round trips:

batch := &pgx.Batch{}
for _, p := range positions {
    batch.Queue(
        `INSERT INTO vessel_positions
         (mmsi, imo, vessel_name, latitude, longitude, location,
          timestamp, processed_timestamp, suspected_glitch, ...)
         VALUES ($1, $2, $3, $4, $5,
                 ST_SetSRID(ST_MakePoint($5, $4), 4326),
                 $6, $7, $8, ...)`,
        p.MMSI, imo, p.VesselName,
        p.Latitude, p.Longitude,
        p.Timestamp, p.ProcessedTimestamp,
        p.SuspectedGlitch, /* ... */
    )
}
results := pool.SendBatch(ctx, batch)

Precomputing that EWKB is exactly what the COPY path would have forced on us. I briefly considered doing it on the batch path too, to skip the server-side ST_MakePoint on every row, but it's cheap enough server-side that it wasn't worth the complexity. Sometimes the schema you designed to make reads fast makes writes slightly harder. I'd make that trade again.

The Character That Broke the Ports

When your database speaks JSON but your structs still think in BSON.

Here is a bug that could only exist in a migration.

One of our nightly jobs rebuilds an internal reference table — the ports lookup that other tables join against. It marshals each port record, upserts it into production, and everything downstream keys off the result. Boring, mechanical, runs at 1 AM, nobody watches it.

The morning after the migration went live, production looked healthy from every angle I checked first: services up, ingest flowing, dashboards green. But the ports table was empty. Zero rows. The rebuild had produced nothing, and because port events join against ports on the port code, every port event created in that window got a null join key — rows that exist but point at nothing. The data was garbage and nothing had complained.

The root cause was one word.

The reference-row struct, a holdover from the MongoDB era, still carried dual serialization tags — something like:

type PortRef struct {
    Code string `bson:"port_code" json:"portCode"`
    // ...
}

The upsert helper works by marshaling each struct to JSON, then extracting a key field by name. The key parameter passed in was "port_code" — the BSON tag, the name MongoDB's driver used. But json.Marshal emits the JSON tag: "portCode". The helper looked for a field called port_code in the JSON document, found nothing, and returned an error. Not a silent error, technically — it threw a clear "key field not found". But nothing alerted on the nightly rebuild, and an error nobody checks is as good as silent. It ran, it failed, it failed again, every night at 1 AM, for days.

The fix was changing one string: "port_code" to "portCode".

The real fix was broader. I searched the codebase and found nearly 240 leftover bson:"..." tags scattered across the data-contract structs. The MongoDB driver wasn't even imported anymore — these tags were pure vestigial code, left over from the old world. Every one was a potential version of the same bug: a name from a system that no longer existed, waiting to be confused with a name from the system that did.

I spent two days on this — two days staring at logs, convinced the records themselves were malformed, before I thought to check the struct tags. It's a naming collision between two eras of the same codebase, and Go is happy to let it happen: struct tags are opaque strings the compiler ignores completely. No linter will save you. You notice it yourself, or you wait for production to notice it for you.

One Box, Not Three

All of this runs comfortably on a single Postgres box. A large, high-volume dataset with a bounded retention window, and the spatial and time-range queries stay fast — because hypertable chunk-drop retention and compression do the heavy lifting, not a bigger machine. PostgreSQL with the right extensions, doing the work that previously took MongoDB plus a constellation of application-level workarounds for everything MongoDB couldn't do natively.

A bulk carrier transiting a Norwegian fjord — one of the position reports streaming in around the clock.

What I'd Tell You at a Bar

Look, MongoDB was the right call when we started. I'd choose it again for that stage. The mistake was staying six months too long — past the point where the data had obviously hardened into a shape and we were just too busy to deal with it.

Honestly, moving the data was the easy part. The hard part — still ongoing — is finding all the places where the old system's assumptions are baked into the code. A struct tag referencing a serialization format you don't use anymore. A key parameter someone copied from a different struct's bson tag. Those survive the migration and sit there until they don't.

We're still finding bson tags. Probably will be for a while.