Sa.HybridFileStorage.Postgres 0.20.0

dotnet add package Sa.HybridFileStorage.Postgres --version 0.20.0
                    
NuGet\Install-Package Sa.HybridFileStorage.Postgres -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.HybridFileStorage.Postgres" Version="0.20.0" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="Sa.HybridFileStorage.Postgres" Version="0.20.0" />
                    
Directory.Packages.props
<PackageReference Include="Sa.HybridFileStorage.Postgres" />
                    
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.HybridFileStorage.Postgres --version 0.20.0
                    
#r "nuget: Sa.HybridFileStorage.Postgres, 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.HybridFileStorage.Postgres@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.HybridFileStorage.Postgres&version=0.20.0
                    
Install as a Cake Addin
#tool nuget:?package=Sa.HybridFileStorage.Postgres&version=0.20.0
                    
Install as a Cake Tool

Sa.HybridFileStorage.Postgres

English | Русский

PostgreSQL-backed file storage provider for Sa.HybridFileStorage. Stores files as BYTEA in a partitioned database table with automatic partition management, scheduled migration, and background cleanup.


Table of Contents


Overview

PostgresFileStorage implements IFileStorage backed by PostgreSQL. File binary data is stored as BYTEA columns in a partitioned table. Key characteristics:

  • Keyed registration — every registration owns its table, its settings, its migration and its cleanup schedule; several independent storages coexist in one container
  • Automatic partitioning — declarative list + range partitioning via Sa.Partitional.PostgreSql
  • Automatic data source — a chained AddDataSource(...) is optional: without one the storage reuses the default IPgDataSource or an already-registered NpgsqlDataSource
  • Upsert semantics — ON CONFLICT DO UPDATE handles re-uploads transparently
  • Scheduled migrations — background job pre-creates future partitions
  • Background cleanup — drops old partitions beyond retention period
  • Non-seekable stream handling — buffers unseekable streams into an internal pooled stream
  • Timestamp in File ID — includes Unix seconds for range partition resolution

File ID Format

pg://{basket}/{tenantId}/{unixTimestamp}/{fileName}

Examples:

  • pg://files/42/1751347200/report.pdf
  • pg://docs/7/1751347200/invoice.csv
  • pg://share/100/1751347200/data.bin

The timestamp is the Unix epoch seconds of the upload date (UTC midnight). It determines which partition the row belongs to.


Installation

dotnet add package Sa.HybridFileStorage.Postgres

This package depends on Sa.Data.PostgreSql and Sa.Partitional.PostgreSql.


Quick Start

Registration is keyed: every call takes a registration key, and each key produces an independent storage with its own options (its own TableName, retention and partition settings), its own partitional module, and its own keyed IFileStorage. The same instance is also aliased as an unkeyed IFileStorage, which is what the hybrid container collects into the failover chain — several keyed storages are tried in registration order and the first success ends the operation.

using Sa.HybridFileStorage.Postgres;

// One storage per key, each owning its table.
builder.Services.AddSaPostgreSqlFileStorageChained("files", b => b
    .Options(ob => ob.Configure(o =>
    {
        o.TableName = "files";
        o.StorageType = "pg";
        o.Basket = "share";
        o.ExpireDays = 365 * 3;               // drop this storage's partitions after 3 years
    })))
    .AddDataSource(ds => ds
        .Options(ob => ob.Configure(o => o.ConnectionString =
            "Host=localhost;Database=mydb;Username=postgres;Password=password")));

TableName is required and unique. It has no default: every registration must name the table it owns, and two registrations may not declare the same table — they would both run migration and cleanup over one physical table, so the collision is rejected at startup.

When the data source is already registered by something else, skip the chaining overload — the storage resolves it automatically (see Reusing an NpgsqlDataSource):

builder.Services.AddSaPostgreSqlFileStorage("binary", b => b
    .Options(ob => ob.Configure(o =>
    {
        o.TableName = "binary_data";
        o.SchemaName = "storage";
        o.PartByRange = PartByRange.Month;
    })));

