Row-level DELETE for data correction and compliance

[bluestreak01]

Summary

Support row-level deletes on WAL tables via standard SQL:

DELETE FROM trades WHERE symbol = 'TESTSYM' AND ts IN '2026-03';

A WHERE clause is mandatory. Non-WAL tables, views, and materialized views are rejected. This complements the existing partition-level lifecycle tools (DROP PARTITION, TTL, TRUNCATE) with surgical removal of individual rows.

Motivation

Time-series data is append-mostly, but real deployments still need targeted deletion:

• Compliance — right-to-be-forgotten (GDPR/CCPA) requests require removing specific rows, not whole partitions
• Data correction — bad ticks, mis-keyed instruments, faulty sensor readings polluting analytics
• Cleanup — removing test or backfill artifacts from production tables without downtime

Today these require CREATE TABLE AS SELECT workarounds or partition-level surgery. Row-level DELETE makes this a single, replication-safe SQL statement.

How it works

DELETE is recorded as a WAL SQL transaction and executed during WAL apply, choosing the cheapest strategy per predicate:

1. Pure designated-timestamp ranges collapse to a single empty replaceRange over the deleted interval
2. Other predicates rewrite survivor rows in timestamp windows under one atomic replace-range transaction
3. An opt-in bounded-disk mode commits eligible Parquet-backed deletes one window at a time to limit transient disk usage

Deletes replicate through WAL like any other transaction and work with deduplication, materialized views, and cold storage.

Enterprise

Table-level DELETE permission integrates with the ACL model, so operators can grant deletion rights per table, group, or service account.

Related

• Implementation: feat(sql): DELETE FROM <table> WHERE ... on WAL tables questdb#7403
• Enterprise ACL companion: questdb/questdb-enterprise#1132