pgvector in Node.js: find similar reviews and recurring complaints across app and extension stores

When the same complaint arrives in five different words across three stores, keyword search misses most of it. "Stopped working after the update", "popup is blank since 2.4" and "does not work anymore" are one bug, and only one of them contains the word "update". Embeddings solve that by turning each review into a vector, and pgvector lets you store and search those vectors in the PostgreSQL database you already run. Using pgvector in Node.js takes plain SQL and one small npm package.

This pgvector Node.js guide builds a small review similarity system: a schema for reviews from the Chrome Web Store, Edge Add-ons, Firefox Add-ons, Google Play and the App Store, a job that embeds new reviews with OpenAI or Gemini, queries for similar reviews and near duplicates, a way to group complaints into issues and count them per version, and advice on indexes, including the new pg_vectorsearch extension released on October 13, 2026. The SQL was run on PostgreSQL 18.6 with pgvector 0.8.7 (through PGlite) on October 18, 2026.

Key takeaways

• pgvector adds a vector type and distance operators to PostgreSQL. For review search, use cosine distance (<=>) and keep embeddings next to the review rows.
• Store 768-dimension halfvec embeddings: half the size of vector, plenty for short reviews, and both OpenAI and Gemini can return that size.
• Filter by listing first. A review only needs to be compared with reviews of the same app or extension.
• There is no universal "duplicate" threshold. Pick one by reading pairs from your own data.
• HNSW is the default index choice. The new pg_vectorsearch is alpha and needs PostgreSQL 18 or later, so watch it rather than ship it.

Why pgvector for review analysis#

Review text is short, messy and repetitive, which suits embeddings well. Once each review is a vector, three useful questions become one SQL query each:

• What else did people say about this? Find the 10 reviews closest to one review.
• Is this the same review again? Find pairs that are almost identical, across stores or from the same user.
• Which complaint is growing? Assign each new low-rating review to the nearest known issue and count by app version.

You could use a separate vector database, but reviews already live in rows with a store, a listing, a rating, a version and a date. The pgvector README sums up the appeal: you get vector search "plus ACID compliance, point-in-time recovery, JOINs, and all of the other great features of Postgres." Joins and WHERE clauses on those columns are exactly what review analysis needs.

If you only want labels such as "bug" or "feature request" on each review, an LLM classifier is simpler, and our guide to app review analysis with an LLM covers it. Embeddings shine when you do not know the categories in advance.

Set up pgvector and a reviews table#

pgvector is available on most managed PostgreSQL services and installs from source or package managers elsewhere. Enable it once per database, then create the table:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE reviews (
  id              bigserial   PRIMARY KEY,
  store           text        NOT NULL,  -- 'chrome', 'edge', 'firefox', 'google_play', 'app_store'
  listing_id      text        NOT NULL,
  review_key      text        NOT NULL,  -- the store's own ID, or a hash of author + date + text
  rating          smallint    NOT NULL CHECK (rating BETWEEN 1 AND 5),
  body            text        NOT NULL,
  app_version     text,
  posted_at       timestamptz NOT NULL,
  embedding       halfvec(768),
  embedding_model text,
  UNIQUE (store, listing_id, review_key)
);

CREATE INDEX reviews_listing_posted ON reviews (listing_id, posted_at DESC);
CREATE INDEX reviews_embedding ON reviews USING hnsw (embedding halfvec_cosine_ops);

CREATE TABLE review_issues (
  id         bigserial    PRIMARY KEY,
  listing_id text         NOT NULL,
  label      text         NOT NULL,
  centroid   halfvec(768) NOT NULL
);

A few choices worth explaining:

• halfvec instead of vector. halfvec stores half-precision floats. In our test, one 768-dimension halfvec took 1,544 bytes, against roughly 3 KB for vector. For short reviews the precision loss does not matter.
• 768 dimensions. OpenAI's text-embedding-3-small defaults to 1,536 and Gemini's embedding models to 3,072, but both accept a smaller size. Google recommends 768, 1,536 or 3,072 in its embeddings guide.
• embedding_model column. Vectors from different models are not comparable. Recording the model lets you re-embed safely when you switch.
• review_key and the unique constraint. Review collectors run every day. The constraint makes re-imports harmless, the same idempotent pattern as in our guide to PostgreSQL time series for daily store stats.

