pgvector eCommerce Product Search: Supabase Setup (2026)

Building a custom pgvector ecommerce product search on Supabase allows high-growth merchants to bypass the steep licensing fees of third-party search vendors while retaining full control over their discovery algorithms. By combining Postgres full-text search with vector embeddings, you can deliver sub-100ms hybrid search results directly from your primary database.

Why choose pgvector ecommerce product search over Algolia?

Choosing pgvector ecommerce product search on Supabase over Algolia is primarily a decision of cost control, data ownership, and architectural simplicity. While Algolia charges based on operations and record counts — which scales aggressively for large catalogues — Postgres handles millions of queries within your standard database compute footprint.

In our experience, merchants in the £1M-£15M GMV band often overpay for search licensing when their actual requirements could be solved directly in their primary database. Algolia's pricing scales aggressively with query volume and catalogue updates. If you have a catalogue of 50,000 products and perform frequent inventory syncs, your monthly bill can easily climb into the hundreds of pounds.

However, Algolia is not just a database — it is a polished search product. It gives you out-of-the-box merchandising, analytics, and typo tolerance that you must build yourself if you go the Postgres route. If you do not have dedicated engineering resources to maintain search relevance, rolling your own is a bad decision. But for headless storefronts with engineering capability, pgvector offers a highly performant alternative.

How do I build semantic search for eCommerce with pgvector?

Building semantic search with pgvector requires generating vector embeddings for your product catalogue and storing them in a Postgres database. You then query this table using vector distance operators to find the most semantically similar products to a user's search query.

To start, you must enable the pgvector extension on your Supabase instance. This extension introduces the vector data type, allowing you to store high-dimensional embeddings directly alongside standard product columns. In our work with headless Shopify storefronts, we typically use OpenAI's text-embedding-3-small model, which outputs a 1536-dimensional vector representing about 6.15 kilobytes of data per product.

Once the extension is active, you define your product schema. Here is the SQL structure we use:

CREATE TABLE products ( id bigint PRIMARY KEY, title text NOT NULL, description text, sku text, handle text NOT NULL, embedding vector(1536) );

This approach works exceptionally well for capturing intent. For example, a search for "warm winter wear" matches "wool coats" even if the exact words do not overlap. However, pure semantic search fails on exact SKU matching or specific brand queries where keyword matching is required.

Managing the HNSW index trade-offs on Supabase

Hierarchical Navigable Small World (HNSW) indexes in pgvector provide fast approximate nearest neighbor search at the cost of high memory usage and slow index build times. For production eCommerce catalogues, HNSW is almost always preferred over IVFFlat because it maintains high search accuracy even as product data changes.

In the Supabase vector indexes documentation, HNSW is recommended for most production use cases. It builds a multi-layer graph where each node represents a vector. When pgvector searches this graph, it skips large paths to find nearest neighbors quickly, maintaining a search latency p95 we target under 100ms edge-cached.

To create an HNSW index on Supabase using cosine distance, you run the following query:

CREATE INDEX ON products USING hnsw (embedding vector_cosine_ops);

In pgvector version 0.7.0 and above, you can index vectors with up to 2,000 dimensions, which easily accommodates standard models. However, HNSW indexes are built in memory. If your database RAM is insufficient to hold the index, Postgres will fall back to slow disk-based scans, causing search latency to spike. We typically see build times slow down significantly if you try to index large catalogues on small database instances, meaning you must size your Supabase database tier carefully.

Implementing hybrid search in Postgres with Reciprocal Rank Fusion

Hybrid search combines the semantic depth of vector embeddings with the keyword precision of Postgres full-text search using Reciprocal Rank Fusion (RRF) to merge and score the results. This ensures that queries for exact brand names or SKUs succeed alongside conceptual searches like "warm winter wear".

To implement this, we generate a tsvector from the product title and description. Postgres native full-text search handles the keyword matching, while pgvector handles the similarity search. The RRF algorithm then scores each document based on its rank in both result sets, using a smoothing constant of 60.

Here is the Postgres function we write to execute the hybrid query:

