Sa.Partitional.PostgreSql
0.20.0
dotnet add package Sa.Partitional.PostgreSql --version 0.20.0
NuGet\Install-Package Sa.Partitional.PostgreSql -Version 0.20.0
<PackageReference Include="Sa.Partitional.PostgreSql" Version="0.20.0" />
<PackageVersion Include="Sa.Partitional.PostgreSql" Version="0.20.0" />
<PackageReference Include="Sa.Partitional.PostgreSql" />
paket add Sa.Partitional.PostgreSql --version 0.20.0
#r "nuget: Sa.Partitional.PostgreSql, 0.20.0"
#:package Sa.Partitional.PostgreSql@0.20.0
#addin nuget:?package=Sa.Partitional.PostgreSql&version=0.20.0
#tool nuget:?package=Sa.Partitional.PostgreSql&version=0.20.0
Sa.Partitional.PostgreSql
English | Русский
Declarative PostgreSQL table partitioning library for .NET 10 — supports range (day / month / year) and list partitioning with automated migration, cleanup scheduling, and in-memory caching.
Installation
dotnet add package Sa.Partitional.PostgreSql
Contents
- Installation
- Properties
- Requirements
- Quick Start
- Configuration — the Options Pipeline
- Settings in Code — Full Reference
- Supported Strategies
- Fluent Builder API
- Key Types
- Naming Conventions
- Behaviour Notes
- Documentation
- Architecture
- Dependencies
- License
Properties
The library gathers everything needed for partitioning into three setting types, a fluent builder, and three public services. Below is a tour of the features with links to the details.
Partition management
- Auto-migration — a background job pre-creates future partitions so INSERT does not stall on CREATE.
MigrationScheduleSettings,IMigrationService - Guard against gaps. A gap is an INSERT into a partition that does not yet exist — either the insert fails, or (with
WithDefaultPartition()) it lands in the catch-all. Migration creates partitions ahead of time — for several days/months forward (ForwardDays) — so traffic never falls into a void.MigrationScheduleSettings - Auto-cleanup — moves old partitions out of circulation:
Drop(delete) orArchive(move into an archive schema), with an opt-in prune of empty list branches.PartCleanupScheduleSettings,IPartCleanupService - In-memory cache of partition metadata — a lookup never hits the database.
PartCacheSettings - Programmatic control —
IPartitionManager:Migrate,EnsureParts, manual window control. - Adoption of an existing table —
AdoptTableconverts a plain table with data once and continues managing its partitions.
Fluent Builder
- Range and list partitioning, combinable hierarchically to any nesting level.
Supported strategies - Multiple levels on one table:
PartByList+PartByRange+TimestampAs.Fluent Builder API - Tuning:
WithFillFactor,WithPartSeparator,WithPartTablePostfix, custom constraint and post-SQL hooks.
Default partition
- Catch-all for rows that matched no partition, enabled via
WithDefaultPartition().Fluent Builder API - Needed when other services write into the table without knowing about partitioning: while the right partition does not exist yet, those rows accumulate in
def$instead of breaking the insert.Quick start - Removes the conflict on partition creation. When the migration creates the real partition, PostgreSQL refuses if
def$already holds rows that belong to it. The library moves those rows out ofdef$into the new partition without collisions — through a staging table inside a transaction — so the catch-all never blocks later migrations.Behaviour notes
Cleanup modes
- Drop — physical removal (
DROP TABLE). Good for ephemeral data (outbox, audit, logs).CleanupMode = Drop - Archive — move into a separate schema (
ALTER TABLE ... SET SCHEMA). The archive schema is created automatically; the table keeps its name.CleanupMode = Archive
Named modules
- Each module has its own tables, cache, and migration/cleanup schedulers. Split them by database (
AddDataSource) or by business logic (keyedAddSaPartitional("name", ...)).Quick start
Settings
- Bind to
configurationorcodethrough one pipeline (Microsoft.Extensions.Options), one named instance per module. - All three setting types:
PartCacheSettings,MigrationScheduleSettings,PartCleanupScheduleSettings.
General
- Everything computes in UTC — partition names, range boundaries, and cache windows do not depend on the host time zone.
Behaviour notes - Cache consistency, transient-retry, and DDL serialization are described in
Behaviour notes. - Partition name formats — in
Naming conventions.
Requirements
- .NET 10, Native AOT compatible.
- Transitive dependencies:
Sa.Data.PostgreSql(Npgsql wrapper with a retry strategy),Sa.Schedule(background jobs). - Logging must be resolvable from DI — the repository and the migration service take
ILogger<T>. ASP.NET Core and Worker hosts provide it out of the box; a bareServiceCollectionneedsservices.AddLogging().
Quick Start
Minimal example — one table, one level
The simplest case: a table with daily range partitioning on creation date. Migration pre-creates partitions 3 days ahead; cleanup drops anything older than 7 days.
builder.Services.AddSaPartitional((sp, builder) =>
{
// No schema name => uses "public" (or current_search_path)
builder.AddSchema(schema =>
{
schema.AddTable("events",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"message text NOT NULL",
"created_at bigint NOT NULL")
.PartByRange(PgPartBy.Day, "created_at");
});
})
// Migration: pre-create partitions 3 days ahead
.AddPartMigrationSchedule(b => b.Configure((_, opts) =>
opts.ForwardDays = 3))
// Cleanup: drop partitions older than 7 days
.AddPartCleanupSchedule(b => b.Configure((_, opts) =>
opts.DropPartsAfterRetention = TimeSpan.FromDays(7)));
After startup the library checks which partitions are missing and creates them — ForwardDays calendar days starting with today, so ForwardDays = 3 here means today, tomorrow and the day after. The background job repeats every ~4 hours.
Two levels — list + range (multi-tenant layout)
Split data by client (list), then within each client partition by day (range). Every combination gets its own table: orders__acme_corp__y2026m10m13.
builder.Services.AddSaPartitional((sp, builder) =>
{
builder.AddSchema(schema =>
{
schema.AddTable("orders",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"client_id varchar(50) NOT NULL",
"amount decimal(18,2) NOT NULL",
"created_at bigint NOT NULL")
.PartByList("client_id") // first level: clients
.PartByRange(PgPartBy.Day, "created_at") // second level: days
.WithDefaultPartition(); // catch-all for unknown clients
});
})
.AddPartMigrationSchedule(b => b.Configure((_, opts) =>
opts.ForwardDays = 3))
.AddPartCleanupSchedule(b => b.Configure((_, opts) =>
opts.DropPartsAfterRetention = TimeSpan.FromDays(7)));
Here .WithDefaultPartition() ensures that an order from an unknown client does not throw — it lands in the default partition at every nesting level.
Three levels — geography → client → day
Deep hierarchy for global systems: region → city → day. Partition names: log__EU__Paris__y2026m10m13.
builder.Services.AddSaPartitional((sp, builder) =>
{
builder.AddSchema(schema =>
{
schema.AddTable("logs",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"region varchar(10) NOT NULL",
"city varchar(50) NOT NULL",
"level varchar(5) NOT NULL",
"created_at bigint NOT NULL")
.PartByList("region", "city") // two list columns at one level
.PartByRange(PgPartBy.Month, "created_at"); // months instead of days
});
})
.AddPartMigrationSchedule(b => b.Configure((_, opts) =>
opts.ForwardDays = 90)) // monthly partitions look further ahead
.AddPartCleanupSchedule(b => b.Configure((_, opts) =>
opts.DropPartsAfterRetention = TimeSpan.FromDays(90)));
For monthly partitions it makes sense to look 90 days ahead (3 months) and retain data for 90 days instead of the usual 7–30.
Multiple modules — isolated configs in one container
Each module owns its tables, its cache, its migration schedule, and its cleanup schedule. Keyed registration lets you separate them by database or business domain.
// Module "outbox" — its own DB, own data source
services.AddSaPartitional("outbox", (sp, builder) =>
{
builder.AddSchema("public", schema =>
{
schema.AddTable("outbox_messages",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"payload jsonb NOT NULL",
"created_at bigint NOT NULL")
.PartByRange(PgPartBy.Day, "created_at");
})
.AddDataSource(ds => ds.Options(ob => ob.Configure(o =>
o.ConnectionString = "Host=db1;Database=outbox_db")));
})
.AddPartMigrationSchedule(b => b.Configure((_, opts) => opts.ForwardDays = 3));
// Module "audit" — shared DB, default data source
services.AddSaPartitional("audit", (sp, builder) =>
{
builder.AddSchema("public", schema =>
{
schema.AddTable("audit_events",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"entity_type varchar(50) NOT NULL",
"created_at bigint NOT NULL")
.PartByList("entity_type")
.PartByRange(PgPartBy.Day, "created_at");
});
// Data source is picked from the shared registration below
})
.AddPartMigrationSchedule(b => b.Configure((_, opts) => opts.ForwardDays = 3));
// Shared data source for all modules without their own DataSource
services.AddSaPostgreSqlDataSource(ds => ds
.Options(ob => ob.Configure(o =>
o.ConnectionString = "Host=db-shared;Database=all_in_one")));
Module "outbox" works with its own outbox_db; module "audit" shares the common one. Each has its own background jobs: Migration job [outbox], Cleanup job [outbox], Migration job [audit], etc.
Adopting an existing plain table
When the table already exists and holds data, declare it with AdoptTable instead of AddTable. The
first migration converts it once, inside a single transaction:
- the original table is renamed to
{table}__backup$and kept; - the partitioned root is created from the same field declarations;
- partitions covering
min(range column) … max(range column)are created — and, for a list level, the distinct list values are read from the data; - every row is copied with an explicit column list, the identity sequence is moved past the copied ids, and the row counts are verified before commit.
services.AddSaPartitional((sp, builder) =>
{
builder.AddSchema(schema =>
{
schema.AdoptTable("orders",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"client_id varchar(50) NOT NULL",
"amount decimal(18,2) NOT NULL",
"created_at bigint NOT NULL") // must be declared: an existing table cannot be extended
.PartByRange(PgPartBy.Day, "created_at");
});
});
Requirements and limits:
- the table must be an ordinary table (
relkind = 'r'); a table that is already partitioned is rejected — the library never adopts tables that have partitions; - the declared column set must match the live table exactly;
- do not copy the source
PRIMARY KEYclause into the field declarations: the library generates its own primary-key constraint (it has to include the partition key); - incoming foreign keys and dependent views block the conversion: after the rename they would keep pointing at the backup;
- indexes, triggers and constraints other than the generated primary key stay on the backup;
- the copy is not online: the rename takes
ACCESS EXCLUSIVEand every row is rewritten; - the backup is kept after a successful conversion — drop it manually once the result is verified,
or set
.WithConversionBackup(ConversionBackupMode.Drop)to remove it inside the conversion transaction after the row-count check; - one table must belong to a single module: two modules adopting the same table would fight over its partitions;
- the conversion runs inside the migration's
RunTimeout; raise it for large tables.
Repeated runs are no-ops: once the root is partitioned and the cache table exists, the table is
treated as managed and old methods (Migrate, EnsureParts, Clean, Archive) keep working as
usual.
Configuration — the Options Pipeline
The three scalar settings types — PartCacheSettings, MigrationScheduleSettings,
PartCleanupScheduleSettings — are served by the standard Microsoft.Extensions.Options
pipeline, one named instance per module (the module key, or the unnamed instance for the
keyless module). Each AddPart* method takes one callback that collects three kinds of intent:
| Call | Purpose |
|---|---|
FromConfiguration(path) |
Bind the settings from a configuration section, e.g. "Partitional:Cache" or "Partitional:outbox:Cache" (last call wins) |
Configure((sp, settings) => ...) |
Apply code settings — the same (IServiceProvider, TSettings) delegate as before |
Options(ob => ...) |
Escape hatch: the raw OptionsBuilder<T> (Configure / PostConfigure / Validate) |
The pipeline order is fixed — section → code → Options(...) actions → validation — no matter
how the calls are split across the fluent chain: configuration is the baseline, code wins over it,
and Options(...) has the final word.
builder.Services.AddSaPartitional((sp, builder) => { /* table declarations */ })
.AddPartCache(b => b
.FromConfiguration("Partitional:Cache")
.Configure((_, s) => s.CacheTtl = TimeSpan.FromMinutes(10)))
.AddPartMigrationSchedule(b => b
.FromConfiguration("Partitional:Migration")
.Configure((_, s) => s.AsBackgroundJob = true))
.AddPartCleanupSchedule(b => b.FromConfiguration("Partitional:Cleanup"));
{
"Partitional": {
"Cache": { "CacheTtl": "00:10:00", "CachedFromDate": "1.00:00:00" },
"Migration": { "ForwardDays": 3, "AsBackgroundJob": true, "ExecutionInterval": "02:00:00" },
"Cleanup": { "DropPartsAfterRetention": "14.00:00:00", "InitialDelay": "00:05:00" }
}
}
A keyed module binds its own section — one named instance per module keeps the modules isolated:
services.AddSaPartitional("outbox", ...)
.AddPartCache(b => b.FromConfiguration("Partitional:outbox:Cache"));
Settings in Code — Full Reference
Below is every property of all three settings types, with comments and defaults.
<a name="partcache"></a>PartCacheSettings — Partition Metadata Cache
In-memory cache so reads do not hit the cache table on every check.
| Property | Default | Description |
|---|---|---|
CachedFromDate |
1 day |
Look-back window of a reload: partitions whose range starts before now − CachedFromDate are dropped from the snapshot. Sufficient to still capture the partition currently being written to. |
CacheTtl |
5 min |
How long a loaded snapshot stays usable. The deadline is checked on the read path, so the cache owns no timer. |
.AddPartCache(b => b.Configure((_, opts) =>
{
opts.CachedFromDate = TimeSpan.FromDays(1); // capture the current partition
opts.CacheTtl = TimeSpan.FromMinutes(5); // refresh every 5 minutes
}))
<a name="migration"></a>MigrationScheduleSettings — Auto-Migration (Pre-Creation)
Background job that pre-creates future partitions so inserts are not blocked waiting for table creation.
| Property | Default | Description |
|---|---|---|
ForwardDays |
2 days |
How many calendar days to pre-create on each tick, starting with today. Set to 3 to be safe that tomorrow's traffic finds no missing partition. Must be between 1 and 3660 (a typo there would ask for tens of thousands of tables). |
AsBackgroundJob |
false |
Run as a hosted service (true) or only manually via IMigrationService? |
MigrationJobName |
"Migration job" |
Job name in logs and the scheduler. When multiple modules exist, qualify it: "Migration job [outbox]". |
ExecutionInterval |
~4h ± jitter |
Interval between migration ticks. Jitter up to 59 minutes prevents thundering-herd when several instances restart simultaneously. |
WaitMigrationTimeout |
3 sec |
How long to wait for the migration lock. If contended — the tick skips (returns -1), the next tick tries again. Does not queue. |
RunTimeout |
10 sec |
Time budget for a single run, counted from acquiring the lock. Should stay below HostOptions.ShutdownTimeout (30 s by default). DDL is idempotent, so an interrupted run is safely finished by the next tick. |
.AddPartMigrationSchedule(b => b.Configure((_, opts) =>
{
opts.ForwardDays = 3; // 3 days ahead
opts.AsBackgroundJob = true; // run in background
opts.MigrationJobName = "Migration job [outbox]";
opts.ExecutionInterval = TimeSpan.FromHours(4);
opts.WaitMigrationTimeout = TimeSpan.FromSeconds(3);
opts.RunTimeout = TimeSpan.FromSeconds(10);
}))
<a name="cleanup"></a>PartCleanupScheduleSettings — Auto-Cleanup
Background job that retires old partitions.
| Property | Default | Description |
|---|---|---|
DropPartsAfterRetention |
30 days |
Age threshold after which a partition is retired. For event sourcing often 90–365 days; for outbox messages — 7–30 days. |
CleanupMode |
Drop |
What to do with an expired partition: Drop — delete entirely, Archive — move into an archive schema. |
ArchiveSchemaName |
"archive" |
Schema where partitions are moved when CleanupMode = Archive. Ignored in Drop mode. Tables keep their name — name collisions within the same schema cause errors. |
ArchiveCollisionMode |
Fail |
What to do when the archive schema already holds that name: Fail leaves the live partition attached and logs the clash, RenameExisting moves the older archived copy to {name}{sep}prev$N and archives the new one under the canonical name. |
PruneEmptyListParts |
false |
Also drop list-level partitions whose whole subtree holds no rows (and whose value is not declared by the migration). |
AsBackgroundJob |
false |
Run as hosted service or only manually via IPartCleanupService? |
ExecutionInterval |
~4h ± jitter |
Interval between cleanup ticks. |
InitialDelay |
1 min |
Delay before the first run after application start. Allows migration and initial data population to complete. |
.AddPartCleanupSchedule(b => b.Configure((_, opts) =>
{
opts.DropPartsAfterRetention = TimeSpan.FromDays(7);
opts.CleanupMode = PartCleanupMode.Drop;
opts.ArchiveSchemaName = "archive";
opts.AsBackgroundJob = true;
opts.InitialDelay = TimeSpan.FromMinutes(1);
}))
<a name="cleanup-mode-drop"></a>CleanupMode = Drop
Standard behaviour — physically removes the partition via DROP TABLE. Data is permanently lost. Suitable for ephemeral data (outbox, audit, logs).
<a name="cleanup-mode-archive"></a>CleanupMode = Archive
Moves the partition to another schema (ALTER TABLE ... SET SCHEMA "archive"). The table is preserved but stops participating in queries against the main schema. The archive schema is created automatically (CREATE SCHEMA IF NOT EXISTS); a database role without CREATE on the database has to create it in advance.
Tables keep their name, so an archived copy and a later partition of the same window collide: if data is written into a window that was already archived, the leaf is re-created in the live schema (and becomes visible to cleanup again), and the second archive pass finds the name taken. With the default ArchiveCollisionMode = Fail the pass logs EventId 410 and leaves the live partition attached instead of detaching it and failing on the name — rename or drop the stale archived copy by hand. With ArchiveCollisionMode = RenameExisting the library moves the older archived copy (and its indexes) to {name}{separator}prev$1, then $2, …, and archives the new partition under the canonical name, so a query against archive."{name}" always sees the most recent data (EventId 411 records the rename).
Empty list partitions
Cleanup retires range leaves only; a tenant (or any other list value) that stopped being used keeps its intermediate {table}{separator}{value} partition and its def$ child forever. PruneEmptyListParts = true additionally drops such a list-level partition when nothing in its subtree holds a row and its value is not declared by the table's migration support (so the next migration tick does not re-create it). It is off by default: dropping a structural partition that a writer may target is a decision for the application.
Supported Strategies
| Strategy | Description | Example |
|---|---|---|
| Range | Partitioning by time intervals — day, month, or year | events__y2026m06d26, events__y2026m07 |
| List | Partitioning by discrete key values (strings or numbers) | orders__RU, orders__42 |
Both strategies can be combined hierarchically: a list-partitioned root can have range-partitioned children (orders__RU__y2026m06d26).
Range key types
The range column may be a bigint with Unix seconds (the default) or a timestamp/date column. The
type is detected from the declared field:
| Column declaration | Bounds in DDL | Notes |
|---|---|---|
bigint / int8 |
FOR VALUES FROM (1793001600) TO (1793088000) |
Default; the library adds the column when it is not declared |
timestamptz / timestamp with time zone |
FROM ('2026-10-29 00:00:00+00') TO (...) |
UTC |
timestamp / timestamp without time zone |
FROM ('2026-10-29 00:00:00') TO (...) |
Treated as UTC |
date |
FROM ('2026-10-29') TO ('2026-10-30') |
The cache table still stores from_date/to_date as Unix seconds, so cleanup, retention and the
public DateTimeOffset API are identical for every mode. Use .WithRangeKey(RangeKeyType...) to
override the detected type for a column declared through a domain or an alias.
Fluent Builder API
Schema + Table Declaration
services.AddSaPartitional((sp, builder) =>
{
builder.AddSchema("outbox", schema =>
{
// Range-partitioned by day
schema.AddTable("messages",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"tenant_id varchar(50) NOT NULL",
"created_at bigint NOT NULL")
.PartByRange(PgPartBy.Day, "created_at")
.WithFillFactor(80);
// List-partitioned by region
schema.AddTable("orders",
"id bigint GENERATED BY DEFAULT AS IDENTITY",
"region varchar(10) NOT NULL",
"created_at bigint NOT NULL")
.PartByList("region")
.AddMigration(["EU"], ["US"], ["APAC"]); // three branches, one value each
});
});
CreateTable(name)+AddFields(...)andAddTable(name, fields...)are the same thing. At least one field is required: the first declared column becomes the primary-key id column, andBuild()throwsInvalidOperationExceptionwithout it.- A column that is not declared among the fields but is needed for partitioning is added to the root table by the library — a
bigint NOT NULL— so declaring the range column explicitly (as above) is usually what you want. PartByRange(partBy, fieldName?)andTimestampAs(fieldName?)do not depend on call order — the more specificTimestampAsalways wins. When both are omitted,created_atis used.
ITableBuilder — methods in action
Below is a single table configured with every available method. Each block is annotated:
schema.AddTable("events",
// ── Columns ───────────────────────────────────────────
// First declared column becomes the PK id column.
"id bigint GENERATED BY DEFAULT AS IDENTITY",
// Additional columns (any DDL accepted by PostgreSQL).
"tenant_id varchar(50) NOT NULL",
"message text",
"status varchar(20)")
// ── Partitioning strategy ───────────────────────────────
// Range by day on "created_at". Column defaults to "created_at"
// if omitted; PartByRange and TimestampAs are order-independent,
// with TimestampAs always winning.
.PartByRange(PgPartBy.Day, "created_at")
// Override the range column explicitly (more specific than
// PartByRange's second argument).
.TimestampAs("updated_at")
// List on top of range — each PartByList call adds one nesting
// level (one column per level).
.PartByList("region", "priority")
// ── Naming & formatting ─────────────────────────────────
// Separator between table name and key values (default "__").
// Must not be empty or contain '"'.
.WithPartSeparator("_")
// Cache-table postfix (default "part$").
// Result: {table}{sep}{postfix}, e.g. "events__part$".
.WithPartTablePostfix("meta$")
// Catch-all DEFAULT partition at every nesting level so rows
// whose key was never migrated are kept instead of rejected.
// Creates names like "{table}{sep}def$", "{table}{sep}{val}{sep}def$".
.WithDefaultPartition()
// ── Storage tuning ───────────────────────────────────────
// fillfactor for generated range leaves only (10..100; anything else throws).
// Root table and list partitions are emitted without it.
.WithFillFactor(80)
// ── Extra SQL hooks ──────────────────────────────────────
// Raw SQL appended after CREATE TABLE of the root table.
.AddPostSql(() => "INCLUDE (tenant_id, status)")
// Override the PK constraint name (default "pk_{table}").
// Return only the name — the library emits CONSTRAINT ... PRIMARY KEY.
.AddConstraintPkSql(() => "pk_events_created_at_tenant_id")
// ── Migration values ─────────────────────────────────────
// One array = one branch: exactly one value per PartByList
// column, outermost level first.
.AddMigration(["EU", "high"], ["EU", "low"])
// Two-level shorthand: parent + its children.
.AddMigrationChildren("US", "high", "low") // ["US","high"], ["US","low"]
// Or draw the tree — same result, any depth.
.AddMigrationTree(t => t
.Node("APAC", apac => apac
.Branch("high")
.Branch("low")))
// Custom provider implementing IPartTableMigrationSupport.
.AddMigration(myCustomProvider)
// Async lazy factory — resolved on-demand during migration.
.AddMigration(async ct => { /* query DB, return StrOrNum[][] */ })
// ── Finalise ─────────────────────────────────────────────
// Builds and validates the immutable ITableSettings snapshot.
.Build();
Branches are explicit. One
StrOrNum[]argument is one branch — exactly one value perPartByListcolumn. Several branches are separate arguments; the two-level shorthand has its own name; a deep hierarchy can be drawn as a tree:.AddMigration(["EU", "high"], ["EU", "low"]) // two two-level branches .AddMigration(["EU"], ["US"]) // two one-level branches .AddMigrationChildren("EU", "high", "low") // EU → {high, low} .AddMigrationTree(t => t // the same, drawn as the partition tree .Node("EU", eu => eu .Branch("high") .Branch("low")))A branch whose length does not match the list levels (or any value on a range-only table) fails at
Build(), with the branch and the expected count in the message.
How each call maps onto the partition tree, for a table declared as
.PartByList("country", "city").PartByRange(PgPartBy.Day):
| Call | Branches declared | Partition tables produced |
|---|---|---|
.AddMigration(["RU", "Moscow"]) |
["RU","Moscow"] |
orders__RU__Moscow__y2026m11d09 |
.AddMigration(["RU","Moscow"], ["RU","Kazan"]) |
two | orders__RU__Moscow__y…, orders__RU__Kazan__y… |
.AddMigrationChildren("RU", "Moscow", "Kazan") |
two (same as above) | orders__RU__Moscow__y…, orders__RU__Kazan__y… |
.AddMigrationTree(t => t.Node("RU", ru => ru.Branch("Moscow").Branch("Kazan"))) |
two (same as above) | orders__RU__Moscow__y…, orders__RU__Kazan__y… |
orders PartByList("country", "city"), PartByRange(Day)
└── orders__RU ← "RU" (only the parent)
├── orders__RU__Moscow ← ["RU","Moscow"] (one branch)
│ └── orders__RU__Moscow__y2026m11d09 ← the range leaf of one date
└── orders__RU__Kazan ← ["RU","Kazan"] (the second branch)
└── orders__RU__Kazan__y2026m11d09
AddMigrationChildren is exactly that subtree: the first argument names the parent level, every
following argument adds one child branch. A one-level list therefore takes one value per branch —
.AddMigration(["acme"]) for one tenant, .AddMigration(["acme"], ["globex"]) for two. A
three-level list needs full branches (.AddMigration(["EU","DE","Berlin"])) or
AddMigrationTree with a nested Node; AddMigrationChildren covers two levels only.
Key Types
<a name="api-manager"></a>IPartitionManager
Main entry point for programmatic partition management:
public interface IPartitionManager
{
Task<int> Migrate(CancellationToken ct = default);
Task<int> Migrate(DateTimeOffset[] dates, CancellationToken ct = default);
Task EnsureParts(string tableName, DateTimeOffset date, StrOrNum[] partValues, CancellationToken ct = default);
}
Migrate()— pre-create all missing partitions for today + the forward windowMigrate(dates[])— pre-create only for specific datesEnsureParts()— guarantee a specific partition exists (creates it if missing). It completes when the partition exists and reports failures as exceptions; it does not return a bool, because a cache miss is not a failure
<a name="api-migration"></a>IMigrationService
The service behind IPartitionManager; resolve directly to wait for an in-flight run:
public interface IMigrationService
{
CancellationToken OnMigrated { get; }
Task<int> Migrate(CancellationToken ct = default);
Task<int> Migrate(DateTimeOffset[] dates, CancellationToken ct = default);
Task<bool> WaitMigration(TimeSpan timeout, CancellationToken ct = default); // true = completed, timeout/cancel = false
}
- One run at a time.
Migrate()waits for the migration lock at mostWaitMigrationTimeoutand then skips the tick (returns-1) instead of queueing.Migrate(dates)is an explicit request, so it waits indefinitely and also honours the caller's token. -1means "not completed" — deadline elapsed or cancelled, not "a negative number of partitions". Generated DDL is idempotent, so the next run finishes the remainder.- A started background run is bounded by
RunTimeoutand is not interrupted by the caller's token: DDL is idempotent, so finishing is cheaper than restarting. OnMigratedis a one-shot signal — once a migration completes it stays triggered, answering "has a migration ever completed", not "is one running now".
<a name="api-cleanup"></a>IPartCleanupService
public interface IPartCleanupService
{
Task<int> Clean(CancellationToken ct = default); // older than DropPartsAfterRetention
Task<int> Clean(DateTimeOffset toDate, CancellationToken ct = default);
}
Clean retires partitions according to PartCleanupScheduleSettings.CleanupMode — Drop deletes them,
Archive detaches them and moves them into ArchiveSchemaName (PostgreSQL 13+). Either way it does so without
invalidating the in-memory cache — a call outside the background job leaves a stale snapshot for up to
PartCacheSettings.CacheTtl. Rely on the scheduled job when the same process must see the change immediately.
PgPartBy
Partitioning strategy with three predefined values. Both the name and the covered range are computed from the UTC calendar:
| Value | Name pattern | Example | Range |
|---|---|---|---|
PgPartBy.Day |
y{YYYY}m{MM}d{DD} |
events__y2026m06d26 |
StartOfDay → +1 day |
PgPartBy.Month |
y{YYYY}m{MM} |
events__y2026m07 |
StartOfMonth → +1 month |
PgPartBy.Year |
y{YYYY} |
events__y2026 |
StartOfYear → +1 year |
Additional factory methods:
PgPartBy.FromRange(PartByRange.Day); // from PartByRange enum (falls back to Day)
PgPartBy.FromPartName("root"); // from a partition name; null/unknown => Part.Root
StrOrNum
Discriminated union for list partition keys — supports both string and numeric values:
// Implicit conversions
StrOrNum s = "tenant_a"; // → ChoiceStr
StrOrNum n = 42L; // → ChoiceNum
// Pattern matching
result.Match(
onChoiceStr: v => Console.WriteLine($"String: {v}"),
onChoiceNum: v => Console.WriteLine($"Number: {v}")
);
// Formatting
s.ToFmtString(); // "s:tenant_a"
StrOrNum.FromFmtStr("n:42"); // → ChoiceNum(42)
StrOrNum.TryFromFmtStr("n:x", out _); // false — strict counterpart, no fallback
- Supported implicit conversions:
string,int,long,short. - A string payload is escaped in the
s:/n:protocol:,and\are backslash-escaped, so a key like"EU,west"survives the round-trip through the cache table'spart_valuescolumn. A malformedn:value falls back toChoiceStrwith the raw text; useTryFromFmtStrto detect the corruption. - The explicit numeric conversions are lossy by design:
(long)"abc"is0, and(long)42Lstringifies through the invariant culture.
Naming Conventions
Partitions follow predictable naming patterns (separator defaults to "__"):
| Component | Pattern | Example |
|---|---|---|
| Range (day) | {table}{sep}y{YYYY}m{MM}d{DD} |
events__y2026m06d26 |
| Range (month) | {table}{sep}y{YYYY}m{MM} |
events__y2026m07 |
| Range (year) | {table}{sep}y{YYYY} |
events__y2026 |
| List (nested) | {table}{sep}{val1}{sep}{val2} |
orders__RU__Moscow |
| List + range leaf | {table}{sep}{val1}...{sep}y{YYYY}m{MM}d{DD} |
orders__RU__y2026m06d26 |
| Cache table | {table}{sep}{postfix} |
events__part$ |
| Catch-all | {table}{sep}def$ |
orders__def$ |
Constraints and guarantees:
- Identifiers must fit 63 bytes of UTF-8, not 63 characters.
Build()rejects a too-long schema, root-table or cache-table name; a too-long partition name throws when its DDL is generated (key values are only known then). - A key value must not contain the separator:
["a__b"]and["a", "b"]would otherwise collapse onto one table name. - All identifiers and literals are quoted and escaped, so a key holding
"or'cannot terminate them and append SQL. - Schemas are auto-created via
CREATE SCHEMA IF NOT EXISTS. - Child partitions use
PARTITION OF parent FOR VALUES FROM (...) TO (...)(range) orFOR VALUES IN (...)(list). Range bounds are integers for abigintkey and quoted timestamps/dates for a timestamp/date key. - After each range partition is created, a row is written into the cache table (
id,root,part_values,part_by,from_date,to_date). The insert is an upsert (ON CONFLICT (id) DO UPDATE), so a partition re-created on a date whose earlier incarnation was archived becomes visible to the cache and to cleanup again (archivedis reset toFALSE).
Keeping identifiers under 63 bytes
A generated partition name is root + separator + values + separator + range suffix. The error message
breaks that down, reports how many bytes are over the limit, and names the levers:
| Lever | Effect |
|---|---|
WithPartSeparator("_") |
shrinks every separator in every partition name |
| shorter table/root name | removes bytes from every name and from the cache table |
| shorter key values (e.g. numeric tenant ids) | removes the value contribution |
WithPartTablePostfix("meta$") |
shrinks only the cache table name, not partition names |
Practical rules: prefer numeric ids over long strings for list keys, keep table names short, and do not build deep list hierarchies from long values. The library never truncates a name silently — it fails with the byte breakdown instead.
Overflow callback
If the values cannot be shortened, supply a replacement name with WithLongPartitionName. It is
called only when the regular name exceeds 63 bytes — short names are never rewritten:
using System.Security.Cryptography;
using System.Text;
schema.AddTable("orders", /* ... */)
.PartByRange(PgPartBy.Day, "created_at")
.WithLongPartitionName(ctx =>
{
string token = Convert.ToHexString(
SHA256.HashData(Encoding.UTF8.GetBytes(ctx.IdealName)))[..26].ToLowerInvariant();
return $"{ctx.RootTableName}__h${token}"
+ (ctx.RangeSuffix is null ? string.Empty : $"__{ctx.RangeSuffix}");
});
- Called for intermediate list levels and
DEFAULTpartitions too (ctx.IsDefaultPartition). - Must be a pure function: same context → same name on every host and restart.
- Result must be a bare name of at most 63 UTF-8 bytes; the library validates it.
- Uniqueness is the callback's responsibility. A hash of
ctx.IdealNameis the usual approach; make sure the result fits even when the root name is long (otherwise fall back top_<hash>). - The cache-table name is not affected — use
WithPartTablePostfixfor it; the root name itself cannot be replaced.
Behaviour Notes
- UTC everywhere. Partition names, range boundaries and cache windows are computed from the UTC calendar, so two hosts in different time zones create the same partition for the same instant.
- Cache consistency. Background jobs drop the whole snapshot after every run (unconditionally — a partial run could have created partitions too). A manual
Migrate(dates)orClean(...)call does not;EnsurePartsinvalidates the entry of its own table and remembers the partitions it ensured forCacheTtl, so a backdated write outside the snapshot window does not re-run the (idempotent) DDL on every call. The jobs' flush drops that memory as well. - Retries and serialization. Both the read and write path of the repository go through the retry strategy of
Sa.Data.PostgreSql, so a transient database failure does not reach the caller. DDL is additionally serialized per module (oneIPartRepositoryper module): inside a module two partitions are never created concurrently, while two modules that share one data source are not serialized against each other — their partition names never overlap, so the only shared object is theCREATE SCHEMA IF NOT EXISTS/ cache DDL, which is idempotent. The adoption pass is the exception: it runs inside its own transaction under a PostgreSQL advisory lock and does not go through the retry strategy, a transient failure there fails that migration run. - Job error handling. Only the expected stop (cancellation / timeout) is suppressed. A real failure of one table is logged (migration
EventId 104, cleanupEventId 207) and the run continues with the remaining tables, so one broken table does not starve the others; the run as a whole then fails, is retried, and aborts that job — not the application. - Fill factor. Applied to date-range children only. When
WithFillFactorwas never called the clause is omitted and PostgreSQL applies its own default (100). - Logging. Services log through
ILogger<T>using structured[LoggerMessage]events; a failed or unfinished migration is logged and reported as-1instead of throwing out of a scheduler tick.
Documentation
| Document | Contents |
|---|---|
| Guide | Detailed setup, builder DSL, StrOrNum, date ranges, generated DDL |
| API Reference | Interface signatures, architecture diagram, internal components |
Architecture
┌─────────────────────┐
│ IPartitionManager │ ← Public entry point
├─────────────────────┤
│ IMigrationService │ ← Pre-create future partitions
│ IPartCleanupService│ ← Drop old partitions
├─────────────────────┤
│ IPartRepository │ ← DDL execution (CREATE/DROP PARTITION)
│ ISqlBuilder │ ← SQL template generation
│ IPartCache │ ← In-memory metadata cache
├─────────────────────┤
│ MigrationJob │ ← IJob wrapper for Sa.Schedule
│ PartCleanupJob │ ← IJob wrapper for Sa.Schedule
└─────────────────────┘
Dependencies
Sa.Data.PostgreSql— Npgsql client wrapper with retry strategySa.Schedule— Background job scheduling infrastructure
License
MIT
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
-
net10.0
- Microsoft.Extensions.DependencyInjection.Abstractions (>= 10.0.14)
- Microsoft.Extensions.Logging.Abstractions (>= 10.0.14)
- Microsoft.Extensions.Options (>= 10.0.14)
- Microsoft.Extensions.Options.ConfigurationExtensions (>= 10.0.14)
- Sa.Data.PostgreSql (>= 0.20.0)
- Sa.Schedule (>= 0.20.0)
NuGet packages (2)
Showing the top 2 NuGet packages that depend on Sa.Partitional.PostgreSql:
| Package | Downloads |
|---|---|
|
Sa.Outbox.PostgreSql
Simple Outbox for Pg (publishing and using messages) |
|
|
Sa.HybridFileStorage.Postgres
File storage management in Pg |
GitHub repositories
This package is not used by any popular GitHub repositories.
| Version | Downloads | Last Updated |
|---|---|---|
| 0.20.0 | 67 | 11/13/2026 |
| 0.18.0 | 119 | 11/3/2026 |
| 0.17.0 | 127 | 10/29/2026 |
| 0.16.0 | 121 | 10/14/2026 |
| 0.15.0 | 118 | 10/2/2026 |
| 0.12.0 | 194 | 7/28/2026 |
| 0.11.0 | 161 | 7/14/2026 |
| 0.10.0 | 159 | 7/2/2026 |
| 0.9.0 | 153 | 4/29/2026 |
| 0.8.0 | 193 | 2/10/2026 |
| 0.7.1 | 153 | 1/28/2026 |
| 0.7.0 | 154 | 1/27/2026 |
| 0.6.0 | 153 | 1/15/2026 |
| 0.5.0 | 159 | 12/29/2025 |
| 0.4.0 | 346 | 12/17/2025 |
| 0.3.0 | 225 | 10/17/2025 |
| 0.2.0 | 270 | 9/3/2025 |
| 0.1.0 | 243 | 9/1/2025 |
| 0.0.3 | 245 | 6/16/2025 |
| 0.0.2 | 375 | 5/15/2025 |