When Chunk Drops, ctids, and Dead Tuples on a TimescaleDB Hypertable Cause Scheduled Load Spikes

August 20, 2026 Christopher 8 min read

postgresqltimescaledbhypertableretentionautovacuum

Every night between roughly 03:17 and 03:52 UTC, the database was under a heavy load for half an hour. Nothing was scheduled and no customers were active during these windows. ET Ducky's API has a data-retention service built for database sanitation, but it was the single largest source of load on it for a short window each night for months.

This post covers how we traced the spike, what the rewrite changed, and two things we found that we did not expect. Most of it applies to any time-series workload on a TimescaleDB hypertable with per-tenant retention.

The table and the retention model

The Events table is a Timescale hypertable. It holds kernel-telemetry events from every agent across every tenant, and it is chunked on time with a 7-day chunk width. Each organization has its own retention, either a tier default of 14, 90, 365 or 730 days, or a per-org override.

Once per pulse, the retention service ran this for every organization:

DELETE FROM "Events"
WHERE "OrganizationId" = $1 AND "Timestamp" < $2

The deletes were batched, with a small delay between batches. On a plain table this is the right approach. On a hypertable it caused the spike.

Why per-org row DELETEs are expensive on a hypertable

A DELETE in Postgres does not remove a row. It marks the tuple dead and leaves it in the page for vacuum to reclaim later. At about 1.3 million rows a night, that produces 1.3 million dead tuples a night. Autovacuum then has to scan the affected chunks to clean them up. Most of the cost is the autovacuum work that follows the delete.

Hypertables add a second effect. Chunks are partitioned by time only. A chunk holds rows from every organization whose data falls in that time window. A per-org DELETE therefore clears part of a chunk and leaves the chunk in place. The chunk can only be removed once no organization still retains that time range.

This is how the Events hypertable reached about 20 GB with zero live rows. Every tenant's rows had been deleted correctly at their cutoff. Nothing had ever dropped the empty chunks.

One detail slowed the diagnosis. A separate orphan-cleanup path purged organization 0 at a 7-day cutoff. That cutoff fell in the middle of the chunk range instead of at the end, so the dead tuples concentrated in a middle chunk. Retention problems usually show up in the oldest chunk.

Dropping chunks instead of deleting rows

drop_chunks() runs a DROP TABLE on the whole chunk. It reclaims space as a metadata operation. There are no dead tuples and no autovacuum work, and the cost does not depend on how many rows the chunk held.

The tenancy constraint above still applies. A chunk can only be dropped once it is older than the longest retention any active subscriber holds. Dropping it sooner deletes data a paying customer still owns.

The service computes that maximum from the subscription table on every run:

/// <summary>Longest Events retention (days) across the given active subscriptions,
/// floored at the free-tier minimum.</summary>
public static int MaxEventsRetentionDays(
    IEnumerable<(int RetentionTier, int? RetentionDaysEvents)> subs)
{
    var maxDays = MinRetentionDays;          // 14, the free-tier promise floor
    foreach (var s in subs)
    {
        var effective = s.RetentionDaysEvents ?? TierDays(s.RetentionTier);
        if (effective > maxDays) maxDays = effective;
    }
    return maxDays < MinRetentionDays ? MinRetentionDays : maxDays;
}

That gives the safe cutoff. It also determines which organizations still need a row purge:

// Row purge only for orgs whose Events retention is SHORTER than the fleet max.
// Orgs AT the fleet max are retired wholesale by the chunk sweep's drop_chunks
// (metadata-only, no dead tuples); their rows outlive the plan by at most one
// 7-day chunk width.
if (policy.EventDays < fleetMaxEventDays)
{
    total += await BatchDelete(context,
        "DELETE FROM \"Events\" WHERE \"OrganizationId\" = {0} AND \"Timestamp\" < {1}",
        orgId, eventCutoff, ct);
}

An organization at the longest retention in the fleet gets no row purge. Its chunks are dropped whole instead. Organizations below the fleet maximum still need row deletes, because nothing else removes their rows from a chunk that a longer-retention tenant still needs.

If every organization shares one retention tier, that condition is false for all of them and the nightly Events row purge stops running.

Two other tables got the same treatment. AgentHealthMetrics raw chunks now drop at a flat 14 days for everyone. Paid long retention is served from a rollup table, so its row purge only fires for a custom override shorter than 14 days. HardwareHealthMetrics had no retention path at all and had grown without bound since March. It got its own chunk service and a bounded row purge.

Results after deploy

The purge on the following night ran from 03:35:56 to 03:36:31. That is 35 seconds and 11,944 rows. Before the change it was roughly 35 minutes and 1.3 million rows.

