I Built a Database That Never Forgets — Here's Why

Last year, a financial services team I was working with had a nightmare scenario: a regulatory audit required them to prove exactly what their system showed on a specific Tuesday six months ago. Not what the data currently says. What it said then.

Their production Postgres had the current state. Their audit table had some breadcrumbs. Their application logs were partially rotated. Reconstructing the answer took two engineers three weeks of forensic archaeology through backups, WAL archives, and prayer.

This is the problem that drove me to build TensorDB.

---

The Problem With UPDATE

Here's what most databases do when you update a row:

UPDATE accounts SET balance = 5000 WHERE id = 1;

The old value is gone. Destroyed. Overwritten. If you need history, you build it yourself — trigger-based audit tables, event sourcing patterns, CDC pipelines feeding into a data lake. You end up with a Rube Goldberg machine of infrastructure just to answer "what was this value last week?"

Bitemporal databases solve this at the storage layer. Every write is an immutable fact. Nothing is ever overwritten or deleted. The database tracks two independent timelines for every record:

Timeline	What it tracks	Example question
System time	When the database recorded this fact	"What did our system show last Tuesday?"
Business time	When this fact was true in the real world	"What was the contract price on Jan 1?"

The distinction matters more than you'd think. A bank discovers today that a transaction from January had the wrong amount. With a bitemporal model, you correct the business-time record while preserving the system-time history of what you previously believed. Both truths coexist. Auditors can see both.

---

See It in 30 Seconds

You can have TensorDB running in under a minute:

pip install tensordb

from tensordb import PyDatabase

db = PyDatabase.open("/tmp/demo")

# Create a table and insert data
db.sql("CREATE TABLE accounts (id INT, owner TEXT, balance REAL)")
db.sql("INSERT INTO accounts VALUES (1, 'Alice', 10000)")

# Update the balance
db.sql("UPDATE accounts SET balance = 7500 WHERE id = 1")

# Time-travel: what was Alice's balance BEFORE the update?
rows = db.sql("SELECT * FROM accounts FOR SYSTEM_TIME ALL WHERE id = 1")
print(rows)
# → Both versions: the 10000 AND the 7500, with timestamps

That's it. No configuration. No schema migration for audit columns. No background workers. The history is automatic.

Or if you prefer Rust:

cargo add tensordb

let db = tensordb::Database::open("./mydb")?;

db.sql("CREATE TABLE events (id INT PRIMARY KEY, type TEXT, amount REAL)")?;

db.sql("INSERT INTO events VALUES
    (1, 'deposit', 1000),
    (2, 'withdrawal', 250),
    (3, 'deposit', 500)")?;

// What did the ledger look like at any point in time?
let snapshot = db.sql(
    "SELECT * FROM events AS OF SYSTEM TIME '2026-03-07 12:00:00'"
)?;

---

Why Should You Care?

It's Fast. Really Fast.

Operation	TensorDB	SQLite (WAL)	Factor
Point read	276 ns	~400 ns	1.4x faster
Point write	1.9 us	~15 us	8x faster
Batch insert (10k rows)	18 ms	~35 ms	2x faster

These aren't synthetic benchmarks on a tuned cluster. This is single-node, embedded, with full durability guarantees. The write path uses lock-free atomic CAS — no mutexes, no channels, no actor messages on the hot path.

It Speaks PostgreSQL

# Start the server
tensordb-server --data-dir ./mydb --port 5433

# Connect with literally anything that speaks Postgres
psql -h localhost -p 5433 -d mydb

Your existing tools work — psql, pgAdmin, DBeaver, SQLAlchemy, Prisma, any Postgres driver. You get standard SQL plus temporal queries that Postgres doesn't natively support:

-- Standard SQL
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer TEXT, total REAL);
INSERT INTO orders (customer, total) VALUES ('acme', 9999) RETURNING id;

-- Temporal queries (the superpower)
SELECT * FROM orders AS OF SYSTEM TIME '2026-01-15';
SELECT * FROM orders FOR SYSTEM_TIME FROM '2026-01-01' TO '2026-03-01';
SELECT * FROM orders VALID AT DATE '2026-02-15';

It Embeds in Your Binary

No daemon process. No Docker container. No ops overhead. One function call:

let db = Database::open("./path")?;

