QuestDB 10.2: commits that survive a power loss

QuestDB 10.2 is out, and most of what's new replaces something people have been working around. Choosing between fast commits and commits that survive a power loss. Writing one query per level of a report and adding up the totals somewhere else. Repeating the same filter in every query against a table that holds more than the application wants to see. Having a resent row overwrite the one that arrived first.

Here's what's new:

Power-loss durability for WAL tables, enabled with cairo.commit.mode=adaptive, makes commits survive an operating system crash or a power loss, for much less than sync costs. Each commit flushes only its write-ahead log files, and the table files are flushed once a minute. It's opt-in.

GROUPING SETS, ROLLUP and CUBE return detail rows, subtotals and a grand total from one query and one scan, with GROUP BY or SAMPLE BY.

Passthrough views and EXPIRE ROWS: a materialized view can now be a filtered copy of a table (SELECT ... WHERE), kept up to date as rows arrive. EXPIRE ROWS adds row-level retention on top, so the view keeps only the last hour, the outages that are still in effect, or the latest reading per device.

DEDUP SKIP keeps the first row written for a key and discards later copies, the counterpart of UPSERT.

The release also brings faster ingestion of small QWP messages, WAL apply fixes and a long list of other changes. If you only need the summary, read before you upgrade and go to getting 10.2.

Commits that survive a power loss

By default, QuestDB doesn't wait for the disk before acknowledging a commit, so an operating system crash or a power loss can lose the latest commits and leave table files damaged. Adaptive durability mode protects WAL tables from both:

cairo.commit.mode=adaptive

Each commit flushes only its own WAL files. The table files are flushed once a minute, and after a crash QuestDB rebuilds them from the WAL, so it restarts with every table consistent instead of tripping over damaged files.

• Pair it with durable acks. By default, commits share one flush every 50 ms, so a power loss can take the commits acknowledged in that window. With request_durable_ack=local, QWP clients keep each batch until the server confirms it's on the disk, and resend it after a reconnect. cairo.adaptive.commit.group.window=0 makes every commit wait for its own flush instead.
• What it costs: each commit waits for a flush, so it's slower than the default, but costs much less than sync. Larger batches and concurrent writers spread the cost.
• What it doesn't cover: non-WAL tables, or losing the disk itself.

The durability docs have the details.

Subtotals and grand totals with GROUPING SETS, ROLLUP and CUBE

A desk report wants volume per currency pair and venue, a subtotal per pair, and a grand total. ROLLUP returns all three levels from one query, in a single scan:

SELECT symbol, ecn,
       round(sum(quantity) / 1e6) AS volume_m,
       count() AS trades,
       GROUPING_ID(symbol, ecn) AS level
FROM fx_trades
WHERE timestamp IN '$yesterday'
  AND symbol IN ('EURUSD', 'GBPUSD')
GROUP BY ROLLUP(symbol, ecn)
ORDER BY level, symbol, ecn;

A rolled-up column comes back as NULL, and GROUPING_ID() tells those rows apart from real NULLs: 0 is a detail row, 1 a subtotal and 3 the grand total. CUBE adds totals in every direction, and GROUPING SETS lists only the levels you want. With SAMPLE BY 1h ROLLUP(ecn), every hour gets its own per-venue rows and total, and FILL works too.

Grouping sets don't use the vectorized GROUP BY path, so for a single-key sum() a UNION ALL of plain GROUP BY queries can still be several times faster on many cores. They can't be used in materialized views. The reference has the details, and the cookbook has a subtotals and grand totals recipe.

Keeping only the rows you need with EXPIRE ROWS

A grid operator's console lists planned outages, and must only show the ones still in effect. A materialized view can't filter on now(), so until now every query needed its own WHERE planned_end > now().

10.2 adds passthrough views, materialized views that copy their base table row for row, optionally narrowed to some columns or rows. EXPIRE ROWS attaches a policy to one, checked on every read:

CREATE MATERIALIZED VIEW outages_active AS (
  SELECT * FROM planned_outages
) EXPIRE ROWS WHEN planned_end < now();

Outages drop out of outages_active as they end. A condition on the designated timestamp, such as WHEN timestamp < dateadd('h', -1, now()), keeps a rolling window. KEEP LATEST PARTITION BY robot_id gives a fleet dashboard each robot's latest status, and KEEP 10 HIGHEST ON vibration PARTITION BY turbine_id the ten worst readings per turbine.

Only a WHEN condition on the designated timestamp frees disk. The others hide rows from queries, and KEEP policies rank the whole view on every read: in the PR's test, one day of a 20-million-row view took 7.2 ms to read without a policy and 5.0 s with KEEP 2 HIGHEST. Other materialized and live views can't read a view that has a policy.

DEDUP SKIP: keep the first copy of a row

Field gateways buffer sensor readings while they're offline, and resend everything since their last confirmed upload when they reconnect, including readings the server already has. With DEDUP UPSERT, every resent reading replaces the stored one, so a received_at column that recorded when each reading first arrived now shows the resend. The new SKIP mode keeps the first row and discards later ones, like INSERT ... ON CONFLICT DO NOTHING:

CREATE TABLE readings (
    timestamp TIMESTAMP,
    sensor_id SYMBOL,
    temperature DOUBLE,
    humidity DOUBLE,
    received_at TIMESTAMP
) TIMESTAMP(timestamp) PARTITION BY DAY
DEDUP SKIP KEYS(timestamp, sensor_id);

An existing table switches with ALTER TABLE ... DEDUP ENABLE SKIP KEYS(...), without rewriting stored rows. SKIP doesn't compare columns outside the key, and a commit that holds only duplicates leaves the partition untouched, Parquet partitions included. UPSERT got faster at the same case: resending unchanged rows into a Parquet partition no longer rewrites it.

Other changes

• Small QWP messages. One-row messages ingest at about 3.9 million rows per second, up from about 210,000, on one connection with 12 cores and tmpfs. 1,000-row messages are unchanged.
• WAL apply. A backlog of small out-of-order transactions over a wide time range no longer makes WAL apply fall back to one transaction at a time and fall behind. Idle QWP connections no longer block WAL cleanup.
• Parquet indexes. Partitions written by 9.3.4 to 10.0.1 could have indexes that miss rows. Queries over them now fail instead of returning wrong rows, most are repaired automatically, and FORCE REINDEX PARQUET repairs the rest online.
• Fixes in WINDOW JOIN, set operations, SAMPLE BY with FILL, RIGHT and FULL joins, SPLICE JOIN and materialized views, among others. See the release notes page.

Before you upgrade

• DEDUP SKIP. Older releases treat a SKIP table as UPSERT without an error, so keep that in mind before using it if you might need to downgrade.
• Materialized views. The part of a view's query that reads its base table can't use LIMIT or a window function inside an expression. Existing views keep refreshing, but replaying their SHOW CREATE output fails. See the restrictions.
• QWP senders that send the designated timestamp twice, through at() and as a named column, are now rejected. They used to be accepted and stored wrong timestamps.
• Parquet from 9.3.4 to 10.0.1. If you used indexes on Parquet partitions, run FORCE REINDEX PARQUET on tables the compaction job doesn't repair.

Before upgrading, read the upgrade checklist, which covers going back to an earlier release once 10.2 has run on a database.

Getting 10.2

docker pull questdb/questdb:10.2.0

Or download QuestDB directly. The full changelog is on GitHub.

If you try power-loss durability on your own storage, or DEDUP SKIP on a feed that resends data, tell us how it goes on Slack or Discourse.