TimescaleDB Hypertables (Optional)

What this feature is

TaskQ's retention tables (jobs_archive, job_attempts_archive, job_events) are plain Postgres tables by default, and their retention is owned by the row-level sweeps: the prune sweep moves terminal jobs into jobs_archive, the archive expiry sweep hard-deletes rows whose expire_at passed, and the event TTL sweep deletes job_events rows past event_retention_period, all in bounded batches.

On a server with the TimescaleDB extension available, TaskQ can convert those three tables into hypertables (partitioned on their time columns) and register Timescale retention policies, so aged data leaves the database as whole-chunk drops instead of long-window DELETEs. This is an opt-in feature: TASKQ_TIMESCALEDB_HYPERTABLES=true.

Vanilla Postgres remains fully supported and is the default. Nothing in this document is required to run TaskQ.

The three storage modes (the support matrix)

Which mode should you choose? The measured answer, from the campaign's committed benchmarks (benchmarks/results/):

• On Azure (Apache-2 is the only Timescale offering): run vanilla PG. Apache keeps hypertables and chunk management but loses compression and policy-driven retention (both TSL-only) — and TaskQ's Apache mode falls back to the same row-level retention sweeps vanilla uses. The measured reads favor vanilla once the page-keyset index exists (the archive tab's 10M-row walk: 3.63→0.60 ms/page plain vs 113→52 ms over hypertable chunks). Apache's count() metadata win is also columnstore-bound, so it does not apply. What Apache buys is the upgrade path*: on a later TSL-capable deployment, taskq migrate up adopts the full policy and columnstore set in place (pinned).
• Self-hosted with the TSL license (free to use, source-available): the full feature set is worth it at scale — the columnstore's 6.2–20.7× storage reduction and chunk-drop retention pay for their operational surface on large archives; below ~10k archive rows the vanilla column of this table is the cheaper, simpler choice (ops.md §13's framework).
• Vanilla: the default, the simplest operational surface, and — with the page-keyset index — measured as fast or faster on every read this campaign measured. Start here unless a TSL deployment is available and the archive's scale justifies the columnstore.

TaskQ supports three first-class storage modes. The mode is detected from the connected server — taskq.timescale.detect_storage_mode in src/taskq/timescale.py: the extension catalog first (pg_extension — a server that offers the extension but has not created it is a vanilla server), then the extension's own timescaledb.license setting, which states which feature set is in force (timescale — older builds spell it tsl — for the full Timescale-license feature set, apache for the Apache-2 feature set, whether that is a separate Apache-edition build or a TSL-capable build configured down with ALTER SYSTEM SET timescaledb.license = 'apache'). Nothing in the settings influences the verdict. One corner older than the GUC itself: a build with the extension installed but NO timescaledb.license setting at all falls back to the compression machinery's presence (to_regproc('compress_chunk') — defined by TSL-capable builds, absent from Apache-edition ones), the only capability the classification exists to distinguish at those ages; an unrecognized license spelling takes the same machinery probe (the string is never trusted over what the server can actually do). taskq doctor renders the detected mode as the first finding family of every report, with the mode's capability consequences in one glance — run it on any environment to see which mode that database actually presents.

The matrix — every cell is the behavior the detection and the deploy step's branches actually implement, not aspiration:

Capability	timescale-tsl	timescale-apache	vanilla
Detected by	extension installed, license timescale/tsl	extension installed, license apache	no extension installed
Detected by (the GUC-absent corner)	extension installed, no timescaledb.license GUC, to_regproc('compress_chunk') defined	extension installed, no timescaledb.license GUC, compress_chunk absent — or any unrecognized license spelling	—
Hypertables (the three retention tables)	yes	yes (conversion itself is Apache-licensed)	no — plain tables
Columnstore (compression) on the archives	yes — the two archive tables; job_events stays rowstore by measurement	no — a Timescale-license feature; the server refuses every compression API (measured on 2.30.1: FeatureNotSupportedError)	no
Policy-driven chunk-drop retention	yes — retention + compression policies registered per deploy	no — the policies are also Timescale-license features; the server refuses add_retention_policy under the license (measured on 2.30.1)	no
What retention does instead	the row-level sweeps still expire inside young chunks (below the policy floor); expire_at exact only there	the row-level sweeps own all of retention: bounded batch deletes, expire_at exact everywhere, the event-prune watermark kept	the row-level sweeps own all of retention: bounded batch deletes, expire_at exact everywhere, the event-prune watermark kept
enable_hypertables with the flag on	converts all three tables, registers retention + compression policies	converts all three tables, registers nothing, reports the skip in HypertableReport.policies_skipped and logs hypertable-policy-registration-skipped	refuses loudly (TimescaleDBUnavailableError)
taskq migrate disable-hypertables	full mirror: policies removed, vanilla shapes restored	mirror without the policy-removal calls (none can exist); the loud surviving-policy check stays	zero-statement no-op

