Hypertables: declaring them, and repairing them later¶

The framework keeps seven tables as TimescaleDB hypertables where the extension is available. This page covers where those parameters are declared, how to add your own, and — the part most installations eventually need — how to repair a database that gained TimescaleDB after the migrations had already run.

The gap this exists to close¶

A migration creates its table and then converts it:

$schema->ifCapable(DatabaseCapabilities::TIMESCALEDB, function () use ($schema) {
    HypertableRegistry::apply($schema, 'authserver.user_activity_log');
});

ifCapable() is the right tool for creating a hypertable on a fresh install, and the wrong one for its lifecycle. A database that ran that migration before TimescaleDB was installed keeps a plain table for ever: the migration is recorded as applied, so it never runs again.

Such a table is correct in every other respect — the composite primary key (id, <time column>) is created unconditionally, outside the capability block, which is exactly what makes a later repair possible. But it is never partitioned, never compressed, and, most consequentially, its retention policy never applies, so it grows without bound.

That is the normal path for any long-lived installation that adopts TimescaleDB later, not an edge case.

One declaration¶

The parameters live in Pramnos\Database\HypertableRegistry, and both the migrations and the repair read them from there. Nothing holds a copy: a copy of somebody else's policy values drifts the first time they change, and then the two disagree silently.

Table	Time column	Chunk	Compress after	Retention
`tokenactions`	`action_time`	14 days	60 days	3 years
`authserver.twofactor_attempts`	`attempt_time`	7 days	7 days	2 years
`authserver.user_activity_log`	`created_at`	1 day	30 days	24 months
`authserver.user_consents`	`granted_at`	1 month	6 months	7 years
`authserver.data_processing_records`	`processed_at`	1 week	90 days	36 months
`authserver.gdpr_requests`	`requested_at`	1 month	1 year	7 years
`applications.application_stats`	`time`	14 days	60 days	3 years

This table is documentation. The registry is the source.

Choosing `segmentby`: a measurement worth repeating¶

TimescaleDB compresses in batches of up to 1000 rows per segment. Which columns go in segmentby therefore decides whether compression compresses at all — and getting it wrong is not a small loss.

Measured on 2 M changelog rows, 12 entities, 240 000 records over 30 days (tests/Benchmarks/changelog_compression.php):

`segmentby`	chunk	ratio	stored	compress	per-row	recent
`entity`	7 days	12.82	37.5 MB	5.8 s	11.0 ms	4.8 ms
`entity`	1 day	10.02	48.7 MB	4.6 s	12.7 ms	2.6 ms
`entity, itemid`	7 days	0.89	543 MB	74.6 s	16.8 ms	176 ms
`entity, itemid`	1 day	0.59	822 MB	133.6 s	2.2 ms	53 ms

A ratio below 1 means compression made the table larger. A change log is sparse per record — one row changes a handful of times a day — so itemid in segmentby produces segments of a few rows each, far below the batch size, and the per-segment overhead exceeds the saving. 822 MB against 37.5 MB for identical data, and 133 seconds of CPU against 6.

The rule that follows, and it generalises past this table:

segmentby: columns you filter on that have few distinct values.
orderby: the high-cardinality column first. Compressed batches carry min/max metadata for orderby columns, so a filter on one skips batches without decompressing them — which is how the layout above stays fast on a per-row lookup while keeping itemid out of segmentby.

The exception is real and visible in the table: entity, itemid at 1-day chunks wins the per-row lookup, 2.2 ms against 11.0 ms, because the segment is located directly. It costs 22× the disk and compression that does not compress — worth it only for a log read constantly and kept briefly, and that is what the overrides below are for.

The same question for `tokenactions`, with a different answer¶

tokenactions is one row per API request. Whether a high-cardinality segment key works there depends entirely on who calls the API, and the two plausible answers point opposite ways. Measured on 2 M rows over 60 endpoints and 90 days (tests/Benchmarks/tokenactions_compression.php):

callers	`segmentby`	ratio	stored	by-token	by-url
few, long-lived	`tokenid, urlid, method`	6.95	36.8 MB	0.41 ms	0.65 ms
few, long-lived	`urlid, method`	7.72	33.0 MB	6.83 ms	0.44 ms
many, short-lived	`tokenid, urlid, method`	0.50	515.5 MB	5.44 ms	38.5 ms
many, short-lived	`urlid, method`	6.76	37.9 MB	6.68 ms	0.46 ms

With tokenid in the segment key the layout is excellent for a server-to-server API — 0.41 ms on "what did this token do" — and collapses for one serving browser sessions, where it is not even faster: it loses on every axis at once.

