Per-Tenant SQLite: One File, One microVM, and No Noisy Neighbours

Database-per-tenant used to mean a Postgres instance per customer and an invoice to match, which is why most teams read about the pattern and went back to putting a tenant_id column on every table. Then SQLite got a serious concurrency story, a generation of hosted forks and replicated variants showed up, and the idea returned wearing different clothes: one file per tenant, on a disk, opened by your application process. It is a genuinely good idea, and the kind that hides its bill in year two.

I'm Ajay; I build PandaStack, a Firecracker microVM platform. This is not an argument against per-tenant SQLite — the data model is right, and more right than the shared schema most products are stuck with. It is an argument about where the file should live. A thousand tenant databases inside one process is a system with one writer lock per tenant, one shared page cache, one shared address space and one shared crash. Give each tenant a machine instead and you keep what made the file attractive while deleting the parts that page you at 3 a.m.

Why one file per tenant is genuinely attractive again

Start with what the pattern gets right, because it is a lot, and most of it is unavailable at any price in a shared-schema design. These are not micro-optimisations; they change what your operations team is allowed to promise.

• Blast radius is a file. There is no WHERE clause between tenant A and tenant B — there is a path. The canonical multi-tenant data leak, a query that forgot its tenant_id, is not a bug you can write, because the other customer's rows are not in the database you opened.
• Backup, restore and export are file operations. `VACUUM INTO` hands you a consistent copy of one customer's entire dataset in one statement. Restoring one tenant to last Tuesday does not begin with the phrase "point-in-time recovery of a shared cluster."
• GDPR erasure is `rm`. Deleting a tenant from a shared schema is a cascade across forty tables plus the three you forgot, verified by hope and a SELECT count(*). Here it is one unlink, and you can demonstrate it to an auditor.
• No cross-tenant connection-pool contention, because there is no pool. One tenant's long transaction cannot occupy a connection another tenant is queued behind, since "connection" means a file handle.
• The working set fits in page cache. Most tenants in most B2B products have a database measured in megabytes, so reads become memory reads with no network hop and no planner round trip — which is where the "SQLite is absurdly fast" benchmarks come from. They are not lying to you.
• Per-tenant migrations and version pinning become possible at all: migrate one customer, watch them for a day, roll them back, no maintenance window for anybody else.

If your product is B2B, has a natural tenant boundary, and does not need cross-tenant joins on the hot path, this shape fits the domain better than a shared table with a discriminator column ever did. The domain has hard walls in it; a tenant_id column is a convention pretending to be one.

The failure modes of putting all of them in one process

Here is where the honest version of the post starts. Every property above belongs to the file. Every problem below belongs to the process — and almost every write-up of this pattern quietly assumes one application process with a directory of tenant databases underneath it. The two get conflated because the file is the part you designed and the process is the part you inherited.

One writer, and the voicemail it leaves

SQLite in WAL mode gives you concurrent readers and exactly one writer. That is not a limitation to engineer around, it is the design, and much of why the thing is as reliable as it is. But a write transaction against a tenant's database is a lock on that database, and any other writer for that tenant gets SQLITE_BUSY — a return code your driver may or may not surface as something a human can act on before it becomes a support ticket.

Within one tenant this is usually fine, because one tenant is usually not that busy. The problem is head-of-line blocking inside a tenant. A long-running write — a bulk import, a migration, a report that opens a transaction and then does something regrettable like an HTTP call — holds the lock for its whole duration, and every other request for that customer stacks up behind it. Setting `busy_timeout` converts an immediate error into a slow request, which is frequently what you want and occasionally just relabels an outage as latency. SQLITE_BUSY is your architecture leaving a voicemail: not the incident, the notification that one has been scheduled.

The 40GB tenant and everybody else's page cache

The "working set fits in memory" property is true per tenant and false in aggregate. One process holding a thousand tenant databases relies on the host page cache to keep the hot pages of all thousand resident. Then a single customer imports five years of history, their file passes forty gigabytes, and a nightly scan walks the whole thing through the cache. Nothing crashed. No limit was exceeded. No alert fired. Every other tenant's queries simply started hitting disk, and your p99 moved with no deploy to pin it on.

This is the noisy-neighbour problem in an unusually pure form, because there is no knob to turn. A cgroup memory limit applies to your process, not to fairness inside it, and `cache_size` is per connection and does not govern the OS page cache doing the actual work. You cannot give tenant 41 a memory budget, because the contended resource is a kernel cache that has never heard of tenants.

ATTACH, extensions, and a shared address space