Two readings of the matrix worth stating:

• timescale-apache and vanilla differ in exactly one cell — the hypertables. Everything retention-related (the mechanism, the guarantees, the watermark) behaves identically in the two modes; the apache mode's hypertables add chunk partitioning and faster recent-history reads without the policy machinery. They are distinct modes because the schema differs, not because the retention contract does.
• The doctor line, this matrix, and the behavior share one source. The one-glance summaries live in STORAGE_MODE_SUMMARY next to the detection code; this table is their prose form. A mode's capabilities change only when that code changes — the same commit updates all three.

The opt-in flag

Setting	Default	Meaning
TASKQ_TIMESCALEDB_HYPERTABLES	false	When true, the taskq migrate up deploy step requires the TimescaleDB extension and converts the retention tables. When false (the default), setup issues zero new statements against the server and behavior is identical to plain Postgres.

The flag is read by the deploy step only. Workers and clients never consult it, and no runtime code path branches on it: the sweeps keep running unchanged on both modes (see "The sweeps still run" below). What the deploy step does with the flag depends on the detected storage mode (see the matrix above): on timescale-tsl it converts and registers every policy; on timescale-apache it converts and registers nothing (the policies and the columnstore are Timescale-license features the server refuses), reporting the skip loudly; on vanilla it refuses — that contradiction between the flag and the server is what TimescaleDBUnavailableError names.

Enabling on Azure Database for PostgreSQL Flexible Server

1. Allow the extension on the server: add timescaledb to the azure.extensions server parameter, then create it once with an administrative role:

CREATE EXTENSION IF NOT EXISTS timescaledb;

1. Set TASKQ_TIMESCALEDB_HYPERTABLES=true in the environment the deploy step (taskq migrate up) runs with, alongside the retention settings the conversion derives from (TASKQ_ARCHIVE_RETENTION_PERIOD, TASKQ_EVENT_RETENTION_PERIOD). The deploy step loads the worker settings model for this step, so the deploy environment and the workers' environment cannot disagree.

2. Run taskq migrate up. The conversion runs inside the migration advisory lock, after pending migrations apply.

Local development

