Building a document database on top of PostgreSQL

One of our planned products is Smpl Data: a simple document-storage API that gives developers a place to persist JSON documents without standing up a separate database. The elevator pitch is MongoDB-as-a-service with a REST API, built on PostgreSQL.

Building a document database on top of a relational database sounds like a bad idea. Postgres purists are already wincing. But for the workloads we’re targeting — small to medium datasets, flexible shapes, simple queries — it works, and it costs us zero new infrastructure, because it runs on the Postgres we already operate. A disclosure before the design: Smpl Data hasn’t shipped yet. This is the design we’ve settled on, written down before the product exists, which is exactly when design posts are most honest and least battle-scarred.

The use case

The customers are the same ones using our other products: backend developers who want managed infrastructure they don’t operate. When they reach for a document store, it’s for three reasons. The documents don’t share a shape (a preferences store where fields vary by user, region, and app version). The queries are simple (“fetch by ID,” “fetch where this field equals this value”). And they want it over HTTP rather than holding a database connection. MongoDB is the category leader for this, and it’s operationally heavy to self-host and expensive to rent. A simpler version with a cleaner API covers most of what these workloads do.

The data model

The entire document store is one table:

CREATE TABLE document (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES account(id),
    collection TEXT NOT NULL,
    body JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX document_account_collection ON document(account_id, collection);
CREATE INDEX document_body_gin ON document USING GIN(body);

“Collections” are a value in a column, not tables — so creating one requires no DDL and no migration. There’s deliberately no POST /collections endpoint either: a collection exists the moment the first document lands in it, which matches both the MongoDB model and how developers actually think (“store this in user-preferences,” not “first, create a collection”). The body is JSONB — binary JSON that Postgres can index and query — with the GIN index carrying the filtering load.

The API

REST, following our JSON:API conventions: POST /api/v1/documents/{collection} to store, GET .../{collection}/{id} to fetch, PUT to replace, DELETE to delete, and GET .../{collection} to list and query. The collection name lives in the URL, never injected into the document — the body stays exactly what the developer stored, no metadata fields sprouting inside it.

Querying

The v1 query API is equality filters only. GET /api/v1/documents/user-preferences?filter[plan]=enterprise becomes a containment query:

SELECT * FROM document
WHERE account_id = $1
  AND collection = $2
  AND body @> '{"plan": "enterprise"}'
  AND deleted_at IS NULL;

The @> operator is covered by the GIN index, so these stay efficient as collections grow.

Equality-only was a deliberate line to hold. Range queries, full-text, and nested-path filters are all achievable with JSONB, and each one complicates the query builder and the index strategy; equality covers the bulk of what the target use cases do. Range and full-text are roadmapped as opt-in features for collections that declare a schema.

Schema on read

Documents are stored without validation. {"name": "Alice", "plan": "enterprise"} and {"user_id": 123, "preferences": {"theme": "dark"}} can share a collection, and the application is responsible for making sense of what it stored — that’s the flexible-schema promise, delivered literally. For teams that want guardrails, per-collection JSON Schema validation is planned as opt-in: register a schema, and writes that don’t conform get rejected. Unschemed collections stay flexible by default.

Where it won’t compete

Honest limits, because JSONB-on-Postgres is not a general-purpose MongoDB replacement. Large result sets pay a deserialization cost — documents are binary blobs, not columns. Multi-field range queries are weaker than the same queries against properly indexed relational columns. And very high write throughput runs into GIN index maintenance costs that purpose-built document stores absorb more gracefully. For teams storing thousands to tens of thousands of documents with simple access patterns — the actual target — none of these bite.

Before general availability

Three gaps are worth naming before general availability. Pagination: the v1 list endpoint returns everything up to a limit, which is fine for small collections and unacceptable for large ones, so cursor pagination goes in first. Collection stats: “how many documents are in here” shouldn’t require fetching them. And smarter indexing eventually — a BTREE on a generated column beats GIN for known hot fields, but that’s an optimization for a scale the product hasn’t met yet.