Ship the database inside your application. Ideal for edge deployments, CLI tools, desktop apps, or anywhere a full Postgres deployment is overkill.

The SQL Surface Is Complete

This isn't a toy query language. It's a full SQL engine with a hand-written recursive descent parser, cost-based query planner, and vectorized execution:

• DDL/DML: CREATE TABLE, ALTER TABLE, INSERT ... ON CONFLICT (upsert), UPDATE ... RETURNING, DELETE ... RETURNING
• Queries: JOINs (inner, left, right, full outer, cross), subqueries, CTEs (including WITH RECURSIVE), window functions, GROUP BY/HAVING, UNION/INTERSECT/EXCEPT
• Types: INTEGER, REAL, TEXT, BOOLEAN, DATE, TIMESTAMP, INTERVAL, JSON
• Functions: 50+ built-in (string, numeric, date/time, aggregate, window)
• Advanced: foreign keys, materialized views, triggers, user-defined functions, generated columns, JSON operators (->, ->>, @>)

And the error messages are actually helpful:

ERROR T2001: Table "ordres" not found. Did you mean "orders"?

---

How It Works Under the Hood

For those who like to understand the machinery.

Immutable Key Encoding

Every record gets this internal key:

user_key || 0x00 || commit_ts (8B big-endian) || kind (1B)

The user_key prefix means prefix scans retrieve all versions. Big-endian timestamps give chronological ordering for free. The kind byte distinguishes puts from tombstones. Updates don't modify anything — they append new facts with higher timestamps.

LSM Storage Stack

Write ─→ WAL (CRC-framed) ─→ Memtable (BTreeMap)
                                     │ flush
                                     ▼
                            L0 SSTables (sorted)
                                     │ compaction
                                     ▼
                       L1 → L2 → ... → L6
                  (LZ4 for L0-L2, Zstd for L3+)
                  (bloom filters, block cache)

Lock-free writes: AtomicU64::compare_exchange claims a commit timestamp, then writes directly to memtable. No locks on the hot path.

Direct reads: ShardReadHandle with parking_lot::RwLock bypasses shard actors entirely. This is how reads hit 276ns.

Batched durability: A DurabilityThread coalesces WAL fsyncs across shards on a 1ms interval. Individual writes don't pay fsync cost.

Cost-Based Query Planner

The planner evaluates plan variants — PointLookup, IndexScan, FullScan, HashJoin — using table statistics. A learned cost model tracks actual vs. estimated cardinalities and adjusts its estimates from observed query performance.

---

Production-Ready Features

Things you'll need when you go beyond prototyping:

Security: RBAC with users/roles/permissions, row-level security policies, mTLS on pgwire, column-level AES-256-GCM encryption, encryption key rotation without downtime.

Audit: SHA-256 hash-chained audit log. Every DDL and DML event is recorded in a tamper-evident chain. Run VERIFY AUDIT LOG to cryptographically verify integrity.

GDPR: FORGET KEY 'user:42' creates a cryptographic tombstone across all versions — satisfying right-to-erasure while preserving audit log structure.

Observability: 8 diagnostic SQL commands — SHOW STATS, SHOW SLOW QUERIES, SHOW ACTIVE QUERIES, SHOW STORAGE, SHOW COMPACTION STATUS, SHOW WAL STATUS, SHOW AUDIT LOG, SHOW PLAN GUIDES. Plus a health HTTP endpoint.

Specialized engines: Full-text search (BM25), time-series (bucketing, gap fill, LOCF, interpolation), vector search (HNSW + IVF-PQ), event sourcing, graph queries.

---

The Honest Comparison

Use Postgres if you need a battle-tested, general-purpose OLTP database with 30 years of production hardening and a massive extension ecosystem.

Try TensorDB if:

• Bitemporality is your primary requirement, not an afterthought bolted on with triggers
• You want to embed the database directly in your application
• You need structurally append-only storage for compliance (not just "we log changes")
• Sub-microsecond embedded reads matter to you

TensorDB is younger software. It doesn't have Postgres's ecosystem depth. But for the specific problem it solves — immutable, bitemporal, embedded storage with full SQL — it's purpose-built.

---

Get Started in 60 Seconds

Pick your language:

# Rust — embed in your binary
cargo add tensordb