Then there are the sharp edges that are not bugs, and are not going to be fixed, because they are features that predate your use of them:

• `ATTACH DATABASE` is one statement away from a cross-tenant join. Any code path that can build a SQL string can, in principle, attach another tenant's file — the isolation you gained by opening one path is undone by a second one. It does not take an attacker. It takes a helpful debugging endpoint someone shipped on a Friday.
• `load_extension` is arbitrary native code inside your server process. Extensions are shared libraries with full access to your address space: your credentials, your heap, every other tenant's cached pages. If tenant-influenced input can reach extension loading, you have a remote-code-execution question wearing a SQL costume.
• A crash is a fleet event. A segfault in a C library, an OOM kill, a bad mmap — the process dies and every tenant it was serving dies with it. You built a thousand independent databases and handed them one shared fate.
• Disk is shared too. One tenant's runaway `INSERT ... SELECT` fills the volume, and the next write from every other tenant returns SQLITE_FULL. Per-directory quotas are possible; approximately nobody sets them.

When the tenant gets to write the query

Everything above assumes the SQL is yours. Increasingly it is not. The analytics builder that compiles a customer's filters into SQL, the "ask a question about your data" feature where a language model writes the query, the customer-defined metric with a user-supplied expression in it — all of these move the author of the SQL outside your organisation. The moment tenant input reaches the query planner the threat model changes, and SQLite's own documentation is unambiguous: the engine assumes the SQL comes from you.

A query planner is not a security boundary, and a C library is not a sandbox. Neither has claimed to be. Here is a fair sample of what you accept when a stranger's SQL touches your process — and note that none of it involves a vulnerability:

-- 1. The denial of service you shipped as a feature. No bug, no exploit --
--    a recursive CTE with no termination condition, which is legal SQL.
--    It burns a core and grows memory until something outside the query
--    intervenes. Nothing inside the query is going to stop it.
WITH RECURSIVE bomb(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM bomb
)
SELECT count(*) FROM bomb;

-- 2. Disk exhaustion, expressed politely. randomblob is incompressible, so
--    nothing downstream saves you, and the volume it fills is the one every
--    other tenant on this host is also writing to.
CREATE TABLE spill AS
WITH RECURSIVE g(i) AS (
  SELECT 1 UNION ALL SELECT i + 1 FROM g LIMIT 1000000
)
SELECT i, randomblob(16384) FROM g;

-- 3. Cross-tenant read, if the process can see the other file at all. The
--    "isolation" was that you opened one path. This opens a second one.
ATTACH DATABASE '/var/lib/tenants/other-customer/main.db' AS victim;
SELECT * FROM victim.invoices;

-- 4. Native code in your server, if extension loading was ever linked in.
SELECT load_extension('/tmp/helpfully-uploaded.so');

Mitigations exist and you should use all of them: a `sqlite3_progress_handler` that interrupts a query which has burned too many VM steps, the `SQLITE_LIMIT_*` knobs for expression depth and result size, an authorizer callback that refuses ATTACH and PRAGMA, a read-only connection, and never linking extension loading at all. These are real controls and they work. They are also a checklist you must get entirely right, forever, in a C library sharing a process with your credentials — and the cost of missing one item is not a slow query.

The microVM shape: one guest per tenant, the file on a volume

The alternative is boring, and boring is the compliment. Give the tenant a machine. The SQLite file lives on a durable volume attached to a Firecracker microVM; queries run inside that guest; the guest has its own kernel under KVM, its own hard CPU and memory ceiling, its own filesystem and its own network namespace. Your API talks to it over the network instead of over a file handle, which is the one thing you give up.

Nothing about the data model changes — still one file per tenant, still `VACUUM INTO` for a backup, still a delete for erasure. What changes is the price of a bad day:

• Shared process, many files — Isolation: data only; ATTACH and a shared address space can cross it. Failure domain: every tenant in the process, so one OOM or segfault takes them all. Resources: one page cache, one disk, no per-tenant ceiling. Untrusted SQL: unsafe without a carefully maintained authorizer and limit set. Cost: the lowest, right up until the first fleet-wide incident.
• Process per tenant, one host — Isolation: separate address spaces, so a crash is one tenant's crash. Failure domain: one tenant per fault, but a shared kernel and page cache remain. Resources: cgroups give real CPU and memory ceilings; page cache is still communal. Untrusted SQL: better, though the host kernel is what a hostile native extension attacks. Cost: low, and under-used.
• microVM per tenant — Isolation: a separate guest kernel under KVM; ATTACH cannot reach a file that is not on that guest's disk. Failure domain: precisely one tenant, and a guest panic is invisible to the fleet. Resources: vCPU and RAM ceilings set by the hypervisor, and a page cache belonging to one customer. Untrusted SQL: safe enough to allow extensions and user-defined functions deliberately, as a feature. Cost: viable only if idle tenants cost close to nothing.
• Managed Postgres per tenant — Isolation: strong, with an operational model your team already knows. Failure domain: one tenant. Resources: dedicated, but the floor per tenant is much higher. Untrusted SQL: a real permission system, at the cost of a server per customer. Cost: the highest; on PandaStack a managed Postgres create takes 30 to 90 seconds, because it blocks until the server is genuinely accepting connections rather than merely booted.

