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
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Sa.Partitional.PostgreSql" Version="0.20.0" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="Sa.Partitional.PostgreSql" Version="0.20.0" />
                    
Directory.Packages.props
<PackageReference Include="Sa.Partitional.PostgreSql" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add Sa.Partitional.PostgreSql --version 0.20.0
                    
#r "nuget: Sa.Partitional.PostgreSql, 0.20.0"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package Sa.Partitional.PostgreSql@0.20.0
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=Sa.Partitional.PostgreSql&version=0.20.0
                    
Install as a Cake Addin
#tool nuget:?package=Sa.Partitional.PostgreSql&version=0.20.0
                    
Install as a Cake Tool

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

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) or Archive (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 — AdoptTable converts 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 of def$ 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 (keyed AddSaPartitional("name", ...)). Quick start

Settings

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 bare ServiceCollection needs services.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 KEY clause 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 EXCLUSIVE and 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(...) and AddTable(name, fields...) are the same thing. At least one field is required: the first declared column becomes the primary-key id column, and Build() throws InvalidOperationException without 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?) and TimestampAs(fieldName?) do not depend on call order — the more specific TimestampAs always wins. When both are omitted, created_at is 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 per PartByList column. 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 window
  • Migrate(dates[]) — pre-create only for specific dates
  • EnsureParts() — 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 most WaitMigrationTimeout and 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.
  • -1 means "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 RunTimeout and is not interrupted by the caller's token: DDL is idempotent, so finishing is cheaper than restarting.
  • OnMigrated is 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's part_values column. A malformed n: value falls back to ChoiceStr with the raw text; use TryFromFmtStr to detect the corruption.
  • The explicit numeric conversions are lossy by design: (long)"abc" is 0, and (long)42L stringifies 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) or FOR VALUES IN (...) (list). Range bounds are integers for a bigint key 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 (archived is reset to FALSE).

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 DEFAULT partitions 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.IdealName is the usual approach; make sure the result fits even when the root name is long (otherwise fall back to p_<hash>).
  • The cache-table name is not affected — use WithPartTablePostfix for 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) or Clean(...) call does not; EnsureParts invalidates the entry of its own table and remembers the partitions it ensured for CacheTtl, 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 (one IPartRepository per 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 the CREATE 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, cleanup EventId 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 WithFillFactor was 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 -1 instead 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 strategy
  • Sa.Schedule — Background job scheduling infrastructure

License

MIT

Product 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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

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