Library on Postgres and pgvector#

@vid2context/provider-storage-postgres keeps the library (saved contexts, collections, full-text search, key-value state and embedding vectors) in Postgres. Use it for a hosted or multi-user deployment. For one person on one machine, the SQLite library (library-sqlite.md) is simpler and is the default.

It implements the same Storage contract as every other backend, plus KvStore and VectorStore, so the CLI, MCP server, HTTP API and web app need no changes.

Turn it on#

Set a connection string and start any surface:

export VID2CONTEXT_DATABASE_URL='postgres://user:password@host:5432/dbname?sslmode=require'
npm run cli -- https://www.youtube.com/watch?v=jNQXAC9IVRw

• VID2CONTEXT_DATABASE_URL wins over DATABASE_URL. Use the first when DATABASE_URL already belongs to another app.
• With a URL set, Postgres (priority 10) takes over from SQLite (50) and D1 (20).
• VID2CONTEXT_STORAGE=postgres forces it (and fails with a clear error when no URL is set). Any other VID2CONTEXT_STORAGE value turns it off.
• On first start it creates the schema vid2context (or VID2CONTEXT_PG_SCHEMA), enables the vector extension and creates its tables. Nothing else in the database is touched.

Requirements: Postgres 13 or newer with pgvector 0.7 or newer (0.8 or newer recommended, see Vector search).

Where to run Postgres, free first#

Local Docker (free, offline)#

docker run -d --name vid2context-pg -p 127.0.0.1:5432:5432 \
  -e POSTGRES_PASSWORD=change-me -v vid2context-pg:/var/lib/postgresql/data \
  pgvector/pgvector:pg17
export VID2CONTEXT_DATABASE_URL='postgres://postgres:change-me@127.0.0.1:5432/postgres'

The pgvector/pgvector image is the official Postgres image with pgvector built in. No sslmode is needed on localhost.

Neon (free tier)#

Create a project at neon.tech and copy the connection string from the dashboard. It looks like:

postgres://user:password@ep-name-123456.us-east-2.aws.neon.tech/neondb?sslmode=require

pgvector is available on every Neon project; the storage enables it itself. Neon is also the only option on Cloudflare Workers (see Workers). For a long-running Node server either the direct or the pooled (-pooler) host works.

Supabase (free tier)#

In the project settings, open the database connection strings.

• Session mode (port 5432) or the direct connection: use these for a long-running server.
• Transaction mode (port 6543): fine too. The driver never uses named prepared statements, which transaction poolers reject. Leave VID2CONTEXT_PG_STATEMENT_TIMEOUT_MS unset behind it.

Append ?sslmode=require. pgvector is available; the storage runs create extension if not exists vector on first start. If your role may not create extensions, enable "vector" under Database, Extensions first.

Google Cloud SQL (paid)#