# Python — pip install and go
pip install tensordb

# Any language — connect via PostgreSQL protocol
cargo install tensordb-server
tensordb-server --data-dir ./mydb --port 5433
# Then: psql -h localhost -p 5433

Links:

• GitHub — star it if you find it useful
• Documentation — quickstart, SQL reference, architecture guide
• PyPI — pip install tensordb
• crates.io — cargo add tensordb

---

If you're building financial systems, compliance infrastructure, audit trails, healthcare records, or anything where the history of data matters as much as the current state — give it a try and tell me what you think. I read every issue and discussion on GitHub.

And if it breaks, file a bug. That's how it gets better.

(base) walebadr@spark:~/tensorDB$ cat blog-post-devto.md

title: "I Built a Database That Speaks SQL, Vectors, Time-Series, and Natural Language — All at Once"
published: true
description: "TensorDB is an open-source, AI-native multi-model database written in pure Rust. Vector search, NL-to-SQL, bitemporal time travel, full-text search, event sourcing — one embedded binary, 276ns reads."
tags: [rust, database, ai, opensource]

cover_image: https://raw.githubusercontent.com/tensor-db/TensorDB/main/docs/cover.png

Every AI application I've built in the last two years had the same architecture smell: a Postgres for the relational data, a Pinecone or Qdrant for the vectors, an Elasticsearch for full-text search, a TimescaleDB or InfluxDB for metrics, and some Kafka-based event stream gluing it all together. Five databases. Five failure modes. Five sets of credentials, schemas, backup strategies, and 3 AM pages.

One night, staring at a docker-compose.yml that had more services than my application had features, I asked a question that wouldn't leave me alone:

What if one database could do all of it?

Not a lowest-common-denominator compromise. A database purpose-built for the AI era — where vectors live next to relational rows, where time-series data flows in alongside event streams, where you can ask questions in English and get SQL back, and where every single write is an immutable, auditable, time-traveling fact.

That's TensorDB.

---

What TensorDB Actually Is

TensorDB is an open-source, AI-native, multi-model database written in pure Rust. It's a single embedded library — no server process, no Docker container, no JVM — that gives you:

Capability	What You'd Normally Need
Relational SQL (full JOINs, CTEs, window functions)	PostgreSQL
Vector search (HNSW, IVF-PQ, hybrid search)	Pinecone / Qdrant / Weaviate
Full-text search (BM25, stemming, highlighting)	Elasticsearch / Meilisearch
Time-series (bucketing, gap fill, rate calculations)	TimescaleDB / InfluxDB
Event sourcing (aggregates, snapshots, projections)	EventStoreDB / custom Kafka
Change data capture (durable cursors, consumer groups)	Debezium / Kafka Connect
Bitemporal time travel (system + business time)	Custom audit layer / prayer

All of this runs in-process. pip install tensordb. That's it.

---

Show Me the Code

Vector Search + SQL in the Same Query

-- Store documents with embeddings alongside regular columns
CREATE TABLE docs (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT,
    embedding VECTOR(384)
);

-- Create an HNSW index for fast approximate nearest neighbors
CREATE VECTOR INDEX idx_docs_emb ON docs (embedding)
    USING HNSW WITH (m = 32, ef_construction = 200, metric = 'cosine');

-- k-NN search using the <-> distance operator
SELECT id, title, embedding <-> '[0.12, 0.45, ...]' AS distance
FROM docs
ORDER BY distance
LIMIT 10;

No sidecar vector database. No syncing embeddings between services. The vectors live in the same table as your relational data, indexed by the same engine that handles your JOINs.

Hybrid Search: Vectors Meet Full-Text

This is where it gets interesting. Combine semantic similarity with keyword relevance in a single query:

-- BM25 full-text index on the body column
CREATE FULLTEXT INDEX idx_body ON docs (body);

-- Hybrid scoring: 70% vector similarity, 30% BM25 text relevance
SELECT id, title,
    HYBRID_SCORE(
        embedding <-> '[0.12, 0.45, ...]',
        MATCH(body, 'quantum computing'),
        0.7, 0.3
    ) AS score
FROM docs
WHERE MATCH(body, 'quantum computing')
ORDER BY score DESC
LIMIT 10;

