BasekickLabs

Delete Operations

Delete data from an Arc Enterprise cluster by rewriting Parquet files: enable delete in arc.toml, scope by measurement and time range, and audit every deletion.

Arc supports deleting data using a rewrite-based approach that provides precise deletion with zero overhead on write and query operations.

Configuration Required

Delete operations must be explicitly enabled in configuration for safety.

Overview

Arc's delete operations provide:

  • Precise Control: Delete specific rows using WHERE clauses
  • Zero Runtime Overhead: No performance impact on writes or queries
  • Physical Removal: Data is permanently removed by rewriting Parquet files
  • Safety Mechanisms: Multiple safeguards prevent accidental deletion
  • Dry Run Mode: Test operations before execution

How it works

Arc deletes rows by rewriting the Parquet files that hold them:

1. Flush buffered rows

Rows written shortly before the DELETE may still be in the ingest buffer. Since v27.01.1 the DELETE first flushes that measurement's buffered rows and waits for them to reach files, so every row written before the request is deleted. While an earlier flush failure is outstanding (its rows wait for WAL recovery), the DELETE answers 503 with Retry-After: 30 instead. With the WAL off there is no recovery to wait for (a failed flush's rows are lost), and since v27.01.1 such a failure no longer refuses later DELETEs. A dry run does not flush.

In shared storage (Pattern 2, cluster.shared_storage_mode) every writer takes writes, and the DELETE runs on the primary (the Raft leader). Since v27.01.1 the primary first asks every other node of the cluster (readers and compactors too: they can buffer rows from MQTT or an import) to flush its buffered rows of the measurement, then waits up to 30 seconds for the files they flushed to be in the cluster manifest. A node that does not answer within 40 seconds, or whose files do not register in time, is listed in unflushed_nodes and the DELETE answers 503. With allow_unreachable it runs anyway, still lists them, and the rows those nodes hold are not deleted.

A node that crashed is still a node of the cluster until it returns or is removed, so it blocks DELETEs in the meantime. Set cluster.node_id on every node, so a restart does not come back under a new id and leave the old one behind. In a cluster being upgraded to 27.01.1, a node still on an older release does not answer, so DELETEs answer 503 until every node is upgraded, unless allow_unreachable is set.

2. Find affected files

List the measurement's Parquet files that the WHERE clause's time range can touch, and count the rows matching the clause in each, with DuckDB.

Time range. Since 27.01.1, when the WHERE bounds time (time >= … AND time < …, BETWEEN, or now() plus or minus an interval), the DELETE reads only the hour and day partition directories that range can touch, plus one hour on each side. A clause without such a range reads every file of the measurement: an OR at the top level, time = …, time IN (…), or time compared with anything other than a literal or now(). Deployments that still hold data written by Arc 25.12 or earlier set delete.time_pruning = false (see Configuration).

Counting. Files are counted 64 at a time, without keeping their Parquet metadata in memory, so a DELETE's memory follows the chunk, not the table. A file that does not have a column the clause names, or has it with another type, is counted together with files that do, as one read across every file would count it. When no file in the range has the column, it matches nothing.

A file that cannot be counted stops the DELETE before anything is rewritten. A file replaced since the listing (by a compaction) answers 503: retry. A file that cannot be read answers 500 and names it. Before 27.01.1, such a file was skipped and the DELETE answered success.

