From Rows to Vectors, Part 2: A pgvector CRUD Demo in Node.js

In Part 1 I covered the theory: embeddings, distance operators, IVFFlat and HNSW, and three traps of running pgvector inside the Postgres you already have. Theory is cheap until you have to wire it to an API key, a connection pool and a web page. This post is the running version.

The demo is the live part of my talk "The Vector Database We Didn't Buy: Source Ingestion on Node.js and Postgres" at AI Poland 2026, on 19 November in Warsaw and online. My session is pre-recorded, so this article doubles as the written companion: everything you see on screen is here, with the code.

The repo is public: github.com/brunohsouza/pgvector-demo.

What the demo does

One Postgres table, four operations, and a filtered search:

• Create rows with an embedding of their text.
• Read by meaning: the nearest documents to a question, with a similarity score.
• Update a row, which means a new vector.
• Delete a row, and its vector goes with it.
• Bonus: a search filtered by topic, with iterative index scans switched on.

There are two front ends: a web page and a terminal script. The page shows the SQL that just ran and how long each step took, so the audience sees the statement and the timing next to the result.

Every action follows the same path: the page calls a small Express API, the API gets a vector from the embedding provider, then runs one SQL statement against Postgres. Search, insert and update call the embedding model first. List and delete go straight to the database.

Run it

You need Node 22.9 or later and Docker.

git clone https://github.com/brunohsouza/pgvector-demo.git
cd pgvector-demo
docker compose up -d     # Postgres 17 + pgvector on host port 5433
npm install
cp .env.example .env     # then set the key for one embeddings provider
npm start                # http://localhost:3000

The database listens on port 5433, so it does not clash with a local Postgres on 5432. Both the database and the page bind to 127.0.0.1.

The Compose file is short:

services:
  db:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "127.0.0.1:5433:5432"
    volumes:
      - ./db/init.sql:/docker-entrypoint-initdb.d/init.sql:ro

init.sql only runs CREATE EXTENSION IF NOT EXISTS vector;. The table is created by the app, for a reason I'll get to below.

Pick a provider with the npm script:

Anthropic has no embedding model of its own; its docs point to Voyage AI, which is why Voyage is on the list.

Note: the fake provider hashes words. It proves the SQL and the plumbing work without an API key, but it only matches shared words. It does not understand meaning, so don't judge pgvector with it.

The connection: one pool, one hook

import pg from 'pg'
import pgvector from 'pgvector/pg'

export const pool = new pg.Pool({
  connectionString:
    process.env.DATABASE_URL ?? 'postgres://postgres:postgres@localhost:5433/postgres',
  // Runs, and is awaited, on every new connection before it is handed out:
  // teaches node-postgres to read the vector type.
  onConnect: async (client) => pgvector.registerTypes(client),
})

// Without a listener an idle connection dropped by Postgres (restart, OOM) crashes the process.
pool.on('error', (err) => console.error('Postgres pool error:', err.message))

export const toSql = pgvector.toSql

Two details here. registerTypes runs in onConnect, so every pooled connection knows the vector type before your code gets it, and vector columns come back as plain JavaScript arrays. And the error listener: without it, an idle connection that Postgres drops during a restart takes the whole Node process down with it.

One embed function for every provider

The rest of the app calls one function and never knows which provider is behind it:

// One API call for many texts; returns one vector per text, in order.
// kind is 'document' for stored rows and 'query' for questions: Gemini and
// Voyage embed the two slightly differently, OpenAI ignores it.
export async function embed(texts, kind = 'document') {
  const input = Array.isArray(texts) ? texts : [texts]
  return provider.embed(input, kind)
}

It sends many texts in a single API call, which matters when you seed or re-index a table. It also marks each text as a document or a query. Gemini (RETRIEVAL_DOCUMENT / RETRIEVAL_QUERY) and Voyage (input_type) produce slightly different vectors for the two, and OpenAI ignores the flag.

Gemini needs one more line of care:

const { embeddings } = await gemini.models.embedContent({
  model: MODEL,
  contents: input,
  config: {
    outputDimensionality: DIM, // default is 3072, over the HNSW limit of 2,000
    taskType: kind === 'query' ? 'RETRIEVAL_QUERY' : 'RETRIEVAL_DOCUMENT',
  },
})
// Google asks for vectors below 3072 dimensions to be normalised by hand.
return embeddings.map((e) => normalise(e.values))

gemini-embedding-001 returns 3,072 dimensions by default. Part 1 covered the limit: pgvector can index a vector column with HNSW up to 2,000 dimensions. So the demo asks for 1,536, the same size as OpenAI's small model, and normalises the result as Google's docs ask for truncated vectors.

The table follows the model

The vector size is part of the column type, so the app creates the table instead of init.sql:

