WAL SQLiteEdge SQLite Archive
BP-08 // DB-PER-TENANT ARCHITECTURE

Multi-Tenant Database-Per-User SQLite: Unlimited Horizontal Write Scaling on One VPS

SQLiteEdge multi-tenant blueprint: assigning each customer workspace an isolated .sqlite3 WAL file with an LRU handle pool and lazy PRAGMA user_version migrations.

MODELED DESIGN TARGET
100,000+ Aggregate
READ PATH / RPO TARGET
Zero Cross-Tenant Leak
REFERENCE NVME PROFILE
$48–$96 Multi-Core NVMe

SQLite's "single writer" lock applies per file—not per server! When your B2B SaaS assigns each organization (`tenant_8492.sqlite3`) its own file on a 12-core NVMe VPS, 12 different tenants write simultaneously across all 12 CPU cores with zero lock contention, cryptographic-grade tenant isolation, zero row-level-security bugs, and instant 1-file customer data exports.

In our SQLiteEdge BP-08 reference guide, every configuration rule below follows official SQLite 3.46 documentation and public vendor specifications retrieved 2026-11-12.

SQLiteEdge Architecture Rules for Database-Per-Tenant Multi-File SQLite Sharding

  1. Manage open SQLite handles through an in-memory LRU Connection Pool (e.g., max 512 open tenant databases) that cleanly runs `PRAGMA wal_checkpoint(TRUNCATE)` and closes idle handles after 5 minutes.
  2. Cap per-tenant `cache_size = -16384` (16 MiB) and rely on the Linux kernel Page Cache to dynamically prioritize hot active workspaces across 32 GB – 64 GB RAM.
  3. Run schema migrations lazily on tenant database open (`PRAGMA user_version` check)—active tenants migrate in <5ms on first request without multi-hour global table locks!
  4. Back up cold/idle tenant files via incremental sha256 + ZSTD snapshots to a $6 InterServer 1TB vault, streaming only dirty `.sqlite3` files modified in the last 5 minutes.

SQLiteEdge Reference Snippet: LRU Tenant Handle Manager + Lazy user_version Migrator

LRU Tenant Handle Manager + Lazy user_version Migrator 
const MAX_OPEN_TENANTS = 512;
const pool = new Map(); // tenantId -> { db, lastUsed }

export function getTenantDb(tenantId) {
  const safeId = tenantId.replace(/[^a-zA-Z0-9_-]/g, '');
  if (pool.has(safeId)) {
    const entry = pool.get(safeId);
    entry.lastUsed = Date.now();
    pool.delete(safeId);
    pool.set(safeId, entry); // Refresh LRU tail
    return entry.db;
  }

  if (pool.size >= MAX_OPEN_TENANTS) {
    const [oldestKey, oldestVal] = pool.entries().next().value;
    oldestVal.db.pragma('wal_checkpoint(TRUNCATE)');
    oldestVal.db.close();
    pool.delete(oldestKey);
  }

  const db = new Database(`/var/lib/tenants/${safeId}.sqlite3`);
  db.pragma('journal_mode = WAL');
  db.pragma('synchronous = NORMAL');
  db.pragma('cache_size = -16384'); // 16 MiB per active tenant
  runLazyMigrations(db);            // Instant check via PRAGMA user_version
  pool.set(safeId, { db, lastUsed: Date.now() });
  return db;
}

SQLiteEdge Modeled Profile & Tariff Table (BP-08)

Public vendor prices retrieved 2026-11-12 (promotional and renewal rates may change at checkout); throughput figures reflect theoretical single-queue sizing models rather than unarchived lab claims.

Architecture PatternAggregate Server Write TPSTenant Data Export / WipeNoisy-Neighbor Blast Radius
Shared Multi-Tenant Postgres (RLS)~9,000 TPS (Shared WAL)Slow DELETE WHERE tenant_id=...1 runaway query slows all tenants
Single Giant Monolithic SQLite File~22,000 TPS (1 Core Lock)Slow filtered table dumpHeavy writer queues other writers
DB-Per-Tenant SQLite on 8–12 vCPU NVMe95,000+ TPS (All Cores)Instant cp / rm tenant_id.db!Zero—isolated B-tree & lock per org

SQLiteEdge Spec-Matched NVMe & S3 Targets for BP-08

Open this BP-08 workload preset inside the SQLiteEdge PRAGMA Studio →

SQLiteEdge Engineering FAQ for BP-08

  • How many `.sqlite3` files can a single Linux ext4 NVMe filesystem hold comfortably?

    ext4 easily handles 100,000 to 1,000,000+ files when sharded into 2-char hex subdirectories (`/var/lib/tenants/a4/tenant_a49f.sqlite3`) so no single directory exceeds a few thousand entries.

  • Why is lazy migration via PRAGMA user_version a superpower for B2B SaaS?

    In shared Postgres, an `ALTER TABLE` on a 400M-row table risks locking every paying customer at once. With DB-per-tenant SQLite, each 50 MB workspace migrates atomically in ~3ms the moment that team logs in.