Any Postgres with the extension works. The suite pins timescale/timescaledb:2.30.1-pg18 (the newest tagged release matching the suite's Postgres 18 image); the tests read TASKQ_TEST_TIMESCALEDB_IMAGE when set, so CI and local runs can point at their own tag:

docker run -d --name taskq-timescale -p 5432:5432 \
  -e POSTGRES_USER=taskq -e POSTGRES_PASSWORD=taskq -e POSTGRES_DB=taskq \
  timescale/timescaledb:2.30.1-pg18

The image preloads the extension (shared_preload_libraries=timescaledb), so the flag alone is enough: TASKQ_TIMESCALEDB_HYPERTABLES=true taskq migrate up.

What the conversion does

Three tables, each partitioned on its own time column, with chunk intervals derived from the retention settings (retention / 4, clamped to a range of one day to thirty days so a retention window spans a handful of chunks and a chunk drop stays bounded work):

Table	Partition column	Uniqueness	Retention policy from
job_events	occurred_at	UNIQUE (id, occurred_at)	event_retention_period
jobs_archive	finished_at	UNIQUE (id, finished_at)	archive_retention_period
job_attempts_archive	started_at	UNIQUE (job_id, attempt, started_at)	archive_retention_period

The details that matter:

• The migration ledger is untouched. The conversion is setup-time, idempotent DDL outside the checksummed ledger, run on every deploy. The ledger's files stay identical on both engines; there is no migration whose body depends on the server's capabilities. A conversion cannot live in the ledger because the ledger applies each file exactly once, and opt-in happens at an arbitrary point in a database's life; re-running a deploy converges the schema instead (changed retention settings are honored on the next deploy, already-converted tables are skipped). A changed chunk interval is re-asserted the same way, but it shapes future chunks only: existing chunks keep the interval they were created with.
• Uniqueness widens, semantics hold. A hypertable requires the partition column in every unique constraint, so jobs_archive's PRIMARY KEY (id) becomes UNIQUE (id, finished_at). The re-archive guarantee the bare primary key enforced is enforced instead by an explicit NOT EXISTS guard in the archive write: a job id that already holds an archive row is never archived again. The same guard runs on vanilla Postgres, so the prune's ghost semantics (fold an already-archived id, never wedge, never duplicate) are identical in both modes and pinned by the same tests. One stated edge: the guard's promise is keyed to the archive row's existence, and a chunk drop can remove that witness. When the ghost's archive chunk is gone, a retried job's re-prune re-archives — fresh archived_at/expire_at stamps, the live payload wins — where vanilla would still fold, because the row outlives the chunk by the archive-to-expire margin. Bare-id uniqueness would pin the invariant structurally, and a hypertable forbids exactly that; "archived at most once" therefore holds only while the archive chunk lives.
• One foreign key drops. No table may reference a hypertable, so job_attempts_archive's foreign key to jobs_archive(id) ON DELETE CASCADE is removed. Chunk retention replaces the cascade: both tables register policies from the same archive_retention_period. The alignment is by chunk, not by row: an attempt whose started_at precedes its job's finished_at can outlive the parent's chunk by up to one chunk interval. And unlike the cascade it replaces, the chunk drop does not take the attempts with the parent: when the parent's chunk drops first, the parentless job_attempts_archive rows stay queryable until their own chunk drops — at most one chunk interval later, capped at the clamp's thirty days. Vanilla never shows a parentless attempt row (the cascade removes them together); anything that queries attempts directly on the hypertable mode should expect them in that window. job_events' foreign key to jobs(id) ON DELETE CASCADE is kept (hypertable-to-regular-table foreign keys are supported) and enforcement is unchanged.
• The first conversion rewrites populated tables. Conversion uses migrate_data, which takes an access-exclusive lock and rewrites existing rows into chunks inside the deploy step. Size the deploy window for the archive's current row count; an empty or young schema converts in seconds. The window does not start at the copy, though — the constraint surgery in the next two bullets locks and scans the same tables first, and the first background policy run after the deploy drops the whole aged tail in one sweep.
• The constraint surgery takes its own access-exclusive locks. Before create_hypertable runs, the foreign-key drop, the primary-key drops, and every ADD CONSTRAINT ... UNIQUE statement (one per table) each take ACCESS EXCLUSIVE on their table, and each unique add full-scans that table to validate the uniqueness it asserts. On a big archive those scans are part of the deploy window, not a footnote to it: size the window for the constraint scans and the migrate_data copy together.
• The primary key drops before the unique constraint lands. These statements run one per transaction on the deploy connection's autocommit, and the migration advisory lock serializes migrators only — workers keep running through the whole conversion. Between a table's primary-key drop and its unique add, that table holds no unique constraint on its id columns; a duplicate insert that lands in the window makes every subsequent deploy fail on the unique add until the duplicates are removed by hand. Run the enabling deploy in a maintenance window when no worker archives, or accept the window: it spans two adjacent statements, so it is tiny and bounded, but it is not zero.
• Bounded runs convert anyway. migrate up --phase pre, --target, and --max-steps all still run the conversion and re-register the policies when the flag is on: the deploy step's flag check is independent of which migrations a bounded run applies. A run intended to apply one unrelated migration does the full hypertable work too — pinned as current behavior by tests/test_timescale_deploy_e2e.py::test_bounded_run_still_converts.
• The first policy run drops the whole aged tail at once. With policies armed, every chunk older than the retention interval is drop-eligible immediately: the first registration makes the entire historical backlog drop-eligible the moment the enabling deploy finishes, because policy deletion is chunk-granularity — whatever has fully aged past the interval goes as whole-chunk drops. The first time Timescale's background workers fire, that aged tail leaves in one sweep, potentially gigabytes of IO, where vanilla mode expires the same rows gradually, row by row, in bounded batch deletes. Size the post-deploy window for that first policy run too.

The NULL finished_at tail (the pre-flight refusal)

jobs_archive.finished_at is nullable — the archive mirrors every jobs column, and a job's finished_at is NULL through its whole non-terminal life (dispatch stamps finished_at = NULL on claim). TimescaleDB cannot convert the table while the column holds NULLs: the partition column must be NOT NULL, and create_hypertable(..., migrate_data => TRUE) aborts on them with NotNullViolationError.

No production write can put one there. Every terminal transition stamps finished_at = clock_timestamp() (all ten terminal arms in backend/_sql_templates.py, plus the sweeps, the batch-cancel drains, and the actor-deregistration drain), and the prune's archive COPY could not pick a NULL row up anyway: both the candidate window and the lock-time re-read require finished_at < statement_timestamp() - retention, and NULL < x is NULL. What the shape's provenance is: version skew — an older reclaim sweep's pre-reorder shape terminalised budget-carrying cancelled rows without stamping (the shape the finished_at CASE comment in backend/_sweeps.py names). Those rows are the un-draining NULL tail of the prune's cursor walk — invisible to the age predicate forever — and a hand-run backfill or migration tooling that moves them into the archive by hand carries the NULLs along. Direct SQL produces them trivially.

The verdict is debris, and the enable path refuses it loudly with a pre-flight census that runs BEFORE any DDL:

TASKQ_TIMESCALEDB_HYPERTABLES=true: the conversion refuses to run —
"taskq".jobs_archive holds 2 row(s) whose finished_at is NULL, ...
Complete or discard them explicitly, then re-run `taskq migrate up`:
  UPDATE "taskq".jobs_archive SET finished_at = archived_at WHERE finished_at IS NULL;
  DELETE FROM "taskq".jobs_archive WHERE finished_at IS NULL;

The refusal names the row count and both remediations — complete the rows (stamp the archive time as the finish, or any timestamp the operator audits against) or discard them. The operator decides; the conversion never invents a finish timestamp and never deletes on its own.

Why the refusal fires before any DDL, and why that is the whole point: the conversion's statements run one per transaction on the deploy connection's autocommit. Without the census, the failure surfaced mid-migrate_data — after the foreign-key drop, both primary-key drops, and both unique-constraint adds had already committed, and after job_events had converted whole. The stranded state was a half-converted schema: jobs_archive with no primary key (a duplicate-id insert succeeds; the archive-once guard is then app-level only), no attempts foreign key, and job_events already a hypertable — degraded until an operator diagnosed it, while every re-run failed at the same spot. The census makes the refusal atomic: the schema stays byte-identical to what the operator arrived with, debris included.

The census re-runs on every deploy but only counts while the table is still vanilla (an already-converted hypertable's partition column is NOT NULL, so there is nothing to count and no scan). job_attempts_archive and job_events need no census: both partition columns are NOT NULL in the bundled schema, so the NULL shape is structurally impossible there. Once converted, the NOT NULL partition column enforces the shape at DML time: the same insert vanilla accepts is rejected with NotNullViolationError on the converted schema (pinned in tests/web_admin/test_admin_on_hypertables.py), and the admin history walk's NULL-sentinel cursor range is unreachable — harmless — on hypertable mode.

The census is a point-in-time gate, not a lock. It counts in its own autocommit transaction, and the conversion's statements each commit separately after it, so a row written with a NULL finished_at between the census and jobs_archive's create_hypertable re-enters the mid-migrate_data failure and can strand the half-converted schema the census exists to prevent. No lock taken at census time can close that window: the conversion is deliberately per-statement autocommit DDL — each statement independently re-runnable, the same discipline the constraint surgery relies on — so nothing acquired by a lone SELECT spans it. What closes it in practice is the same maintenance window rule the constraint surgery already requires: no TaskQ writer can produce a NULL finished_at row (see above), so the only writer that can poison the window is direct SQL against the database during the enabling deploy. Run the enabling deploy with no writers against the schema, as the pkey-drop window above already requires. If it happens anyway, the refusal's own remediations still converge the wreck: discard (or complete) the NULL rows and re-run taskq migrate up — the census counts whenever the table is still vanilla, wrecked or not.

The columnstore (compression) is adopted for the archive tables

timescale-tsl mode only (the matrix above): under the timescale-apache license the server refuses every compression API — FeatureNotSupportedError — so this whole section is inapplicable there, and the archive tables stay rowstore with the row-level sweeps owning expiry exactly.

The deploy step arms the columnstore on the two archive tables when it converts them (and on every deploy after, converging like the retention policies do):

Table	segmentby	orderby	Rationale
jobs_archive	actor, queue	finished_at DESC	The admin archive tab's newest-first read; low-cardinality filter columns. Never the per-row-unique id: a unique segmentby key makes every compressed batch a single row and collapses the ratio (the documented anti-pattern).
job_attempts_archive	job_id	started_at DESC	Per-job attempt history reads in job order.
job_events	—	—	Stays rowstore — measured, nothing to gain (its writes dominate and its reads are windowed).

The compression policy's compress_after is one chunk interval (the same retention/4-clamped derivation the chunk sizing uses): a chunk compresses once it has stopped receiving rows. The policy is remove-then-add registered like its retention sibling, so a changed archive_retention_period moves compress_after on the next deploy. A changed segmentby/orderby with compressed chunks already on disk is ACCEPTED by the server — with a NOTICE, not an error ("updated compression settings will only apply to future compressions; existing compressed chunks will not be recompressed", measured on 2.30.1): the new shape is the FUTURE chunks' shape, and the already-compressed ones keep their old segmentation until you decompress and recompress them (decompress_chunk, then compress_chunk(chunk, recompress => true)). A deploy that changes the columnstore shape therefore converges the policy silently and leaves the on-disk chunks mixed; the timescaledb_information.compression_settings view shows the setting, not what each chunk actually carries.

One server prerequisite, probed and warned about loudly: timescaledb.max_tuples_decompressed_per_dml_transaction defaults to 100000, and at that default the archive expiry sweep hard-errors on compressed chunks with ConfigurationLimitExceededError (measured: one 10k-row expiry batch decompressed 356633 tuples). At or under the default, enable_hypertables logs a WARNING and returns the warning in the report's decompression_guc_warning. Raise the budget server-wide before relying on the columnstore:

ALTER SYSTEM SET timescaledb.max_tuples_decompressed_per_dml_transaction = '0';  -- 0 = unlimited
SELECT pg_reload_conf();

(or set it on the workers' sessions; it is user-settable). The measured headline: benchmarks/timescale_compression.py recorded a 6.20x storage reduction on the 400k-row archive corpus (269 MB of rowstore chunks → 41 MB once the aged chunks compress), with the young chunk's hot page unchanged.

Who pays what — the rest of the ledger, all p50s off the committed recording (benchmarks/results/timescale-compression.json):

• Cold reads at the admin's actual shapes win big. A windowed status-filtered count over aged chunks runs 4.6x faster than plain (6.40 → 1.39 ms, 2,930 → 251 shared-buffer hits: the ColumnarScan reads segments, not the heap), and an aged newest-first page — the archive tab's shape over old data — 9.3x faster (46.9 → 5.1 ms).
• The id point lookup is the documented weakness, and the fold guard pays it. A by-id lookup (id is a non-segmentby key) costs ~7x plain's p50 on a compressed chunk (0.31 → 2.23 ms): no per-chunk index serves it, so every compressed chunk contributes a bloom-filter segment scan to the Append, and the cost scales with the chunk count (the artifact's plan shows ~106 buffer hits per compressed chunk). The prune's fold guard runs exactly that shape as a NOT EXISTS probe — its exists-miss measured ~6x plain. Readers of old archive rows by id (support lookups, the differential prune) pay it; windowed recent-history readers do not.

And the 6.20x is the aged-in state — a row-level drain gives it back. The same recording's 100k-row archive-expiry drain ran row-level DELETEs against compressed chunks: the hypertable ended the drain at 469 MB against the plain engine's steady 269 MB (1.7x plain — the ratio gone; one 10k batch at the default GUC had to decompress 356,633 tuples, the warning above). The bloat self-heals, but only at chunk drop: nothing in the DELETE path reclaims the decompressed material — the reclaim arrives when the RETENTION policy drops the chunk that holds it (a manual decompress_chunk + compress_chunk cycle reclaims sooner, by hand). The operator's lever is therefore the retention policy's cadence: each policy_retention run drops the fully-aged chunks and their ballast with them, and a stalled policy — background workers down, jobs erroring, next_run sliding — means the bloat is retained indefinitely. Watch both through the Monitoring section: timescaledb_information.jobs (proc_name = 'policy_retention', the next_run that must keep advancing) and the per-hypertable chunk count and size, whose growth-without-bound alert is exactly the stalled-policy signal.

Retention is owned by the policies, mostly

timescale-tsl mode only. In timescale-apache and vanilla the policies cannot be REGISTERED (the apache license refuses the policy APIs; vanilla has no extension): retention is the row-level sweeps alone, full-range, expire_at exact, the event-prune watermark kept on every deletion — the pre-floor behavior, unchanged. The rest of this section describes the timescale-tsl mode.

One honest exception, measured on 2.30.1: policies registered under the full TSL license SURVIVE a later downgrade to timescale-apache (the license cannot remove them — every removal API refuses too), and under the downgraded license they fail on every background run. The compound effect strands rows: the sweeps defer the aged end to the policies' floor (see below), the policies cannot run, so rows older than the dead policies' horizon are deleted by nobody. The doctor's storage-mode family names this state as drift (an apache-licensed server with live policy jobs is downgrade debris, since the healthy apache mode registers nothing) with the only remedy — restore the timescale license (ALTER SYSTEM SET timescaledb.license = 'timescale' and reload), re-run taskq migrate up to converge, then flip and disable properly. The disable path refuses the same state loudly for the same reason.

Once the tables are hypertables, the aged end of the timeline is owned by Timescale's background workers: each policy drops chunks whose time range fully passed its retention. Two consequences to know:

• The retention clock is the partition column, not expire_at. Archive chunks drop on finished_at age; a row's expire_at stamp (archive time plus archive_retention_period) is always later than that, so hypertable retention is stricter than vanilla expiry by however long the row sat in the hot table before archiving. The aged-side rule, stated once: with policies armed, expire_at is honored exactly inside chunk lifetime — the expiry sweep still deletes a row whose expire_at passed while its chunk is present — and the aged end is governed by the policy's partition-column clock. That is the trade the feature makes for chunk-drop performance. event_retention_period = 0 (the disable sentinel, keep everything) registers no policy, which is exactly "keep everything".
• The sweeps still run — above the policy floor. Each sweep probes the armed policy's own drop_after horizon once per run (retention_policy_floor in src/taskq/backend/_retention_floor.py, one catalog query against timescaledb_information, failing open to "no floor" on vanilla Postgres or any probe error) and bounds its DELETE with it: rows older than the floor are the POLICY's — silently, chunk-granular, watermark-blind — and the sweep no longer pays the chunk-fan-out tax to re-delete them. The ownership claim requires the policy to actually RUN: a PAUSED job (alter_job(job_id, scheduled => FALSE)) is registered but never executes, so it reads as "no floor" — the sweep resumes full-range on the aged end rather than deferring to a policy that will never take it (pinned by the interplay module's paused-policy test). Below the floor the sweep owns deletion row-exactly: the event TTL sweep honors the reclaim-outbox carve-out there, and the archive expiry sweep honors expire_at exactly inside chunk lifetime (the policy's clock is finished_at — the chunk alignment column — so the floor bounds finished_at while expire_at stays the row-precise predicate inside the window). Rows older than the floor but inside a young chunk leave at most one chunk interval late, when the chunk itself ages past the boundary — chunk granularity is the trade. The floor is read from the registered policy's own config (drop_after), not re-derived from the settings, so the sweep and the policy can never disagree about where the boundary sits. One more zero needs distinguishing on this mode: an expiry DELETE's first execution after a chunk aged into the columnstore can silently skip the qualifying rows that chunk holds (the decompression path catches up on the next tick — ops.md's watch list has the two zeros and the query that tells them apart). Chunk granularity, not row granularity, governs the policy drops: the chunk containing the newest rows is dropped only when its newest row ages out. The reclaim-outbox carve-out's 100x age cap is bounded by chunk drops, not by the sweep: a chunk ages out at plain retention regardless of its events' kind, so a watch_reclaims consumer must keep its lag inside event_retention_period (or one chunk interval, whichever is larger) on the hypertable mode.
• The chunk drop is a third deleter, and the gap signal does not see it. Migration 01.00.20_02's watermark (job_events_prune_state.pruned_through_id) is what makes a stale consumer cursor fail visible with EventRetentionGapError instead of silently skipping — and that contract ("the watermark can never lag what is already gone") is kept by the two sweep deleters only, each of which advances the watermark in the same statement that deletes. The retention policy advances nothing: a chunk drop removes event ids without moving pruned_through_id, so a consumer cursor below the dropped ids still reads as safe (cursor at or above the watermark) and silently loses the dropped events. No code advances the watermark from the policy path today. That is why the lag rule in the previous bullet is a hard requirement on this mode, not a comfort: on hypertables, keep the watch_reclaims cursor strictly inside event_retention_period — the chunk policy provides no gap signal.

Measured trade-offs: hypertables vs plain PostgreSQL

(The compression figures in this section — the 6.20x storage reduction and the cold-read wins — are timescale-tsl mode only: the columnstore they measure does not exist in the timescale-apache or vanilla modes. The drain table's hypertable column is likewise the policy-armed TSL mode; in the other two modes the "plain" column IS the mode's behavior. See the support matrix above.)

Measured on this repo's own benchmark (benchmarks/timescale_tradeoffs.py, rerunnable end-to-end; 1,000,000 jobs / 400k archive / 400k events / 100k attempts seeded identically on both engines, three stable runs, every dashboard row identity-asserted cross-engine before any timing counted):

Retention drains (the sweeps' real batch shapes):

Drain	plain	hypertable (since the policy floor)	ratio since
Prune 100k jobs → archive	4.5 s	35.9 s	~7.9× slower (untouched by the floor: its DELETE runs on the plain jobs table)
Event TTL (96k rows)	0.30 s	0.04 s	~6.8× faster
Archive expiry (100k rows)	1.14 s	0.32 s	~3.6× faster

Before the retention-policy floor the hypertable ran the same row-level batch DELETEs plain still runs, with the chunk fan-out tax on top — the pre-floor runs measured ~6.6× plain's per-batch cost on the event TTL (those runs predate the committed artifacts; every number in this table is read off benchmarks/results/timescale-tradeoffs.json, and the same tax is visible today on the one drain the floor cannot touch: the prune's, ~7.9× above). The floor moved the aged end's deletion to the policy's chunk drops — the sweeps' cost on the hypertable collapsed to the floor probe plus the empty-window index scan, and the two TTL/expiry legs are now FASTER than plain, which still pays the row-level DELETEs. The trade is unchanged, only cheaper: the aged end leaves at chunk granularity (silently, watermark-blind), one chunk interval later at worst, instead of row-exactly.

The admin dashboard at 1M jobs - parity except the archive tab, which wins big:

Query	plain p50	hypertable p50
Live jobs pages/counts (jobs is never a hypertable)	±10%	parity
Archive tab, newest-first page	45.0 ms	8.6 ms (5.2×)

Chunk pruning serves "recent history" reads from the youngest chunk - the dashboard's most common archive read is the hypertable's best case.

Write path - no cliff: ~3% enqueue tax (that table is plain in both modes - planning noise), ~6% on the hypertable job_events insert.

Aftermath: dead tuples after row-level DELETEs are identical per engine - deleting buys no bloat relief on either. The hypertable's retention win lives in the chunk-drop path (whole-chunk, policy- driven, silent), and since the retention-policy floor the sweeps' aged-end DELETEs are gone from the hypertable entirely - the sweeps' remaining young-chunk work is small, precise, and row-exact. What the feature still trades away: the deletion guarantees above (expire_at exactness on the aged end, the gap signal, the fold guard) against the archive tab's read speed and the aged end's removal cost.

Monitoring

• Policies and their next runs: timescaledb_information.jobs (proc_name = 'policy_retention'; config carries the drop_after interval the deploy step last registered; the compression policies register as policy_compression with compress_after).
• Columnstore settings per column: timescaledb_information.compression_settings (segmentby_column_index / orderby_column_index), and compression_enabled on timescaledb_information.hypertables.
• Converted tables: timescaledb_catalog.hypertable (the internal catalog; timescaledb_information.hypertables is the friendlier view).
• Chunk inventory and sizes: timescaledb_catalog.chunk joined on hypertable_id, or show_chunks('"taskq".job_events').
• A policy that stops firing shows up as chunk counts that grow without bound on the aged end; alert on chunk-count growth per hypertable.

Turning it back off

The deploy step's conversion is forward-only, like the migrations. Setting TASKQ_TIMESCALEDB_HYPERTABLES=false after enabling stops the deploy step from issuing any hypertable SQL, but does not convert the tables back and does not remove the registered policies: a previously converted schema keeps its chunk retention at the last-registered intervals (pinned by tests/test_timescale_deploy_e2e.py::test_flag_off_after_enable_changes_nothing).

The explicit way back is taskq migrate disable-hypertables — the CLI wiring of disable_hypertables in src/taskq/timescale.py, the mirror of the deploy step's enable side. Flip the flag off first: with the flag still true the command exits 1 with the remedy (the library-level gate is a zero-statement no-op; the CLI refuses to let a mistyped invocation look like a completed disable). The command holds the same migration advisory lock the enabling deploy used, and on failure prints the same self-diagnosing report migrate up prints.

The mechanics per hypertable, rename-first so no order of death loses rows: every registered policy (retention and compression) is removed first (idempotently, refusing to swap tables under a live policy), then the rows are copied into a bare restore heap and the copy is count-verified — this exercises the full read path (compressed chunks decompress) BEFORE any name moves — then the hypertable RENAMES to {table}__hypertable_trash (metadata-only and instant: from that moment the rows exist in two places and the vanilla name is free), the migration-built vanilla table moves into the freed name — its shape is cloned from a fresh application of the bundled migrations themselves, never re-typed by hand, so the restored pkeys, indexes, foreign keys, column order, and defaults are byte-equal to a schema that never converted — and the rows return FROM THE TRASH, twin-verified against both copies, with only then the trash and the heap dropped. The traded-away behaviors come back: the bare primary keys reject duplicates again, the job_attempts_archive → jobs_archive foreign key cascades again, and the event id sequence continues from the restored maximum. It is idempotent (re-runs converge) and pinned end to end by the disable legs in tests/test_timescaledb_hypertables.py and tests/test_timescale_deploy_e2e.py (the latter runs the real CLI subprocess). The in-memory twins need nothing: vanilla semantics are the default — there is nothing to disable but the schema.

Two windows to know about, both bounded, both documented honestly:

• The copy window (the mirror of enable's pkey-drop window). Workers keep running through the whole disable: the migration advisory lock serializes migrators only. A row committed between the copy's count-verify and the trash rename lands in the TRASH — and comes back (the rows return from the trash, the superset), so the rename-first swap loses nothing there. The window that remains is the enable path's documented shape: run the disabling deploy in a maintenance window when no worker archives anyway. There is also one loud refusal: the hypertable's widened uniqueness admits rows (same id, different partition-column value) the restored vanilla table's primary key cannot hold; the swap refuses to choose which row survives and names the conflicts instead.
• The event id sequence's one honest gap. The restored sequence is re-anchored to GREATEST(its own position, the restored max(id)) — forward-only. On an EMPTY restored table (a schema whose events were fully reaped before the disable) the anchor re-issues the anchor value itself, and the sequence's own position can sit past ids retention already deleted: the next issued event id can reuse one job_events_prune_state.pruned_through_id already claims gone. A caught-up watch_reclaims consumer (cursor at or above the watermark) will not see that one event; a lagging consumer fails visibly, as designed. One id, once, at the boundary — the price of anchoring forward-only against a table that cannot say which ids below the sequence's position were already issued and reaped.

Crash safety is structural, not procedural: a crash at ANY swap stage (after the verified copy, after the trash rename, after the move-in) leaves every row in at least two places, and the NEXT disable's first act — before any DROP SCHEMA CASCADE — finishes every crashed table's move under the same count/twin verification (_converge_crashed_swaps): a stranded trash renames back, stranded copies are twin-absorbed into the live table, and nothing holding a row whose live twin is missing is ever dropped. Pinned by the crash matrix in tests/test_timescaledb_hypertables.py::test_disable_crash_at_every_swap_stage_converges (every stage × re-run → the full vanilla shape with every row).

Test coverage

tests/test_timescaledb_hypertables.py pins all of this: the flag-off path issues zero statements (a stub connection that raises on first use); opting in on a vanilla server raises TimescaleDBUnavailableError naming the setting; the conversion's catalog shape (hypertables, widened uniqueness, surviving and dropped foreign keys, registered policy intervals); re-enable convergence; a real policy run dropping aged chunks while fresh rows survive; the sweeps' young-chunk behavior; the differential prune script agreeing between vanilla Postgres and the hypertable mode; and chunk pruning in a windowed job_events query's plan. The container legs skip with a reason when Docker is unreachable. tests/test_timescale_deploy_e2e.py runs the real taskq migrate up subprocess against TimescaleDB (conversion with data, preservation, the capability refusal, roll-forward-only, and the bounded-run interaction), and tests/test_timescale_retention_interplay.py pins the policy/sweep composition edges named above: the carve-out defeated by chunk drops, the watermark not advancing on a policy drop, the re-archive witness a chunk drop removes — and the retention-policy floor itself: the below-floor range deleted-count going to zero (with the pre-fix counterfactual), the inside-window rows still deleting row-exactly and watermark-visibly, the floor-or-not end-state parity, the vanilla probe failing open to full-range sweeps, and the policy-less hypertable owning no aged end at all. The columnstore adoption is pinned by test_compression_adopted_on_archives_only (the per-column settings via timescaledb_information.compression_settings, the registered policy_compression jobs, job_events staying rowstore, the hot page read, and re-run convergence) and test_decompression_guc_warning_fires_at_default_and_quiets_when_raised (the loud GUC warning at the 100000 default, quiet at a raised budget). The disable mirror is pinned by the disable legs: the vanilla shape byte-equal to a fresh migration's with every row counted (test_disable_restores_vanilla_shape_and_every_row), the restored behaviors — duplicate rejected by the bare PK, orphan attempts rejected by the restored FK, the sequence continuing (test_disable_restores_vanilla_behaviors), idempotence (test_disable_is_idempotent), the full enable→disable→enable round trip (test_enable_disable_enable_round_trip), the deploy-path disable end to end (test_disable_after_deploy_restores_vanilla) plus the real taskq migrate disable-hypertables subprocess (test_disable_hypertables_cli_subprocess_end_to_end), the crash matrix (killed at every swap stage, converging on the re-run with every row — test_disable_crash_at_every_swap_stage_converges), and the loud refusal of rows the restored table's primary key could not hold (test_disable_refuses_vanilla_key_collisions_loudly), and the serialization pin on the absorb's one drop rule: a real concurrent writer fired into the trash the moment the absorb's LOCK TABLE ... IN EXCLUSIVE MODE statement arrives — the writer either commits before the lock and is absorbed by the twin-guard, or queues behind it and wakes to the relation gone — either way the trash is gone and every row is accounted (test_absorb_lock_holds_the_drop_window_shut_against_a_live_writer).

The three-mode work has its own pins: tests/test_storage_mode_detection.py pins the detection verdict arm by arm (extension absent → vanilla even when the server offers the extension; the license GUC's three spellings — timescale, tsl, apache — each classified; the GUC-absent fallback to the compression machinery's presence), tests/test_cli_doctor.py pins the doctor's first finding family (each mode's one-glance rendering, the flag-on-vanilla drift arm, the read-only property preserved with the family in place), and tests/test_timescale_three_mode_lifecycle.py runs the FULL lifecycle — taskq migrate up (real subprocess), operate, the disable round-trip, the retention drain — against all three configurations on real containers: timescale/timescaledb:2.30.1-pg18 for timescale-tsl, the same image started with -c timescaledb.license=apache for timescale-apache, and plain postgres:18 for vanilla, asserting each mode's documented matrix cell.