Why we built on PostgreSQL

The decision

foundation4 keeps fragment text, vectors, full-text index entries, metadata, classifications and version history in one PostgreSQL database, with the pgvector extension for vector search. We did not add a dedicated vector database, and we did not build on a document database.

This post explains that choice in technical terms. It compares PostgreSQL with two common alternatives: a document database such as MongoDB with a vector index added, and a standalone vector store paired with a separate system of record. It also covers what the choice costs us.

The short version: retrieval over sensitive content is rarely a pure nearest-neighbor problem. Almost every query also asks which reader is asking, which version is current, and which metadata applies. Those questions are relational. Answering them in the same query as the vector search, against the same rows, is what PostgreSQL lets us do.

What one search has to answer

A foundation4 search request carries more than a query. Here is a typical one, for a chat application searching the rooms a user belongs to:

{
  "query": "How do I rotate the database credentials?",
  "type": "similarity",
  "classification": ["internal"],
  "params": {"k": 5},
  "filters": {
    "$and": [
      {"room_id": {"$in": ["GENERAL", "6f1c2a"]}},
      {"timestamp": {"$ge": "2026-09-01T00:00:00.000Z"}}
    ]
  }
}

To answer it correctly, the system has to do five things at once:

1. Find the fragments closest in meaning to the query.
2. Keep only fragments whose classification the reader holds, including classifications inherited through a hierarchy. Holding internal can also mean seeing public.
3. Keep only fragments whose metadata satisfies the filter.
4. Keep only the current version of each document, not versions that were replaced or expired.
5. Return the top k of what is left, with the text decrypted.

Steps 2 to 4 are not optional extras. They decide whether a result is allowed to appear at all. If they run after the vector search instead of inside it, the system wastes work on candidates it throws away, and it can return fewer than k results when most candidates fail the checks.

The data behind those checks also changes all the time. Documents get new versions. Content is expired or deleted. Classifications are added or removed. Whatever stores the vectors has to see those changes at the same moment as whatever stores the documents.

Why not a document database?

A document database looks like a natural fit at first. Our documents arrive as JSON with free-form metadata, and MongoDB stores JSON well. It has multi-document transactions, scales out by sharding, and offers vector and full-text search, in Atlas and in self-managed Community Edition. A team that already runs MongoDB and needs sharding or Lucene-grade text search has good reasons to stay with it.

Our concern is narrower, and it comes from where search runs. In document databases like MongoDB, search often runs beside the database rather than inside it. MongoDB runs vector and full-text search in a separate process, mongot, beside the database server, mongod. mongot keeps its own index of the searchable fields and updates it by reading the database's change stream. Three consequences matter for us.

The filter can see old values. A $vectorSearch filter is evaluated against mongot's index, not the collection. When a document is reclassified, withdrawn or retagged, the collection changes at once and the search index catches up shortly after. For that window, a filter can admit a result on values that are no longer true. Ingestion in foundation4 is asynchronous too: a new document becomes searchable only after a worker processes it. The difference is that once a fragment is searchable, its classification, expiry and metadata are read in the same snapshot as its vector. There is no second copy to fall behind.

Load shows up as drift, not as slower writes. Every write to an indexed collection also has to reach mongot. Under sustained ingestion, such as a busy chat platform, MongoDB's own guidance treats a growing replication lag as a sign that mongot cannot keep up. A lag that outruns the change history forces the index to resynchronize. In PostgreSQL the index update is part of the write transaction. Heavy ingestion makes writes slower and the queue longer, which an operator can see and plan for, but search never falls behind the data.

Integrity is the application's job. A retrieval system stores each document as many fragments, each with vectors and index entries, across several versions. Document databases like MongoDB have no foreign keys, so keeping those pieces consistent through deletes, reclassification and version changes is code the application has to get right every time. In foundation4, the database enforces it, as the next sections show.

Why not a dedicated vector store?

Dedicated vector stores such as Qdrant, Weaviate, Milvus and Pinecone are built for one job, and they do it well. The old criticism that they can only filter after the search no longer holds. Most now apply metadata filters during graph traversal, and several add keyword search beside vector search.