The cell that matters is the last one on the third row. VM-per-tenant has always had the obviously-correct isolation story and the obviously-wrong economics, and everyone who evaluated it stopped at the second half. The only thing that changed is how much an idle tenant costs.

The economics: idle tenants have to cost approximately nothing

If a tenant VM holds four gigabytes of RAM whether or not anybody logged in this month, the pattern dies on the spreadsheet before it reaches a design review. B2B usage is spiky and mostly zero: a few hundred tenants active during business hours in one time zone, and a long tail that opens the product on Thursdays. Paying standing capacity for all of them to isolate the fifty that are working is exactly the mistake this design exists to avoid.

So the lifecycle is the architecture, not an optimisation bolted on later. An idle tenant gets snapshotted and hibernated: guest memory captured, the VM stopped, the durable volume left where it was. The next connection wakes it. That is affordable because waking is a snapshot restore rather than a boot — on PandaStack a create is roughly 179ms p50 and 203ms p99, with the restore step itself near 49ms. The first cold boot of a template, before any snapshot exists, is about 3 seconds, paid once.

That distinction is the whole argument. A customer waiting three seconds for their dashboard because their machine was asleep is a product problem. A customer waiting a couple of hundred milliseconds is a page transition.

from pandastack import Sandbox

# One microVM per tenant. The SQLite file lives on a durable volume mounted
# at /data, so it survives hibernate, wake, and the guest being replaced
# entirely. The rootfs is disposable; the volume is the customer's data.
TEMPLATE = "base"


def open_tenant(tenant_id: str) -> Sandbox:
    """Wake this tenant's machine, or build one on their first request."""
    existing = tenant_vm_id(tenant_id)  # your own tenant -> sandbox mapping
    if existing:
        sbx = Sandbox.get(existing)
        if sbx.status == "hibernated":
            sbx.wake()  # snapshot restore, not a boot
        return sbx

    # persistent=True keeps this out of the idle reaper that recycles
    # ordinary sandboxes. A customer's data VM must not be garbage
    # collected because the customer took a holiday.
    sbx = Sandbox.create(
        template=TEMPLATE,
        persistent=True,
        metadata={"tenant": tenant_id, "role": "sqlite"},
        volumes=[{"name": f"tenant-{tenant_id}"}],
    )
    sbx.exec(
        "sqlite3 /data/main.db "
        "'PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL;'"
    )
    remember_tenant_vm(tenant_id, sbx.id)
    return sbx


def nightly_backup(tenant_id: str) -> bytes:
    """A consistent copy of one customer, and of nobody else. That is the
    whole pitch: no cluster-wide snapshot, no filtering, no anxiety."""
    sbx = open_tenant(tenant_id)
    sbx.exec("rm -f /tmp/backup.db")
    sbx.exec(
        "sqlite3 /data/main.db \"VACUUM INTO '/tmp/backup.db'\"",
        timeout_seconds=300,
    )
    return sbx.filesystem.read("/tmp/backup.db")


def forget_tenant(tenant_id: str) -> None:
    """Erasure in the version you can put in a DPA: destroy the machine,
    then delete its volume. Two operations you can point at in an audit,
    instead of a forty-table cascade you hope was complete."""
    Sandbox.get(tenant_vm_id(tenant_id)).kill()
    delete_volume(f"tenant-{tenant_id}")

Cloning a tenant, because support tickets are the real workload

The property that surprised me most in practice is not isolation, it is the fork. A ticket reading "the revenue report is wrong for us" is normally answered by reading code and guessing, because the one thing you cannot safely do is run against the customer's data. With a copy-on-write fork you get a live, writable clone of that tenant's machine — memory and disk — database file already open and warm, and you can break it as thoroughly as the investigation requires.

