Three retention services delete rows a window at a time where Postgres could drop a partition
- Dominant language
- C#
- Stars
- 6
- Forks
- 7
- Avg merge
- 4h 42m
- Merged PRs (30d)
- 307
Description
Retention is implemented three times, each as a timer that takes an advisory lock, walks the tenants that have rows, and deletes by predicate:
- `WorkflowRunRetentionService.cs:119-183` — advisory lock, tenant sweep, `session.Delete(run)` per run at `:251`
- `WebhookDeliveryRetentionService.cs:139-169` — `DeleteWhere(d => d.CreatedAt < cutoff)` per tenant
- `TokenCleanupService.cs:69-82` — five `DeleteWhere` calls per pass
Marten can partition document tables directly: `PartitionOn(x => x.CreatedAt, x => x.ByRange()...)`, or `ByExternallyManagedRangePartitions()` to hand maintenance to pg_partman, plus `Policies.PartitionMultiTenantedDocuments(...)` for every conjoined-tenant type at once. The event store has its own switch, `Events.UseArchivedStreamPartitioning`, for hot/cold storage of archived streams.
## Why it matters
A `DELETE` is the most expensive way Postgres has of forgetting something. Every removed row is a dead tuple the table keeps until vacuum reclaims it, indexes have to be updated, and the work scales with what is being deleted. Dropping a partition is a catalogue operation: constant time, no dead tuples, no vacuum debt.
These three tables are the ones that grow without bound on a busy instance and are exactly the shape partitioning wants — append-only, time-ordered, never updated after write.
There is a second win. Each service currently reimplements the same pattern: find tenants with rows, take a lock so only one node sweeps, delete, log. That is the same primitive three times, and a fourth module that needs retention writes it a fourth time.
## What to change
Two steps, in order, and the first is useful alone.
1. A retention primitive in core that a module declares against — a document type, a timestamp member, a window — instead of each one shipping a timer and an advisory lock. The three services above become declarations.
2. Range partitioning by time underneath it, so expiry is a partition drop. Start with `WorkflowRun` and `WebhookDelivery`, which are the largest and the least contended.
## What has to stay true
- Retention windows stay per-tenant configurable where they are today. A partition boundary is global; the policy is not, so a tenant with a longer window keeps its rows even when the partition is eligible.
- Partitioning an existing table is a migration, not a config change, and every unique index has to include the partition key. `CreateOnly` stores refuse to boot on a schema delta, so this needs a documented migration path and a `docs/` page, not an auto-applied change.
- The auth documents in `TokenCleanupService` are small and short-lived; predicate deletes may well stay right for them. Do not partition something for symmetry.
## Done when
- One retention primitive exists in core and the three services use it.
- At least one table expires by dropping a partition, proven by a test that asserts the partition is gone rather than that rows are absent.
- `docs/` says what turning it on costs on an existing database.
Found while auditing the stack for unused Marten and Postgres capabilities.
Contributor guide
Research direction
Start with WorkflowRunRetentionService.cs:119-183 and :251, WebhookDeliveryRetentionService.cs:139-169, and TokenCleanupService.cs:69-82 to compare the existing retention patterns. Then review Marten's partitioning options, Policies.PartitionMultiTenantedDocuments, and Events.UseArchivedStreamPartitioning. Done means a core retention primitive is adopted, at least one partition is proven gone by a test, and docs/ explains the migration cost for existing databases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, postgresql
- Domain
- backend, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100