Resolve a specific storage by key, or let the hybrid pipeline collect all of them:

IFileStorage files  = sp.GetRequiredKeyedService<IFileStorage>("files");
IFileStorage binary = sp.GetRequiredKeyedService<IFileStorage>("binary");

// `sp.GetServices<IFileStorage>()` returns one instance per registration,
// in registration order — the order the hybrid failover chain tries them.

Reusing an NpgsqlDataSource

A chained AddDataSource(...) is optional. When no data source is configured for the storage, it resolves one in this order:

  1. a keyed IPgDataSource under this registration's module key (the chained AddDataSource);
  2. the default (unkeyed) IPgDataSource, if AddSaPostgreSqlDataSource(...) was called;
  3. an NpgsqlDataSource already registered in the container — e.g. via Npgsql.DependencyInjection's AddNpgsqlDataSource(...) or services.AddSingleton<NpgsqlDataSource>(...) — wrapped so it reuses the same connection pool and is never disposed by the storage.
// The app already owns a pool; the storage rides on it — no connection string here at all.
builder.Services.AddNpgsqlDataSource("Host=db.internal;Database=mydb;Username=u;Password=p");
builder.Services.AddSaPostgreSqlFileStorage("files", b => b
    .Options(ob => ob.Configure(o => o.TableName = "files")));

With no IPgDataSource and no NpgsqlDataSource at all, resolving the storage fails with an InvalidOperationException naming both missing registrations.

Without a Generic Host

There is no public constructor: the provider needs a partition manager, a recyclable memory-stream manager and (optionally) a time provider, and the registration wires all three — plus the migration and cleanup schedules. Without a Generic Host, build a container directly:

var services = new ServiceCollection();

services.AddSaPostgreSqlFileStorageChained("files", b => b
        .Options(ob => ob.Configure(o => o.TableName = "files")))
    .AddDataSource(ds => ds
        .Options(ob => ob.Configure(o => o.ConnectionString = "Host=localhost;Database=mydb;...")));

await using var provider = services.BuildServiceProvider();
var storage = provider.GetRequiredKeyedService<IFileStorage>("files");

A host is only needed for ValidateOnStart() to run at startup; without one, validation fires on the first read of the options (the resolve above), and the scheduled jobs never start.

Schema resolution

SchemaName is optional. When left null the schema is resolved once at startup, in this order:

  1. the explicit SchemaName, if set;
  2. the first entry of the resolved data source's search path (...;Search Path=storage,public → storage);
  3. public.

GetSearchPath() parses the connection string, so no database round trip is involved.

Validation timing

Validation runs through the standard options pipeline — at the first read of the options (the storage, the DDL or the schema resolver materialises them), or at host start with ValidateOnStart():

Problem Failure
TableName, SchemaName not a bare SQL identifier ([A-Za-z_][A-Za-z0-9_]*, ≤ 63 chars) OptionsValidationException naming the property
TableName empty/not set OptionsValidationException
StorageType > 10 chars, or containing :, / or \ (it becomes the file ID scheme) OptionsValidationException
Basket outside 3–63 chars, not starting with a letter/_, or containing a path separator OptionsValidationException
ExpireDays, MigrationScheduleForwardDays < 1 OptionsValidationException
MaxUploadSizeBytes < 0 OptionsValidationException
FileName longer than 512 characters, or containing a NUL byte ArgumentException on upload
Two registrations declaring the same TableName OptionsValidationException naming both keys
A second registration with the same key InvalidOperationException at registration
key null/empty/whitespace ArgumentException at registration

TableName is used both for the DDL and for every query, so it is rejected rather than quietly rewritten — "\"files\"" used to be silently trimmed, which produced a table the provider then failed to find.


CRUD Examples

Upload from Stream

using var stream = new MemoryStream(Encoding.UTF8.GetBytes("Hello, Postgres!"));
var result = await storage.UploadAsync(
    new UploadFileInput { FileName = "hello.txt", TenantId = 42 },
    stream, ct);