The same primitive covers the migration dry run, which is what database-per-tenant sceptics correctly warn you about. Instead of testing a schema change against a synthetic fixture and meeting the seventeen tenants with weird data in production, you fork the awkward ones and run it for real. On PandaStack a same-host fork lands in 400 to 750ms; cross-host is 1.2 to 3.5 seconds, because the memory image has to travel.

def dry_run_migration(tenant_id: str, migration_sql: str) -> dict:
    """Run a schema change against a real tenant, with the real tenant safe."""
    live = open_tenant(tenant_id)

    # Copy-on-write: memory and disk are shared with the parent until
    # written, so this is cheap even for a large database. The clone is a
    # genuinely different machine on its own network namespace -- it cannot
    # reach back into the tenant it came from.
    clone = live.fork(metadata={"purpose": "migration-dry-run"})
    try:
        clone.filesystem.write("/tmp/migrate.sql", migration_sql)
        r = clone.exec(
            "sqlite3 /data/main.db < /tmp/migrate.sql && "
            "sqlite3 /data/main.db 'PRAGMA integrity_check;'",
            timeout_seconds=600,
        )
        return {"ok": r.exit_code == 0, "output": r.stdout}
    finally:
        clone.kill()


def run_tenant_authored_sql(tenant_id: str, user_sql: str) -> dict:
    """The analytics builder, or the agent that writes its own SQL. The
    query is hostile by assumption -- not because the customer is, but
    because you cannot tell the difference from here."""
    snapshot = nightly_backup(tenant_id)

    # A machine that exists only for this query. ttl_seconds is the backstop
    # the platform enforces: if this process panics mid-query the VM still
    # dies. A WITH RECURSIVE bomb now has nowhere to go but its own RAM,
    # and its own RAM has a ceiling the hypervisor set.
    sbx = Sandbox.create(
        template=TEMPLATE,
        ttl_seconds=180,
        metadata={"tenant": tenant_id, "role": "adhoc-query"},
    )
    try:
        sbx.filesystem.write("/tmp/read.db", snapshot)
        sbx.filesystem.write("/tmp/q.sql", user_sql)
        r = sbx.exec(
            "timeout 60 sqlite3 -readonly -json /tmp/read.db < /tmp/q.sql",
            timeout_seconds=90,
        )
        return {"ok": r.exit_code == 0, "rows": r.stdout[:1_000_000]}
    finally:
        sbx.kill()

Note what the second function does not need: an authorizer callback, a progress handler, a curated list of forbidden functions, or a code review every time someone adds a SQL feature. The query runs against a copy, on a machine with a hard memory ceiling and a TTL, and the machine is destroyed afterwards. ATTACH cannot find another tenant's file because it is not on this disk, and `load_extension` can load whatever it likes into a process that is about to stop existing.

When not to do this

The pattern has a real cost and a real set of workloads it is wrong for. If any of these describe you, the honest answer is a shared database and better query discipline:

• Cross-tenant analytics as core product value. If you benchmark customers against each other, rank a marketplace, or run fleet-wide reporting, one file per tenant means N queries and a fan-in you now operate. A shared columnar store is the correct tool — possibly alongside per-tenant files for the operational data.
• Tens of thousands of tiny free-tier tenants. A shared schema with a rigorously enforced tenant_id is simply cheaper, and the isolation it lacks may be isolation you do not need. Climb the ladder per customer, not for the whole platform at once.
• Migrations at N. A thousand databases is a thousand migrations, and "we shipped the schema change" becomes a job with a progress bar, a retry policy, and a long tail of tenants that failed for their own individual reasons. Plenty of teams do this — but budget for the runner, and for the week you will spend on the seventeen tenants stuck halfway.
• Transactions that must span tenants. If a business operation has to be atomic across two customers, you chose the wrong boundary, and no amount of two-phase commit across SQLite files will fix it.
• No appetite for a control plane. VM-per-tenant means operating a fleet: placement, hibernation policy, wake-on-connect, backups, capacity. Buying that as a platform is one answer; building it is a project with headcount attached.

And the fair caveat about the neighbours: Turso and libSQL, Litestream, Cloudflare D1 and the hosted Postgres branching services all attack overlapping slices of this problem with genuinely different trade-offs around replication, edge placement and consistency. I am not going to quote their numbers at you — check their docs, and check them recently, because that part of the ecosystem moves faster than any blog post. The argument here is about where the boundary sits, and it holds whichever engine ends up behind it.

"Each tenant has their own database" is a claim about your data model. Whether each tenant gets their own bad day is a claim about your infrastructure — and only one of those two ever appears in the postmortem.