Lazy Tenant Partitioning in PostgreSQL

In a multi-tenant system, many tenants can share the same PostgreSQL schema. A common way to separate their data is to partition shared tables by tenant_id with PARTITION BY LIST (tenant_id). Each tenant's rows are stored in a separate partition under the same parent table. When a query filters by tenant_id, PostgreSQL can read only the relevant partition instead of scanning the others. This helps with isolation and performance.

So the real question is when to create those partitions. Startup provisioning sounds simple, but it adds work to every restart and gets slower as the tenant count grows. That is why we provision partitions on first request instead.

The data model

The table is partitioned by tenant:

CREATE TABLE orders (
    id         uuid not null,
    tenant_id  uuid not null,
    -- ... columns ...
    primary key (id, tenant_id)
) PARTITION BY LIST (tenant_id);

CREATE TABLE orders_default PARTITION OF orders DEFAULT;

tenant_id has to be part of the primary key. PostgreSQL requires the partition key to be part of any unique constraint. A tenant partition can then be added with a single statement:

CREATE TABLE orders_t_d4e5f6a7 PARTITION OF orders
    FOR VALUES IN ('d4e5f6a7-...-...');

The remaining question is when that statement should run.

Why startup provisioning does not scale

Let's start with the startup-first approach. On startup, the service lists the active tenants, checks whether partitions already exist for each tenant in the target cluster, and creates any missing ones before it takes traffic. It is easy to understand, and by the time the first request arrives, the schema is ready.

The problem is that every instance has to walk the tenant list on startup, check which tenants are already provisioned in its target cluster, and create the missing ones before it can serve traffic. The existence check is cheap, but it still has to run for every tenant. The expensive part is creating the missing partitions. If many tenants are still missing, that cost stacks up quickly.

That does not work well with autoscaling. In a Kubernetes-style environment, a pod is most useful when it becomes Ready quickly and can start taking traffic. One that spends minutes checking tenants and running DDL first delays scale-out, slows recovery, and stretches out deployments. What most systems need instead is fast and cheap startup.

Provisioning on first request

The other option is to wait and create tenant partitions on the first request.

A good place to do this is in the request pipeline, after the tenant is resolved and before the request reaches a handler:

public async Task InvokeAsync(HttpContext context, ITenantPartitionProvisioner provisioner)
{
    var tenant = context.GetTenant();

    // Fast path: has this tenant already been provisioned in this process?
    if (tenant is not null && !_provisioningCache.Contains(tenant.Id))
    {
        try
        {
            await provisioner.EnsureProvisionedAsync(tenant.Id, tenant.ConnectionString);
            _provisioningCache.Add(tenant.Id);
        }
        catch (Exception ex)
        {
            // Fail closed — see below.
            _logger.LogError(ex, "Partition provisioning failed for {TenantId}", tenant.Id);
            context.Response.StatusCode = StatusCodes.Status503ServiceUnavailable;
            return;
        }
    }

    await _next(context);
}

This keeps startup fast and lets autoscaling work well. Pods can become Ready quickly, deployments recover faster, and the work is spread over time as tenants actually use the system. An instance pays the provisioning cost only for the tenants it actually serves, and only on their first request. In our setup, that first-hit cost is about 150–250 ms.

Making this work safely depends on three more things. The rest of the article covers them one by one: caching the provisioning check, controlling concurrency, and failing closed on error.

Caching the provisioning check

The provisioning path should not query the database on every request. A catalog lookup on each request would replace a one-time startup cost with a permanent per-request cost. That is not a good trade-off.

For that reason, the request pipeline keeps the provisioning state in a process-local cache. On a cache hit, the middleware skips provisioning. On a miss, it calls EnsureProvisionedAsync, which still re-checks the database before creating anything.

The cache can be as simple as this:

// Singleton, one per process. Records the provisioning version applied per tenant.
private readonly ConcurrentDictionary<Guid, int> _provisioned = new();

public bool Contains(Guid tenantId) =>
    _provisioned.TryGetValue(tenantId, out var v) && v == ProvisioningVersion.Current;

public void Add(Guid tenantId) => _provisioned[tenantId] = ProvisioningVersion.Current;

The cache key includes not just the tenant id, but also a provisioning version. ProvisioningVersion.Current increases whenever the set of partitioned tables changes.

A tenant recorded under an older version is treated as not yet provisioned for the current one. Its next request runs provisioning again, and only the missing partitions are created. Without that version, the cache could still say a tenant is already provisioned even after the schema changes. That would prevent the new table's partition from being created for existing tenants.

A shared cache such as Redis is not necessary here. Provisioning is safe to run more than once. If one process has not recorded a tenant yet, it may do a few extra existence checks under load, but it will not create an unsafe duplicate. A process-local cache avoids external coordination.

