TimescaleDB Compression Made Empty Results the Slowest Query

TimescaleDB compression trades indexes for storage. VesselAPI's account of a September 2026 investigation shows what happens when that trade is made on a table where it buys almost nothing, and we think it is one of the best public write-ups of a failure class most teams never measure, the expensive empty result.

The headline numbers are stark. Asking for a vessel's latest port event took 28 ms when one existed and 2.0 to 2.5 seconds when none did, and on the ETA endpoint, requests that ended in not found consumed most of the database time.

This post covers why misses cost more than hits, why the dashboard hid it, how the compression settings caused it, how a verification run passed while testing nothing, what VesselAPI changed, and when compression is not the culprit.

Twenty-Eight Milliseconds or Two Seconds for the Same Query

According to VesselAPI's write-up, on 9 September 2026 its API returned a vessel's most recent port event in 28 ms if the vessel had one, and in 2.0 to 2.5 seconds if it had none.

The ETA lookups were worse. For a vessel whose IMO number could not be mapped to an MMSI, a request took 12 seconds on a quiet database and 30 seconds or more on a busy one, until the statement timeout ended it. Over one night, requests that ended in not found used 71 percent of all database time on the ETA endpoint.

We find the shape familiar. The fast path is the one everyone tests, because test data usually contains the thing being looked up. The slow path only appears with real traffic asking for things that are not there.

Why a Miss Costs More Than a Hit in Almost Every Store

A lookup that can stop early is cheap when it succeeds. With an ORDER BY on time and LIMIT 1, the executor visits the newest data first and stops at the first matching row. A miss has no first row, so it keeps going until it has proved the row does not exist anywhere it could be.

That asymmetry is not specific to TimescaleDB. We see it wherever the fast path depends on stopping early.

• Partitioned tables where the query's predicate does not prune, so a miss visits every partition.
• Bloom or min-max filters that cannot reject a lookup because of how the data is distributed.
• Caches that store hits but not misses, so every not-found request goes to disk.
• Indexes that exist on some segments of a table but not others, so a miss falls through to sequential scans.

Our guide to proving partition pruning happened covers the first case in detail.

The Dashboard Grouped by Raw Path and Hid the Pattern

VesselAPI's latency dashboard averaged by raw path. A single 40-second call for one port sat at the top of a slowest-paths panel and looked like an outlier. Grouping by route template over six hours showed something else. Two port-event routes were slow in the median, and only when the answer was empty.

A port with recent events answered in 10 to 100 ms, while a small port with no events in the requested window took 3.6 to 4.3 seconds, every time. Grouping by raw path had turned a systematic failure into scattered noise.

We would check this in any metrics setup. The OpenTelemetry HTTP metrics conventions define http.route as "The matched route template for the request," which "MUST be low-cardinality." Any latency panel keyed on the raw URL instead mixes identifiers into the group key and hides exactly this pattern. Empty responses also emit no errors, so nothing else raises an alarm. Our p99 latency guide covers the related problem of averages hiding tails.

How TimescaleDB Compression Left Misses With No Usable Index

The port_events table had 764 one-hour chunks, and 760 of them were compressed, segmented by vessel MMSI and ordered by time. Those settings had been copied from the positions table, which is read by MMSI. Port events are queried by other columns, and VesselAPI concludes plainly that the settings were wrong for that table.

• The Index Was Defined and Held Nothing

The table had a btree index on vessel IMO and timestamp. On a compressed chunk that index is still defined but holds nothing, because the rows live in an internal table whose only index covers the segment column and time metadata. Only the four or so uncompressed chunks at the head of the table had a usable IMO index.

A vessel with a recent event was found in those head chunks and the query stopped. A vessel with no event produced a plan with one sequential scan per compressed chunk. The same miss by MMSI, the segment column, took 0.12 seconds on the same chunks.

• NULL Bloom Filters Passed Every Lookup

