Retention and Compression

Retention & compression

Both are reset-safe policies you attach to a hypertable model, and both can also be managed at runtime via $timescale (the path to use with the manual config).

Data retention (@timescale.retention)

Drop old chunks automatically. The generator emits a reset-safe add_retention_policy(...):

/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
/// @timescale.retention(dropAfter: "30 days")
model SensorReading {
  time        DateTime
  deviceId    Int
  temperature Float

  @@id([deviceId, time])
}

dropAfter is an interval: chunks whose data is older than it are dropped on TimescaleDB's schedule.

await prisma.$timescale.addRetentionPolicy("SensorReading", { dropAfter: "30 days" });
await prisma.$timescale.removeRetentionPolicy("SensorReading");

Changing dropAfter: TimescaleDB won't update an existing policy whose interval differs — add_retention_policy(… if_not_exists => TRUE) keeps the old one and warns. To change the window, removeRetentionPolicy(...) first (or change the annotation and reset). This is a TimescaleDB behavior, not a plugin limitation.

Data compression (@timescale.compression)

Compress old chunks with TimescaleDB's columnstore (hypercore). Requires TimescaleDB ≥ 2.18.

/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
/// @timescale.compression(after: "7 days", segmentBy: "deviceId", orderBy: "time DESC")
model SensorReading {
  time        DateTime
  deviceId    Int
  temperature Float

  @@id([deviceId, time])
}

• after (required) — chunks older than this interval are converted to the columnstore on schedule.
• segmentBy (optional) — the column(s) the columnstore groups rows by; the main tuning knob (queries that filter/group by these stay fast, and ratios improve). One Prisma field name, or several comma-separated.
• orderBy (optional) — ordering within each segment, e.g. "time DESC" (comma-separate for several, each field [ASC|DESC] [NULLS FIRST|LAST]).

segmentBy / orderBy take Prisma field names and are mapped to the underlying @map columns. Under the hood the generator emits the two statements TimescaleDB needs — enable the columnstore, then add the policy (add_columnstore_policy is a procedure, hence CALL):

ALTER TABLE "SensorReading" SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = '"deviceId"',
  timescaledb.orderby = '"time" DESC'
);
CALL add_columnstore_policy('"SensorReading"', after => INTERVAL '7 days', if_not_exists => TRUE);

await prisma.$timescale.addCompressionPolicy("SensorReading", {
  after: "7 days",
  segmentBy: "deviceId",            // or ["deviceId", "siteId"]
  orderBy: "time DESC",             // or [{ column: "time", direction: "desc" }]
});
await prisma.$timescale.removeCompressionPolicy("SensorReading");

Changing segmentBy / orderBy / after: like retention, TimescaleDB won't update an existing policy in place — changed segment/order settings apply only to future compressions. To change them, removeCompressionPolicy(...) first (or change the annotation and reset). removeCompressionPolicy stops the policy but leaves the columnstore enabled — disabling it fails once chunks are compressed.

Compression pairs with chunk skipping (range stats only exist on compressed chunks) and with on-demand compressChunk / decompressChunk.