Controlling concurrency

The next problem is the race you usually only notice under load: two first requests at the same time. When two requests for the same new tenant arrive a few milliseconds apart, both miss the cache, both call EnsureProvisionedAsync, and both try to create the partitions. One succeeds and the other may fail. If provisioning is not serialized and wrapped in one transaction, creating several tables can also leave the tenant half set up.

A PostgreSQL transaction-scoped advisory lock, taken per tenant, fixes this:

public async Task EnsureProvisionedAsync(Guid tenantId, string connectionString)
{
    await using var conn = new NpgsqlConnection(connectionString);
    await conn.OpenAsync();
    await using var tx = await conn.BeginTransactionAsync();

    // Serialize provisioning for this tenant. Other tenants are unaffected.
    var (ns, key) = PartitionLockKey(tenantId);
    await using (var lockCmd = new NpgsqlCommand(
        "SELECT pg_advisory_xact_lock(@ns, @key)", conn, tx))
    {
        lockCmd.Parameters.AddWithValue("ns", ns);
        lockCmd.Parameters.AddWithValue("key", key);
        await lockCmd.ExecuteNonQueryAsync();
    }

    // Re-check inside the lock: did the previous holder already do the work?
    if (await PartitionsExistAsync(conn, tx, tenantId))
    {
        await tx.CommitAsync(); // release the lock with a clean commit, not a rollback
        return;
    }

    await CreateAllPartitionsAsync(conn, tx, tenantId);

    await tx.CommitAsync(); // ends the transaction and releases the advisory lock
}

The helper turns tenantId into a stable advisory-lock key. It combines a fixed namespace for partition provisioning with a deterministic hash of the tenant id:

// Deterministic advisory-lock key for a tenant.
// Namespace classifier + a hash over the whole id
static (int ns, int key) PartitionLockKey(Guid tenantId)
{
    const int ns = 0x50_41_52_54; // "PART" as ASCII
    Span<byte> bytes = stackalloc byte[16];
    tenantId.TryWriteBytes(bytes);
    int key = unchecked((int)Crc32.HashToUInt32(bytes));
    return (ns, key);
}

The hash is not for security. It only needs to map the tenant id to a consistent lock key.

There are a few details that matter here:

• The advisory lock is per tenant. It prevents two processes from provisioning the same tenant at the same time.
• The lock is transaction-scoped. Because this uses pg_advisory_xact_lock, PostgreSQL releases it automatically if the request fails.
• The transaction should stay small. Creating a partition still takes a short-lived lock on the parent partitioned table, so this path should do only the missing work and commit immediately.
• The code checks again after taking the lock. The first request creates the partitions. The ones behind it wait, check again, and see that the work is already done.

This path runs only on a cache miss, which becomes rare once the cache is warm.

Failing closed

If provisioning fails, the request should stop. The tenant is not ready yet, so the application should not continue and risk writing data to the wrong place. In our case, the middleware returns 503 Service Unavailable. That is a clear signal to retry later, and it is much safer than pretending the request succeeded.

The DEFAULT partition should be treated the same way. Data in the default partition means a write missed the normal provisioning path and should be investigated. Without a DEFAULT partition, that same write would fail loudly instead. With a DEFAULT partition, it is captured silently, so monitoring matters.

The cost profile

Lazy provisioning moves the cost instead of removing it. The numbers below are from our setup. The exact numbers may change, but the shape usually stays the same:

What you pay for is simple: one request per tenant per process is slower. In our setup, that extra latency is about 150–250 ms. What you get back is fast pod readiness, faster recovery after deployments, and provisioning work spread over time instead of being paid all at once at startup. For a platform with many tenants created on the fly, that trade is usually worth it.

When startup provisioning is the right choice

For a small, fixed set of tenants — a few dozen, added by hand — provisioning at startup is the better choice. Create the partitions in startup code or in a migration and move on; lazy provisioning is not worth the extra complexity there.

The deciding factor is how many tenants there are and how they arrive. Lazy provisioning earns its place when tenants are many and created on the fly. That is exactly where startup provisioning starts to break down.

Frequent schema changes make that difference even sharper. With startup provisioning, every new partitioned table means re-checking and re-provisioning every existing tenant on the next boot. With the lazy path, you bump the provisioning version and let each tenant catch up on its next request.

The wider point is not only about PostgreSQL. "Do all the work upfront" and "do the work when it is needed" are two real choices. The cleaner-looking option is not always the one that runs best at scale. The job is to work out which property matters most for your system. At a few thousand tenants, that property is usually fast startup.

The code examples from this article are available in the GitHub repo.

Turkish version: Türkçe okuyun