TimescaleDB keeps a bloom filter per compressed batch for columns such as the IMO, which should let a miss skip most batches. In VesselAPI's data, a large minority of rows carry no IMO. A batch whose IMO values are all NULL carries a NULL bloom filter, and a NULL filter passes every lookup, so the safety net did far less than expected.

• A COALESCE Subquery Skipped the Filter Entirely

The by-port query compared the port code with a COALESCE over a subquery. In the versions VesselAPI ran, that expression was not pushed down to the bloom filter, so every batch in every chunk of the window was decompressed. That took 6.05 seconds for a port with two rows, against 0.84 seconds with a plain bound parameter.

• Compression Saved Two Megabytes

The compression statistics showed 1,689 MB before compression and 1,687 MB after. Segmenting by MMSI inside a one-hour chunk meant most segments held a single row, so there was nothing to compress across. On the ETA table, where vessels report many times an hour, the same settings gave 3.0x.

We think this is the central lesson. An optimisation you have not checked against your real data distribution is a hypothesis. Here it cost the use of every index except one across 760 chunks and returned 2 MB.

A Byte-Identical Check That Tested Nothing

Before switching the ETA endpoints to a new read path, VesselAPI compared 4,105 API responses between the old and new modes, found no differences and counted it as a pass. The release then returned 500 on two endpoints for about half an hour, because of a NULL scanned into a Go string, before post-deploy probes caught it and it was rolled back.

The diff had never run against the new code. A server from an earlier test still held the port, both new servers failed to bind with address already in use in a log nobody read, and every request went to the old process. The 4,105 responses matched because one server produced all of them.

A second gap appeared on re-running. Every inbound request in earlier runs had returned 400 on both sides, because the script asked for a page of 200 against a limit of 50. The harness had been confirming that two identical error bodies were identical. We wrote about the same class of failure in CI/CD steps that stay green and do nothing.

What VesselAPI Changed and the Lesson That Generalises

For port_events, VesselAPI moved to daily chunks with no compression, so the btree indexes work on every chunk, at the cost of about a sixth more disk. It decompressed 762 chunks one per transaction under live traffic, with lock timeouts and retries.

For ETAs, it kept compression, which earned its ratio there, and added a plain table holding the newest report per vessel, destination, ETA value and IMO. A one-row-per-vessel design returned different answers from the history table, which is why the key grew. After moving a background refresh job to its own small connection pool, ETA p99 was 32 ms.

The change we would copy is in verification. The diff script now exits if its port is taken and checks for its own server's startup line, and a test scans a real row from the new table. Checks should fail loudly when they cannot prove they are testing what they claim. When we investigate a slow path ourselves, we start by connecting read-only to the database, logs and code so the plan, the traffic and the query text can be read side by side.

When Compression Settings Are Not the Problem

We would not conclude that TimescaleDB compression is risky in general. On the ETA table, the same settings compressed 3.0x, and moving to six-hour chunks raised that to 3.9x, so compression stayed. The problem was applying settings tuned for one access pattern to a table queried another way.

A miss is also cheap when the lookup column has a usable index on every chunk or matches the segment column. Before changing compression, we would run the miss path with EXPLAIN ANALYZE, which the PostgreSQL documentation on EXPLAIN describes as executing the query and showing true row counts and run time per plan node, and confirm where the time goes.

Measure the Empty Case Before You Tune TimescaleDB Compression

The VesselAPI case is a reminder that the most expensive queries can be the ones that return nothing, and that ordinary dashboards are built in a way that hides them. We would take four questions to our own systems this week.

1. Does one latency panel group by route template, and do its medians still look healthy?
2. For the three busiest lookups, what is the latency of a zero-result query compared with a hit?
3. For each index or TimescaleDB compression setting we rely on, what data condition would silently disable it?
4. For each verification harness we trust, how could it pass while testing nothing?

When a slow path like this turns up in production, Operate runs dedicated AI agents that investigate root causes with cited evidence and propose fixes for engineers to review, with a separate agent on a different model checking each proposed root cause.