Show HN: Embed an SQLite database in your PostgreSQL table

Original Article ↗ Hacker News Discussion ↗

Concept

  • Extension stores an entire SQLite database as a PostgreSQL column type.
  • Intended pitch: simplifies multitenancy by giving each tenant (or row) its own embedded SQLite DB.
  • Current implementation writes SQLite DBs to /tmp, then back into the column; acknowledged as a hack and likely to change.

Proposed Use Cases

  • Per-tenant schemas for SaaS apps where users define custom tables and schemas.
  • Shipping prepared SQLite DBs to client devices (e.g., configs, parameters) and syncing or analyzing them centrally in Postgres.
  • Storing many small, isolated datasets (e.g., per “session” or per device) while still leveraging Postgres features like transactions and row-level security.
  • Using it as a format bridge for serving SQLite data over HTTP or syncing with tools like LiteFS (in theory).

Multitenancy & Schema Design

  • Some argue it “solves” multitenancy by putting one DB per tenant row and avoiding cross-tenant joins.
  • Others note existing approaches (per-tenant schemas, row-level security, per-database/per-server tenants) are battle-tested and often simpler.
  • Concern that thousands of embedded SQLite DBs can become large and hard to manage; JSONB-based schemas or standard tables may be more practical.

Implementation & Performance Concerns

  • Current full-DB rewrite on every update is potentially very slow, especially with many rows.
  • Suggestions to:
    • Use PostgreSQL’s TOAST and Expanded Datum API to keep a live in-memory SQLite connection per value.
    • Store SQLite pages as separate rows so only changed pages are updated.
  • /tmp file usage is widely seen as fragile; in-memory or expanded types preferred.

Alternatives & Comparisons

  • Many suggest JSONB (plus schema metadata tables) for flexible per-tenant schemas.
  • Others prefer SQLite foreign data wrappers over embedding whole DBs.
  • Some point out SQLite’s loose type discipline (unless in STRICT mode) undercuts the idea of strong enforced schemas.

Broader DB Debates & Reactions

  • Discussion drifts into when to prefer PostgreSQL vs SQLite (networked access, replication, concurrency, scaling, extensions).
  • Mixed reception: some find the idea fun and creatively useful; many see it as a “1NF crime,” a curiosity, or a future maintenance nightmare.