Can Postgres handle
vector search?

Keep the eligible rows in view

An exact query finds the nearest eligible vectors under the selected distance metric. It gives us a reference answer for evaluating an approximate index. It doesn't tell us whether the embedding represents what the visitor meant: a mathematically nearest product can still be a poor suggestion. The search explanation separates those questions.

If a tenant filter leaves only a small catalogue, comparing those remaining vectors may be enough. I'd measure that before adding an approximate index. A conventional index on the tenant field can help narrow the rows; the planner decides whether to use it. The total number of vectors across every tenant can be much less informative than how many rows each request needs to consider. PostgreSQL documents ordinary index types and how to inspect a plan.

BEGIN;
SET LOCAL enable_indexscan = off;
SELECT id,
       embedding <=> '[0.2,0.8,0.1]'::vector AS distance
FROM product_embeddings
WHERE tenant_id = 42 AND available
  AND embedding IS NOT NULL
  AND vector_norm(embedding) > 0
ORDER BY embedding <=> '[0.2,0.8,0.1]'::vector
LIMIT 10;
ROLLBACK;

For cosine search, pgvector does not index null or zero vectors; vector_norm(vector) returns the Euclidean norm. This evaluation excludes products without a nonzero embedding from semantic retrieval and tracks their coverage separately. A product awaiting its embedding is not an ANN miss. If it must remain discoverable, provide another route, such as lexical search, while the embedding is generated or repaired. The index eligibility notes and function reference document the conditions.

Use the same non-null, nonzero embedding conditions in both exact and approximate queries, alongside the tenant and availability filters. Keep the embedding model and dataset snapshot the same when comparing answers. Otherwise, a missing product might reflect a stock update or a changed vector rather than approximate retrieval. Record the query plan as well as the duration. EXPLAIN (ANALYZE, BUFFERS) executes the query and reports actual work; its instrumentation also has overhead. The EXPLAIN guide explains those limits.

A short result can be a search problem

When exact retrieval is too expensive, evaluate pgvector's HNSW or IVFFlat indexes. They trade exhaustive distance evaluation for approximate retrieval. In these index scans, filters apply after candidates are found, so a selective tenant or availability condition can leave fewer than the requested ten results. Increasing search effort or enabling iterative scans can help, within configured limits. The filtering documentation describes this behaviour.

That matters for our shop. Suppose a candidate list contains mostly other tenants' products. Removing them is correct, but doesn't fill the empty places. A response containing three products may mean that only three are eligible, or that retrieval stopped before finding the other eligible neighbours. The exact reference distinguishes these cases. A tenant filter still has to be enforced correctly; its effect on retrieval quality is an additional concern, not permission to relax it.

The pgvector changelog records iterative index scans in version 0.8.0. This is a useful change to include when revisiting an older evaluation: the filtered-search path has another way to continue searching. It doesn't establish that your provider has that version or that any particular settings meet your latency and recall requirements. Check the installed extension and rerun the filtered cases. The changelog supplies the release evidence.

I'd try the query shapes that occur in the application: a large shop, a tiny shop, an almost-empty availability filter, and the busiest combination. An average across them can hide a tenant that rarely gets useful results. Partitioning or separate tenant indexes may be candidates, but they introduce more structures to create, maintain and plan queries against. They need their own evaluation rather than a general rule that every tenant gets a partition.

Measure retrieval and the rest of the application together

For each sampled query, compare the returned IDs with the exact eligible neighbours. If an approximate result includes eight of ten reference neighbours, its recall@10 is 0.8 for that query. This is an illustrative calculation. Define how to handle ties at the cutoff and cases with fewer than ten eligible products. Keep expected empty results separate from failures to find existing neighbours.

The decision isn't just which index searches fastest alone. Our catalogue's transactions share resources with vector retrieval. A configuration that meets a search target but makes checkout unreliable fails the application's test. Conversely, an exact query that comfortably meets the target can be a good result; we don't need approximate retrieval merely because it's available.

The index has a life after its first build

Include new products, deletions and replacement embeddings in the workload. PostgreSQL needs vacuuming to reclaim obsolete row versions and maintain its tables. Index creation can also affect live traffic: a normal build blocks writers, while CREATE INDEX CONCURRENTLY permits writes at the cost of extra work and other restrictions. Plan that work rather than treating an initial bulk-load result as normal operation. See routine vacuuming and concurrent index builds.

pgvector uses PostgreSQL's write-ahead log for replication and point-in-time recovery. That is useful integration, but an actual recovery still depends on configured backups, retained logs and a working restore procedure. Test the target environment with its required extension files, then verify rows and search behaviour. Recreating a search index or regenerating embeddings may take longer than restoring the small part of the database you originally tested. pgvector replication support, PostgreSQL recovery and extension installation requirements establish the pieces.

There is a specific current maintenance issue to account for. The maintainer's 1 October 2026 report identifies an IVFFlat index-build buffer overflow affecting pgvector 0.8.6 and earlier, fixed in 0.8.7. The reported precondition is a database user able to create an IVFFlat index; the issue can lead to arbitrary code execution. The maintainer recommends upgrading affected installations when possible. This is documented release evidence, not a finding about your installed database. The maintainer's report gives the scope.

A managed provider decides which extension versions you can install and how you upgrade them. RDS, for example, documents version-dependent permissions and extension allowlists. Confirm availability, the actual extension version, and the upgrade path on your chosen service. An upstream release date doesn't prove a managed deployment includes the fix. RDS extension management illustrates those constraints.

What would change the starting recommendation?

For our catalogue, I'd keep PostgreSQL with pgvector in the evaluation first. The records, eligibility conditions and vectors can live together, and the team can investigate retrieval using the database it already operates. Generating embeddings can still be asynchronous; saving a product and its eventual embedding in the same database doesn't make that generation instantaneous. The relational explanation follows what transactions do protect.

I'd compare a separate retrieval service if the measured workload needs independent scaling or resource isolation, filtered recall remains unacceptable, or index maintenance and recovery don't fit the team's targets. A separate service then has to demonstrate those benefits while accounting for delivery delays, deletes, authorization filters and rebuilding its own copy. There is no row-count threshold in the evidence collected here that settles that tradeoff for every application.

My judgement is therefore conditional: PostgreSQL has the mechanisms to be a serious option, especially when retrieval belongs close to existing relational data. Keep it if the filtered workload meets the agreed quality and operating targets. Reconsider it when measured shortcomings justify another system. Feature documentation supports running that evaluation; it doesn't substitute for the result.