Cloud SQL for PostgreSQL supports pgvector. Connect through the Cloud SQL Auth Proxy (postgres://user:password@127.0.0.1:5432/db) or a private IP with ?sslmode=require. The first start must run as a user that may create extensions (a member of cloudsqlsuperuser); after that a regular user with rights on the schema is enough.

Railway (paid)#

Deploy a Postgres service with pgvector (the pgvector template or the pgvector/pgvector image) and use its DATABASE_URL variable. Railway's plain Postgres image may not include pgvector; if the first start fails with "pgvector is not installed", switch the image.

Alibaba Cloud ApsaraDB RDS for PostgreSQL (paid)#

ApsaraDB RDS for PostgreSQL ships pgvector as the vector extension. Create a database and an account, add your server's address to the whitelist, and use the internal endpoint from inside the same VPC:

postgres://account:password@pgm-xxxx.pg.rds.aliyuncs.com:5432/vid2context?sslmode=require

If extension creation is restricted for your account, create vector once from the console or as the privileged account.

Settings#

The connection string never appears in logs or error messages: errors scrub it and its password, and say which variable to check.

Tenancy#

Every table has a tenant_id column, every primary key starts with it, and every query filters by it. An instance is bound to one tenant, VID2CONTEXT_TENANT, when it is created. Two deployments with different tenants can share one database and schema without seeing each other's videos, collections, keys or vectors, even when they save the same video id.

A multi-user server can keep one pool and switch per request:

import { openPostgresStorage } from '@vid2context/provider-storage-postgres';

const base = await openPostgresStorage(ctx); // tenant from VID2CONTEXT_TENANT
const forUser = base.forTenant(`user:${userId}`); // same pool, other tenant

Tenant ids are plain strings up to 200 characters. Choosing and authenticating them is the caller's job (see hosted mode).

Tables and indexes#

Full-text search#

Each video's segments are indexed with the Postgres text search configuration of its transcript language (english for en, german for de, and so on; languages without one, such as Chinese or Japanese, use simple). Stemming means a search for "elephant" finds "elephants".

search() parses the query with websearch_to_tsquery, which accepts any input without errors:

Results are ranked by ts_rank_cd, normalised to 0 to 1. Each hit has the raw segment in text and a snippet with the matches wrapped in **.

Vector search#

Vectors of different models are never compared. The embedding column has no fixed size so one table holds every model, and the storage creates one partial HNSW index per model on first use:

CREATE INDEX vectors_hnsw_<hash> ON vectors
  USING hnsw ((embedding::vector(1024)) vector_cosine_ops)
  WHERE model = 'embed/ovh/bge-m3';

Why this design: one table keeps tenancy, deletes and backups simple, while the partial expression index gives each model its own graph with the right dimension. Queries repeat the same expression and put the model name in as a literal, so the planner can match the index. On a real Postgres 17 with pgvector this is the plan for a library query:

Limit
  ->  Index Scan using vectors_hnsw_6559e8beb945e022 on vectors
        Order By: ((embedding)::vector(1024) <=> '[...]'::vector(1024))
        Filter: (tenant_id = 'plan'::text)

Details:

• Models with more than 2,000 dimensions use a halfvec index (up to 4,000). Larger vectors are stored and searched exactly, without an index.
• A model keeps one dimension count. Sending a different size for the same model id is an error; give the other embedding a different model id.
• The tenant and video filters apply while the graph is scanned. pgvector 0.8 and newer keeps scanning until enough rows pass the filters (hnsw.iterative_scan). On older pgvector, or when a filter is very selective, a query that comes back short is rerun as an exact scan, so results are always complete.
• Scores are cosine similarity, -1 to 1.

storage.vectorQuerySql(query) returns the exact statements a query runs, so you can EXPLAIN it on your own data.

Migrations#

Migrations are versioned SQL files in providers/storage-postgres/migrations/ (0001_library.sql, ...). They only ever add things. They are applied automatically on start:

1. A quick read of schema_migrations. When it is current, nothing else runs, so a role without DDL rights can use a schema someone else migrated.
2. Otherwise one transaction takes pg_advisory_xact_lock on a key derived from the schema name. A second instance starting at the same moment waits on that lock.
3. Each migration runs inside a DO block that checks schema_migrations after the lock is held, so a migration is applied exactly once even when several instances start together.

The lock is transaction-scoped rather than the session-scoped pg_advisory_lock because transaction poolers and Neon's HTTP driver do not keep a session across transactions.

To add a migration: create the next numbered file, write {{schema}}.table for every table (it is replaced with the configured schema), never drop or rewrite data, then run node providers/storage-postgres/scripts/embed-migrations.mjs. That embeds the SQL into src/migrations.generated.ts so Workers, which have no filesystem, can apply it; a test fails if the two drift apart.

Cloudflare Workers#

Workers cannot open TCP sockets without nodejs_compat, so on Workers the provider uses Neon's HTTP driver and DATABASE_URL must be a Neon URL. The package's workerd and worker export conditions point bundlers at an entry without postgres.js, so the Worker bundle contains no Node built-ins. Set the URL as a secret:

npx wrangler secret put DATABASE_URL

Every request is one HTTPS call to Neon; there is no pool to manage. Migrations run on the first request of each isolate (a single quick read once the schema is current).

Backups#

Everything lives in one schema, so a schema dump is a complete backup:

pg_dump --schema=vid2context --format=custom --file=vid2context.dump "$VID2CONTEXT_DATABASE_URL"
pg_restore --dbname="$TARGET_URL" vid2context.dump

The dump has no extensions in it: run create extension if not exists vector on the target first. Managed hosts add their own backups on top: Neon keeps history you can restore or branch from, Supabase takes daily backups, and Cloud SQL, Railway and ApsaraDB offer automated backups and point-in-time recovery on their paid plans.

Errors#

Testing#

Unit tests run on PGlite (Postgres compiled to WebAssembly, with the real pgvector extension), offline:

npx vitest run providers/storage-postgres

LIVE=1 adds tests against DATABASE_URL when it is set, or against a throwaway pgvector/pgvector container plus Neon's open-source HTTP proxy when Docker is available. They cover the full storage contract on both drivers, concurrent first starts under the advisory lock, the HNSW query plan, and the provider running inside workerd through Miniflare.