Skip to main content

Postgres + pgvector Backend

The Postgres backend stores embeddings, metadata, and full-text content in a single relational database using the pgvector extension. This gives you ACID transactions, hybrid search (dense vectors and full-text ranking in one query), and JSONB metadata filtering, all without a separate vector service.

Prerequisites​

RequirementMinimum version
PostgreSQL14+ (15+ recommended for HNSW index type)
pgvector extension0.5.0+ (CREATE EXTENSION vector); 0.8.0+ for iterativeScan
Node.js18+ (uses the pg npm package)

Quick start — Docker​

docker run -d \
  --name agentos-pgvector \
  -e POSTGRES_PASSWORD=password \
  -p 5432:5432 \
  pgvector/pgvector:pg16

# Verify
psql postgresql://postgres:password@localhost:5432/postgres \
  -c "CREATE EXTENSION IF NOT EXISTS vector; SELECT extversion FROM pg_extension WHERE extname='vector';"

The pgvector/pgvector image ships with the extension pre-installed. No manual compilation needed.

Manual setup​

If you are using an existing Postgres instance (self-hosted or managed), install pgvector manually:

-- Run as a superuser or a user with CREATE EXTENSION privilege.
CREATE EXTENSION IF NOT EXISTS vector;

AgentOS creates its own tables on first use, unless manageSchema is false (Tables made by your migrations). The schema looks like:

CREATE TABLE IF NOT EXISTS "<prefix>my_collection" (
  id            TEXT PRIMARY KEY,
  embedding     vector(1536),          -- pgvector column
  metadata_json JSONB,                 -- GIN-indexed for filtering
  text_content  TEXT,                  -- raw text for hybrid search
  tsv           tsvector GENERATED ALWAYS AS (to_tsvector('english'::regconfig, COALESCE(text_content, ''))) STORED,  -- textSearchConfig, 'english' by default
  created_at    BIGINT NOT NULL,
  updated_at    BIGINT
);

-- Indexes created automatically, each named after the table ("<prefix>my_collection_hnsw", "_metadata", "_fts")
-- and made only when the table has no index of its kind:
-- 1. HNSW index for approximate nearest neighbor search
-- 2. GIN index on metadata_json for JSONB filtering
-- 3. GIN index on tsv for full-text search

Configuration​

import { PostgresVectorStore } from '@framers/agentos/cognition/rag';

const store = new PostgresVectorStore({
  id: 'my-pg-store',
  type: 'postgres',
  connectionString: 'postgresql://postgres:password@localhost:5432/agent_memory',
  poolSize: 10,              // Connection pool size (default: 10)
  defaultDimension: 1536,    // Default embedding dimensions (default: 1536)
  similarityMetric: 'cosine', // 'cosine' | 'euclidean' | 'dotproduct'
  tablePrefix: 'agent1_',    // Optional prefix for multi-tenancy
});

await store.initialize();

Configuration options​

OptionTypeDefaultDescription
connectionStringstringrequired unless pool or adapter is givenStandard Postgres connection URI
poolPgPoolLike (a pg.Pool)noneA pool you own: the store opens none, and close() leaves it open
adapterStorageAdapter (an open PostgreSQL adapter of @framers/sql-storage-adapter)noneAn adapter you own, given without pool and connectionString: the store opens no pool, close() leaves it open, and withStorageTransaction() runs the store's statements in one of its transactions (An adapter you own, and its transactions)
poolSizenumber10Max concurrent connections in the pool the store opens
defaultDimensionnumber1536Embedding vector dimensions for new collections
similarityMetricstring'cosine'Distance function: cosine, euclidean, or dotproduct
tablePrefixstring''Table name prefix for multi-tenant deployments
manageSchemabooleantruefalse: the store runs no DDL; your migrations make the tables from PostgresVectorStore.schemaSql()
textSearchConfigstring'english'Text search configuration of the tsv column and the lexical queries
iterativeScan'strict_order' | 'relaxed_order'nonepgvector's iterative index scan for filtered query() and hybridSearch()
efSearchnumberpgvector's (40)hnsw.ef_search for a search, 1 to 1000
maxScanTuplesnumberpgvector's (20,000)hnsw.max_scan_tuples for an iterative scan

The Postgres backend runs hybrid search in one SQL statement: pgvector HNSW for dense vectors and PostgreSQL full-text ranking (ts_rank over the tsv column; not BM25), fused with Reciprocal Rank Fusion (RRF). alpha and fusion are accepted and not read: the two ranks always count equally.