The framework ships urlid, method, which is never bad. An installation that knows its callers are few and long-lived takes the faster lookup deliberately:

'hypertables' => [
    'tokenactions' => ['segmentby' => 'tokenid, urlid, method'],
],

The cost of the safe default is a token-history listing at 6.8 ms rather than 0.4 ms — an admin screen rather than a hot path, and the analytical reads go through the hourly continuous aggregate rather than this table.

Existing installations keep what they have

HypertableRegistry::apply() sets compression only on a table that has none, so a changed segmentby reaches new databases only. To adopt it on an existing one, decompress and recompress the chunks deliberately — per chunk, since the disk headroom needed is the size of the largest one.

Retuning one without editing the framework¶

None of those intervals fit every installation — a busy API's tokenactions and a quiet one's are the same declaration and very different amounts of disk. Override per table in app/app.php:

'hypertables' => [
    'tokenactions'      => ['retention' => '10 years'],
    'pramnos.changelog' => ['compress_after' => '3 days'],
],

An override changes only the keys it names; the rest keep the framework's values. Naming a table that is not declared registers it, so an application can use the same block for its own tables. A key the spec does not recognise is ignored rather than passed through — these values end up inside create_hypertable() and the policy calls, and a typo must not become an unknown option in somebody else's migration.

Then apply it:

php pramnos timescale:ensure --dry-run   # what would change
php pramnos timescale:ensure --fix       # change it

Changing a policy that already exists¶

timescale:ensure compares the interval, not merely whether a policy is there. A declaration that no longer matches the database is reported as drift and repaired by removing the policy and adding it back — add_retention_policy() raises on a duplicate, so replacing is the only way to change one.

retention policy (1 year → 90 days)

Two spellings of the same duration are not drift. PostgreSQL hands intervals back as @ 90 days while a declaration says 90 days, and treating that as a change would rewrite every policy on every run, for ever, over a leading @.

An interval the comparison cannot parse — a composite like 1 mon 15 days, or whatever a future server version prints — is treated as equal, so nothing is reconfigured. That is the safe direction: reading an unfamiliar spelling as drift would rewrite the policy on every run, which is the failure this whole comparison exists to avoid. Also not normalised across units: 24 months and 2 years are reported as different, because they are different in PostgreSQL and guessing otherwise would be the comparison inventing a policy nobody declared.

On a backend without TimescaleDB¶

The command still runs, and it does something: continuous aggregates are refreshed on every backend, because the defect there belongs to the backend without the extension — four migrations registered the refresh only inside their TimescaleDB branch, so on plain PostgreSQL the materialized view was created and never updated again.

Only then does the hypertable half bow out, and it says where retention actually comes from:

TimescaleDB is not available on this connection (mysql).
No hypertable was touched. On this backend both compression and retention are the
policy engine's job (service:policy-engine).

Exit 0, deliberately — including for "nothing to do" and "no extension". A repair command that exited non-zero because a database is already correct would fail every deployment pipeline it was added to.

An interval that cannot be parsed — 1 year 6 mons 3 days — is left alone. The bias is deliberate: a false positive is permanent churn against the scheduler, while a false negative costs one changed number not taking effect, which is the situation this replaced rather than one it introduces.

One hypertable that is deliberately not in the registry¶

authserver.audit_log is created as a hypertable — 7-day chunks, compressed after 90 days — by its own migration, and is not declared in the registry.

Table	Time column	Chunk	Compress after	Retention
`authserver.audit_log`	`event_timestamp`	7 days	90 days	none

Two deliberate absences, and both are the point:

No retention. An audit trail is the one table where the framework deciding to drop old rows would be wrong. An installation that wants a retention policy adds one itself, knowing what it is buying.

Not in the registry, because the registry is what timescale:ensure reads — and a declaration there would convert existing installations. Converting a live audit table means dropping and rebuilding its primary key and rewriting every row into chunks, under lock, on a table that other things hold foreign keys into. The migration guards on hasTable() and leaves such a database exactly as it is.

The cost is that timescale:ensure will not report drift on this table. That was judged the better half of the trade: the alternative is rewriting somebody's audit log because they ran a maintenance command.

On an existing installation the table stays a plain table with a 32-bit auditid. Widening it later is possible but is a maintenance-window job — decompress, rebuild the primary key, recompress — so if you are running one and expect volume, plan it rather than discover it.

Repairing a database¶

