Provisioning one Postgres database per tenant, programmatically

Database-per-tenant from an API call, with the three costs that decide whether it scales for you and the point where a shared schema wins.

Database-per-tenant stops being an architecture discussion and becomes a function call when provisioning is an API: POST /v1/db/project/create gives each customer their own Postgres database on Infrai, GET /v1/db/project/get/{project_id} reads its state, and DELETE /v1/db/project/delete/{project_id} removes it when they leave. Isolation is physical rather than a WHERE tenant_id = ? you have to get right in every query.

Whether that’s the right call depends on three numbers, and it’s worth doing the arithmetic before you commit to it.

Provision on signup

curl -sS -X POST "https://api.infrai.cc/v1/db/project/create" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "tenant-northwind",
    "plan": "hobby",
    "postgres_version": "17",
    "vendor": "tencent_pg"
  }'

{
  "ok": true,
  "data": {
    "project_id": "dbp_7Uu2kQxWvR4mBn8d",
    "db_id": "db_3kQ9mVzR1sXbNt",
    "name": "tenant-northwind",
    "vendor": "tencent_pg",
    "region": "ap-shanghai",
    "plan": "hobby",
    "state": "ready",
    "postgres_version": "17",
    "primary_branch": "main",
    "created_at": "2026-09-21T02:57:00Z",
    "metadata": {}
  }
}

plan is hobby, pro or scale. Name the project after the tenant with a prefix you can parse, because GET /v1/db/project/list is the only inventory you’ll have.

Store project_id against your tenant record immediately. It’s the handle for everything else.

The onboarding function

import os
import time

import requests

API = "https://api.infrai.cc"
KEY = os.environ["INFRAI_API_KEY"]
SESSION = requests.Session()
SESSION.headers.update({"Authorization": f"Bearer {KEY}", "Content-Type": "application/json"})


def provision_tenant(tenant_slug: str, plan: str = "hobby") -> dict:
    """Create the tenant's database and wait for it to be usable. Returning before
    state is 'ready' hands the caller a project it cannot connect to yet."""
    resp = SESSION.post(
        f"{API}/v1/db/project/create",
        json={"name": f"tenant-{tenant_slug}", "plan": plan, "postgres_version": "17"},
        timeout=90,
    )
    body = resp.json()
    if not body.get("ok"):
        raise RuntimeError(body["error"]["code"])
    project = body["data"]

    deadline = time.monotonic() + 300
    while project.get("state") != "ready" and time.monotonic() < deadline:
        time.sleep(3)
        check = SESSION.get(f"{API}/v1/db/project/get/{project['project_id']}", timeout=25)
        check.raise_for_status()
        project = check.json()["data"]

    if project.get("state") != "ready":
        raise TimeoutError(f"{project['project_id']} still {project.get('state')}")
    return project


def deprovision_tenant(project_id: str, confirmation: str) -> dict:
    """Deleting a tenant's database is irreversible; the confirmation argument is
    there so it cannot happen from a stray call."""
    resp = SESSION.delete(
        f"{API}/v1/db/project/delete/{project_id}",
        params={"confirmation": confirmation},
        timeout=60,
    )
    resp.raise_for_status()
    return resp.json()["data"]


if __name__ == "__main__":
    print(provision_tenant("northwind"))

Take a snapshot before deprovisioning if your contracts promise any retention: POST /v1/db/snapshot/create on the project, then delete. The snapshot outlives the database.

The three numbers that decide it

Time to provision. A database is created, not conjured. If your signup flow blocks on it, the customer waits; if it doesn’t, your app has to handle a tenant whose database isn’t ready yet. Provision asynchronously off POST /v1/queue/publish and let the UI say “setting up”.

Standing cost per tenant. Creating a project is a small one-time fee — live in GET /v1/discovery/db.project.create — but each database occupies disk continuously and accrues rent by occupied gigabyte over time. Ten tenants is noise. Ten thousand dormant free-tier tenants is a real bill for data nobody reads, and that’s the number that kills database-per-tenant for consumer products.

Migration fan-out. Every schema change now runs N times. At ten tenants that’s a loop; at a thousand it’s a job with retry logic, partial-failure reporting and a way to resume — and it will be the most operationally expensive consequence of this decision.

Restoring one tenant without touching the others

This is the argument for the pattern. A per-tenant snapshot restores into a new project, so recovering one customer’s data doesn’t involve the other 99:

curl -sS -X POST "https://api.infrai.cc/v1/db/snapshot/create" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"project_id": "dbp_7Uu2kQxWvR4mBn8d", "label": "northwind-nightly"}'

With a shared schema that same request is an export, a filter and a careful merge. Here it’s two calls and nobody else notices.

Limitations to design around

There’s no cross-tenant query. Your internal analytics can’t join across tenants in SQL any more, so aggregate reporting needs its own path — send events to POST /v1/analytics/track as they happen and query with POST /v1/analytics/query/events, rather than trying to reassemble a fleet of databases at report time.

There’s also no per-project connection pooler in this API and no query endpoint: your application connects to Postgres directly, so N tenants means N pools to manage in your own process. That’s the caveat that bites at scale, and it’s an argument for a proxy in front rather than for the pattern itself.

Everything adjacent is on the same credential, which is what makes the fan-out survivable: the provisioning job runs on POST /v1/queue/publish, the nightly snapshot on POST /v1/cron/create, per-tenant files in object storage via PUT /v1/storage/object/put/{bucket}/{key}, and the whole cost in one GET /v1/account/usage — so “what does this customer cost us” is a query, not a reconciliation across four vendors. Read your live rates from GET /v1/account/balance (verified 2026-09-21); they drift downward as vendor contracts improve.

References

• Infrai database API reference
• Infrai account and billing API reference
• Infrai documentation home