const results = await store.hybridSearch(
  'my_collection',
  queryEmbedding,
  'natural language query text',
  {
    topK: 10,
    rrfK: 60,  // RRF constant (default: 60)
  },
);

How it works internally:

  1. Dense CTE: Finds top candidates by pgvector HNSW distance (<=> for cosine).
  2. Lexical CTE: Finds top candidates by ts_rank() against the tsvector column: plainto_tsquery under the store's text search configuration, which asks for every word, or to_tsquery over the query's own words when match or prefix is given (Lexical search, and the words of a hybrid search).
  3. Fusion CTE: Merges both result sets with 1/(k + rank_dense) + 1/(k + rank_lexical); a document missing from one list takes rank 10,000 there. Each list holds up to topK × 3 candidates, and a metadata filter applies to each before ranking.
  4. Final join: Fetches full documents for the top fused results.

This avoids two separate queries and application-level fusion.

Multi-tenancy via schema isolation​

For SaaS deployments where each tenant needs isolated data:

// Tenant A
const storeA = new PostgresVectorStore({
  // ...
  tablePrefix: 'tenant_a_',
});

// Tenant B
const storeB = new PostgresVectorStore({
  // ...
  tablePrefix: 'tenant_b_',
});

Each prefix creates a separate set of tables: "tenant_a_my_collection", "tenant_a__collections", etc., with indexes named after each table ("tenant_a_my_collection_hnsw" and so on). Postgres keeps 63 bytes of a name: where a table's name leaves no room for a whole index name, createCollection() cuts the table's part of it and adds eight hex digits of a hash of the table's name, so each index keeps a name of its own. Alternatively, use Postgres schemas (SET search_path) for stronger isolation. For many small tenants in one table, see One collection, many tenants.

One collection, many tenants​

A table per tenant suits a few large tenants. For many small ones, keep one collection, give every document its tenant's keys in its metadata, and pass them as a filter: the options and methods below keep each search, change and deletion inside the filter.

A pool you own​

import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
const store = new PostgresVectorStore({ id: 'docs', type: 'postgres', pool, manageSchema: false });

With pool, the store opens no pool of its own and close() leaves yours open; connectionString is then not needed. With none of pool, adapter and connectionString, initialize() throws.

An adapter you own, and its transactions​

The store also runs on an open PostgreSQL adapter of @framers/sql-storage-adapter, and joins a transaction that adapter opened, so that vector writes commit or roll back with the transaction's other writes:

import { createPostgresAdapter } from '@framers/sql-storage-adapter';

const db = createPostgresAdapter({ connectionString: process.env.DATABASE_URL });
await db.open();
const store = new PostgresVectorStore({ id: 'docs', type: 'postgres', adapter: db, manageSchema: false });

await db.transaction((tx) => store.withStorageTransaction(tx, async (vectors) => {
  await tx.run('UPDATE sources SET version = ? WHERE id = ?', [version, sourceId]);
  await vectors.delete('chunks', undefined, { filter: { sourceId } });
  await vectors.upsert('chunks', passages);
}));

With adapter, the store opens no pool and close() leaves the adapter open. Give it without pool and connectionString: with either as well, initialize() throws vector_connection_ambiguous before it reaches a database. The adapter must be a PostgreSQL adapter with transactions (kind 'postgres' and the transactions capability); any other throws vector_adapter_requires_postgres_transactions.

withStorageTransaction(tx, work) takes the adapter that StorageAdapter.transaction passed to its callback and gives work a view of the store whose statements all run on that transaction's connection. Pass tx, not db: on the adapter itself the view's writes would run on pooled connections and commit at once, outside the transaction, so withStorageTransaction(db, work) throws vector_transaction_required, on the store and on a view of it. The view opens no pool, commits nothing and runs no DDL: like a store with manageSchema: false, it takes the metric from similarityMetric. Its upsert() writes in the caller's transaction instead of opening one, and a search with iterativeScan, efSearch or maxScanTuples sets them with SET LOCAL and sets the earlier values back after the search, so the rest of the transaction keeps its own. The transaction commits when its callback returns and rolls back when the callback throws. When work settles the view stops working: close() and shutdown() do nothing, healthCheck() answers false, checkHealth() answers { isHealthy: false }, fetchByIds() with no ids answers an empty list, and every other call throws vector_transaction_closed.