php pramnos timescale:ensure --dry-run    # what would change, and how big
php pramnos timescale:ensure              # do it
php pramnos timescale:ensure --table=authserver.user_activity_log

--dry-run reports each declared table's state, the row count of anything pending conversion, and the total. Read it first — see the warning below.

The command is idempotent. Every step is guarded by its own existence check, so a second run is a no-op and a run against a correct database changes nothing. That is not a nicety: add_compression_policy() and add_retention_policy() raise on a duplicate rather than no-opping, so an unguarded repair would work exactly once and fail ever after.

On a database without the extension the command changes nothing and says so. Retention there is handled by the software policy engine (service:policy-engine), which is a different mechanism, not a broken one.

Conversion takes an exclusive lock

Converting an existing table runs create_hypertable(…, migrate_data => true), which rewrites the table into chunks under an exclusive lock. On a years-old user_activity_log that is not instant, and writes block for the duration. Run --dry-run first, read the row counts, and pick your window.

What it checks, in what order¶

Is the table there at all? A declared table that this installation never created is reported as absent — not every installation enables every feature.
Is the primary key usable? TimescaleDB requires the partitioning column in every unique constraint. The framework creates these keys unconditionally, so this should always hold — which is why it is verified rather than assumed. A table whose key omits the time column is reported as blocked, with its actual key, instead of failing with a driver error.
Convert, then enable compression, then add the two policies. The order is not cosmetic: a compression policy on a non-hypertable raises, and so does compression where the setting was never enabled.

Declaring your own¶

An application registers its tables the same way, and gets the same repair:

use Pramnos\Database\HypertableRegistry;

HypertableRegistry::register('readings', [
    'time_column'    => 'measured_at',
    'chunk_interval' => '1 day',
    'compress_after' => '7 days',
    'retention'      => '2 years',
    'segmentby'      => 'device_id',
    'orderby'        => 'measured_at DESC',
]);

Register during bootstrap — a service provider is the natural place — so that both your migration and timescale:ensure see it.

Then in the migration:

$schema->ifCapable(DatabaseCapabilities::TIMESCALEDB, function () use ($schema) {
    HypertableRegistry::apply($schema, 'readings');
});

Omitting retention means "keep for ever", and omitting compress_after means "never compress automatically" — neither is given a default, because inventing a retention policy for a table that did not ask for one deletes data.

Your table must have the partitioning column in its primary key, and in every unique constraint. PRIMARY KEY (id, measured_at), not PRIMARY KEY (id).

Retention without TimescaleDB¶

SchemaBuilder::addRetentionPolicy() is not Timescale-only. Without the extension it registers a row in pramnos.framework_policies, and Pramnos\Policy\PolicyEngine — run by the service:policy-engine daemon — executes it as an ordinary DELETE. So a declared retention works on MySQL and plain PostgreSQL too; only the mechanism differs.

	TimescaleDB	MySQL / plain PostgreSQL
Mechanism	`add_retention_policy()` drops whole chunks	`PolicyEngine` deletes rows
Granularity	chunk boundaries	exact, by the time column
Runs from	the extension's own scheduler	`service:policy-engine`

It deletes in batches, and you can size them¶

A retention DELETE on a table with real backlog is the kind of statement that holds locks for as long as it takes — in a daemon, against a table the application is still writing to. So the engine issues bounded statements and repeats them:

$engine->register('retention', 'changelog', [
    'interval'    => '90 days',
    'time_column' => 'created_at',
    'batch'       => 5000,   // rows per statement; the default
    'max_batches' => 200,    // passes per run; the default
]);

batch × max_batches is the most one run will remove. A table with more backlog than that is not cleared in one pass, deliberately: the engine runs on a schedule and the rest goes next time. Holding the daemon for an hour on its first execution looks exactly like a hang, and gets it killed.

Both values are clamped to something sensible. A batch of 0 from a config file would otherwise be a loop that deletes nothing for two hundred passes while reporting success — the quietest failure available.

Two different statements underneath

PostgreSQL has no LIMIT on DELETE, so the bounded form selects physical row ids first — DELETE FROM t WHERE ctid IN (SELECT ctid FROM t WHERE … LIMIT n). MySQL uses DELETE … LIMIT n. Same behaviour, different SQL, and both are covered by the integration suite for that reason.

Writing late data into a compressed table¶

A hypertable with a compression policy stops accepting writes into the ranges it has already compressed. Every application that writes late data meets this: a delayed reading, a backfill, a correction, a webhook that arrives months after the event it describes. Without somewhere to put those rows, the choice is "compression or the ability to correct data" — which is why tables that get updated after the fact usually end up uncompressed for ever.