CREATE OR REPLACE FUNCTION hybrid_product_search( query_text text, query_embedding vector(1536), match_limit int ) RETURNS TABLE ( id bigint, title text, handle text, score float ) AS $$ BEGIN RETURN QUERY WITH vector_search AS ( SELECT p.id, ROW_NUMBER() OVER (ORDER BY p.embedding <=> query_embedding) AS rank FROM products p LIMIT match_limit * 2 ), keyword_search AS ( SELECT p.id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(to_tsvector('english', p.title || ' ' || p.description), to_tsquery('english', query_text)) DESC) AS rank FROM products p WHERE to_tsvector('english', p.title || ' ' || p.description) @@ to_tsquery('english', query_text) LIMIT match_limit * 2 ) SELECT p.id, p.title, p.handle, coalesce(1.0 / (60.0 + v.rank), 0.0) + coalesce(1.0 / (60.0 + k.rank), 0.0) AS score FROM products p LEFT JOIN vector_search v ON p.id = v.id LEFT JOIN keyword_search k ON p.id = k.id WHERE v.id IS NOT NULL OR k.id IS NOT NULL ORDER BY score DESC LIMIT match_limit; END; $$ LANGUAGE plpgsql;

The main trade-off of hybrid search is complexity and query planning. Running two separate searches and fusing them in a single database transaction increases CPU usage. If your query volume spikes, this can push latency higher if not properly optimised with caching.

Keeping your Supabase vector search implementation in sync via webhooks

Keeping product embeddings in sync with your storefront requires an asynchronous worker queue triggered by Shopify webhooks. Whenever a product is created, updated, or deleted, Shopify sends a webhook payload that your middleware processes to regenerate and save the updated vector embedding.

According to the Shopify webhooks documentation, you can subscribe to the products/update topic to receive real-time updates. When your endpoint receives the payload, it extracts the updated product details, calls your embedding provider, and executes an upsert query on your Supabase instance.

Here is the general structure of the webhook receiver payload we process:

{ "id": 7890123456, "title": "Merino Wool Winter Coat", "body_html": "A warm winter coat made from 100% organic merino wool.", "handle": "merino-wool-winter-coat" }

This event-driven sync is highly efficient but introduces a synchronization lag. If a product goes out of stock or its price changes, there is a delay of several seconds to minutes before the search index reflects the change. During high-traffic flash sales, this can lead to displaying out-of-stock items in search results if you do not check real-time inventory at the API edge.

When does a custom Postgres search stack fail?

A custom pgvector search stack fails when your catalogue exceeds hundreds of thousands of active SKUs or when your business requires complex, real-time merchandising rules that must be managed by non-technical teams. In these scenarios, the engineering overhead of building custom dashboards for search tuning outweighs any infrastructure savings.

If you have a small catalogue of 5,000 products, pgvector is incredibly fast and cheap. But if you have 500,000 variants with complex location-based inventory and pricing, writing the SQL to handle these filters alongside vector similarity becomes a nightmare. We have previously discussed how to structure a modern advanced search and filter system for eCommerce, but shifting from pure filters to semantic understanding is where search gets interesting.

By default, native Shopify search has a p95 latency of typically 200-400ms, which is acceptable for many smaller merchants. If your annual GMV is under £1M, the complexity of managing a Supabase database sidecar is hard to justify. To understand the wider landscape of semantic discovery, you can read our breakdown of AI search for eCommerce and what actually works.

The next steps for your discovery architecture

To move forward, you should audit your current search latency and licensing costs to determine if a migration to a Postgres-based vector search makes financial and technical sense. Start by benchmarking your existing setup against a small Supabase proof-of-concept using a subset of your product feed.

If you have a dedicated engineering team, rolling a custom search sidecar is a highly rewarding project that can slash your SaaS fees. But do not underestimate the maintenance overhead of managing vector embeddings and tuning database performance. If you want to explore whether a custom search architecture is right for your stack, our team can help you design and deploy a high-performance database sidecar through our eCommerce development services. We can help you evaluate the trade-offs, build the webhook sync pipelines, and ensure your search latency remains low.