In a cluster with compaction commit on (every node runs Arc 2027.01.1 or later), the DELETE first catches up with the cluster manifest, then scans the files the manifest lists for the measurement (those in the DELETE's time range), plus files this node is still registering. A missing file outside the time range is neither waited for nor reported. A file in storage the manifest does not list, such as a compaction output not committed yet, is left alone. A file the manifest lists that is not on the node yet (a compaction output on its way) is waited for, up to 30 seconds per scan; a file the manifest stops listing meanwhile is no longer waited for. A file still missing answers 503 listing it in unreachable_files, unless allow_unreachable is set, in which case the DELETE runs without it and still lists it. A dry run does not wait and lists the files it could not count.

With allow_unreachable (v27.01.1+), a missing file is waited for only while something is delivering it: the replication puller has it queued or in progress, or a staged compaction holds it. A file missing for 5 seconds with nothing delivering it is skipped for the rest of the request. Without the flag a DELETE can wait up to 30 seconds per scan, and it may scan more than once, so give clients a timeout well above 30 seconds. The rows of a file in unreachable_files are not deleted: run the DELETE again once the file is on the primary.

On a shared bucket (S3, Azure), a DELETE scans what queries read:

  • A listed file whose object is gone is skipped. The exception is a compaction output its finisher is still copying into place, which the DELETE waits for.
  • An object of the measurement the manifest does not list (such as a compaction output left by a release before 2027.01) is scanned too: its rewrite is registered, or it is removed when every row matches. With stream claims on, such an object is left alone.
  • The input objects of a committed compaction that has not finished are skipped: its output holds their rows.
  • In shared storage, an object of the measurement the manifest does not list that is under two minutes old is left as it is, and the DELETE answers 503 (the rows of the other files are deleted). It is most likely another writer's file on its way to the manifest: that writer registers a file it flushed within 30 seconds without looking at it again, so rewriting it now would leave the manifest listing an object that no longer exists. A retry finds it registered.

3. Rewrite files

For each affected file:

  1. Write the rows that do not match, SELECT * FROM read_parquet(file) WHERE NOT (<where>), to a new file with a fresh name (<name>_rewrite_<nanoseconds>.parquet) in the same partition directory
  2. Publish the new file, then remove the old one (in a cluster, both in one manifest update)

A file whose rows all match is removed.

Wide files (v27.01.1+)

The copy holds, per DuckDB thread, a decoded row group (about 9 bytes per value however sparse the data, so about 3 GB for 122,880 rows of 3,100 columns) plus the Parquet writer's state for every column, much of it outside database.memory_limit. Before each rewrite Arc reads the file's footer and estimates that. When the main DuckDB would need more than half its effective memory limit, the file is rewritten on a private DuckDB instance instead: one thread, half the main memory limit, its own spill directory, sandboxed to the file's own directories and with no extensions, keeping the row order. On S3 or Azure the file is downloaded first. There is no setting.

Measured on DuckDB 1.5.6 with a 4 GB limit: a DELETE on a 3,100-column, 983k-row file took 24 s at about 1.9 GB, where before it spilled for 7 minutes and then ran out of memory. 905 columns took 18 s, 10,000 columns 23 s.

What to expect:

  • Rewritten wide files are larger and slower to read until compaction merges them. Their row groups are smaller (32,768 rows at 905 columns, 8,192 at 3,100, 2,048 at 10,000), so each file carries more column chunks and a larger footer: a one-second query on a rewritten 3,100-column file took 240 ms instead of 30 ms on the original.
  • Wide rewrites run one at a time, single-threaded, as all rewrites already run one at a time. A long narrow file can take the same path on a many-core node.
  • The private instance's memory is on top of the main one's. With the default memory limit (80% of the machine) the two together can exceed the machine while queries run.
  • A WHERE that calls an extension function (arcx, httpfs) fails on a wide file.
  • A file whose footer cannot be read is not rewritten: it is reported in failed_files (207) and the DELETE can be retried.

Every rewrite, wide or not, now keeps Arc's Parquet metadata (arc:tags, arc:dedup_exact, arc:decimals); before v27.01.1 a rewrite dropped it, so a rewritten continuous-query file lost its exact-duplicate marker at compaction.

Crash safety

The old file stays in place until its replacement is published, so a crash leaves either the old file or the new one, never a partial file. Two DELETEs rewrite one at a time.

Compaction during a DELETE

In a cluster with compaction commit on, a DELETE publishes its rewrite of a file only while the file is still registered with the content it read. If a compaction replaced the file while the DELETE ran, the DELETE waits until the compaction's output reaches the node (up to 30 seconds), scans again and deletes from the output. After three rounds of files changing under it, it answers 503 with success: false: the rows of the files listed in files_processed are deleted; retry to finish.

Files in cold storage

With tiered storage, files tiering moved to cold storage are covered by a DELETE too (since 27.01.1):

  • A cold file whose rows all match is deleted, the way retention deletes cold files. Its rows are counted in deleted_count, and the file is listed in cold_deleted_files.
  • A cold file where only some rows match is left as it is. The answer is 207 with success: false: cold_files and failed_files name the file, and cold_rows_not_deleted counts its matching rows. Arc does not rewrite part of a cold file. Narrow the clause so that it matches whole files (a time range covering whole days, since cold files are daily files), or let retention remove them.
  • A file that is still being moved to cold storage, or that has a copy in both tiers, is left as it is and reported; a retry handles it once the move completes.
  • A cold file that does not have a column the clause names (an older file) is counted together with cold files that have it. When no cold file in the time range has it, it matches nothing.
  • If cold storage is configured but could not be examined (its backend is unusable on this node, or listing it failed), the response says so in cold_not_examined, with 207.

Cold rows that would be deleted count against max_rows_per_delete and the confirmation threshold; matching rows left in cold storage do not. A dry run reports both kinds, and its deleted_count includes only the rows the real run would delete.

Every cold file in the DELETE's time range (every cold file of the measurement, when the clause has no time range) is read to count its matching rows, so a DELETE over many files in cold storage takes longer.

Configuration

Delete operations must be explicitly enabled and configured.

Configuration file

Edit arc.toml:

[delete]
enabled = true
confirmation_threshold = 10000
max_rows_per_delete = 1000000
time_pruning = true

Environment variables

export ARC_DELETE_ENABLED=true
export ARC_DELETE_CONFIRMATION_THRESHOLD=10000
export ARC_DELETE_MAX_ROWS_PER_DELETE=1000000
export ARC_DELETE_TIME_PRUNING=true

Configuration parameters

  • enabled (boolean): Enable/disable delete functionality (default: false)
  • confirmation_threshold (integer): Row count requiring explicit confirmation (default: 10000)
  • max_rows_per_delete (integer): Maximum rows allowed per operation (default: 1000000)
  • time_pruning (boolean): Read only the partitions the WHERE's time range can touch (default: true, since 27.01.1). It relies on every file under an hour or day directory holding only rows of that hour or day, which holds for everything Arc has written since 26.01.1. Arc 25.12 and earlier put a file in the directory of the hour it was flushed, and compaction and cold migration keep that directory, so set it to false on a deployment that still holds data written by those versions: a windowed DELETE would otherwise leave rows that arrived late.

API endpoints

Delete data

Execute a delete operation:

POST /api/v1/delete

Request Body:

{
  "database": "telegraf",
  "measurement": "cpu",
  "where": "host = 'server01' AND time < '2024-01-01'",
  "dry_run": false,
  "confirm": false
}

Parameters:

  • database (string, required): Target database name
  • measurement (string, required): Target measurement name
  • where (string, required): SQL WHERE clause for deletion. Columns may be qualified by the measurement name (cpu.host = 'a' or "cpu".host = 'a' on measurement cpu, v27.01.1+); the qualifier is dropped before the clause runs. Quote a measurement named like an SQL keyword ("order".id).
  • dry_run (boolean, optional): Test without deleting (default: false)
  • confirm (boolean, optional): Confirm the operation; required for anything but a dry run (default: false)
  • allow_unreachable (boolean, optional, clusters): run the DELETE although files the cluster manifest lists have not reached the primary yet, or, in shared storage, other nodes could not flush their buffered rows first; those rows are not deleted. Applies with stream takeover or compaction commit, to the first scan and to the scan after a compaction replaced a file, and in shared storage to the flush of the other nodes (default: false)

Response:

{
  "success": true,
  "deleted_count": 15000,
  "affected_files": 3,
  "rewritten_files": 3,
  "execution_time_ms": 1250,
  "dry_run": false,
  "files_processed": [
    "cpu_20231215_100000_000000000.parquet",
    "cpu_20231220_100000_000000000.parquet",
    "cpu_20231221_100000_000000000.parquet"
  ]
}
  • files_processed lists the names of the files rewritten or removed (on a dry run, the files that would be).

  • failed_files (when present) lists files that could not be rewritten; the response is then 207 with success: false, and the rows of those files are not deleted.

  • unreachable_files (clusters) lists manifest files the primary did not have: skipped with allow_unreachable, or the reason for a 503 without it.

  • cold_deleted_files, cold_files, cold_rows_not_deleted and cold_not_examined describe what the DELETE did in cold storage; see Files in cold storage.

  • unflushed_nodes (shared storage) lists the other nodes whose buffered rows could not be flushed first, each with its health, the reason, and what to do: left with allow_unreachable, or the reason for a 503 without it.

    {
      "success": false,
      "unflushed_nodes": [
        "node writer-0 (state dead): no answer: failed to read length: read tcp 10.42.0.166:54706->10.42.0.168:9100: i/o timeout; bring it back, remove it from the cluster if it is gone for good, or set allow_unreachable=true to leave the rows it holds"
      ],
      "error": "Delete rejected: 1 other node(s) could not flush their buffered rows of this scope first, so the delete would not see them; retry, or set allow_unreachable=true to leave the rows they hold"
    }

Get configuration

Retrieve current delete configuration:

GET /api/v1/delete/config

Response:

{
  "enabled": true,
  "confirmation_threshold": 10000,
  "max_rows_per_delete": 1000000
}

Safety mechanisms

1. WHERE clause required

Delete operations must include a WHERE clause to prevent accidental full-table deletion.

Intentional Full Delete:

{
  "where": "1=1"  // Explicitly delete all rows
}

2. Confirmation threshold

Operations exceeding the configured threshold require explicit confirmation:

{
  "where": "time < '2024-01-01'",
  "confirm": true  // Required if deleted_count > threshold
}

Without Confirmation:

{
  "error": "Operation would delete 15000 rows, exceeding threshold of 10000. Set confirm=true to proceed."
}

3. Maximum rows limit

Hard cap prevents extremely large operations that could exhaust resources:

{
  "error": "Operation would delete 2000000 rows, exceeding maximum of 1000000"
}

4. Immutable rewrites

A rewrite is published under a new file name before the old file is removed: a crash leaves the old file or the new one, never a partial file.

Usage examples

Example 1: delete old data

import os
import requests

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Delete data older than a specific date
response = requests.post(
    "http://localhost:8000/api/v1/delete",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "database": "telegraf",
        "measurement": "cpu",
        "where": "time < '2024-01-01'",
        "confirm": True
    }
)

