Skip to content
Multigrid

Databases and storage for AI

Postgres, pgvector, SQLite, Redis and object storage for AI applications — real DDL, real index parameters, and the storage arithmetic worked out rather than asserted.

Most writing about storage for AI is about vector databases as a category. That is the wrong altitude for the person who has to ship something. The question is not whether vectors are interesting; it is which index to create on which column, what that index costs in memory, what happens to it when a row is deleted, and why a query that was fast yesterday returns four rows today.

So these pages are tutorials against engines you can install this afternoon. Postgres with pgvector does the bulk of the work, because for the overwhelming majority of applications it is the right answer and because its behaviour is inspectable — you can ask it what it did. SQLite covers the local-first case, Redis the cache and queue, object storage the documents and weights that have no business in a row.

Where a number matters it is derived on the page, with every assumption named: bytes per row at each precision, index memory as a function of m, the selectivity at which a post-filtered vector search stops being able to return ten results at all. Those derivations cannot go stale, and you can check every one of them against your own table.

pgvector From Install to First Query

Install the extension, create the column, load vectors, build an HNSW index and read the query plan — every statement written out, with both plans annotated.

11 min read

Choosing and Tuning a pgvector Index

HNSW against IVFFlat, with index memory derived from m and dimension, build time given as a scaling law you can extrapolate, and a SQL harness that measures recall on your own table.

12 min read

Filtering and Vector Search in One Query

Why a WHERE clause and an ANN index fight each other, the selectivity at which a post-filter cannot return ten rows at all, and the four ways to get correct results anyway.

11 min read

Full-Text Search in Postgres for Hybrid Retrieval

tsvector, GIN indexes and ranking in Postgres, then fusing the lexical scores with pgvector results in one SQL statement using reciprocal rank fusion.

11 min read

SQLite as an AI Application Database

Vector search in SQLite two ways — exact scan in application code, and the sqlite-vec extension — with the arithmetic that tells you which row count is your ceiling.

11 min read

Redis for Caching, Queues and Vectors

Three AI workloads on Redis — response cache, job queue, vector index — and why the eviction policy each one needs means they cannot share an instance.

11 min read

Object Storage for Documents, Weights and Artefacts

Key layout that survives reprocessing, lifecycle rules for each class of AI artefact, and the four line items on an object storage bill — including the two that are not bytes.

11 min read

A Documents Table That Survives Re-Indexing

Full DDL for documents, versions, chunks and embeddings, keyed on content hashes so that re-chunking and re-embedding become idempotent operations you can run twice.

12 min read

Storing Embeddings: Types, Precision and Row Size

Bytes per row for every pgvector type, multiplied out to a million rows with index overhead included, and the binary-plus-rerank pattern that cuts the index by 96 per cent.

12 min read

Migrations on a Table With 50 Million Vectors

Online index builds, batched backfills that do not blow up replication lag, and an expand-contract sequence where every step is individually reversible.

12 min read

Backups and Restore for AI Data

Which AI artefacts to back up and which to rebuild, why a logical dump of a vector table restores far slower than you expect, and the restore drill that finds out before an incident does.

11 min read

Row-Level Security for Multi-Tenant Retrieval

The query that returns another tenant's documents, the Postgres RLS policy that closes it, the test that proves it, and the pooling mistake that reopens it.

13 min read

Storing Telemetry From AI Calls

A partitioned request table, the cardinality rule that keeps a metrics system alive, retention tiers with rollups, and why you cannot average a p95.

12 min read

An Append-Only Schema for Prompts, Runs and Outputs

A schema derived from the questions an auditor asks, made append-only with revoked privileges and a trigger, with an honest account of what a hash chain does and does not prove.

12 min read

Deletion That Reaches the Vector Index

What DELETE does to an HNSW index, why a tombstone is not a deletion until the index is rebuilt, and what you can and cannot honestly claim about erasure.

12 min read

Graph Storage Alongside Vectors

Nodes, edges and chunks in one Postgres schema, recursive CTEs with cycle protection, and the specific question types where traversal beats similarity.

11 min read

Connection Pooling for AI Workloads

Why a thirty-second model call breaks normal pool sizing, the Little's law calculation that gives the right number, and the four rules that keep a slow request from exhausting the pool.

11 min read

Caching Retrieval Results

A cache key that includes every input which can change the answer, invalidation by version prefix rather than by scan, and an honest treatment of semantic caching's threshold problem.

11 min read

Handling Files Users Upload to an AI Feature

Presigned uploads with a size cap that is actually enforced, type detection that does not trust the client, quarantine before processing, and per-user quotas that cannot be raced.

12 min read

Modelling Conversations, Branches and Edits

Why a chat history is a tree rather than a list, the ancestor-path column that makes rendering one branch a single index lookup, and the queries each shape makes cheap or impossible.

12 min read

Other topics