One query. Two ranking signals. Zero infrastructure.

Natural Language to SQL

TensorDB ships with a pure-Rust inference engine — no Python, no external API calls, no GPU required. A bundled Qwen3 0.6B model runs in-process with constrained SQL decoding:

from tensordb import PyDatabase

db = PyDatabase.open("/tmp/mydb")
db.sql("CREATE TABLE sales (id INT, product TEXT, amount REAL, region TEXT)")
db.sql("INSERT INTO sales VALUES (1, 'Widget', 99.99, 'US'), (2, 'Gadget', 149.99, 'EU')")

# Ask in English, get SQL results
result = db.natural_language("What's the total sales amount by region?")
# Internally generates: SELECT region, SUM(amount) FROM sales GROUP BY region
# Returns: [{"region": "US", "amount": 99.99}, {"region": "EU", "amount": 149.99}]

The model runs a tool-calling loop: it introspects table schemas, generates SQL, executes it, and returns structured results. All in-process. All at the speed of Rust.

Time Travel That Just Works

Every write in TensorDB is an immutable fact. Nothing is ever overwritten or deleted. You get automatic time travel for free:

-- Insert a record
INSERT INTO accounts VALUES (1, 'Alice', 10000);
-- Update it
UPDATE accounts SET balance = 7500 WHERE id = 1;
-- Update it again
UPDATE accounts SET balance = 3200 WHERE id = 1;

-- What was Alice's balance at any point in time?
SELECT * FROM accounts AS OF SYSTEM TIME '2026-03-01 12:00:00';

-- Show me EVERY version that ever existed
SELECT * FROM accounts FOR SYSTEM_TIME ALL WHERE id = 1;
-- Returns all three versions: 10000, 7500, 3200 — with timestamps

-- Business time: when was this fact true in the real world?
SELECT * FROM accounts FOR APPLICATION_TIME AS OF '2026-01-15';

Two independent timelines — system time (when was it recorded?) and business time (when was it true?) — tracked automatically on every row. This is SQL:2011 bitemporal, not a hack bolted on with triggers.

Time-Series, Natively

CREATE TIMESERIES TABLE metrics (ts TIMESTAMP, sensor TEXT, value REAL)
    WITH (bucket_size = '1h');

-- Downsample to hourly averages
SELECT TIME_BUCKET('1h', ts) AS hour, sensor, AVG(value)
FROM metrics
GROUP BY hour, sensor;

-- Fill gaps in sparse data
SELECT TIME_BUCKET_GAPFILL('1h', ts) AS hour,
       LOCF(AVG(value)) AS value  -- Last Observation Carried Forward
FROM metrics
GROUP BY hour;

-- Rate of change per second
SELECT ts, RATE(value) AS change_per_sec FROM metrics;

Event Sourcing, Built In

-- Aggregate-centric event streams with snapshots
-- Idempotency keys prevent duplicate processing
-- Cross-aggregate queries with full SQL

---

Why Should You Care About the Performance?

Operation	TensorDB	SQLite (WAL)	sled	redb
Point read	273 ns	1,145 ns	278 ns	570 ns
Point write	3.6 us	41.9 us	4.3 us	1,392 us
Throughput (reads/s)	3.8M	—	4.6M	—

4.2x faster reads than SQLite. 11.6x faster writes. And this isn't a stripped-down key-value store — it's a full SQL engine with JOINs, window functions, and a cost-based query planner.

How?

• Lock-free writes: AtomicU64::compare_exchange claims a commit timestamp, writes directly to memtable. No mutexes on the hot path.
• Direct shard reads: ShardReadHandle bypasses the actor channel entirely — 273ns from function call to result.
• Group-commit WAL: A DurabilityThread coalesces fsyncs across shards. Individual writes don't pay the fsync penalty.
• Vectorized execution: Columnar 1024-row RecordBatch engine for analytical queries.
• Optional SIMD: AVX2/NEON-accelerated bloom probes and checksums behind --features simd.

---

The AI-Native Difference

TensorDB doesn't just store data for AI applications. It uses AI to make itself better.

Learned Cost Model

The query planner doesn't just use static heuristics. It trains a lightweight online model from observed query performance — tracking actual vs. estimated cardinalities and adjusting its cost estimates via SGD. Your queries get faster the more you run them.

