Bring Your Own PostgreSQL (pgvector)

Connect your own Postgres cluster as the storage backend for a HostWebhook Vector Store. Chunks live in your database, the embedding column uses pgvector, and you keep full control of indexing strategy, backups, and access policies. Storage doesn't count against your plan's quota.

When to use this

Pick this backend when:

• You're already on Postgres. Adding pgvector to an existing cluster is one extension install — no second datastore to operate.
• Compliance / data residency. Your data stays inside your VPC, your tenant, your geographic region.
• Joins matter. If your retrieval augmented generation flow needs to join vector search results against tabular data (orders, users, products), keeping both in the same Postgres gives you a single SQL query instead of an app-side merge.
• Tunneled / private clusters. Postgres hosted on a homelab, a Mac on your desk, a private VPC, or a Railway service in a different project. Pair the credential with hostwh expose-tcp and HostWebhook talks to your DB through the reverse tunnel.

Prerequisites

• Postgres 13+. Most managed Postgres services support pgvector out of the box: Neon (free tier), Supabase, AWS RDS (recent), Google Cloud SQL, Railway Postgres, Fly Postgres.
• The vector extension installed in your target database. One-time setup:

CREATE EXTENSION IF NOT EXISTS vector;

Setup walkthrough

1. Create a Postgres credential

Settings → Credentials → + New Credential → pick PostgreSQL Connection. Paste your connection string. The dashboard auto-detects the database name from the URI path.

postgres://user:password@host:5432/database?sslmode=require

For private clusters, optionally pick a tunnel from the Reverse tunnel dropdown. The credential carries the tunnel id; the runtime + the test path both honor it transparently.

2. Create the Vector Store

Settings → Vector Stores → + New store. In the Backend picker pick PostgreSQL (pgvector). Fill in:

• Postgres credential — the one you just created.
• Schema — defaults to public. Stick with that unless your team uses schema-per-environment.
• Table — e.g. vector_chunks. HostWebhook auto-creates this on first use; multiple stores can share the same table because every row carries a store_id filter.
• Embedding dimensions — must match your model. 1536 for OpenAI text-embedding-3-small, 3072 for text-embedding-3-large, 768 for text-embedding-ada-002.

3. Test connection

Click Test connection before saving. Three outcomes:

• ✓ Connected, table found — you're good. Save the store and start ingesting.
• ✓ Connected, table not found — the dashboard surfaces the exact CREATE TABLE + CREATE INDEX SQL and a green Auto-setup table button. Click it to have HostWebhook run the DDL idempotently against your cluster (extension + table + 4 indexes), then re-test automatically. You can also paste the SQL into your own client if you need custom index parameters (HNSW vs IVFFlat).
• ✗ Connection failed — pgvector extension is not installed — same flow: a green Auto-install extension button runs CREATE EXTENSION IF NOT EXISTS vector for you. Needs a role with CREATE EXTENSIONprivileges; on managed Postgres that's usually the database owner. Falls back to a clear permission error + copy-pasteable SQL if your role can't do it.
• ✗ Connection failed — host unreachable — common with private clusters. Use a tunnel (see below).

Schema HostWebhook creates

On first use the backend ensures (idempotently) the following structure. You can run this manually to customize index types or permissions, then mark the store created.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS public.vector_chunks (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id        TEXT NOT NULL,
  content         TEXT NOT NULL,
  embedding       VECTOR(1536) NOT NULL,
  metadata        JSONB NOT NULL DEFAULT '{}'::jsonb,
  source_doc_id   TEXT,
  source_doc_name TEXT,
  source_doc_type TEXT,
  source_hash     TEXT,
  token_count     INTEGER,
  char_count      INTEGER,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS vector_chunks_store_id_idx
  ON public.vector_chunks (store_id);
CREATE INDEX IF NOT EXISTS vector_chunks_store_doc_idx
  ON public.vector_chunks (store_id, source_doc_id);
CREATE INDEX IF NOT EXISTS vector_chunks_store_hash_idx
  ON public.vector_chunks (store_id, source_hash);
CREATE INDEX IF NOT EXISTS vector_chunks_embedding_idx
  ON public.vector_chunks USING hnsw (embedding vector_cosine_ops);

HNSW vs IVFFlat

HostWebhook uses hnsw by default — it's faster on read, more memory-hungry, and doesn't need a separate training step. For very large tables (> 5M chunks) and tight RAM budgets, swap to IVFFlat manually:

-- Drop the HNSW index, create IVFFlat instead.
-- The 'lists' parameter should be ~rows/1000 for good recall.
DROP INDEX IF EXISTS vector_chunks_embedding_idx;
CREATE INDEX vector_chunks_embedding_idx
  ON vector_chunks
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 500);

-- IVFFlat needs ANALYZE after every batch ingest to stay accurate.
ANALYZE vector_chunks;

Security

Same posture as every other BYO backend:

• The connection string is encrypted at rest with AES-256-GCM and decrypted only for the duration of a single query/insert call.
• Marking the credential sensitive strips identity fields (host, database, account label) from the API response while keeping the credential fully functional inside the backend.
• When a tunnel id is attached, the SSRF guard is bypassed (private hosts are intentional behind a tunnel) and the local forwarder hop is plaintext — TLS is restored end-to-end on the customer's side of the agent.
• Schema and table names go directly into SQL because Postgres can't parameterize identifiers; both are validated against [a-z_][a-z0-9_]{0,62} before use. Other values use prepared statements (no SQL injection by construction).

Cost model

Storage is on your bill, not HostWebhook's. We don't count BYO chunks against your plan quota — neither the chunk count nor the 5 MB HostWebhook-managed storage cap, the pot your organization shares between built-in AI Node memory and stores on our cluster. That cap is identical on every plan, so bringing your own database is the way past it; upgrading is not. The latency you see depends on:

• Network distance between Railway (where HostWebhook runs) and your Postgres host. Co-locate when possible.
• Index type — HNSW typical query time is <50ms for 1M chunks; IVFFlat is similar with proper lists tuning.
• Embedding model — 1536-dim vectors are the sweet spot; 3072-dim is ~2x slower at query time but with better recall.

Troubleshooting

"pgvector extension is not installed"

Click Auto-install extension on the test result if your credential's role has CREATE EXTENSION privileges (typical for Neon, Railway, the Postgres docker image, and AWS RDS where rds_superuser was granted). On Supabase the extension is enabled from the dashboard's Database → Extensions panel. If Auto-install returns a permission error, ask your DBA to run CREATE EXTENSION vector; as a superuser, then retry the test.

"Invalid schema/table name"

Schema and table names must match [a-z_][a-z0-9_]{0,62}. Lowercase, underscores, no quotes, max 63 chars (Postgres identifier limit).

"connection refused"

Public host: check the security group / firewall allows Railway egress. Private host: use a tunnel. The credential's Test connection button surfaces the exact error.

"out of memory" during ingest

Likely the HNSW index build hitting a memory ceiling on a small cluster. Drop the index, ingest first, then create it. Or upgrade the cluster — HNSW indexes typically need ~1.5x the embedding column size during creation.