Console.WriteLine(result.FileId);
// pg://files/42/1751347200/hello.txt
// (timestamp = today's UTC midnight as Unix seconds)

Upload Non-Seekable Stream

// Non-seekable streams are automatically buffered into an internal pooled stream
await using var nonSeekable = CreateNonSeekableStream();

var result = await storage.UploadAsync(
    new UploadFileInput { FileName = "blob.dat", TenantId = 7 },
    nonSeekable, ct);

// Internally: copied → buffered → upserted → buffer recycled

The whole stream is buffered in memory before it is written (Npgsql needs the length up front). Set MaxUploadSizeBytes to bound that allocation — an oversized upload is rejected with InvalidOperationException and no row is written.

Download to Memory

byte[]? downloaded = default;
await storage.DownloadAsync(result.FileId, async (stream, token) =>
{
    downloaded = await stream.ReadAllBytesAsync(token);
}, ct);

Download Direct Processing

await storage.DownloadAsync(result.FileId, async (stream, token) =>
{
    // Process stream directly — no intermediate buffering
    using var reader = new BinaryReader(stream);
    while (reader.ReadByte() is byte b)
    {
        // ...
    }
}, ct);

Get Metadata

var metadata = await storage.GetMetadataAsync(result.FileId, ct);
if (metadata != null)
{
    Console.WriteLine($"Basket: {metadata.Basket}");          // files
    Console.WriteLine($"Tenant: {metadata.TenantId}");        // 42
    Console.WriteLine($"Name:   {metadata.FileName}");        // hello.txt
    Console.WriteLine($"Type:   {metadata.StorageType}");     // pg
}

null means the row is gone (deleted, or never uploaded): the values come from the table, not from parsing the file ID. A parsable ID alone is not a file.

The same holds before anything has been written at all: the table is created by the first upload or by the migration schedule, so a read that runs before either answers "not found" — DownloadAsync/DeleteAsync return false, GetMetadataAsync returns null — instead of raising 42P01.

Delete

bool deleted = await storage.DeleteAsync(result.FileId, ct);
// Parses tenantId and timestamp from File ID for targeted DELETE

Partitioning

Files are stored in a partitioned table with dual partitioning strategy:

  1. List partitioning — by (tenant_id, basket) tuple
  2. Range partitioning — by created_at (date)

Schema auto-creation

The provider uses Sa.Partitional.PostgreSql to manage partitions:

-- Auto-created table structure (abridged):
CREATE TABLE public.files (
    id         TEXT NOT NULL,
    name       VARCHAR(512) NOT NULL,
    size       BIGINT NOT NULL,
    file_ext   VARCHAR(64) NOT NULL,
    tenant_id  INT NOT NULL,
    basket     VARCHAR(63) NOT NULL,
    data       BYTEA NOT NULL,
    created_at BIGINT NOT NULL,       -- range key, Unix seconds (UTC midnight of the upload day)
    CONSTRAINT pk_files PRIMARY KEY (id, tenant_id, basket, created_at)
) PARTITION BY LIST (tenant_id);

-- The tree is nested, not two independent strategies:
--   PARTITION BY LIST (tenant_id)  →  PARTITION BY LIST (basket)  →  PARTITION BY RANGE (created_at)
-- Each (tenant_id, basket) pair therefore gets its own set of date partitions.

Each tenant (and basket) gets its own set of date partitions, so the default PartByRange.Day with ExpireDays = 365 * 3 keeps about 1095 leaf partitions per tenant. With many tenants pick PartByRange.Month or Year, or a shorter retention — the partition count is the operational cost of this layout.

Partition strategies

Strategy PartByRange value Use case
Day PartByRange.Day High-volume systems, fine-grained cleanup
Month PartByRange.Month Medium volume, balanced granularity
Year PartByRange.Year Low volume, simple management

Migration schedule

Every registration configures its own migration schedule. New partitions are pre-created in advance (default: MigrationScheduleForwardDays = 2) via a background job:

options.MigrationScheduleForwardDays = 2;