Write-Rate Anomaly Detection

Per-table EMA-based statistics flag unusual write patterns in real time. A sudden 10x spike in writes to your payments table? TensorDB notices. Budget: <500ns per write — you won't feel it.

Query & Compaction Advisors

The engine analyzes your access patterns and recommends optimizations:

SHOW AUTO TUNE RECOMMENDATIONS;
-- "Consider adding an index on orders.customer_id (hit 847 times in scans)"
-- "Cache hit rate for 'events' table is 23% — consider increasing block_cache_bytes"

SUGGEST INDEX FOR SELECT * FROM orders WHERE status = 'pending' AND total > 100;
-- Recommends: CREATE INDEX idx_orders_status_total ON orders (status, total)

In-Process Inference

The embedded Qwen3 model isn't just for NL-to-SQL. It powers:

• Risk scoring: Inline anomaly detection per write with fast pattern matching
• Auto insights: Pattern synthesis from write streams
• SQL template speculation: Pre-generates candidate SQL templates to reduce query latency

All running in Rust. No Python runtime. No HTTP calls. No cold starts.

---

Production-Ready, Not a Toy

Security

-- RBAC with granular permissions
CREATE USER analyst WITH PASSWORD 'secure';
GRANT SELECT ON orders TO analyst;

-- Row-level security
CREATE POLICY region_filter ON orders
    USING (region = CURRENT_USER_ATTRIBUTE('region'));

-- Column-level AES-256-GCM encryption
CREATE TABLE patients (
    id INTEGER PRIMARY KEY,
    name TEXT,
    ssn TEXT ENCRYPTED,
    diagnosis TEXT ENCRYPTED
);

Plus mTLS on the PostgreSQL wire protocol, encryption key rotation without downtime, and a SHA-256 hash-chained audit log you can cryptographically verify:

VERIFY AUDIT LOG;
-- { verified: 12847, broken_at: null }

GDPR Erasure

FORGET KEY 'user:42' FROM users;

Cryptographic tombstone across all temporal versions. Right-to-erasure satisfied while preserving the integrity of the append-only ledger.

PostgreSQL Wire Protocol

tensordb-server --data-dir ./mydb --port 5433

# Connect with any Postgres client
psql -h localhost -p 5433 -d mydb

psql, pgAdmin, DBeaver, SQLAlchemy, Prisma, any Postgres driver — they all work. Your existing tools connect without changes.

Observability

Eight built-in diagnostic commands:

SHOW STATS;              -- Cache hit/miss, operation counts
SHOW SLOW QUERIES;       -- Queries exceeding threshold
SHOW ACTIVE QUERIES;     -- Currently running
SHOW STORAGE;            -- SSTable levels, file sizes
SHOW COMPACTION STATUS;  -- Background compaction state
SHOW WAL STATUS;         -- Write-ahead log health
SHOW AUDIT LOG;          -- Tamper-evident event history
SHOW PLAN GUIDES;        -- Pinned query plans

Plus a /health HTTP endpoint on the server port + 1.

Complete SQL

This isn't a half-baked query parser. It's a full SQL engine:

• 60+ built-in functions: string, numeric, date/time, aggregate, window, vector, time-series, full-text
• JOINs: inner, left, right, full outer, cross
• Subqueries: IN, EXISTS, scalar subqueries in WHERE
• CTEs: including WITH RECURSIVE
• Window functions: ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG
• Upserts: INSERT ... ON CONFLICT DO UPDATE
• Transactions: BEGIN, COMMIT, ROLLBACK, SAVEPOINT
• Data interchange: COPY TO/FROM CSV, JSON, Parquet. read_csv(), read_json(), read_parquet()

And when you misspell something:

ERROR T2001: Table "ordres" not found. Did you mean "orders"?

---

The Architecture, for the Curious