Make the calls on the view one at a time, awaiting each inside work: they share the transaction's one connection, and two searches with settings at once interleave their SET LOCAL and restore statements there, so that one search can run under the transaction's own values and the transaction can go on under the store's. Keep neither the view nor tx after work, and let a failure reach the transaction's callback; a transaction that catches a failed write and commits anyway is outside this contract. Compute embeddings before the transaction opens, so that it holds its connection for the reads and writes alone. On a store made with pool or connectionString, withStorageTransaction() throws vector_adapter_required.

Tables made by your migrations​

manageSchema: false turns off every statement that changes the schema: initialize() runs no CREATE EXTENSION and makes no collections table, and createCollection() does nothing. CREATE EXTENSION vector and the tables are then yours, and PostgresVectorStore.schemaSql() prints one collection's statements for a migration:

const statements = PostgresVectorStore.schemaSql('chunks', 1536, {
  metric: 'cosine',          // the default; 'euclidean' and 'dotproduct' choose the other operator classes
  tablePrefix: 'app_',
  textSearchConfig: 'simple',
});
// CREATE TABLE IF NOT EXISTS "app_chunks" (...), then CREATE INDEX for "app_chunks_hnsw", "_metadata" and "_fts"

The third argument may also be the metric alone: schemaSql('chunks', 1536, 'cosine'). Give the store the same prefix, text search configuration and similarityMetric: with manageSchema: false it takes the metric from similarityMetric and reads no collections table. schemaSql() throws unless the collection's name, with and without its prefix, is letters, digits and underscores that do not start with a digit, the name with its prefix is at most 54 characters (Postgres keeps 63 bytes of a name, so the longest index name, ending in _metadata, stays whole), and the dimension is a positive integer. dropCollection() throws on a store with manageSchema: false: its tables are dropped by a migration too.

Text search configuration​

textSearchConfig (default 'english') names the configuration of the tsv column that createCollection() adds and of every lexical query; it must be a plain lower-case name. 'simple' lower-cases words and stems none, which suits text in several languages. A column keeps the configuration it was made with, so give a store over an existing table the configuration its column was made with.

Filters on array fields​

$in, $nin, $all and $contains read a field that holds an array as well as one that holds a single value:

OperatorSingle valueArray
$in: [...]the value, as text, is in the listthe array holds a value in the list
$nin: [...]the field is present and its value, as text, is not in the listthe field is present and the array holds no value in the list
$all: [...]no matchthe array holds every value
$contains: vthe text contains vthe array holds v

An array's elements are compared as JSON values, so $in: [5] matches an array that holds the number 5 and not one that holds the string '5'; a single value is compared as text.

await store.query('chunks', embedding, {
  topK: 8,
  filter: { tenantId: 'org_1', aclGroups: { $in: ['acct:42', 'org:org_1'] } },
});

A metadata key in a filter, and a key updateMetadata() sets or removes, must be letters, digits, underscore, dot or hyphen, since a filter's key is written into the SQL; any other key throws.

A key's condition takes $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $all, $exists, $contains and $textSearch, which matches a string value that contains the text, in any case. Any other operator, $in, $nin or $all without an array, and a condition with no operator throw rather than drop out of the SQL, so no search, change or deletion runs wider than its filter.

Deleting and changing by filter​

await store.delete('chunks', undefined, { filter: { sourceId: 'doc_7' } });
await store.updateMetadata('chunks', { sourceId: 'doc_7' }, { aclGroups: ['org:org_1'], folderId: null });

delete() deletes by ids when they are given; otherwise by options.filter, where a filter that yields no condition deletes nothing; otherwise every row when deleteAll is set. updateMetadata() merges the patch into the metadata of every document the filter matches: a key given as null is removed, the others replace the stored values, and the rest stays. It throws when the filter holds no condition, so that no call changes every document.

A filter compiles each comparison of a key's value to an expression on metadata_json->>'key' or metadata_json->'key', which the GIN index on metadata_json does not serve (PostgreSQL: jsonb indexing), so without an index of your own on that expression delete() and updateMetadata() by such a filter read every row of the collection, and a large collection changed this way is better split into narrower ones. $exists compiles to jsonb_exists(metadata_json, 'key'), the function behind jsonb's ? operator, since the SQL storage adapter rewrites every ? in a statement to a placeholder; the index does not serve the function either.

// Every word, each also matching a longer stored word: "chap budg" finds "chapters of the budget".
await store.lexicalSearch('chunks', 'chap budg', { topK: 20, match: 'all', prefix: true, filter: { tenantId: 'org_1' } });