Cleanup schedule

Every registration configures its own cleanup schedule: old partitions beyond the registration's retention period are dropped:

options.ExpireDays = 365 * 3;  // drop this storage's partitions older than 3 years

Scheduled Maintenance

Each registration registers its own two background jobs:

Job Purpose Configuration
Migration Pre-create this storage's upcoming partitions forwardDays, asBackgroundJob
Cleanup Drop this storage's old partitions after its retention dropPartsAfterRetention (TimeSpan)

Both run as background hosted services and use the same PostgreSQL connection pool as the storage.


Settings Reference

PostgresFileStorageOptions

Property Description Default
SchemaName PostgreSQL schema; null means auto-detect (see above) null → search path / public
TableName Table name for file data — required, unique per registration —
StorageType Scheme prefix in File ID "pg"
Basket Scope/container name (used as list partition key) "share"
IsReadOnly Prevent write/delete operations false
PartByRange Range partitioning granularity PartByRange.Day
MigrationScheduleForwardDays Days ahead to pre-create partitions 2
ExpireDays Retention period before partition drop (days) 365 * 3
MaxUploadSizeBytes Largest accepted upload in bytes; 0 means no limit 0

Registration extensions

Method Description
AddSaPostgreSqlFileStorage(string key, Action<IPostgresFileStorageBuilder>?) Register a keyed provider; returns the service collection
AddSaPostgreSqlFileStorage(string key, PostgresFileStorageOptions) Same, from a prepared instance (copied, not retained)
AddSaPostgreSqlFileStorage(string key, IOptions<PostgresFileStorageOptions>) Same, from a ready-made options instance
AddSaPostgreSqlFileStorageChained(string key, Action<IPostgresFileStorageBuilder>?) Same, returns IPartConfiguration for .AddDataSource(...)

Registration keys must be unique per collection: a second call with the same key throws InvalidOperationException — it would collide on the named options instance, the keyed partitional module and the keyed IFileStorage.

DI services

Service Purpose
IFileStorage (keyed by registration key) The provider itself (singleton per key)
IFileStorage (unkeyed) One alias per registration — what the hybrid container orders into the failover chain
PostgresFileStorageOptions (named by the registration key) The storage's live options; read via IOptionsMonitor<...>.Get(key)
IPartitionManager (keyed) This storage's partition maintenance, from Sa.Partitional.PostgreSql
TimeProvider Defaults to TimeProvider.System; register your own to control UploadedAt

Dependencies

Package Purpose
Sa.Data.PostgreSql Npgsql client (IPgDataSource)
Sa.Partitional.PostgreSql Declarative partition management (IPartitionManager)

Data Model

The underlying table structure:

Column Type Purpose
id TEXT Canonical File ID (primary key part)
name VARCHAR(512) Original file name
size BIGINT File size in bytes
file_ext VARCHAR(64) File extension (e.g., "pdf", "png")
tenant_id INT Tenant identifier (list partition key)
basket VARCHAR(63) Container/scope name (list partition key)
data BYTEA Raw file binary content
created_at BIGINT Upload date (UTC midnight as Unix seconds, range partition key)

size and created_at are BIGINT: the provider writes the stream length and the Unix timestamp as 64-bit values.


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

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
0.20.0 86 11/13/2026
0.18.0 100 11/3/2026
0.17.0 100 10/29/2026
0.16.0 99 10/14/2026
0.15.0 100 10/2/2026
0.12.0 134 7/28/2026
0.11.0 127 7/14/2026
0.10.0 137 7/2/2026
0.9.1 131 5/8/2026
0.9.0 127 4/29/2026
0.8.3 145 3/11/2026
0.8.1 131 3/6/2026
0.8.0 128 2/10/2026
0.7.0 136 1/27/2026
0.6.0 133 1/15/2026
0.5.0 141 12/29/2025
0.4.0 315 12/17/2025
0.3.1 436 11/19/2025
0.3.0 181 10/17/2025
0.2.0 230 9/3/2025
Loading failed