Client Layer:  Rust API │ Python (PyO3) │ Node.js (napi-rs) │ pgwire │ CLI
                            │
                   ┌────────┴────────┐
                   │   SQL Engine     │
                   │ Parser → Planner │──→ Vectorized Execution
                   │   → Executor     │    (1024-row RecordBatch)
                   └────────┬────────┘
                            │
            ┌───────┬───────┼───────┬───────┐
            │       │       │       │       │
        Relational  FTS   Vector  TimeSeries Event
        (typed SQL) (BM25) (HNSW)  (bucket)  Sourcing
            │       │       │       │       │
            └───────┴───────┼───────┴───────┘
                            │
                   ┌────────┴────────┐
                   │  Shard Engine    │
                   │ Lock-free writes │──→ CDC (durable cursors)
                   │ Direct reads     │
                   └────────┬────────┘
                            │
                   ┌────────┴────────┐
                   │  LSM Storage     │
                   │ WAL → Memtable → │
                   │ SSTables (L0-L6) │
                   │ Bloom │ LZ4/Zstd │
                   └─────────────────┘

Immutable key encoding: user_key || 0x00 || commit_ts (8B) || kind (1B) — prefix scans retrieve all versions, big-endian timestamps give chronological order for free, the kind byte distinguishes puts from tombstones.

LSM-tree storage: WAL (CRC-framed, group-commit) → Memtable (BTreeMap) → SSTables with bloom filters, block cache, LZ4/Zstd compression, multi-level compaction from L0 through L6.

Epoch-Ordered Append-Only Concurrency (EOAC): A single Arc<AtomicU64> global epoch counter unifies transactions, MVCC, point-in-time recovery, and incremental backup under one mechanism. No two-phase locking. No undo logs.

---

How It Compares — Honestly

	TensorDB	PostgreSQL	SQLite	Pinecone
Relational SQL	Full	Full	Full	No
Vector search	HNSW + IVF-PQ	pgvector (extension)	No	Native
Full-text search	BM25 native	tsvector	FTS5	No
Bitemporal	Native (SQL:2011)	No	No	No
Time-series	Native	TimescaleDB (ext)	No	No
Embedded (no server)	Yes	No	Yes	No
NL-to-SQL	In-process	No	No	No
Point read latency	273 ns	~5 us	1,145 ns	Network
Immutable ledger	Yes	No	No	No

Use PostgreSQL if you need 30 years of production hardening and the deepest extension ecosystem on the planet.

Use TensorDB if you're building an AI-native application that needs vectors + SQL + time travel + search in one place, especially if you want to embed it directly in your binary.

---

Get Started in 60 Seconds

# Python
pip install tensordb

# Rust
cargo add tensordb

# Any language — PostgreSQL wire protocol
cargo install tensordb-server
tensordb-server --data-dir ./mydb --port 5433
psql -h localhost -p 5433

from tensordb import PyDatabase

db = PyDatabase.open("/tmp/mydb")

# Relational
db.sql("CREATE TABLE users (id INT PRIMARY KEY, name TEXT, plan TEXT)")
db.sql("INSERT INTO users VALUES (1, 'Alice', 'pro'), (2, 'Bob', 'free')")

# Vector search
db.sql("CREATE TABLE embeddings (id INT PRIMARY KEY, vec VECTOR(3))")
db.sql("INSERT INTO embeddings VALUES (1, '[0.1, 0.2, 0.3]')")
db.sql("SELECT id, vec <-> '[0.15, 0.25, 0.35]' AS dist FROM embeddings ORDER BY dist LIMIT 5")

# Time travel
db.sql("UPDATE users SET plan = 'enterprise' WHERE id = 1")
db.sql("SELECT * FROM users FOR SYSTEM_TIME ALL WHERE id = 1")
# Returns both versions: 'pro' and 'enterprise'

---

The Bigger Picture

The AI era needs a new kind of database. Not a relational database with vectors bolted on. Not a vector database that added SQL as an afterthought. Not five databases duct-taped together with Kafka.

TensorDB is built from scratch for this world: where embeddings are first-class citizens, where every fact is immutable and auditable, where you can search by meaning AND by keyword AND by SQL — and where the database itself uses machine learning to optimize its own performance.

It's open source, it's written in Rust, and it fits in a single binary.

Links:

• GitHub — star it if this resonates
• Docs — SQL reference, architecture guide, interactive playground
• PyPI — pip install tensordb
• crates.io — cargo add tensordb

If you're building something where AI meets data — RAG pipelines, agent memory, recommendation engines, compliance systems, real-time analytics — I'd love to hear what you think. Every issue and discussion on GitHub gets a response.

And if it breaks, file a bug. That's how databases grow up.