The middle chunk autovacuumed down to zero dead tuples. That also released a pinned xmin horizon which had been blocking vacuum elsewhere. One other chunk still held about 1.1 million dead tuples, below the autovacuum threshold. We left them. That chunk was due to drop about a week later, and the drop reclaims the space without any vacuum work.

Retention tiers became a floor

Dropping chunks first changes a customer-facing behavior. That belongs in the documentation.

An organization at the fleet maximum is served by chunk drops, so its rows can survive up to one full chunk width past its stated retention. That is seven days for Events and hardware metrics, and one day for health metrics. Organizations below the fleet maximum are still purged at their exact cutoff.

The retention number is a guaranteed minimum. Data can live slightly longer than the number says, and never less. Customers with a contractual deletion obligation need to know this up front.

ctid is only unique within one physical table

The original batching helper used a common pattern for deleting many rows without locking the table:

DELETE FROM some_table
WHERE ctid IN (SELECT ctid FROM some_table WHERE <predicate> LIMIT 5000)

On a plain table that is correct. On a hypertable it can delete live rows belonging to a different tenant.

ctid is a physical location, a page number and an offset within one physical table. It is unique within that table and nowhere else. A statement against a hypertable expands across every chunk, and each chunk is a separate physical table with its own ctid space. A ctid read from chunk A can therefore match an unrelated live row at the same page and offset in chunk B. The delete then removes that row.

Pairing the ctid with tableoid ties each one to the chunk it came from:

DELETE FROM some_table
WHERE (tableoid, ctid) IN (
    SELECT tableoid, ctid FROM some_table
    WHERE <predicate>
    LIMIT 5000
)

tableoid is the OID of the physical table the row is in, so on a hypertable it identifies the chunk. On a plain table the pairing does nothing and costs nothing. If your codebase contains hypertables, write it this way everywhere.

The hardware-metrics purge uses a different approach. It deletes in bounded time slices, which the per-chunk (OrganizationId, Timestamp) index serves directly, so it never needs ctid at all.

Legal holds block chunk drops for every tenant

The chunk service honors legal holds. It floors the cutoff at the oldest data belonging to any held organization. That is correct, and it has a storage consequence worth monitoring.

An active hold does two things. It freezes chunk retention at the held organization's oldest data, which affects every tenant because chunks are shared. It also exempts that organization from row purges. One flag on one tenant is enough to hold every chunk back to that tenant's oldest row.

On the first run of the hardware chunk service, 7 of 15 eligible chunks dropped. Seven organizations were flagged LegalHold = true. All seven were stale test artifacts, and they pinned the cutoff to mid-May.

If you implement holds, add a report listing the active ones with their age and their oldest retained row. Nothing else will tell you a hold is stale.

An RLS-filtered session returned zero rows with no error

The verification query for those legal holds returned zero rows. With no holds visible, the chunk service's conservative behavior looked like a defect, so a manual drop_chunks was run. It removed the held organizations' hardware chunks from mid-May through mid-July.

The query returned zero rows because the interactive psql session was connected as the application role with no tenant GUC set. That role is subject to row-level security. Under RLS with no tenant context, tenant-scoped tables return nothing. There is no error and no warning.

Every interactive session as the app role now opens with:

SET app.bypass_rls = 'on';

The same condition exists in the application. The chunk service reads the subscription table with .IgnoreQueryFilters() for this reason. It runs under root tenant context with no CurrentOrgId, so without that call the EF tenant filter returns zero subscriptions. Zero subscriptions computes a fleet maximum of 14 days, which would drop chunks a 730-day customer still owns. Any code that makes a fleet-wide decision from a tenant-scoped query should check that the query can see rows at all.

All 7 holds were confirmed stale and the flags were cleared, so no recovery was required.

Things to check on your own system

If you run a hypertable with per-tenant retention, check the following.

Is anything dropping your chunks, or are you only deleting rows? Compare hypertable_size() against a count(*). A large table with few live rows means chunks are not being dropped.
Does any code path use bare ctid in a batched delete against a hypertable? Search for ctid and pair every occurrence with tableoid.
What is the longest retention any active tenant holds, and is that number computed at runtime or hardcoded? A hardcoded value breaks when you sell a longer tier.
Do you have any legal holds, retention exemptions, or similar flags that are older than the case that justified them?
When a query against a tenant-scoped table returns empty, confirm the session can see rows at all before acting on it.

What this did not explain

There are still recurring load humps around 08:00 to 10:00, 16:00 to 18:00, and 23:00 to 00:30 Pacific. They were not part of this diagnosis and they are not the retention service. The next steps there are checking for backup-window overlap and attributing the cumulative temp-file spills with log_temp_files. That is a separate investigation.

The 03:17 spike is resolved. The remaining humps have a different cause and are still open.

ET Ducky

Documentation and pricing are available on this site.

View Pricing