The problem is not the vector store. It is the second system it implies. A vector store holds vectors and a copy of the metadata needed to filter them. The documents, their versions, the classification scheme and the API keys still live in a system of record, usually a relational database. Every change now has to land in two places.

No transaction spans both. Creating a new version means writing the version to the database and upserting its vectors to the store. Expiring the old version means updating both again. If the process fails between the two writes, the systems disagree. Teams solve this with an outbox table, a sync worker and a reconciliation job. That is real code, and it has its own failure modes.

Deletion has to reach every copy. When content must no longer exist, it has to leave the database, the vector store and the vector store's own snapshots. A delete that reaches one and not the other leaves vectors that still match queries. In foundation4, one delete request removes every version of a document in one transaction. Foreign keys with ON DELETE CASCADE carry the delete from the document to its fragments, and from each fragment to its vectors and full-text entries.

Scoping data is duplicated. To filter by classification inside the vector store, the classification has to be copied onto every vector. Inherited classifications and taxonomies either expand in the application or get flattened into each record. When a classification is removed or a taxonomy changes, those copies have to be rewritten.

Backups happen at two moments. Two systems back up on two schedules. After a restore, you have to find what one system has and the other lacks before you trust either.

There is one more system to run. It needs its own deployment, upgrades, monitoring, credentials and security review. Some vector stores are offered only as a hosted service, which rules them out for a network that cannot reach the internet.

A dedicated vector store earns its place when vector search itself is the bottleneck: very large collections, very high query rates, little relational scoping. That is a real workload. It is not the one foundation4 is built for, which is search over an organization's own content, scoped to each reader.

How foundation4 uses PostgreSQL

The three designs side by side, for the questions above:

Question	PostgreSQL with pgvector (foundation4)	Document database with a search process	Vector store plus a system of record
Where vectors live	In the same database as the text, metadata and versions	In a search index beside the database	In a second system
Filter reads the same data as the ranking	Yes, one snapshot	No, the search index's copy	No, the vector store's copy
When a change reaches search	When its transaction commits	After the search process reads it	After a sync job copies it
Integrity between fragments, vectors and versions	Foreign keys, enforced by the database	Application code	Application code across two systems
Hierarchies (inheritance, taxonomies)	A recursive query in the same database	A lookup stage, or copied values	Expanded by the application, or copied values
Delete or erase a document	One transaction, cascading	One collection, index follows	Two systems, plus their snapshots
Backup	One consistent snapshot	One, index rebuilt from it	Two, taken at different moments
Horizontal scale-out	No, one primary server	Yes, sharding	Yes, in most vector stores

One schema per pipeline

foundation4 uses two schemas. public holds shared objects: pipelines, documents, versions, models, API keys and permissions. embeddings holds a dedicated set of tables for each pipeline:

• a classifications table, with one encryption key per classification;
• a texts table, one row per fragment, with the encrypted text, document, version, position, expiry time and metadata;
• a vectors table for each embedding model, with an HNSW index for each supported distance strategy;
• a full-text table, when full-text search is enabled, with a GIN index.

Each pipeline's size and query load stay out of the other pipelines' indexes, and deleting a pipeline drops its tables in one operation.

One query per search

For a vector search, the API server embeds the query and resolves the reader's classifications, including inherited ones, with a recursive query on the pipeline's classification hierarchy. PostgreSQL then selects the matching fragments from the index, restricted to those classifications, the metadata filter and current versions, in a single query. The same query reads each fragment's classification key, and the API server decrypts the text before returning it.

The filter applies before the k limit, not after. One caveat is honest to state: HNSW is an approximate index, so a very selective filter can still return fewer than k vector results. foundation4 requires pgvector 0.8.0 or later and turns on its iterative index scan in relaxed-order mode, which keeps scanning the graph when filters remove too many candidates. Full-text search, which uses an exact index, always returns up to k matching fragments.

Transactions cover text and vectors together

Ingestion is asynchronous. A worker splits each document, computes the embeddings, encrypts the fragment text and writes the fragments, vectors and full-text entries to PostgreSQL in one transaction, together with the document's new status. Foreign keys tie each vector and each full-text entry to its fragment, so a vector cannot exist without its text.