// A question's own words on the hybrid search's lexical leg: any of them may match.
await store.hybridSearch('chunks', embedding, 'what did we decide about the budget', { topK: 8, match: 'any' });

lexicalSearch() searches the tsv column alone, with no embedding, and ranks by ts_rank. The query's words are its runs of letters and digits, lower-cased, at most 32. match asks for every word ('all') or any word ('any', the default), and prefix: true lets each word match a stored word that begins with it. A query with no word returns no document and runs no search.

hybridSearch() takes the same match and prefix for its lexical leg, with match 'any' when only prefix is given, and a query with no word then runs the dense search alone. Without either option its lexical leg reads the query with plainto_tsquery, which asks for every word.

Filtered searches and iterative scans​

pgvector applies a filter after it scans an HNSW index, so a filtered query() or hybridSearch() can return fewer than topK rows: the scan holds hnsw.ef_search candidates (40 by default) whether or not they pass the filter (pgvector: iterative index scans). With iterativeScan (pgvector 0.8.0 and later) the scan goes on until the filtered search has its rows or has visited hnsw.max_scan_tuples (20,000 by default):

const store = new PostgresVectorStore({
  id: 'docs', type: 'postgres', pool, manageSchema: false,
  iterativeScan: 'strict_order', // or 'relaxed_order'
  efSearch: 100,                  // hnsw.ef_search, 1 to 1000
  maxScanTuples: 20000,           // hnsw.max_scan_tuples
});

With any of the three set, query() and hybridSearch() run in a transaction on one connection with SET LOCAL, so the settings never reach the pool's other users. On a view of withStorageTransaction() that transaction is the caller's, and the search sets each setting's earlier value back when it ends. relaxed_order may return rows slightly out of distance order: query() sorts its rows again by similarity, and hybridSearch() returns its rows in fused-score order. lexicalSearch() reads no HNSW index and runs with no setting.

Cloud providers​

Any managed Postgres with pgvector works. Just set the connection string:

ProviderConnection string example
Neonpostgresql://user:pass@ep-cool-grass-123456.us-east-2.aws.neon.tech/neondb?sslmode=require
Supabasepostgresql://postgres:pass@db.xyzabc.supabase.co:5432/postgres
AWS RDSpostgresql://postgres:pass@mydb.cluster-xyz.us-east-1.rds.amazonaws.com:5432/mydb
Google Cloud SQLpostgresql://postgres:pass@/mydb?host=/cloudsql/project:region:instance
Azure Flexible Serverpostgresql://postgres:pass@myserver.postgres.database.azure.com:5432/mydb?sslmode=require

All of these support pgvector. Neon and Supabase have it pre-installed. For RDS, enable the pgvector extension in the parameter group.

Troubleshooting​

ERROR: could not open extension control file "vector"​

pgvector is not installed. On managed services, check that the extension is enabled in your database configuration. For self-hosted:

# Ubuntu/Debian
sudo apt install postgresql-16-pgvector

# macOS (Homebrew)
brew install pgvector

Then run CREATE EXTENSION vector; as a superuser.

ERROR: different vector dimensions​

You changed defaultDimension after creating a collection. pgvector enforces dimension constraints at the column level. Drop and recreate the collection, or create a new collection with the correct dimension.

Connection refused / timeout​

  • Verify the connection string host, port, and credentials.
  • Check that pg_hba.conf allows connections from your IP.
  • For Docker: ensure -p 5432:5432 is set and the container is running.
  • For cloud: check firewall / security group rules.

Pool exhaustion (too many clients already)​

Increase poolSize in the config, or reduce concurrent usage. The default of 10 is usually sufficient for single-agent deployments. Multi-agent setups may need 20-50.

Postgres for the cognitive Brain​

Beyond the PostgresVectorStore, AgentOS runs the entire cognitive Brain on Postgres via three named factories. The Brain class is dialect-agnostic; the factory chooses the backend.

import { Brain } from '@framers/agentos/memory';

const brain = await Brain.openPostgres(
  'postgresql://user:pass@host:5432/db',
  { brainId: 'companion-alice', poolSize: 10 },
);

brainId is required in Postgres mode because all brains share the same schema. The discriminator scopes every query so two brains in the same database stay isolated. Per-file SQLite mode derives brainId from the filename automatically.