CREATE TABLE documents (
  id         bigserial PRIMARY KEY,
  title      text NOT NULL,
  body       text NOT NULL,
  topic      text NOT NULL,
  embedding  vector(1536) NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

The 1536 comes from the provider. On start-up, the app reads the current column type from pg_attribute with format_type(atttypid, atttypmod), and if it is not vector(<dimensions of this provider>), it recreates the table and reloads the sample rows.

This is the rule from Part 1 in code form: whichever model you pick, store and query with the same one. Switch models and you re-embed everything. Vectors from two models live in different spaces, and comparing them returns numbers that mean nothing.

The four operations

All SQL lives in named constants, so the page can show exactly what ran.

Create. Embed every body in one API call, then insert:

export async function createDocuments(docs) {
  const vectors = await embed(docs.map((d) => d.body), 'document')
  const ids = []

  for (const [i, d] of docs.entries()) {
    const { rows } = await pool.query(SQL.insert, [d.title, d.body, d.topic, toSql(vectors[i])])
    ids.push(rows[0].id)
  }
  return ids
}

INSERT INTO documents (title, body, topic, embedding)
VALUES ($1, $2, $3, $4)
RETURNING id::int

Read. Order by cosine distance and limit, which is the shape an HNSW index can serve:

SELECT id::int, title, topic, body,
       1 - (embedding <=> $1) AS similarity
  FROM documents
 ORDER BY embedding <=> $1
 LIMIT $2

<=> is cosine distance, so 1 - distance gives a similarity where higher is closer. The function wraps it with two timers: one around the embedding call, one around the query.

Update. New text means a new vector, written in the same statement:

UPDATE documents
   SET title = $2, body = $3, topic = $4, embedding = $5, updated_at = now()
 WHERE id = $1

Delete. One line. The vector lives in the row, so it goes with the row:

DELETE FROM documents WHERE id = $1

That last one is the quiet argument for keeping vectors in Postgres. With a separate vector store, delete is two writes in two systems, and you own the job that reconciles them when one fails.

What the page shows

The sample data is five short documents across three topics: tax, pets and food. The two-minute flow in the recording:

1. Search "the tax due when you sell shares at a profit". Selling shares comes first (0.769), CGT basics second (0.723), and Puppy training a distant third (0.482). The SQL panel shows the statement, plus embed 364 ms · query 21 ms.
2. Edit Pizza dough into a short note about crypto gains, tagged as tax. The page re-runs the last search, and the document that was a pizza recipe a moment ago now sits in the tax results. One UPDATE, new vector included.
3. Delete Selling shares. The results update straight away. One DELETE.
4. Filter. Choose "Only pets" and search "looking for a young cat". A new kitten comes first (0.624), Puppy training second (0.608).

Two things worth watching in those numbers.

First, the timing. The embedding call takes hundreds of milliseconds, while the query takes tens. On a table this small, the API round trip is almost the whole request. That ratio is your first optimisation target long before the index is.

Second, the kitten. The question never contains the word "kitten" and still finds it, because the match is on meaning. Run the same flow with npm run start:fake and this is the step that breaks.

Note: with five rows, Postgres usually skips the index and scans the table, so these results are exact. The HNSW index is there for the real table, and for the next section.

The filtering trap, fixed in code

Part 1's first production trap: with an HNSW index, the WHERE runs after the index scan. The scan returns ef_search candidates (40 by default), then the filter throws away the ones from other topics, and you get fewer rows than your LIMIT asked for. No error, just fewer rows.

Since pgvector 0.8.0, iterative index scans keep scanning until there are enough matching rows. The demo turns them on for the filtered search only, inside a transaction:

// With a filter, turn on iterative scans first (pgvector 0.8+), or the
// WHERE runs after the index scan and you get fewer rows than you asked for.
async function searchInTopic(q, topic, k) {
  const client = await pool.connect()
  let releaseError
  try {
    await client.query('BEGIN')
    await client.query('SET LOCAL hnsw.iterative_scan = strict_order')
    const { rows } = await client.query(SQL.searchInTopic, [toSql(q), topic, k])
    await client.query('COMMIT')
    return rows
  } catch (err) {
    // ROLLBACK itself fails when the connection is gone; the original error is the one to report.
    await client.query('ROLLBACK').catch(() => {})
    // Passing the error to release() makes pg-pool discard the client instead of reusing a dead one.
    if (isConnectionError(err)) releaseError = err
    if (err.code === '42602' || err.code === '42704') {
      throw Object.assign(new Error('Filtered search needs pgvector 0.8 or later.'), { status: 400 })
    }
    throw err
  } finally {
    client.release(releaseError)
  }
}

SELECT id::int, title, topic, body,
       1 - (embedding <=> $1) AS similarity
  FROM documents
 WHERE topic = $2
 ORDER BY embedding <=> $1
 LIMIT $3

Why the ceremony around one SET:

• SET LOCAL, not SET. A plain SET sticks to the connection, and the connection goes back to the pool. The next request that borrows it inherits your setting without asking for it. SET LOCAL ends with the transaction.
• One client for the whole transaction. pool.query can hand each statement to a different connection, so BEGIN, SET LOCAL and the SELECT must share one checked-out client.
• strict_order keeps results in exact distance order. relaxed_order trades that for recall and speed; the trade-offs are in Part 1.
• Older pgvector. If the setting does not exist, Postgres raises 42704 (unknown parameter) or 42602 (invalid name). The demo turns that into a clear message, and the terminal script prints it and skips the bonus step instead of crashing.
• Dead connections. If the error came from a dropped connection, passing it to release() tells the pool to discard that client instead of handing a dead one to the next request.

Apart from the SET LOCAL line, that is ordinary node-postgres transaction hygiene, and it is the part most tutorials skip.

What the demo leaves out

It is a demo, so it is honest about scale: five rows, one table, one index. It does not cover hybrid search with Reciprocal Rank Fusion, halfvec, binary quantisation or replicas. Those are in Part 1, and they matter once you have real volume. It shows the part every project starts with: the CRUD path, where the vector is just another column that inserts, updates, and deletes with its row.

If you want to see it running, my session at AI Poland 2026 on 19 November walks through this flow, then goes further into source ingestion on the same table. Clone the repo, put in one API key, and the page is up in a few minutes.

And you? When you added semantic search, did the vector go in the row next to the data, or into a separate store with a sync job? If you went the second way, I'd like to know what the reconciliation looked like after the first failed write. Drop a comment if you have questions.

Bruno Souza, Software Engineer