Ecommerce search engine: typo tolerance, facets and vectors in SQL
An ecommerce search engine turns the few words a shopper types into the products they mean, in a useful order, with a count next to every filter. SereneDB does it in SQL over the product table: BM25 ranking that forgives typos and knows the store's synonyms, facet counts from the index, and vector similarity in the same query.
curl https://install.serenedb.com | shWhat does an ecommerce search engine do? Five searches over one catalog
An ecommerce search engine takes the words a shopper types, misspelled, in their own vocabulary or as a description of what they want, and returns the matching products in a useful order, with counts for every filter. Below is a demo store of 24 products and five searches. Pick one to run it; each is one SQL statement.
SELECT id, title, price, round(BM25(products_idx.tableoid)::numeric, 2) AS bm25 FROM products_idx WHERE title @@ (ts_levenshtein('wireles', 1) && ts_levenshtein('hedphones', 1)) ORDER BY bm25 DESC, id;
| id | title | price | bm25 |
|---|---|---|---|
| 6 | Kids' wireless headphones | 39.00 | 1.62 |
| 1 | Wireless noise-cancelling headphones | 199.00 | 1.45 |
ts_levenshtein matches each word within one edit of a term in the index, wireles to wireless and hedphones to headphones, and && keeps the products that match both. BM25 puts the shorter title first.SELECT id, title, brand, price FROM products_idx WHERE title @@ 'trainers' ORDER BY BM25(products_idx.tableoid) DESC, id;
| id | title | brand | price |
|---|---|---|---|
| 7 | Road running trainers | Stride | 120.00 |
| 11 | Canvas slip-on sneakers | Stride | 55.00 |
| 12 | Kids' light-up sneakers | Lumen | 45.00 |
SELECT CASE WHEN category IS NOT NULL THEN 'category' WHEN brand IS NOT NULL THEN 'brand' ELSE 'price_band' END AS facet, coalesce(category, brand, price_band) AS value, count(*) AS n FROM products_idx WHERE title @@ 'running' GROUP BY GROUPING SETS ((category), (brand), (price_band)) ORDER BY facet, n DESC, value;
| facet | value | n |
|---|---|---|
| brand | Stride | 4 |
| brand | Northpeak | 1 |
| category | footwear | 2 |
| category | audio | 1 |
| category | kitchen | 1 |
| category | outerwear | 1 |
| price_band | $100–200 | 3 |
| price_band | $50–100 | 1 |
| price_band | under $50 | 1 |
GROUPING SETS query counts them per category, brand and price band, and the counts come from the index's term dictionary without reading a row: the numbers a sidebar prints next to each filter.SELECT id, title, round((emb <-> [0.00, 0.05, 0.90, 0.25]::FLOAT[4])::numeric, 3) AS distance FROM products_idx ORDER BY distance, id LIMIT 4;
| id | title | distance |
|---|---|---|
| 19 | Vacuum flask, 1 litre | 0.071 |
| 20 | Leak-proof travel mug | 0.200 |
| 21 | Thermal coffee carafe | 0.255 |
| 23 | Gooseneck electric kettle | 0.274 |
WITH fused AS ( SELECT id, 'bm25' AS list, RANK() OVER (ORDER BY s DESC) AS rank FROM ( SELECT id, BM25(products_idx.tableoid) AS s FROM products_idx WHERE title @@ plainto_tsquery('running headphones') ORDER BY s DESC LIMIT 10) lex UNION ALL SELECT id, 'vector', RANK() OVER (ORDER BY dist) FROM ( SELECT id, emb <-> [0.70, 0.70, 0.00, 0.05]::FLOAT[4] AS dist FROM products_idx ORDER BY dist LIMIT 10) vec ) SELECT p.id, p.title, string_agg(f.list || ' #' || f.rank, ' + ' ORDER BY f.list) AS ranks, round(SUM(1.0 / (60 + f.rank)), 4) AS rrf FROM fused f JOIN products p ON p.id = f.id GROUP BY p.id, p.title ORDER BY rrf DESC, p.id LIMIT 5;
| id | title | ranks | rrf |
|---|---|---|---|
| 4 | Bone conduction running headphones | bm25 #1 + vector #1 | 0.0328 |
| 2 | Wireless earbuds with charging case | vector #2 | 0.0161 |
| 6 | Kids' wireless headphones | vector #3 | 0.0159 |
| 1 | Wireless noise-cancelling headphones | vector #4 | 0.0156 |
| 22 | Collapsible running bottle | vector #5 | 0.0154 |
What an ecommerce search engine needs from its database, and how SereneDB does it in SQL
Behind a store's search box the engine has to forgive typos, know the store's synonyms, suggest as the shopper types, count facets, rank by relevance and by meaning, and see price and stock changes quickly. Lists of ecommerce search tools compare products; these are the jobs, each with the SereneDB feature that does it and its docs.
| Need | How SereneDB does it | Looks like |
|---|---|---|
| Typo tolerance | ts_levenshtein matches a word within an edit distance of an indexed term; a swap of two letters counts as one edit. Docs → | title @@ ts_levenshtein('runing', 1) |
| Synonyms | A synonym step in the text search dictionary expands words in the indexed text and in the query alike. Docs → | expand_solr_synonyms('sneakers, sneaker, trainers, trainer |
| Autocomplete | Prefix suggestions walk the term dictionary of the search log, ranked by how often each query was searched. Docs → | ts_dict_agg(query) … WHERE query LIKE 'hea%' |
| Facet counts | Counts per category, brand and price band come from the term dictionary, for the catalog or for one search's results. Docs → | GROUP BY GROUPING SETS ((category), (brand), (price_band)) |
| Filters | Stock and price bands are indexed columns that generated columns keep in step with every write. Docs → | availability @@ 'in stock' |
| Relevance tuning | ^ boosts one field over another, and BM25 is a number you can multiply by a business signal such as sales. Docs → | BM25(products_idx.tableoid) * ln(10 + sales_30d) |
| Semantic search | Embeddings sit in the same index as the text, and <-> orders products by distance to the query's vector. Docs → | emb <-> [0.00, 0.05, 0.90, 0.25]::FLOAT[4] |
| Hybrid ranking | Reciprocal rank fusion adds 1 / (60 + rank) from a BM25 list and a vector list. Docs → | SUM(1.0 / (60 + f.rank)) |
| Fresh prices and stock | Writes reach search at the next refresh, every 1000 ms by default; VACUUM (REFRESH_TABLE) publishes them at once. Docs → | VACUUM (REFRESH_TABLE) products; |
| Search analytics | The search log is a table, so zero-result queries and top searches are GROUP BY queries beside the catalog. Docs → | WHERE results = 0 GROUP BY query |
| Your stack's driver | SereneDB speaks the PostgreSQL wire protocol, so the PostgreSQL driver of your store's backend connects. Docs → | psql -h localhost -p 7890 -U postgres |
Ecommerce search architecture: one product table, one index, SQL out
The catalog is one table, and one inverted index holds its text with the store's dictionary, the facet columns and the embeddings. Typo-tolerant search, facet counts, the nearest products and their fusion are SELECT statements that the storefront's backend sends over the PostgreSQL protocol. Hover a stage, or a lane inside the index.
Prices and stock change in place
The catalog is an ordinary table that the store writes to with INSERT and UPDATE. A write reaches search at the next refresh, every 1000 ms by default; VACUUM (REFRESH_TABLE) publishes it at once.
A vector beside the text, in the same row
Each product carries its embedding in a fixed-size FLOAT[N] column, from your model or from ai_embed() in SQL. The demo store writes four-dimensional vectors by hand so the distances stay readable.
One table: text, facets, price, stock, vector
Generated columns derive the price band and availability from price and stock, so an UPDATE of either keeps the facets right. Facet columns are NOT NULL, which lets the counts come straight from the term dictionary.
The search log is a table too
The backend inserts one row per search: the query, stored as a single keyword, and how many products it returned. Autocomplete and the zero-result report read it.
Text, facets and vectors in one index
Title and description go through the shop_en dictionary: lowercase, accents folded, and synonyms expanded. Brand, category, price band and availability are indexed whole, and the embedding gets an ivf vector index, all in one CREATE INDEX.
Typos and synonyms in the match
ts_levenshtein matches each word within an edit distance, && requires all of them, and BM25 ranks what matched. Synonyms need nothing in the query: the dictionary already expanded them.
Every facet count in one query
GROUP BY GROUPING SETS counts category, brand and price band over the search's matches in one pass, and EXPLAIN shows the counts read from the term dictionary (TsDict) without opening a row.
Nearest products, then fused with BM25
emb <-> query is the distance from each product to the query's vector, so semantic search is an ORDER BY with a LIMIT. Reciprocal rank fusion then ranks the BM25 list and the vector list with RANK() and adds 1 / (60 + rank) per product. The fusion is step 5.
Suggestions from what shoppers searched
A prefix on the logged queries walks the term dictionary, and each suggestion's count is how often it was searched. The outer query drops the suggestions that returned nothing.
Your backend sends SQL
SereneDB speaks the PostgreSQL wire protocol, so the backend sends these statements through its PostgreSQL driver, with the shopper's words as parameters. This is step 3's search.
Searches that found nothing
The zero-result report is a GROUP BY over the search log, in the same database as the catalog it explains.
What a store's search needs from one engine
products_idxThe shop_en dictionary folds case, accents and synonyms the same way in the titles and in the shopper's query.
{sneaker,sneakers,trainer,trainers}Facet counts are read from the term dictionary, so there is no second table to keep in step with the catalog.
TsDict: category, brand, price_bandBM25 and nearest-neighbor search read the same index, so a hybrid search is one statement.
emb ivf (metric = 'l2')Price and stock writes reach search every 1000 ms by default (refresh_interval), or at once with VACUUM (REFRESH_TABLE).
VACUUM (REFRESH_TABLE) productsTutorial: build product search in SQL, in five steps
From an empty server to typo-tolerant, faceted and hybrid product search: start SereneDB, load a 24-product catalog with its dictionary and index, then search, count facets and fuse keywords with vectors.
docker run -d --name serenedb -p 7890:7890 \ -e POSTGRES_PASSWORD=<password> serenedb/serenedb psql -h localhost -p 7890 -U postgres
CREATE TEXT SEARCH DICTIONARY shop_en AS split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false) | expand_solr_synonyms('sneakers, sneaker, trainers, trainer earbuds, earbud, earphones, earphone flask, thermos') WITH (frequency, position, norm); SELECT ts_lexize('shop_en', 'trainers') AS trainers; CREATE TABLE products ( id INTEGER PRIMARY KEY, title VARCHAR NOT NULL, description VARCHAR NOT NULL, brand VARCHAR NOT NULL, category VARCHAR NOT NULL, price DECIMAL(8,2) NOT NULL, stock INTEGER NOT NULL, sales_30d INTEGER NOT NULL, emb FLOAT[4] NOT NULL, price_band VARCHAR GENERATED ALWAYS AS ( CASE WHEN price < 50 THEN 'under $50' WHEN price < 100 THEN '$50–100' WHEN price < 200 THEN '$100–200' ELSE '$200+' END) STORED NOT NULL, availability VARCHAR GENERATED ALWAYS AS ( CASE WHEN stock > 0 THEN 'in stock' ELSE 'out of stock' END) STORED NOT NULL ); CREATE INDEX products_idx ON products USING inverted ( id, title shop_en, description shop_en, brand, category, price_band, availability, emb ivf (metric = 'l2')); INSERT INTO products (id, title, description, brand, category, price, stock, sales_30d, emb) VALUES (1, 'Wireless noise-cancelling headphones', 'Over-ear headphones with active noise cancelling and a 40-hour battery.', 'Sonora', 'audio', 199.00, 14, 310, [0.95, 0.05, 0.00, 0.05]::FLOAT[4]), (2, 'Wireless earbuds with charging case', 'True wireless earbuds, sweat resistant, 8 hours per charge.', 'Sonora', 'audio', 89.00, 40, 520, [0.85, 0.45, 0.00, 0.05]::FLOAT[4]), (3, 'Waterproof Bluetooth speaker', 'Portable speaker rated IP67 for the beach, the pool and the trail.', 'Lumen', 'audio', 59.00, 0, 140, [0.80, 0.05, 0.00, 0.45]::FLOAT[4]), (4, 'Bone conduction running headphones', 'Open-ear headphones that let you hear the traffic on a road run.', 'Stride', 'audio', 129.00, 9, 95, [0.70, 0.70, 0.00, 0.10]::FLOAT[4]), (5, 'Wired studio headphones', 'Closed-back monitoring headphones with a 3 m coiled cable.', 'Sonora', 'audio', 149.00, 6, 40, [0.95, 0.00, 0.00, 0.00]::FLOAT[4]), (6, 'Kids'' wireless headphones', 'Volume-limited headphones with soft ear cushions.', 'Sonora', 'audio', 39.00, 25, 110, [0.85, 0.05, 0.00, 0.00]::FLOAT[4]), (7, 'Road running trainers', 'Cushioned daily trainers with a breathable mesh upper.', 'Stride', 'footwear', 120.00, 25, 410, [0.00, 0.95, 0.00, 0.10]::FLOAT[4]), (8, 'Waterproof trail running shoes', 'Deep lugs and a waterproof membrane for muddy trails.', 'Northpeak', 'footwear', 145.00, 12, 180, [0.00, 0.85, 0.00, 0.50]::FLOAT[4]), (9, 'Carbon racing shoes', 'Lightweight racers with a carbon plate for race day.', 'Stride', 'footwear', 230.00, 4, 60, [0.00, 1.00, 0.00, 0.00]::FLOAT[4]), (10, 'Leather hiking boots', 'Ankle-high boots with a waterproof lining and a rubber toe cap.', 'Northpeak', 'footwear', 180.00, 8, 75, [0.00, 0.20, 0.05, 0.80]::FLOAT[4]), (11, 'Canvas slip-on sneakers', 'Casual sneakers for everyday wear, machine washable.', 'Stride', 'footwear', 55.00, 30, 200, [0.00, 0.30, 0.00, 0.10]::FLOAT[4]), (12, 'Kids'' light-up sneakers', 'Sneakers with LED soles that flash with every step.', 'Lumen', 'footwear', 45.00, 0, 90, [0.05, 0.30, 0.00, 0.05]::FLOAT[4]), (13, 'Windproof running jacket', 'Light jacket with reflective trim for runs after dark.', 'Stride', 'outerwear', 95.00, 11, 130, [0.00, 0.80, 0.10, 0.50]::FLOAT[4]), (14, 'Packable rain jacket', 'Waterproof shell that packs into its own pocket.', 'Northpeak', 'outerwear', 110.00, 18, 160, [0.00, 0.20, 0.10, 0.95]::FLOAT[4]), (15, 'Insulated down parka', 'Down-filled parka for sub-zero winters in the city.', 'Northpeak', 'outerwear', 260.00, 5, 45, [0.00, 0.00, 0.30, 0.85]::FLOAT[4]), (16, 'Fleece hoodie', 'Midweight fleece hoodie with a kangaroo pocket.', 'Northpeak', 'outerwear', 65.00, 22, 240, [0.00, 0.20, 0.25, 0.30]::FLOAT[4]), (17, 'Heated vest with battery', 'Three heating zones powered by a USB power bank.', 'Lumen', 'outerwear', 140.00, 0, 70, [0.05, 0.10, 0.45, 0.60]::FLOAT[4]), (18, 'Touchscreen softshell gloves', 'Windproof gloves with conductive fingertips.', 'Northpeak', 'outerwear', 35.00, 40, 150, [0.05, 0.30, 0.20, 0.60]::FLOAT[4]), (19, 'Vacuum flask, 1 litre', 'Double-wall steel flask that keeps drinks hot for 24 hours.', 'Kettleworks', 'kitchen', 35.00, 50, 380, [0.00, 0.05, 0.95, 0.20]::FLOAT[4]), (20, 'Leak-proof travel mug', 'Insulated mug that fits car cup holders.', 'Kettleworks', 'kitchen', 25.00, 60, 450, [0.00, 0.05, 0.90, 0.05]::FLOAT[4]), (21, 'Thermal coffee carafe', 'Keeps a full pot of coffee hot through a long meeting.', 'Kettleworks', 'kitchen', 45.00, 15, 85, [0.00, 0.00, 0.90, 0.00]::FLOAT[4]), (22, 'Collapsible running bottle', 'Soft 500 ml bottle that rolls up into a race vest.', 'Stride', 'kitchen', 18.00, 35, 150, [0.00, 0.85, 0.10, 0.10]::FLOAT[4]), (23, 'Gooseneck electric kettle', 'Temperature-controlled kettle for pour-over coffee.', 'Kettleworks', 'kitchen', 79.00, 7, 90, [0.00, 0.00, 0.80, 0.00]::FLOAT[4]), (24, 'Conical burr coffee grinder', 'Forty grind settings from espresso to French press.', 'Kettleworks', 'kitchen', 129.00, 3, 55, [0.00, 0.00, 0.70, 0.00]::FLOAT[4]); VACUUM (REFRESH_TABLE) products;
SELECT id, title, price, sales_30d FROM products_idx WHERE (title @@ (ts_levenshtein('runing', 1) ^ 2.0) OR description @@ ts_levenshtein('runing', 1)) AND availability @@ 'in stock' ORDER BY BM25(products_idx.tableoid) * ln(10 + sales_30d) DESC, id LIMIT 5;
SELECT CASE WHEN category IS NOT NULL THEN 'category' WHEN brand IS NOT NULL THEN 'brand' ELSE 'price_band' END AS facet, coalesce(category, brand, price_band) AS value, count(*) AS n FROM products_idx WHERE title @@ ts_levenshtein('runing', 1) AND availability @@ 'in stock' GROUP BY GROUPING SETS ((category), (brand), (price_band)) ORDER BY facet, n DESC, value; SELECT unnest(ts_dict_agg(brand)) AS brand, unnest(ts_dict_count(brand)) AS n FROM products_idx WHERE title @@ ts_levenshtein('runing', 1) AND category @@ 'footwear' ORDER BY n DESC, brand;
WITH fused AS ( SELECT id, 'bm25' AS list, RANK() OVER (ORDER BY s DESC) AS rank FROM ( SELECT id, BM25(products_idx.tableoid) AS s FROM products_idx WHERE title @@ plainto_tsquery('running headphones') ORDER BY s DESC LIMIT 10) lex UNION ALL SELECT id, 'vector', RANK() OVER (ORDER BY dist) FROM ( SELECT id, emb <-> [0.70, 0.70, 0.00, 0.05]::FLOAT[4] AS dist FROM products_idx ORDER BY dist LIMIT 10) vec ) SELECT p.id, p.title, string_agg(f.list || ' #' || f.rank, ' + ' ORDER BY f.list) AS ranks, round(SUM(1.0 / (60 + f.rank)), 4) AS rrf FROM fused f JOIN products p ON p.id = f.id GROUP BY p.id, p.title ORDER BY rrf DESC, p.id LIMIT 5;
What teams build on SereneDB for ecommerce search
Search-as-you-type from the search log
The suggestions under a search box are past searches that start with what the shopper has typed, most searched first. In SereneDB a LIKE 'hea%' prefix on the logged queries walks the index's term dictionary, and ts_dict_count says how often each was searched. The outer query leaves out the searches that returned nothing, so a suggestion never leads to an empty page.
WITH typed AS ( SELECT unnest(ts_dict_agg(query)) AS suggestion, unnest(ts_dict_count(query)) AS searches FROM searches_idx WHERE query LIKE 'hea%' ) SELECT suggestion, searches FROM typed WHERE suggestion NOT IN (SELECT query FROM searches WHERE results = 0) ORDER BY searches DESC, suggestion LIMIT 5;
| suggestion | searches |
|---|---|
| headphones | 6 |
| heated vest | 2 |
The searches that found nothing
A search that returns nothing is a shopper the catalog failed: a word it lacks or a product it does not sell. The search log sits in a table beside the catalog, so the report is a GROUP BY, and it can join the orders or the catalog in the same query.
SELECT query, count(*) AS searches FROM searches WHERE results = 0 GROUP BY query ORDER BY searches DESC, query;
| query | searches |
|---|---|
| tumbler | 3 |
| headlamp | 2 |
| yoga mat | 2 |
| head torch | 1 |
Price and stock changes, searchable at the next refresh
Prices and stock change all day. The catalog row is updated in place; its generated columns recompute the price band and availability in the same statement; and the change reaches search at the next refresh, every 1000 ms by default, or at once with VACUUM (REFRESH_TABLE). The facets follow without a rebuild.
UPDATE products SET price = 99.00, stock = 0 WHERE id = 7; VACUUM (REFRESH_TABLE) products; SELECT id, title, price, price_band, availability FROM products_idx WHERE title @@ 'trainers' ORDER BY id;
| id | title | price | price_band | availability |
|---|---|---|---|---|
| 7 | Road running trainers | 99.00 | $50–100 | out of stock |
| 11 | Canvas slip-on sneakers | 55.00 | $50–100 | in stock |
| 12 | Kids' light-up sneakers | 45.00 | under $50 | out of stock |
An ecommerce search engine is the part of an online store that turns a shopper's query into a ranked list of products. It matches words despite typos and synonyms, filters by attributes such as price and stock, counts the products behind every filter, and orders the results by relevance, meaning and business signals. SereneDB does each of these in SQL over the store's product table.
With fuzzy matching: each word of the query matches indexed terms within a small edit distance. In SereneDB, ts_levenshtein('hedphones', 1) matches headphones, one inserted letter away, and a swap of two letters counts as one edit. The typos search above finds both pairs of wireless headphones for wireles hedphones. The fuzzy search recipe covers distances and prefixes.
A synonym list declares the words that mean the same thing to shoppers, such as sneakers and trainers. SereneDB applies it in the text search dictionary, to the indexed text and to the query alike, so either word finds the products written with the other. A one-way rule, such as laptop => notebook, rewrites in one direction only; the synonyms recipe shows both forms.
Faceted search shows, next to each filter value, how many products the current search would return with it: footwear 2, audio 1, Stride 4. In SereneDB the counts come from the inverted index's term dictionary, several facets in one query with GROUP BY GROUPING SETS, and they follow the search: the facets example counts only the products whose title says running. The faceted search recipe adds price ranges and drill-downs.
Keyword search matches the words of the query; semantic search matches what the query means, by comparing embeddings. keep my tea warm shares no word with any product in the demo store, so as keywords it matches nothing, while its embedding is closest to the vacuum flask and the travel mug. Most stores want both: hybrid search runs the two and fuses the rankings, as step 5 does.
Start from what shoppers search and do not find: the search log's zero-result queries name the missing synonyms and products. Then tune the ranking in the query: boost the title over the description with ^, multiply BM25 by a business signal such as 30-day sales, set BM25's k1 and b, and fuse in vector similarity. In SereneDB each is a change to the SELECT, so two rankings can run side by side before one ships; the relevance tuning recipe walks through them.
At the next refresh of the inverted index, every 1000 ms by default (refresh_interval), or at once after VACUUM (REFRESH_TABLE). Generated columns such as the price band and availability are recomputed by the same UPDATE, so the facets follow. Reads of the products table itself, such as the stock check at checkout, see the committed row at once.
By the searches your shoppers make. Check how each engine handles typos and synonyms, whether facet counts follow the search, whether it ranks by meaning as well as by keywords, how its ranking is tuned, how soon price and stock changes reach search, how your backend talks to it, and where your search logs live. The checklist above maps each of these to SereneDB.
Yes. SereneDB speaks the PostgreSQL wire protocol on port 7890, and its docs list drivers such as psycopg for Python, node-postgres for JavaScript, Npgsql for .NET and tokio-postgres for Rust. Search queries are SELECT statements, with the shopper's words passed as parameters.
Yes. SereneDB is open source under the Apache-2.0 license, with its source on GitHub, and its Docker image runs every statement on this page.
Export the products as JSON or CSV and load them with COPY, which reads newline-delimited JSON by default, then create the dictionary and the index as in step 2. Code that writes to Elasticsearch's _bulk endpoint can post to SereneDB's Elasticsearch API instead, and SQL reads those indexes as tables. SereneDB vs Elasticsearch maps Query DSL to SQL.
Get started with SereneDB
Start SereneDB, load your catalog with a dictionary and an inverted index, and search it in SQL from your store's backend or any PostgreSQL client.