Getting reviews into the table is the collection step. Our guides to Chrome extension review notifications and App Store and Google Play review alerts cover where each store's reviews come from.

Embed new reviews with pgvector in Node.js#

The embedding job picks up reviews without a vector, sends them to the model in one batch and writes the vectors back in one statement:

// embed-reviews.js: give every new review an embedding, 100 at a time.
import pg from 'pg';
import pgvector from 'pgvector';
import OpenAI from 'openai';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const openai = new OpenAI();
const MODEL = 'text-embedding-3-small';

async function embed(texts) {
  const res = await openai.embeddings.create({ model: MODEL, input: texts, dimensions: 768 });
  return res.data.sort((a, b) => a.index - b.index).map((d) => d.embedding);
}

export async function embedPendingReviews(embedFn = embed, model = MODEL) {
  const { rows } = await pool.query(
    `SELECT id, body FROM reviews
     WHERE embedding IS NULL AND length(body) >= 20
     ORDER BY id LIMIT 100`,
  );
  if (rows.length === 0) return 0;

  const vectors = await embedFn(rows.map((r) => r.body.slice(0, 4000)));
  await pool.query(
    `UPDATE reviews AS r
     SET embedding = v.embedding::halfvec, embedding_model = $3
     FROM unnest($1::bigint[], $2::text[]) AS v(id, embedding)
     WHERE r.id = v.id`,
    [rows.map((r) => r.id), vectors.map((v) => pgvector.toSql(v)), model],
  );
  return rows.length;
}

Notes on the details:

• pgvector.toSql() from the pgvector Node.js package turns an array into the [0.1,0.2,...] text form that PostgreSQL casts to halfvec. The package also supports Knex, Kysely, Prisma, Drizzle, Sequelize and others. Call pgvector.registerTypes(client) from pgvector/pg only if you read vectors back into JavaScript.
• Skip very short reviews. "Great" and "ok" carry no signal and would cluster together with every other one-word review.
• Sort by index. The OpenAI response includes the position of each input, so sorting keeps vectors aligned with rows.
• Cost is small. OpenAI's embeddings guide puts text-embedding-3-small at about 62,500 pages per dollar, and a review is a fraction of a page.

To use Gemini instead, swap the embed function. Each text must be wrapped in its own Content object: the guide warns that "Adding multiple inputs directly to the contents parameter produces one aggregated embedding for all inputs."

import { GoogleGenAI } from '@google/genai';

const ai = new GoogleGenAI({});

async function embedWithGemini(texts) {
  const res = await ai.models.embedContent({
    model: 'gemini-embedding-2',
    contents: texts.map((t) => ({ parts: [{ text: `task: sentence similarity | query: ${t}` }] })),
    config: { outputDimensionality: 768 },
  });
  return res.embeddings.map((e) => e.values);
}

gemini-embedding-2 does not accept the task_type field, so the task goes in a text prefix. Google lists the sentence similarity prefix for comparing texts and says not to use it for search or retrieval.

Run the job from your existing scheduler after each review import. If you trace your model calls, wrap the embedding request in a span as in our guide to LLM observability with OpenTelemetry, with operation: 'embeddings', so you can see its cost next to the rest of your AI usage.

Query 1: find reviews similar to one review#

The cosine distance operator <=> returns 0 for identical direction and grows as texts differ. This query returns the 10 reviews of the same listing closest to review $1:

SELECT r.id, r.store, r.rating, r.app_version, r.body,
       round((r.embedding <=> q.embedding)::numeric, 3) AS distance