print(f"Deleted {response.json()['deleted_count']} rows")
print(f"Execution time: {response.json()['execution_time_ms']}ms")

Example 2: delete specific host data

import os

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Delete data from a specific host
response = requests.post(
    "http://localhost:8000/api/v1/delete",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "database": "telegraf",
        "measurement": "cpu",
        "where": "host = 'server01' OR host = 'server02'",
        "confirm": True
    }
)

Example 3: dry run first

import os

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Always test with dry run before deleting
dry_run = requests.post(
    "http://localhost:8000/api/v1/delete",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "database": "telegraf",
        "measurement": "cpu",
        "where": "host = 'server01'",
        "dry_run": True
    }
)

print(f"Would delete {dry_run.json()['deleted_count']} rows")
print(f"Affected files: {dry_run.json()['affected_files']}")

# Review the files that would be affected
for name in dry_run.json()['files_processed']:
    print(f"  {name}")

# If satisfied, execute for real
if input("Proceed? (yes/no): ") == "yes":
    result = requests.post(
        "http://localhost:8000/api/v1/delete",
        headers={"Authorization": f"Bearer {ARC_TOKEN}"},
        json={
            "database": "telegraf",
            "measurement": "cpu",
            "where": "host = 'server01'",
            "dry_run": False,
            "confirm": True
        }
    )
    print(f"Deleted {result.json()['deleted_count']} rows")

