ManagedRangePartitions: rolling time-window range partitions with additive roll-forward and aged-partition drop

[jeremydmiller]

The gap

Weasel has a managed partition manager for the LIST strategy — ManagedListPartitions (Postgres) keeps its partition set in a side table, adds partitions at runtime, and deliberately sits outside the generic table delta so it "needs no IgnorePartitionsInMigration toggling." That is exactly the right shape for a partition set that changes over the life of the application.

The RANGE strategy has no equivalent. RangePartitioning.CreateDelta compares a declared list of ranges against actual:

• missing declared ranges → PartitionDelta.Additive (good — Weasel can add partitions)
• actual has ranges the declaration no longer contains → PartitionDelta.Rebuild
• actual has more ranges than declared → PartitionDelta.Rebuild

For a time-series table those last two are the normal steady state, not an anomaly. The declared window moves forward every month; last year's partitions are still on disk and are supposed to be dropped per a retention policy, not treated as drift. On a multi-gigabyte table a Rebuild is catastrophic, so the only viable options today are IgnorePartitionsInMigration or an "externally managed" mode — both of which mean the application hand-writes CREATE TABLE … PARTITION OF and DROP TABLE itself.

The SQL Server side has the same gap in its own idiom: Weasel.SqlServer.Tables.Partitioning.RangePartitioning takes a static list of boundary values, so rolling a new period forward (ALTER PARTITION SCHEME … NEXT USED + ALTER PARTITION FUNCTION … SPLIT) and retiring an aged one (MERGE) is left to the application.

Why it matters beyond convenience

Applications that opt out don't just take on DDL authorship — they lose Weasel's ordering and dependency management. Concrete case from JasperFx/CritterWatch#886: a hand-rolled rebuild of a partitioned table re-created it in a schema where Marten's mt_immutable_timestamptz helper hadn't been created, so a perfectly ordinary computed index over a DateTimeOffset member failed with 42883: function … does not exist. The index was fine; bypassing Weasel was the bug. Any application doing time-series partitioning today is exposed to that class of failure.

Proposal

A ManagedRangePartitions analogous to ManagedListPartitions, driven by a policy rather than a static list:

• Window policy — a period (month/week/day), how many periods to provision ahead of now, and how many to keep behind before an aged partition is dropped.
• Roll-forward — create missing periods additively at startup and on demand; never rebuild because "now" moved.
• Retention drop — aged partitions beyond the keep-behind window are dropped (Postgres DROP TABLE, SQL Server MERGE RANGE), which is what makes range partitioning worth having: reclaim is O(1) instead of a mass DELETE.
• A DEFAULT/overflow partition so rows outside the provisioned window are never rejected.
• Both engines: Postgres (PARTITION OF … FOR VALUES FROM … TO …) and SQL Server (partition function + scheme, SPLIT/MERGE, NEXT USED filegroup).

Consumers then declare intent — "monthly, 12 ahead, drop past 45 days" — and Weasel owns every DDL statement.

Acceptance

• No consumer needs IgnorePartitionsInMigration or an externally-managed escape hatch to run a rolling time-partitioned table.
• Rolling the window forward is Additive, never Rebuild.
• Dropping an aged partition is a policy outcome, not drift.
• Idempotent and safe to run on every startup, including concurrently from multiple nodes.

Downstream consumers to unblock: JasperFx/marten (ByExternallyManagedRangePartitions), JasperFx/polecat (ByExternallyManagedRange), and JasperFx/CritterWatch, which currently hand-writes all of this for its metrics table in both flavors.

[jeremydmiller]

Positioning note, separate from the spec above so the technical detail stays clean.

This is worth treating as a headline capability rather than an internal unblock, because the competitive field is thinner than it looks.

Postgres. The incumbent is pg_partman: an extension plus pg_cron, provisioned by whoever owns the database, living outside the application's deployment. It works, but it's DBA-side infrastructure — the application can't declare it, can't version it alongside its code, can't review it in a PR, and can't assume it exists in every environment it deploys into. Native declarative partitioning needs no extension at all, so Weasel managing the window from inside the app is strictly less to install, not more.

SQL Server. There is essentially nothing. Sliding-window partition management is a hand-rolled T-SQL chore with published recipes and no standard tooling — the canonical guidance amounts to "write a script that sets NEXT USED and SPLITs, and another that MERGEs." Filling that gap is real differentiation, and it's differentiation Polecat could use that isn't "Marten, but SQL Server."

Three things make the story bigger than the feature:

1. This is what makes retention cheap. Partitioning's real payoff isn't query pruning, it's O(1) reclaim — drop or merge a partition instead of a mass DELETE and the bloat and vacuum storm that follow. Every team with telemetry, audit, event or log tables hits this, almost always after an incident. Two concrete ones from the last week in JasperFx/CritterWatch: #871 (a one-row table holding 370 MB with 19,266 autovacuums) and #886 (a 3.8 GB table OOM'ing a 1 GiB pod and restart-looping the console).
2. One implementation lifts three products — Marten, Polecat, and anyone using Weasel directly for schema management. Weasel is the least-marketed piece of the stack, and this is the kind of feature people would adopt Weasel for.
3. It's on-thesis. The partition strategy becomes a line of C# applied at startup and versioned with the code, instead of a cron job somebody configured once in an environment nobody can reproduce.

Two cautions if it's going to carry that weight:

• Keep the surface declarative — period, ahead, retain. The moment it grows knobs it drifts toward "we built a DBA tool," which is a much harder promise to keep than "we manage your rolling window."
• Verify the SQL Server edition claim before it appears in any copy. I believe table partitioning has been available in all editions since SQL Server 2016 SP1 rather than being Enterprise-only, but that is exactly the sort of claim that shouldn't ship unverified.