TSDB data gap improvements

[jmthomas]

The Data Gap calculation assumes continuous data. Data gap doesn't really make sense for ad-hoc data but it could still be useful for ingesting old data. Ensure there is a way to calculate gaps even with ad-hoc (not continuous data).

The Data Gap calculation appears to freeze with lots of data missing. Investigate whether this is due to large tables or large gaps and mitigate (perhaps force a smaller query or large gap size).

During the Data Gap query ensure there are backing raw log files. If the raw log files don't exist then there's nothing we can do to repair the gap anyway.

Can the Data Gap query support sample by day? d?

The TSDB page should show any WAL table errors on the page.

[crawlchat]

Thanks for this detailed feature request! This is clearly well-grounded in real-world pain points (as seen in related issue #3449 ) and the original TSDB management feature request . Here's a breakdown of the four items:

1. 🔄 Ad-hoc Data Gap Support

The current gap detection uses SAMPLE BY time-bucketing which assumes continuous data. For ad-hoc (infrequently transmitted) packets, empty time buckets don't necessarily mean a gap — the data just isn't expected at every interval.

Suggested approach:

• Add a "data mode" toggle on the gap UI: Continuous vs Ad-hoc
• For ad-hoc mode, instead of using SAMPLE BY, compare the raw log files in S3 against what's actually in QuestDB — if a .bin.gz file exists in the bucket but no corresponding rows exist in QuestDB for that time window, that's a real gap
• This aligns with how BucketUtilities.files_between_time() is already used for re-import discovery

2. ⚠️ Gap Query Freeze / Performance

The freeze is likely caused by a SAMPLE BY query spanning a very large time range with mostly-empty buckets, which QuestDB has to scan and generate a huge result set for.

Suggested mitigations:

• Cap the gap query to a maximum time window (e.g., 30 days at a time), paginating through larger ranges
• Use a coarser bucket size (e.g., SAMPLE BY 1h or 1d) for initial scans, then drill down
• Add a query timeout on the backend to avoid hanging the UI
• This is a real-world problem seen in TSDB/QuestDB, wal_pending_row_count always growing #3449 where the gap tool only showed May 29th despite data existing further back

3. 📁 Verify Backing Raw Log Files Before Showing Gaps

This is an important UX guard — showing a "gap" that can't be repaired is misleading.

Suggested approach:

• During gap detection, for each detected gap time range, call BucketUtilities.files_between_time() to check if .bin.gz files exist in S3 for that period
• Only surface gaps that have at least one backing raw file
• Gaps with no raw files could either be hidden or shown with a distinct "unrecoverable" indicator

4. 🔴 WAL Table Errors on TSDB Page

WAL errors (growing wal_pending_row_count) are already a known issue but currently not surfaced in the Admin UI.

Suggested approach:

• On the TSDB tab, query QuestDB's wal_tables() or table_wal_status system function
• Display any tables with wal_pending_row_count > 0 or error status prominently (e.g., a red banner or error row in the metrics table)
• Optionally link to the recovery steps (exec into container, remove bad partition folder, restart TSDB)

---

These are all solid, actionable improvements — would any of these need additional scoping or clarification before being triaged for a sprint? Also, is there a specific COSMOS version target for these fixes?