Example 4: delete with confirmation

import os

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Large delete requiring confirmation
response = requests.post(
    "http://localhost:8000/api/v1/delete",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "database": "telegraf",
        "measurement": "cpu",
        "where": "time < '2023-01-01'",
        "confirm": True  # Explicitly confirm large operation
    }
)

Example 5: complex WHERE clause

import os

ARC_TOKEN = os.environ["ARC_TOKEN"]

# Delete based on multiple conditions
response = requests.post(
    "http://localhost:8000/api/v1/delete",
    headers={"Authorization": f"Bearer {ARC_TOKEN}"},
    json={
        "database": "telegraf",
        "measurement": "cpu",
        "where": """
            host IN ('server01', 'server02', 'server03')
            AND time BETWEEN '2023-01-01' AND '2023-06-30'
            AND usage_idle < 10
        """,
        "confirm": True
    }
)

Performance characteristics

Delete operations are computationally expensive but designed for infrequent use:

Execution times

Small Files (10MB):

  • Read + Filter + Write: ~50-100ms per file

Medium Files (100MB):

  • Read + Filter + Write: ~500ms-1s per file

Large Files (1GB):

  • Read + Filter + Write: ~2-5s per file

Performance factors

  • Files in the time range: Only the files the WHERE's time range can touch are read; a clause without a time range reads every file of the measurement
  • File Size: Larger files take longer to rewrite
  • Selectivity: Fewer deleted rows = faster (less data movement)
  • Storage I/O: Disk speed affects read/write performance
  • Concurrent Load: Other operations may slow deletion