FROM reviews q
CROSS JOIN LATERAL (
  SELECT * FROM reviews r
  WHERE r.listing_id = q.listing_id AND r.id <> q.id AND r.embedding IS NOT NULL
  ORDER BY r.embedding <=> q.embedding
  LIMIT 10
) r
WHERE q.id = $1
ORDER BY distance;

In our test, with synthetic vectors built around three topics, the query for "Stopped working after the last update" returned the identical review from a later version first, then the Edge review "Does not work anymore after updating" and "Broken since 2.4, popup is blank", with the dark mode requests and the five-star review far behind. That is the behavior you want: one complaint, three phrasings, two stores.

To search by a phrase instead of a review, embed the phrase with the same model and pass the vector as a parameter in place of q.embedding.

Query 2: catch near-duplicate reviews#

Near duplicates are worth finding for two reasons: the same user posting the same text on several stores (one person, not five), and coordinated or copy-pasted reviews. For each recent review, find its single closest earlier review:

SELECT a.id, a.store, a.body, b.id AS closest_id, b.store AS closest_store,
       round((a.embedding <=> b.embedding)::numeric, 3) AS distance
FROM reviews a
CROSS JOIN LATERAL (
  SELECT r.id, r.store, r.embedding FROM reviews r
  WHERE r.listing_id = a.listing_id AND r.id < a.id AND r.embedding IS NOT NULL
  ORDER BY r.embedding <=> a.embedding
  LIMIT 1
) b
WHERE a.listing_id = $1
  AND a.posted_at >= now() - interval '30 days'
ORDER BY distance
LIMIT 50;

The question everyone asks next is what distance counts as a duplicate. There is no universal number: it depends on the model, the dimension count and the language. Run this query, read the top 50 pairs, and note where pairs stop being "the same thing". That cut-off is your threshold. Repeat the exercise whenever you change the embedding model.

Query 3: group complaints into issues and count them per version#

Clustering every review is possible, but a simpler loop works better for a product team: a person names an issue once, from a few example reviews, and the database assigns new reviews to the nearest named issue.

Create an issue from example reviews. pgvector's avg aggregate works on halfvec, so the centroid is one query:

INSERT INTO review_issues (listing_id, label, centroid)
SELECT listing_id, $2, avg(embedding)
FROM reviews
WHERE id = ANY($1::bigint[])
GROUP BY listing_id
RETURNING id;

Then count recent low-rating reviews per issue and version, keeping only those within your threshold ($2):

SELECT i.label, r.app_version, count(*) AS reviews
FROM reviews r
CROSS JOIN LATERAL (
  SELECT label, centroid FROM review_issues
  WHERE review_issues.listing_id = r.listing_id
  ORDER BY centroid <=> r.embedding
  LIMIT 1
) i
WHERE r.listing_id = $1 AND r.rating <= 2
  AND (r.embedding <=> i.centroid) < $2
GROUP BY i.label, r.app_version
ORDER BY i.label, r.app_version;

The output is a small table such as "Broken after update: 3 reviews on 2.4.0, 1 on 2.4.1". When the count for an issue jumps on a new version, you have a regression with quotes attached. Reviews that are far from every issue are the interesting ones: list them weekly and name new issues from them. Our guide to reading review and rating trends covers how to read those counts against rating changes.

Choose an index: none, HNSW, IVFFlat or the new PRISM#

By default pgvector does exact search, which the README notes "provides perfect recall". Whether you need an approximate index depends on how many vectors each query scans, and with a listing_id filter that is usually one app's reviews, not the whole table.

HNSW settings that matter#

The README lists the HNSW defaults: m of 16 and ef_construction of 64 at build time, and hnsw.ef_search of 40 at query time. Raise ef_search for better recall at the cost of speed. HNSW supports up to 2,000 dimensions for vector and 4,000 for halfvec, so 768 is comfortable.

Filtering is the trap. The README explains that with approximate indexes, "filtering is applied after the index is scanned", so a WHERE that matches 10% of rows returns about 4 rows on average with the default ef_search of 40. Since version 0.8.0, iterative index scans fix this:

SET hnsw.iterative_scan = relaxed_order;

With relaxed_order, results can be slightly out of order, so re-sort them in an outer query if exact order matters. Keep pgvector current, too: 0.8.x releases in 2026 fixed several HNSW vacuuming and IVFFlat build issues, according to the changelog.

The new option: pg_vectorsearch#

On October 13, 2026, Tiger Data released pg_vectorsearch 0.1.0, and its launch drew a busy Hacker News discussion. It adds a PRISM index, which its README describes as "conceptually similar to pgvector's IVFFlat" with quantization and partitioning, and it can index existing pgvector vector and halfvec columns without rewriting them. Queries using pgvector's operators keep working.

Read the fine print before planning a migration. The README marks it "alpha. Meant for testing, not production", it requires PostgreSQL 18 or later, and the 0.1.0 changelog lists known issues, including that a WHERE filter "can return fewer rows than LIMIT" and that it indexes "at most 1968 dimensions". It is also not on most managed PostgreSQL services yet, which several commenters pointed out. For a review table filtered by listing, HNSW or no index at all is the right choice today.

A checklist before you ship#

1. Enable vector, add halfvec(768) and embedding_model columns.
2. Embed reviews longer than about 20 characters, in batches, after each import.
3. Use cosine distance and always filter by listing.
4. Pick a duplicate threshold from your own pairs and write it down with the model name.
5. Name a handful of issues from example reviews and count them per version.
6. Add HNSW when per-listing scans get slow, and turn on iterative scans if you filter.
7. Re-embed everything when you change models, never mix vectors.

Where Extenify fits#

pgvector answers what users are saying. You still need the reviews and the context around them: which version shipped when, how the rating moved and what competitors' users complain about. Extenify collects store listing data every day for your apps and extensions and your competitors' on the Chrome Web Store, Edge Add-ons, Firefox Add-ons, Google Play and the App Store, including reviews, ratings and versions, with extension review monitoring alerts to email, Slack, Discord, Telegram or Teams. It is a browser extension analytics dashboard that gives your embedding job a steady feed and your issue counts a timeline. Every plan starts with a free trial, and the pricing page has the details.

Frequently asked questions#

How do I use pgvector with Node.js?#

Enable the extension with CREATE EXTENSION vector, add a vector or halfvec column, and use the pgvector npm package to convert arrays with pgvector.toSql() before passing them as query parameters to pg or your ORM. Order results with a distance operator such as <=> and a LIMIT.

Should I use vector or halfvec?#

For text embeddings of reviews, halfvec is a good default: half the storage and memory, with negligible quality loss for similarity search. Use vector if you need more than half-precision or a library that only supports it.

What distance should I use for OpenAI or Gemini embeddings?#

Cosine distance (<=>) with the matching _cosine_ops index. OpenAI recommends cosine similarity, and cosine ignores vector length, so it works even if a shortened embedding is not normalized.

Do I need a vector index for pgvector?#

Not always. Without one, pgvector does exact search, which is fast enough when a filter such as listing_id limits each query to a few thousand rows. Add HNSW when queries slow down, and enable iterative index scans if you combine it with filters.

Is pg_vectorsearch better than pgvector?#

It is an index that works alongside pgvector rather than a replacement for its types and operators. Its authors report faster queries than HNSW on very large datasets, but version 0.1.0 is alpha, requires PostgreSQL 18 and has known issues with filtered queries. Test it, but keep HNSW in production for now.

Summary#

pgvector turns your PostgreSQL database into a place to search reviews by meaning. Store 768-dimension halfvec embeddings next to each review with the model name, embed new reviews in batches from Node.js, and use cosine distance with a listing filter for similar reviews, near duplicates and issue counts per version. Choose duplicate thresholds from your own data, start without a vector index or with HNSW plus iterative scans, and keep an eye on pg_vectorsearch as it matures.

Image credits#

• Cover photo: Sharpened colored pencils in a radial bundle by Orianna Radomile on the WordPress Photo Directory, CC0.