An open makes the brain's tables in the first schema on the connection's search path, where Postgres creates a table whose name names no schema (PostgreSQL: the schema search path), and then each table's indexes on the table its name resolves to. A connection whose search path puts a schema of its own ahead of an initialized public (search_path=tenant_a,public) gets every table and index in that schema, though public holds the same names. A search path that does not name pg_temp searches the session's temporary schema first (PostgreSQL: search_path), so on a session holding a temporary table named like a brain table, that session's queries and the open's indexes reach the temporary table. That holds for an open on one session, such as the connection PostgresAdapter.withConnection hands its callback. A pool runs each statement on whichever connection is free, so its connections must share their session state: a temporary table or a search_path that one connection kept would give the brain another table on that connection alone, which is why withConnection asks its callback to undo what it set. An open leaves out the CREATE INDEX statement for an index the table already has, since Postgres takes the table's lock before it checks IF NOT EXISTS, so an open does not wait for a long write transaction on the table to end.

Multi-tenant isolation​

const aliceBrain = await Brain.openPostgres(connStr, { brainId: 'companion-alice' });
const bobBrain = await Brain.openPostgres(connStr, { brainId: 'companion-bob' });

// alice's traces are not visible to bob (and vice versa).
await aliceBrain.run(
  `INSERT INTO memory_traces (brain_id, id, type, scope, content, created_at)
   VALUES ($1, $2, $3, $4, $5, $6)`,
  ['companion-alice', 't1', 'episodic', 'user', 'private to alice', Date.now()],
);

Sharing an adapter pool​

When the application already owns a StorageAdapter (e.g. wilds-ai's foundation store) and wants the brain to share that connection pool:

import { Brain } from '@framers/agentos/memory';
import { createDatabase } from '@framers/sql-storage-adapter';

const adapter = await createDatabase({
  postgres: { connectionString: process.env.DATABASE_URL },
});
const brain = await Brain.openWithAdapter(adapter, { brainId: 'companion-alice' });

Pool contention: the pool is shared across all brains opened against the same adapter. Five brains opened against a max: 10 pool compete for the same 10 connections; one slow query can starve the others. Size the pool for the total concurrent query load across all brains, not per-brain. For high-fan-out deployments (e.g., one process serving 50 active brains), consider a dedicated pool per brain via Brain.openPostgres(connStr, { brainId, poolSize: N }) instead of sharing.

Schema migrations take a pg_advisory_xact_lock keyed on the brainId, which serializes concurrent first-opens of the same brain. The lock is per-brain (different brainIds boot in parallel; same brainId from two workers serializes), so pool sizing should account for one extra connection-second per brain at process startup.

Opens of different brains, from one process or several, set up the tables they share at the same time, and each statement of that setup commits on its own. CREATE ... IF NOT EXISTS is not atomic on Postgres: when two opens make the same table or index, the one that writes the catalog second waits for the other, and fails once the other commits, with unique_violation (23505) on a pg_catalog index, duplicate_table or duplicate_object, and a column added without IF NOT EXISTS fails the same way, as duplicate_column. The open then runs that step again, which finds the object, and runs it again each time it loses such a race, up to eight runs. A step loses at most once for each object it makes; on a fresh database, the step that makes the two indexes of memory_traces.scope_id can lose once for each. The column itself is added with ADD COLUMN IF NOT EXISTS, which takes the table's lock before it looks for the column, so the second of two opens that add it waits for the other and finds it. A migration's transaction that loses a race rolls back and runs again the same way. A unique violation on any other table is not a race, and the open fails with it.

Schema migration​

The first call to Brain.openPostgres (or openSqlite) on an existing v1 database runs an idempotent v1→v2 migration that adds the brain_id column to every brain-owned table and updates primary keys to (brain_id, id). SQLite uses the recreate-table dance; Postgres uses ALTER TABLE ADD COLUMN + ALTER TABLE ADD PRIMARY KEY. Subsequent opens are no-ops once the schema is at v2.

Portable export / import​

The brain's exportToSqlite() and importFromSqlite() decouple portability from live storage. Use a Postgres-backed live brain in production while keeping .wildsoul-style snapshots as portable SQLite files:

const liveBrain = await Brain.openPostgres(connStr, { brainId: 'alice' });
await liveBrain.exportToSqlite('/tmp/alice-snapshot.sqlite');

// Later, fork into a different brain (importing rewrites brain_id).
const forkBrain = await Brain.openPostgres(connStr, { brainId: 'alice-fork' });
await forkBrain.importFromSqlite('/tmp/alice-snapshot.sqlite');

When the import finishes, it rebuilds the full-text column (_tsv) of the importing brain's rows, so memory_search finds each imported trace by the words it holds, a trace the brain held before and the file rewrites included.

See also​