Best practices

1. Keep disabled by default

Only enable delete operations when needed:

[delete]
enabled = false  # Enable only when necessary

2. Always use dry run

Test operations before execution to verify scope:

# Step 1: Dry run
result = requests.post(..., json={"dry_run": True})
print(f"Would delete {result['deleted_count']} rows")

# Step 2: Review affected files
for file in result['files']:
    print(f"{file['path']}: {file['action']}")

# Step 3: Execute if satisfied
result = requests.post(..., json={"dry_run": False, "confirm": True})

3. Consider retention policies

For time-based deletion, use retention policies instead:

# Instead of manual deletes:
# requests.post("/api/v1/delete", json={"where": "time < '2024-01-01'"})

# Use retention policies:
requests.post("/api/v1/retention", json={
    "retention_days": 90,
    "buffer_days": 7
})

4. Monitor execution times

Track deletion performance for capacity planning:

import time

start = time.time()
result = requests.post("/api/v1/delete", json={...})
elapsed = time.time() - start

print(f"Deleted {result['deleted_count']} rows in {elapsed:.2f}s")

5. Batch large deletes

Break large deletions into smaller batches by time range:

from datetime import datetime, timedelta

# Instead of one large delete:
# WHERE time < '2023-01-01'

# Batch by month:
start = datetime(2022, 1, 1)
while start < datetime(2023, 1, 1):
    end = start + timedelta(days=30)

    requests.post("/api/v1/delete", json={
        "where": f"time >= '{start.isoformat()}' AND time < '{end.isoformat()}'"
    })

    start = end

6. Understand storage impact

Deletion rewrites files, which may temporarily increase storage usage:

# Before deletion: 100MB original file
# During deletion: 100MB original + 60MB temp file = 160MB
# After deletion: 60MB rewritten file

Ensure sufficient disk space for temporary files during operations.

Limitations

Not for frequent operations

Delete operations rewrite entire Parquet files, making them expensive. They are designed for infrequent, manual operations only.

Use Cases:

  • Removing test data
  • Deleting specific hosts/sensors
  • One-time cleanup operations

Not Suitable For:

  • Automated recurring deletions (use retention policies)
  • High-frequency data cleanup
  • Real-time data removal

Explicit WHERE required

Full-table deletion requires explicit WHERE 1=1:

# This will fail:
{"where": ""}  # Error: WHERE clause required

# Explicit full delete:
{"where": "1=1", "confirm": True}

Maximum row limits

Large deletions are subject to max_rows_per_delete configuration:

# Will fail if exceeds limit:
{"where": "time < '2020-01-01'"}  # May exceed max_rows

# Solution: Batch by time range
{"where": "time >= '2023-01-01' AND time < '2023-02-01'"}

One DELETE at a time

DELETEs rewrite one at a time; a second one waits for the first. Writes are not blocked.

Troubleshooting

Delete not enabled

Problem: delete.enabled is false (the default).

Solution:

[delete]
enabled = true

Confirmation required

Problem: Operation exceeds confirmation threshold.

Solution: Add confirm: true:

{"confirm": true}

Exceeds maximum rows

Problem: Deletion would affect more rows than max_rows_per_delete.

Solutions:

  1. Batch the operation by time range
  2. Increase max_rows_per_delete (carefully)
  3. Use retention policies for large-scale cleanup

No rows deleted

Problem: deleted_count: 0 but expected deletions.

Solutions:

  • Verify WHERE clause syntax matches data
  • Check that data exists in the specified database/measurement
  • Use dry run to inspect affected files

Delete fails naming a file

Problem: The DELETE answers 500 with could not count the rows of <file>, or 503 with files changed under the delete while it counted them.

Solutions:

  • 503: a compaction replaced the file while the DELETE counted it. Retry.
  • 500: the file cannot be read (a corrupt or truncated Parquet file). Every DELETE whose time range includes it fails the same way until the file is repaired or removed. Nothing was deleted.

Slow execution

Problem: Delete operations take longer than expected.

Solutions:

  • On a table of hundreds or thousands of columns, upgrade to v27.01.1: before it, a rewrite of a wide file spilled for minutes per row group and could run out of memory (see Wide files)
  • Check file sizes (large files take longer)
  • Monitor disk I/O performance
  • Batch operations during low-traffic periods
  • Consider using retention policies for time-based cleanup

On this page