Pramnos\Database\DeferredWriteQueue is that somewhere.

Declaring the table¶

Add deferred_writes to the declaration, and — if a late row should correct an existing one rather than duplicate it — the columns that identify it:

HypertableRegistry::register('readings', [
    'time_column'     => 'measured_at',
    'chunk_interval'  => '1 day',
    'compress_after'  => '7 days',
    'deferred_writes' => true,
    'conflict'        => ['device_id', 'measured_at'],
    'conflict_update' => ['value'],          // optional
]);

conflict_update defaults to every column that is not part of conflict. Name it explicitly when some columns must survive an overwrite — an audit stamp, a flag an operator set by hand.

Writing¶

Replace the direct insert with write():

use Pramnos\Database\DeferredWriteQueue;

$queue = new DeferredWriteQueue($database);

$queue->write('readings', [
    'device_id'   => 42,
    'measured_at' => $timestamp,
    'value'       => 19.4,
]);

It returns true when the row went into the table and false when it was queued. The row's time comes from the declared time_column; pass it as a third argument when it lives somewhere else.

The cutoff is read from the live compression policy, not from a constant, and cached for the life of the process — a bulk import pays one query, not one per row. On a database with no policy, on MySQL, and on any development or CI box without TimescaleDB, there is no cutoff, nothing is ever deferred, and this is a plain insert. That is what lets the same code run on every backend.

A write that is cleared and then fails anyway — the policy compressed the chunk in the second between the two — is queued rather than lost.

Draining¶

php pramnos timescale:drain                  # write everything waiting
php pramnos timescale:drain --status         # what is waiting, per table
php pramnos timescale:drain --table=readings # one table
php pramnos timescale:drain --retry-failed   # re-queue rows that failed

Run it from cron, as often as your tolerance for late data requires. Hourly is a reasonable default.

What makes it worth having: the drain groups the backlog by chunk. A compressed chunk has to be decompressed before it accepts a write and compressed again afterwards, and that pair costs the same for one row as for ten thousand. Paying it once per row is the obvious implementation and an unusable one. The grouping is the pattern; everything else is bookkeeping.

It asks TimescaleDB only for the chunks that actually have rows waiting, so a drain is proportional to the backlog rather than to the table's age.

When something cannot be written¶

A batch runs in one transaction. If it raises, the batch is replayed row by row, so one bad row is marked failed and its five hundred blameless neighbours are still written — the difference between a queue that drains and one that jams behind a single row.

Failed rows are kept, with the error message, and never retried on their own: a row that fails once usually fails the same way for ever, and a queue that retries it hourly hides the problem instead of showing it. Fix the cause, then --retry-failed.

The chunk is compressed again even when every row in it failed. A chunk left decompressed never recompresses on its own — the policy only looks at chunks it has not already handled — so this would otherwise be a silent storage regression.

From code¶

$queue->pending('readings');              // rows waiting
$queue->failed('readings');               // rows that could not be written
$queue->tablesWithPendingRows();          // which tables have work
$queue->writeCutoff('readings');          // the live cutoff, or null
$queue->retryFailed('readings');          // put failures back in the queue
$queue->process('readings');              // drain, returns per-table stats

The queue lives in pramnos.deferredwrites, created by a framework core migration on every backend. In the pramnos schema because it is the framework's own bookkeeping — nobody writing an application queries it, DeferredWriteQueue does, and public is the application's. On MySQL, which has no schemas, the name flattens to a pramnos_ prefix.

Moved on 2026-08-30. It was created in public on 12 August, so unlike the tables that moved the same week this one had been deployed — the move is its own migration (move_deferredwrites_to_pramnos) rather than an edit to the one that created it. ALTER TABLE … SET SCHEMA is a catalogue update: no row is copied, nothing is locked beyond the statement, and the migration is a no-op where the table is already in place. Address the table through DeferredWriteQueue::TABLE and none of this matters to a caller.

Checking state from code¶

$schema = $database->schema();

$schema->hasHypertable('authserver.user_consents');       // partitioned?
$schema->isCompressionEnabled('authserver.user_consents'); // setting enabled?
$schema->hasCompressionPolicy('authserver.user_consents'); // job scheduled?
$schema->hasRetentionPolicy('authserver.user_consents');   // job scheduled?
$schema->primaryKeyColumns('authserver.user_consents');    // ['id', 'granted_at']

All four return false on a backend without TimescaleDB rather than raising, so they are safe to call from portable code.