When a new version arrives, the previous version stays searchable until the new one is processed, so a document never drops out of search during an update. Changes to a pipeline's classifications also run in one transaction.

Version history and point-in-time reads

Every submission creates a version, identified by its creation time in microseconds. Replaced and expired versions stay in the database until they are deleted. Document and fragment reads accept an as_of time and return the content as it stood then. Search always runs on current content. Point-in-time reads are for audit and for reproducing an earlier answer.

Full-text search with the same scoping

Vector search is weak on exact strings: product codes, error messages, names and acronyms. foundation4 handles these with PostgreSQL's own text search. The worker detects the language of each fragment and builds its index entry with the matching PostgreSQL configuration, so approve refunds matches "approving a refund". Full-text search applies the same classification and metadata scoping as vector search. An application that wants both kinds of match today runs both searches and merges the lists by rank, as the guide to combining results shows.

What is encrypted, and what PostgreSQL must read

Stored text is encrypted with a separate key for each classification. Vectors, full-text index entries and metadata are not encrypted, because PostgreSQL compares them directly to answer searches and filters. That is the trade the design makes: the database can only scope a query on values it can read. Content that must stay encrypted belongs in the document text, not in metadata.

One backup, one restore

The persistent state of a deployment is the PostgreSQL database plus two secrets: the application secret and the master key. A pg_dump reads one consistent snapshot of all of it. The work queue and the cache hold only work in progress and short-lived data, so they are not part of the backup. After a restore, there is nothing to reconcile between stores.

How it scales

We don't claim that PostgreSQL out-scales document databases like MongoDB on raw capacity. They shard data across servers, and foundation4 does not. What we do claim is that foundation4 scales where retrieval actually spends its effort, and that it behaves predictably as load grows.

• The expensive work scales out. Splitting text and computing embeddings are the heaviest steps in ingestion. They run on stateless workers that scale independently of the API servers. PostgreSQL stores, filters and ranks.
• Ingestion and search are separate paths. The API server accepts a document and hands the processing to the workers, so a large ingestion load does not degrade search latency.
• Pipelines grow independently. Each pipeline has its own tables and indexes, so one large pipeline does not slow another's indexes, and a pipeline can be dropped in one operation.
• Load is visible, not silent. When ingestion outpaces processing, documents wait in the queue with a pending status. An operator sees that in the metrics and adds workers. Search keeps answering from consistent data the whole time.

The last point is the one that matters most at scale. In a design with a separate search index, heavy writes show up as an index that quietly drifts from the data. In foundation4 they show up as a queue you can measure.

What it costs

Every design gives something up. These are the limits we accept, all stated in the docs.

• One primary server. foundation4 sends every read and write, searches included, to one database URL. Read replicas get no traffic. High availability comes from the database service failing over behind a stable host name. A team that needs to shard data across many servers should look elsewhere.
• A connection budget. Each API server and worker holds database connections. Adding replicas means checking the total against PostgreSQL's max_connections.
• A queue with a time limit. The work queue keeps each job for 24 hours. A backlog the workers can't clear in that time leaves documents pending until the application submits them again, so large loads go in paced rounds.
• Approximate vector search under tight filters. As above, a very selective filter can return fewer than k vector results. Full-text search does not have this limit.
• A ceiling on vector size. pgvector's HNSW index limits the number of dimensions, so the largest embedding models cannot serve a pipeline.
• The database can read what it filters on. Vectors, full-text entries and metadata are stored without application-layer encryption. The classification keys live in the same database as the text they protect, so a full database backup carries the same sensitivity as the database. Database access, storage encryption and backup handling stay with the operator.

For search over an organization's own content, scoped to each reader, we think these are the right costs. They are well understood, they are PostgreSQL's costs rather than new ones, and teams that already run PostgreSQL know how to manage them.

One database, one answer

When a security reviewer asks which system decides what a reader can see, the answer is one query in one database. When an operator asks what to back up, the answer is one database and two secrets. When a document must be erased, one request removes it from the database. Backups keep it until they age out, so backup retention belongs in any erasure plan.

The architecture page and the database guide cover the details. To talk through how foundation4 fits your environment, request a briefing.