Deduplicating feature requests with pgvector: the threshold is a trap

Two of our users asked for the same thing last month. One wrote "please add a way to export the report as a spreadsheet." The other wrote "CSV download for reports?" I filed them as separate requests, because I read them three weeks apart and did not connect them. That is the whole problem with a feedback board. Duplicates do not announce themselves. They arrive slowly, in different words, and by the time you notice, the votes are split three ways and the one thing everybody wants looks like three things nobody cares much about. I spent a weekend building semantic dedup for this, mostly to understand how it works before deciding whether to adopt something that already does it. Here is what I got wrong. String matching does not survive contact with real users The obvious first attempt is trigram similarity, which Postgres gives you for free: CREATE EXTENSION IF NOT EXISTS pg_trgm; SELECT title, similarity(title, 'CSV download for reports') FROM feature_request ORDER BY similarity(title, 'CSV download for reports') DESC LIMIT 5; On my two examples this scores about 0.13. They share almost no characters. Meanwhile "export report to PDF" scores higher than either, because it shares the words export and report while asking for something completely different. Lexical similarity measures how things are spelled. Duplicate feature requests are duplicated in meaning and almost never in spelling, because two people describing the same frustration will reliably reach for different words. Embeddings, and the setup that actually matters The fix is to compare meaning, which means embeddings and pgvector. CREATE EXTENSION IF NOT EXISTS vector; ALTER TABLE feature_request ADD COLUMN embedding vector(1536); CREATE INDEX ON feature_request USING hnsw (embedding vector_cosine_ops); Two notes on that index, both of which cost me time. HNSW builds slower and eats more memory than IVFFlat, but it does not need training data to be present before you build it. On a table that starts empty and grows one request at a time, IVFFlat's list centroids are computed from whatever happens to be there at build time, which for a new board is nothing useful. Use HNSW unless you are backfilling a large corpus in one shot. The operator has to match the index. vector_cosine_ops pairs with . If you build a cosine index and then query with (L2 distance), Postgres will not error. It will silently do a sequential scan and you will conclude that pgvector is slow. Embedding both the title and the body is worth it, but truncate: const input = [title, body ?? ""].join("\n\n").slice(0, 8000); const res = await openai.embeddings.create({ model: "text-embedding-3-small", input, }); Titles alone are too short to carry much signal. "Dark mode" and "Night theme" embed close together, but "Export" and "Download" do not, because a bare verb has almost no context to work with. The threshold is a trap Here is the part every tutorial skips. You will search for a cutoff, find someone saying 0.85 cosine similarity means duplicate, and hardcode it. It will not work, for two independent reasons. Similarity scores are not comparable across text lengths. A pair of three-word titles and a pair of three-paragraph descriptions do not live on the same scale. Short texts cluster high, because there is less content to disagree about. If you tune your threshold on short requests, long ones will never trip it, and vice versa. In my data the same conceptual distance landed around 0.91 for title-only pairs and around 0.74 once bodies were included. High similarity does not mean same request. This one is worse, because it fails in the direction that damages trust: Pair Cosine Same request? "Export to CSV" / "Export to Excel" very high No. Different file format, different work "Dark mode" / "The UI burns my eyes at night" moderate Yes "Add SSO" / "Add SAML login" very high Probably, but not certainly "Slack integration" / "Discord integration" very high No. Not even close Anything with a named entity in it breaks the assumption badly. Slack and Discord occupy nearly the same region of embedding space, and they are completely different pieces of work. A pure threshold merges them and you have just told two sets of users their request is the other one's request. What I ended up with Two changes, and the second matters more than the first. Do not auto-merge. Suggest. Retrieve neighbours, show them to a human, let the human decide: SELECT id, title, 1 - (embedding $1) AS similarity FROM feature_request WHERE status 'merged' ORDER BY embedding $1 LIMIT 5; No WHERE similarity > x clause at all. Rank, take the top handful, and put a person in the loop. The cost of a missed merge is a duplicate. The cost of a wrong merge is a user being told their idea is something it isn't. Run it at submission time, not as a cleanup job. This is the actual insight, and it took me embarrassingly long to see. If you dedupe nightly, you are cleaning up a mess. If you run the same query while the user is still typing, and show "3 people asked for something similar" with the existing requests listed, a good fraction of them will click the existing one and upvote it instead of creating a new one. The dedup problem is much easier to prevent than to solve. The same vector search, moved earlier by thirty seconds, changes from a fuzzy classification problem into a UI affordance. On not building this Having built it, I am not going to run it. It is a solved problem, my products are not feedback boards, and the maintenance is real: embeddings need backfilling when you change models, and model changes are not optional forever. So I have been looking at what already implements it. Canny and Featurebase both do dedup, but their pricing scales with tracked users or admin seats. Fider is open source but is a feedback board only, with no roadmap or changelog attached. FeedLog is the closest to what I described here: MIT licensed, self-hostable, and it runs the similarity search at input time rather than as a batch job, which is the part I care about. Its hard dependency is Postgres 17 with pgvector, which will be unsurprising if you read this far. The AI features are optional; without an API key the board and voting still work, they just stop suggesting merges. I am a few weeks into evaluating it, not a year, so treat that as a pointer rather than a recommendation. The takeaway I would actually defend: if you are building this yourself, skip the threshold. Rank, show, and let a person press the button. And move it to the moment of submission, where it stops being a machine learning problem.