Introducing Postgres Lite: A Pure JS Embedded Engine for Node.js, Bun, and the Browser

Source: Dev.to

PostgreSQL is arguably the most loved database in the world. But when it comes to local‑first development, edge computing, or browser‑based apps, we usually reach for SQLite.

Why? Because running a full Postgres instance in the browser or as a zero‑dependency embedded process in Node/Bun has traditionally been hard—requiring heavy WASM binaries or network proxies.

What is Postgres Lite?

Postgres Lite is a custom‑built SQL engine that implements the PostgreSQL dialect using pure TypeScript/JavaScript. It’s designed to be a robust SQLite alternative that speaks “Postgres,” allowing you to use the same schemas, queries, and logic from your backend directly in the frontend or local environments.

• Zero Emulation: No WASM overhead.
• Zero Dependency: Pure JS/TS logic only.
• Universal: Runs in Bun, Node.js, and modern browsers (via IndexedDB).

The Engineering Behind the Performance

Building a database engine from scratch requires more than just parsing SQL. Postgres Lite implements several advanced techniques to handle 1 M+ records with ease:

1. Slotted Page Layout – Data is organized into fixed 4 KB pages using a slotted‑page architecture, preventing fragmentation and enabling efficient management of variable‑length records (e.g., JSONB, TEXT).

2. B‑Tree Indexing & O(log n) Lookups – Primary keys and unique constraints are backed by a persistent B‑Tree implementation, keeping point lookups lightning‑fast even as the dataset grows.

3. Write‑Ahead Logging (WAL) & ACID Compliance – Every mutation is logged to a persistent .wal file before being applied to the main storage. On crash, the engine automatically replays the WAL on the next boot.

4. Volcano Execution Model – An iterator‑based processing model pulls rows through the execution plan one by one, so a SELECT * on a million‑row table uses a constant, minimal memory footprint.

5. LRU Buffer Pool – A sophisticated Least‑Recently‑Used cache keeps frequently accessed pages hot and minimizes physical I/O to disk or IndexedDB.

Quick Start

Installation

npm install @pglite/core
# or
bun add @pglite/core

Usage in Node.js / Bun

import { PGLite } from "@pglite/core";
import { NodeFSAdapter } from "@pglite/core/node-fs";

const db = new PGLite("app.db", { adapter: new NodeFSAdapter() });

await db.exec(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    metadata JSONB
  )
`);

await db.exec(
  "INSERT INTO users (name, metadata) VALUES ($1, $2)",
  ["Alice", { role: "admin" }]
);

const results = await db.query(
  "SELECT * FROM users WHERE name = $1",
  ["Alice"]
);
console.table(results);

Usage in the Browser

import { PGLite } from "@pglite/core";
import { BrowserFSAdapter } from "@pglite/core/browser";

const db = new PGLite("browser_storage", { adapter: new BrowserFSAdapter() });
// Everything else is the same!

Supported Features

Despite its “Lite” name, the engine supports a vast subset of Postgres syntax:

• Complex Joins: INNER, LEFT, LATERAL, and CROSS JOIN.
• Advanced Querying: CTEs (WITH), UNION, INTERSECT, and subqueries.
• Window Functions: ROW_NUMBER(), RANK() via OVER (PARTITION BY …).
• JSON Power: Full support for JSONB operators (->, ->>, @>, ?).
• Upserts: ON CONFLICT (col) DO UPDATE SET …

Performance Benchmark

Internal tests show:

• Point Lookups (PK): ~0.2 ms – 0.5 ms.
• Sequential Scan: 100 k rows in < 40 ms.
• Memory Usage: Constant under heavy query load thanks to the Volcano model.

If you are a database enthusiast or a JS developer looking for a better way to handle local data, check out the project on GitHub:

https://github.com/pglite/pglite