We do sell AI powered search and we use AI/LLMs for a whole host of things on a day to day basis at Layers but art generation is not one of them and that’s on purpose.

Every illustration on uselayers.com was drawn by Alberto Larizza. Alberto is one of my closest friends and a fantastic pattern designer. He’s from Tuscany and did pattern work for houses like Gucci in Italy prior to coming over to the US.

He drew it all in dots. 103,802 of them across 13 illustrations, each dot placed by hand over a number of months earlier in the year.

We’ve always wanted them to move. This month Sumit, one of our product designers, finally got around to it, and the prototype was beautiful:

Hover over one and the dots get pushed out of the way and then spring back. Tap it, and a shockwave goes through.

There was one problem: the prototype had an 8MB JSON file that had every dot from the SVG. I had to figure out how to feasibly get these into the browser and have it work for lower-end devices w/o any jitter or lag on the page.

175 MB of Illustrator Exports

Alberto did the Illustrator artwork. Sent over as SVG exports (65 files, a few zips at around 175MB) and 13 of those ended up on the site.

Illustrator doesn’t export a dot as a dot. You end up with a mixture of <circle>, <ellipse> and <path> elements and any dot that’s been clipped or expanded will come out as a little bezier path with its own fill attribute.

The security padlock is a perfect example: 2,510 circles, 470 ellipses, and 3,644 paths, which comes to precisely 6,624 dots in around 730KB.

The browser only needs x, y and a radius per dot so step one was converting each SVG down to that:

There’s not much to say about the conversion really. The rest of this post is about getting 1.25MB down to 82KB.

Three Floats per Dot

The initial version shipped a Float32Array per illustration: [width, height, x, y, r, x, y, r, …], little-endian. 12 bytes a dot.

// The original loader
const floats = new Float32Array(await res.arrayBuffer())
return {
  width: floats[0],
  height: floats[1],
  dots: Array.from(floats.subarray(2)),
}

Float32 is around 7 significant digits. The widest example is 900px in source units and it renders at a few hundred CSS pixels. 412.3871 and 412.3906 end up on the same pixel so most of those digits are effectively wasted.

(Same version also ran the hero background loop at 60fps continuously, even if the mouse was just sitting still. Getting to that.)

What Hand-Placed Dots Look Like as Data

Before I went to any format I spent a bit of time fiddling with the files.

The first thing I noticed was rows. If you sort by y and then x, 78% to 99% of dots have the exact same y as the dot right before them.

Alberto builds the pattern row by row and it appears in the numbers.

Radii are pretty much limited too. The original nine illustrations each have somewhere between 104 and 228 distinct radius values. The three latest have 900+ (again that’s our SVG converter reading the radii off the bounding boxes, float noise). 8-bit quantization will have flattened that out anyway.

X is where the entropy is. Nearly every x is fractional, and the neighbors in a row are a few px apart.

I didn’t write a clever codec at all. I just laid the data out so the compressor could see the rows.

DOT2

DOT2 is the format I went with. It’s built at deploy time via a small Node script and the decoder is around 40 lines.

Encoding, in order:

1. Sort by y then by x. Each dot in an illustration is one color, so draw order is irrelevant.
2. Quantize x and y to uint16 across the width and height of the artwork. Radius becomes uint8, scaled to the largest dot in that illustration.
3. Store each x and y as a delta from the previous dot, wrapped mod 65,536 so negative steps still fit.
4. Write all the x deltas, then all the y deltas, then all the radii (planes, not interleaved).
5. Emit raw Brotli q11 and gzip -9 copies with a content hash in the filename.

Compressed one plane at a time the y deltas reduce to 8% of their original size and the x deltas get down to 25%.

5 bytes per dot before compression. With Brotli it’s 0.79 on average.

Encoder inner loop:

const x = Math.round((dots[j] / width) * 65535)
const y = Math.round((dots[j + 1] / height) * 65535)
view.setUint16(HEADER + i * 2, (x - previousX) & 65535, true)
view.setUint16(HEADER + count * 2 + i * 2, (y - previousY) & 65535, true)
view.setUint8(
  HEADER + count * 4 + i,
  Math.round((dots[j + 2] / maxRadius) * 255),
)

Decoder side:

x = (x + view.getUint16(HEADER + i * 2, true)) & 65535
dots[i * 3] = (x / 65535) * width

On the 900px illustration one uint16 step is approximately 0.014px. Worst center error after a round trip across all assets: less than 0.011px. Worst radius error: less than 0.0065px.

It’s lossy, no doubt about it. I wasn’t going to trust the maths on that so there’s a pixel by pixel comparison further down.

The decoder validates prior to allocation. It checks the DOT2 magic (which is also the version), checks the flags, caps the caps count at 100k, checks the bounds on width and height and requires the buffer to be exactly 24 + count x 5 bytes.

Anything off and it throws, and you keep seeing the PNG that was already there.

Where the Bytes Actually Went

Halfway through writing this I went to explain why each step had been useful and realized I’d never actually measured the steps, only the final number.

So I went back and ran Brotli q11 over every intermediate layout, all 13 assets:

I would have got the majority of this wrong if you’d asked me before hand.

• Quantizing on its own did nothing. It was 1% larger than Brotli on the raw floats.
• Sorting on its own made it worse (one file actually almost doubled). I suspect the export order had already tended to group the nearby dots together and sorting the interleaved data broke that up.
• Planes did the majority of it actually (more than half off). Once it’s its own stream Brotli will see thousands of repeated row values in a row.
• Deltas saved around 1KB. Everyone goes for the deltas first and that was by far the smallest gain. I left them in there anyway as the Y plane ends up being pretty much all zeros.

So: 93% smaller than the uncompressed source, and 63% smaller than Brotli on the original floats. The 63% is the one I care about, since the 93% mostly measures “you weren’t compressing before.”

Let the Browser Decompress It

Browsers already do the decompression of Brotli and gzip so I’ve skipped a WASM decoder or a decompression library. Delivery was all about serving the correct file with the correct headers.

`npm run dots` takes each .dots.bin path, writes out the compact files and a manifest to its corresponding .dots URL (hashed). The pages continue to reference the original paths and the component then looks up the hashed one.

A Cloudflare Worker handler only matches against paths in the manifest. It correctly parses Accept-Encoding (q values, identity;q=0, all of it) and then serves the .br, the .gz or the raw file. The browser does the decompression and the client then decodes the entire buffer into a Float32Array.

Content-Type: application/octet-stream
Content-Encoding: br
Vary: Accept-Encoding
Cache-Control: public, max-age=31536000, immutable

immutable is fine as the hash is in the filename. Change the art and you get a new URL. The old hashed files remain so the cached pages don’t 404 their dots.

Two smaller things in the handler:

• It drops Range and If-Range on the way to the asset binding. The files are small and are always sent as a whole. A range against a .br file is going to be a different byte range to a range against the raw file. Not worth working out which one the client intended.
• It sets encodeBody: ‘manual’ as the bytes are already compressed. Otherwise the Workers runtime might compress them again.

Unit Tests Passed, the Page Didn’t

Codec tests were green from the start. The page was still broken in local preview.

The preview bridge decompresses responses on the way through but kept the compressed Content-Length. So decoded bytes showed up with the old, smaller length and got truncated. The decoder’s length check caught it and threw (good, that’s its job).

Fix was deleting Content-Length and piping the body through an empty TransformStream so that nothing downstream had a length to forward. I reproduced it in an isolated Miniflare runtime rather than a mocked Response as the mock would have just happily passed.

Smaller Files, Same Frames

Smaller files don’t make a frame any faster, though. The illustrations and the background grid are separate engines, and each had its own problem.

The Illustrations

The original loader ran Array.from on the Float32Array and then built an object per dot:

// Before: one object per dot, with a color string on each
list.push({ ux, uy, hx: 0, hy: 0, x: 0, y: 0, vx: 0, vy: 0, r: 1, sr, color })

Biggest illustration is 12,115 dots. So 12,115 objects, eleven fields each, plus setting ctx.fillStyle on every dot every frame. And every dot is the same color.

Now it’s ten flat typed arrays:

type ParticleBuffers = {
  ux: Float64Array; uy: Float64Array; sr: Float64Array  // source space
  hx: Float64Array; hy: Float64Array                    // home, canvas space
  x: Float64Array;  y: Float64Array                     // current
  vx: Float64Array; vy: Float64Array                    // velocity
  r: Float64Array                                       // radius, canvas space
}

Yes those are Float64Arrays (after I’d spent a whole section cutting precision). The wire format will be small, but the simulation should behave exactly as it did before. JS numbers are doubles so Float64 will preserve the spring maths bit for bit.

fillStyle is set once per frame for single color art (image sampled art still sets it per dot as those are effectively different colors). Each dot still has its own arc and fill. The sleep when settled and pause when offscreen behavior was already in place; I left it be.

The Background Grid

The hero background is a grid of glyphs (·, ◦, •, ●) which light up around your cursor and then fade out.

The old loop would touch every cell every frame. Build a string key like “14,22”, do a map lookup, apply the decay, do the brush test and draw. Then just requestAnimationFrame again unconditionally.

At 390×844 with 11 px cells that’s 2,772 cells and 2,772 template strings per frame, ~60 frames a second, on a page where nobody has moved the mouse.

New loop:

// Before
for (every row) for (every column) { /* key, decay, brush test, draw */ }
frameId = requestAnimationFrame(draw)

// After
for (const [key, value] of influence) { /* decay active cells only */ }
if (painting) { /* visit only the brush's bounding box */ }
for (const [key, value] of influence) { /* draw active cells only */ }
if (painting || influence.size) frameId = requestAnimationFrame(draw)

Numeric keys (row * columns + column). Decay only walks lit cells. The brush only walks its own bounding box. When the last lit cell fades out, it stops requesting frames; pointer movement or a resize starts it again.

I went through and changed a lot of this at once and benchmarked it all together so I can’t break down the improvement per change.

The Optimization I Threw Out

The obvious next step with the illustrations was to batch up every arc into a single path and then just call fill() once. Thousands of Canvas calls down to one.

I built it and it was faster, though the artwork was different.

When circles are on the same path they get antialiased together rather than individually. The edges which were previously feathering into the background become a bit heavier and the whole image ends up looking a little darker, as if the contrast has been nipped up a bit.

Most people probably wouldn’t notice. The whole point was animating Alberto’s work without changing it, though, so I reverted it and kept individual fills.

Proving It Looks the Same

I didn’t want to eyeball it so I wrote a benchmark that renders old engine + old data side by side with new engine + DOT2 at DPR 2.

Every illustration (240 and 600 CSS px), once at rest and once after 12 scripted frames of pointer movement plus a shockwave. 52 comparisons.

The cost per comparison: mean channel difference less than 0.2/255 and less than 0.5% of pixels off by more than 16 on any channel.

Worst case was 0.153/255 at rest, 0.134/255 in motion and 0.211% of pixels over 16 (that run was headless Chrome on Linux). The two images below are from an earlier run on macOS.

Merchandising at rest, original on the left:

Toys mid-shockwave, same scripted input on both:

They aren’t pixel-identical, and the numbers above show it. I couldn’t see a difference.

Still His Dots

Every dot that Alberto placed is still there, within about a hundredth of a pixel of where he put it. The 13 illustrations come to 82KB on the wire and the grid behind them stops drawing when you stop moving.

The codec itself isn’t what I’m most interested in. It only worked because of the way the art was created. Alberto draws in rows so the data came in rows and the real win was in the way we laid it out so Brotli could see it.

New illustrations go through the same pipeline: illustrator export, a script that pulls the x, y and radius out of the SVG, then npm run dots. Alberto is still drawing by hand. The site just hosts it for less.

If you’re shipping art that someone has made by hand then open the files before you pick a format. How it’s been drawn will usually tell you what format to store it in.

If you like this kind of work, follow along at layers.engineering.

Shipping 103,802 Dots in 82 KB was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Every search on Layers runs as one Postgres query. Vector and keyword retrieval, dozens of availability and channel filters, merchandising and behavioral signals, reranking, and facets all happen in one statement. For years, the part of that query that hurt most was keyword relevance.

Keyword relevance is one piece of that puzzle, not the whole thing. This post is about why TIN fit that piece when nothing else did, and how we use it as a building block: first under keyword scoring, then as a retrieval channel next to vectors.

What one Layers search has to do

A storefront search on Layers is one SQL statement with more than 25 CTEs. Every stage feeds a weighted blend, which is how merchandising, behavioral, and inventory signals end up in one score.

Two stages do a lot of quiet work. Query understanding turns “diamond chain necklace” into the query itself plus weighted expansions like “diamond link necklace,” and each gets its own vector search. Expansions blend into a product’s match when they fit it better than the original query, and they can bring in products the query alone missed. Intent works inside the same statement: when we detect a product type, products of that type move up a recall tier and get a small, capped boost, unless they already fill more than a third of the top 20.

The data underneath makes it harder. Brands store product data in wildly different shapes. We keep normalized tables for all of it, plus rolled-up tables for serving so nothing gets aggregated at read time. One retrieval query can touch tens to hundreds of tables and apply dozens of filters to decide whether a product can be sold on a given channel or in a given region.

Whatever we put under keyword search had to live with these constraints:

• One query per search. Product retrieval, reranking, and facet retrieval happen together.
• Many searches per request. Intent and query understanding can turn a few terms into as many as six searches.
• Heavy row churn. 60–80% of rows are updated throughout the day.
• Dynamic schemas. Tables are partitioned and indexed per tenant workload.
• Partitions and partial indexes, and the ability to combine scans.
• Latency back under our SLAs by Q4, or we’d risk losing customers.

The ranking DSL

Our legal name is Building Blocks for Ecommerce, and we mean it. We want to give technical teams, or our solutions team, the pieces to build exactly what a brand needs, without it getting complicated or us babysitting it forever. Today there are more than thirteen billion distinct ways to rank a search or category page on Layers.

Nobody writes that search query by hand. Everything that touches ranking ends up as a config, and a compiler builds the single SQL statement from it. TIN is one of those blocks. The compiler decides where it sits.

Here’s a trimmed ranking configuration with illustrative weights and four features: semantic similarity, product-title keyword relevance, revenue, and inventory.

{
  "name": "default",
  "version": 1,
  "features": [
    { "feature": "text_similarity", "weight": 0.25 },
    { "feature": "keyword:title", "weight": 0.03, "normalize": "minmax" },
    { "feature": "query_metric:revenue", "weight": 0.15, "fallback": 0.0 },
    { "feature": "variants.inventory_quantity", "weight": 0.03,
      "transform": "log1p", "normalize": "minmax", "fallback": 0.0 }
  ],
  "options": { "normalization": "minmax", "combiner": "linear" }
}

The compiler turns each feature into a column, then turns the weights, transforms, and normalization into one linear expression. fallback becomes the coalesce default, log1p becomes ln(1 + …), and minmax divides by the spread across the candidate set.

-- compiled output, trimmed
ranking_features as materialized (
  select ns.product_id,
    coalesce(sum(ns.norm_score * ns.weight) filter (where ns.type = 'text'), 0) as f_0,
    coalesce(min(kw_title.kw_0), 0)          as f_1,
    coalesce(min(qpm.revenue_score), 0)      as f_2,
    coalesce(min(frv.inventory_quantity), 0) as f_3
  from normalized_scores ns
  left join kw_title on kw_title.product_id = ns.product_id
  left join qpm      on qpm.product_id      = ns.product_id
  left join frv      on frv.product_id      = ns.product_id
  group by ns.product_id
),
ranking_scores as materialized (
  select rf.product_id,
      0.25 * coalesce((rf.f_0 - fs.min_0) / nullif(fs.max_0 - fs.min_0, 0), 0)
    + 0.03 * coalesce((rf.f_1 - fs.min_1) / nullif(fs.max_1 - fs.min_1, 0), 0)
    + 0.15 * rf.f_2
    + 0.03 * ln(1 + greatest(coalesce((rf.f_3 - fs.min_3) / nullif(fs.max_3 - fs.min_3, 0), 0), 0))
    as ranking_score
  from ranking_features rf
  cross join feature_stats fs
)

Real configs run to dozens of features, and every one of them compiles into the same statement.

• Semantic: text and image embedding similarity, for the query and each expansion. Shaped with per-type normalization and expansion blending.
• Keyword: relevance per field (titles, collections, option codes, metafields, tags), scored by TIN. Shaped with a weight per field and lower weights for expansions.
• Behavioral: click-through, add-to-cart, purchase, and revenue rates over varying windows. Shaped with window weighting and fallbacks for products with no history.
• Catalog: inventory and how recently a product was published. Shaped with log transforms and exponential recency decay.
• Merchandising: brand-defined metrics, applied as capped relevance boosts on top of the blend.
• Intent: detected product type, applied as recall-tier shifts and boosts, capped by how much of the top results already match.

Around the features sit recall tiers, relevance cuts, variant matching, and the final sort. Each of those is configurable too, which is where the thirteen billion combinations come from. A brand can rank search and category pages differently, lean on revenue for one surface and recency for another, or turn keyword relevance on for a single metafield, without anyone touching SQL. Keyword is one family in that table, which is exactly why it needed to be a block and not an engine.

Why other engines didn’t fit

Layers spent almost five years as a skunkworks project inside Proton before going GA in Q4 2025. In 2020 and 2021 we tried MySQL, Postgres, Solr, and Elastic. In 2022 we settled on Postgres for our data, with keyword search on Meilisearch. Replicating from our database to search indexes in six US regions lagged, and shoppers in different regions saw different results. Sometimes one region’s index would update mid-session, reshuffle everything, and products would vanish.

I’ve worked with almost every open-source and commercial search engine out there. I’ve run Elastic clusters chewing through terabytes of records, and I helped Google bring Vertex AI Search for Retail to the first Shopify merchant. Every one of them was purpose-built, and the purpose never lined up with ours. Ranking was basic. You got structured data or unstructured data and nothing in between. Most of them started as document search and had product catalogs bolted on later. Each thought it was the destination. We needed something to build on.

We tried going semantic-only on pgvector, with a homegrown Go engine for query parsing and rewriting. That got us to GA, but by spring 2026 the Go engine clearly wouldn’t scale. We ported the ranking system to SQL, with keyword reranking on top of vector search, and learned the harder lesson: ignoring keyword retrieval was a mistake. The brands we signed had shoppers who still typed one or two words, not sentences.

In early 2026, halfway through removing the Go engine, I talked to Sam at PlanetScale about custom Postgres extensions, our desire to build something ourselves, and he mentioned pgrx. We stayed the current course anyway, with some of the nastiest SQL I’ve written. The reranker kept failing in niche ranking cases. We needed a replacement for native text search and a keyword channel that could sit alongside vectors within the same statement.

We prototyped with ParadeDB and Tiger’s text search extension.

• ParadeDB, we felt, wouldn’t be flexible enough for our disjointed schema without significant changes, and it wasn’t available where our data lives.
• Tiger came closest to a general-purpose text search extension that fit our schemas, but it wasn’t available where our data lives either.

In late spring, I asked Sam whether custom pgrx extensions, or Tiger specifically, were coming to their platform. If not, the plan was to spend Q3 and Q4, plus part of our in-progress round, building a keyword engine into Postgres ourselves. The reply came back almost immediately: “We’re working on something cool, do you wanna see?”

Meeting TIN

That’s how I met the engineers behind TIN. We walked them through the constraints above. Then they opened a terminal and started querying the full Reddit dataset, over a terabyte, on a local machine. Unindexed, it was slow. BM25 didn’t impress me. Everyone has BM25. The retrieval and scoring flexibility did: “foo in the first 10% of the document,” or “bar and baz within ten words of each other.”

That’s what we’d been half-solving in our query and intent engines, and mostly solving with a SQL keyword ranker built on Postgres text search functions, with no index in sight.

Then they built an index and ran a top-k query for “beer and cheese” against the same data. Around 100 ms, highlighted documents and all.

On that call we shared the day’s slowest search. The EXPLAIN ANALYZE output ran 594 lines, and the query took 732 ms to return a page of products and its facets.

Limit  (actual time=701.027..701.174 rows=7 loops=1)
  ...
Planning Time: 26.287 ms
Execution Time: 705.745 ms

The biggest sink was ranking features, mostly keyword scoring: about 185 ms joining and aggregating scores across every searchable field to produce features for 200 products.

->  GroupAggregate  (actual time=278.545..463.542 rows=200 loops=1)
      ->  Merge Left Join  (actual time=277.518..297.087 rows=348 loops=1)
            ...
            ->  Seq Scan on search_vrnts_optns_<tenant> rkw  (actual time=0.017..1.418 rows=8029 loops=1)

We had tried keyword indexes. They made it worse. Our candidates come from a CTE, and a GIN scan can’t intersect with that, so Postgres read the whole table and filtered afterward. GIN also only helps with matching: ts_rank_cd still has to read every matching row's tsvector to score it. We dropped keyword indexes entirely. The keyword cost had nowhere to go without an index built for the job.

The first block: keyword scoring

In the DSL, keyword relevance is one kind of feature. A config says which fields to score and how much each one weighs: collection titles, product titles, the variant options a brand marks searchable, description metafields, and tags. Each field is scored against the shopper’s query and each expansion, with expansions weighted lower. How it gets computed is the compiler’s problem.

Before TIN, every field got three Postgres scores, each taking the best of the query and its expansion: ts_rank_cd over a tsvector built on the fly, plus strict_word_similarity and similarity from pg_trgm. That's 27 keyword features per product, all computed per row at query time.

Putting TIN under that feature meant changing what the compiler emits: a ==> match and tin.score() instead of three functions. Every existing brand configuration kept working. Nothing to migrate on their side. The candidate set stayed the vector set, so every downstream stage still started from the same products. It just got its keyword features from an index. Here's the product-title feature for "diamond chain necklace" plus one expansion, "diamond link necklace."

-- before, trimmed
"_rkw_2_vals_0" as materialized (
  select vals.kw_value,
    greatest(ts_rank_cd(vals.kw_tsv_english, tsq.tsq_0) * 1,
             ts_rank_cd(vals.kw_tsv_english, tsq.tsq_1) * 0.8) as kw_0,
    greatest(strict_word_similarity('diamond chain necklace', vals.kw_value) * 1,
             strict_word_similarity('diamond link necklace', vals.kw_value) * 0.8) as kw_1,
    greatest(similarity(vals.kw_value, 'diamond chain necklace') * 1,
             similarity(vals.kw_value, 'diamond link necklace') * 0.8) as kw_2
  from (
    select dv.kw_value, to_tsvector('english', dv.kw_value) as kw_tsv_english
    from (
      select distinct coalesce(rkw.title, '') as kw_value
      from searchable_products_<tenant> as rkw
      where rkw.id in (select product_id from normalized_scores)
    ) as dv offset 0
  ) as vals
  cross join (
    select plainto_tsquery('english', 'diamond chain necklace') as tsq_0,
           plainto_tsquery('english', 'diamond link necklace')  as tsq_1
    offset 0
  ) as tsq
),
"_rkw_2" as (
  select rkw.id as product_id,
    max(dv_0.kw_0) as kw_0, max(dv_0.kw_1) as kw_1, max(dv_0.kw_2) as kw_2
  from searchable_products_<tenant> as rkw
  inner join "_rkw_2_vals_0" as dv_0 on dv_0.kw_value = coalesce(rkw.title, '')
  where rkw.id in (select product_id from normalized_scores)
  group by rkw.id
)

-- after
"_rkw_2" as materialized (
  select m.product_id,
    max(m.score) as kw_0,
    0.0::double precision as kw_1,
    0.0::double precision as kw_2
  from (
    select rkw.id as product_id, tin.score(rkw.ctid) * 1.0 as score
    from searchable_products_<tenant> as rkw
    where rkw.title ==> 'diamond chain necklace'
      and rkw.id in (select product_id from normalized_scores)
    union all
    select rkw.id as product_id, tin.score(rkw.ctid) * 0.8 as score
    from searchable_products_<tenant> as rkw
    where rkw.title ==> 'diamond link necklace'
      and rkw.id in (select product_id from normalized_scores)
  ) as m
  group by m.product_id
)

The CTE’s output shape is unchanged: one row per product, with kw_0, kw_1, and kw_2. The two trigram columns compile to constant zeros, so they drop out of the blend once min-max normalization runs, and nothing downstream had to be rewritten. The planner turns each match into a TIN Text Search Scan, and in some branches TIN folds our candidate-ID restriction into the same pass.

Recall tiers compare raw keyword scores against cutoffs we tuned for ts_rank_cd and trigram similarity. BM25 sits on a different scale, so those cutoffs move with the score. That is compiler work, not a brand migration.

Catalogs where every product looks alike

While we were previewing TIN, semantic search hit a new problem: keyword stuffing. Brands were AI-generating product descriptions, and the fluff buried what the product actually was. PlanetScale then shipped dense-term elision: high-frequency terms drop out of the BM25 score. Products can still match those words. The words just stop counting toward relevance. Density comes from each catalog’s own data. Nobody maintains a stop list.

Just as important, it happens at query time, as an option. We turn it on where it helps and leave it off where it doesn’t (note: we have not turned it off to date).

“Diamond chain necklace” is the case that sold us.

In that catalog, diamond is not the product. Nearly every product can be bought in diamond or in another stone. That choice is a variant option, and we apply it in a later stage, after a product has been retrieved. Using “diamond” to rank products is the wrong job. It does not tell a chain necklace apart from a chain bracelet. It tells you which stone the shopper wants once they have a product.

Our old scorer was ts_rank_cd plus trigram similarity. Neither cares how common a term is, so "diamond" counted in full. It dominated, and diamond chain bracelets kept showing up in the top 10, sometimes the top 25. With TIN, "diamond" is elided from the score on that catalog. "Chain" and "necklace" decide the order. The stone option is handled after that.

IDF would have shrunk “diamond.” TIN drops a term that dense from the score entirely, so it never walks that postings list. A word matters for this stage of ranking only if it tells products apart. Stone does not, on this catalog. It would be in a general store where few products can be set with a diamond, and their TIN keeps it. If a brand’s assortment shifts, the scoring follows.

The engines we used before shipped opinions: a fixed stop list, or a self-learning box. TIN gives us a default computed from the catalog, an option we control at query time, and one more block we compose with the rest of the ranking.

TinQL: everyone does BM25

Everyone does BM25. Hardly anyone gives you control over it. What sold me in that first demo wasn’t the score, it was the query language around it: position (“in the first 10% of the document”), proximity (“within ten words of each other”), and matching on one set of terms while scoring on another, all in a single scan.

That last one is the expansion block. Match broadly on “diamond link necklace,” score on what the shopper typed. The compiler emits one scan per query variant today. Collapsing those into a single scan is the same feature, composed differently, not a new system.

The bigger opportunity is everything we still do with regex. A fair amount of our query handling runs through generated regex patterns today, and our research says much of it maps directly onto TinQL. That moves the logic out of application code and into the same index scan that does the scoring.

Here’s what that looks like. For “blouse,” one TinQL expression layers an exact match, a fuzzy match that forgives a typo, and prefix and suffix wildcards, each with its own weight:

OR(blouse^10, blouse~1, (OR(REGEX(.*blouse), REGEX(blouse.*)))^0.3)

For “curly hair products,” it adds proximity. All three words score highest. Next come the words appearing close together, in any order, at two strengths. Then a fuzzy match on at least two of the three words, and finally the wildcards:

OR(
  (AND(curly, hair, products))^10,
  (SPAN(MAXGAPS(3, UNORDERED(...))))^5,
  (SPAN(MAXGAPS(3, UNORDERED(...))))^2,
  ATLEAST(2, curly~1, hair~1, products~1),
  (OR(REGEX(...)))^0.3
)

Exact matches, proximity, typo tolerance, partial words, and how much each one counts, all in one expression and one index scan. Today that logic is spread across regex in our query handling and functions in our SQL.

What the scoring block changed

On a real storefront search, a short query plus one expansion, moving keyword scoring to TIN took the query from about 264 ms to about 51 ms. Roughly 5× faster end to end, with the SQL around it unchanged.

• Total (planning + execution): 264.3 ms → 50.8 ms
• Execution: 255.8 ms → 39.1 ms
• Keyword scoring stage: ~227 ms → ~14 ms
• Vector retrieval (unchanged): ~18 ms → ~18 ms
• Planning: 8.6 ms → 11.7 ms

Almost all of that came from the keyword stage, which went from about 227 ms to about 14 ms. It used to be 88% of execution time. Now vector retrieval is the bigger cost again. Planning went up about 3 ms. I’ll trade 3 ms for 200.

Here’s one field from that search, the description metafields. Before, scoring 175 distinct descriptions meant building a tsvector for each one at query time and running three scoring functions on it: about 40 ms for one field.

CTE _rkw_3_vals_0
  ->  Nested Loop  (actual time=2.537..39.619 rows=175 loops=1)
        ->  Subquery Scan on dv_2  (actual time=2.194..9.695 rows=175 loops=1)
              ->  Unique  (actual time=2.143..2.307 rows=175 loops=1)
                    ->  Sort  (actual time=2.140..2.175 rows=743 loops=1)

After, the whole field takes about 2 ms. The TIN scan itself takes 0.055 ms and touches 14 pages. TIN decided “diamond” is a dense term in this catalog and elided it from the score.

CTE _rkw_3
  ->  GroupAggregate  (actual time=1.948..1.956 rows=0 loops=1)
        ...
        ->  Custom Scan (Text Search Scan) on search_vrnts_mflds_<tenant> rkw_4  (actual time=0.043..0.055 rows=2 loops=1)
              Index: search_vrnts_mflds_tin_search_value_idx
              Query: AND(diamond, chain, necklace)
              Scoring: dense-term elision
              Elided Terms: diamond
              Page Touches:
                Total: 14, ~0.1 MiB

Everything around it stayed the same: vector retrieval, the candidate filter, behavioral and inventory features, intent tiers, and the weighted blend. We blend normalized scores instead of using rank fusion, so changing how one feature gets computed doesn’t mess with the merchandising signals on top.

The next block: hybrid retrieval

Scoring was the first place TIN fit, because the candidate set did not have to change. Hybrid retrieval is the same block used a second way: TIN finds products, vector search finds products, and the compiler merges them before ranking.

That merge is the work. It is still one statement, still the same DSL.

• The candidate set becomes a union. Products only TIN finds have no vector distance, so the compiler decides what a missing semantic score means before any feature is computed.
• Normalization is over the merged set. Every feature is min-max normalized across the candidates. A wider set changes every product’s normalized values, including products that were already there.
• Thresholds follow the new distributions. Recall tiers, intent caps, and the relevance cut were tuned when vectors defined the set. They get retuned against the merged set so a brand that never touched its config keeps behaving.
• Eligibility covers both channels. Channel, region, publish, and B2B rules already live in our roaring-bitmap availability system. A keyword channel searching the catalog has to respect the same rules. We apply that bitmap as a post-filter today, the same as with the other Postgres search extensions we tried, which has been performant on the catalogs we run, and we are working through pushing it earlier on larger sets.
• Configurations stay configs. At thirteen billion combinations, the check is real search traffic, not a hand review.

Exact title and brand matches already come back from vector search. The embedding model is good at that. They were not always first. TIN’s tokenizing function is how we pull those matches forward, since we translate queries to English and that model typically stems them.

Building on it

For five years we kept looking for a search engine. Every one we tried wanted to own the whole problem: the index, the ranking, the opinions. What we actually needed was a piece we could put exactly where the problem was, inside a query we already controlled.

TIN is that piece. It went in under one feature without a single brand config changing. It handles catalogs where every product shares a word. Its query language covers logic we’ve been holding together with regex. And hybrid retrieval is the same block, placed a second time. The ranking DSL doesn’t know which one it’s looking at, and that’s the point.

There is far more to come about our experience, thoughts on the performance, and latency comparisons. Until then, thanks to the team at PlanetScale for building the thing we kept trying to build ourselves ❤️.

If you want to read more about our planner/builder, we’ve dumped some scrubbed internal documentation to a gist.

How PlanetScale TIN Became the Foundation for Layers Search was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

The count is not a minor feature for us; it’s a selling point. That’s the reason why facets have been the biggest engineering investment at Layers since 2024-having involved thousands of hours, a frivolous amount of money over three years, three rewrites, and Francis’ sanity at points, all for the sake of a UI component we’ve found that the majority of shoppers do not use. We just completed the fourth rewrite, which has reduced one merchant’s facet storage from 4.7 GB → 13 MB. This reduction is not from a new compression algorithm; it’s because we realized the same small number of facts had been stored thousands of times. This is how we got to this point and what we’re rolling out now.

A facet panel must have a count alongside each value, not only for the ones the shopper has chosen but also for those they have not yet selected, when all those values are scoped to a certain collection or search query. It has to be collection-scoped, disjunctive, and incremental all at the same time, and pre-aggregation is a necessity. The issue then becomes what to pre-aggregate and where to do it.

The two designs we outgrew

We’ve been through this twice already, briefly:

The trigger-maintained counters (2024) involved a per-merchant table listing (collection, facet, value, count), the counts being kept entirely by triggers in Postgres that executed dynamic plpgsql code each time a product was written. It functioned long enough to be deployed, but it failed in the manner in which this kind of design always fails: since increment-based counters have no way to verify themselves against a true reference value, a missed or duplicate trigger causes the count to become corrupted silently and permanently. Also, there was no means of answering a disjunctive count from a simple number; to do that you need the actual set of members for it, not just the running total. Regardless of how well the triggers performed, a counts-only approach was a dead end.

Materialized views (2024–2026). One MV per merchant per facet, with an actual product-ID array as the member set, unlocking filtered counts for the first time. This one didn’t drift; it just got expensive. MVs refresh by full rebuild, so refresh cost scales with catalog size, not with how much actually changed. Fine at ten merchants. Ruinous once a single merchant’s catalog got large. At peak facetable attribute count, we had almost two thousand materialized views all scoped by merchant.

The two cases mentioned above deserve their own posts, while what I’d like to focus on in the remainder of this one is the design we’re currently running, the reason it began to show flaws, and the rewrite that we’ve created and tested as a replacement.

Where we are today: incremental array rollups

In production today, we use a rollup table partitioned by merchant; there is one row for each (collection, facet, value), and in this row an `int8[]` containing the raw Shopify product IDs is stored as the set of members, together with a `member_count` that is updated atomically. Changes are stored in a buffer and are then processed by debounced jobs specific to (merchant, facet), with a checkpointed and resumable reconcile serving as a fallback when something goes wrong.

Within each (collection, facet, value) bucket, the member set itself is sharded, split across N rows keyed by `product_id % modulus` rather than kept as one array per bucket. The reason is concurrency, not size. A popular facet value like “In Stock: true” on a large collection can be touched by hundreds of product writes a minute. If that value lived in a single row, every one of those writes would serialize against the same row lock, and the write that loses the race pays for a full array rewrite that it didn’t even need. Splitting the member set across shard rows spreads that contention across N lock targets instead of one, so writes to the same facet value can happen concurrently as long as they land on different shards.

It works, but it’s a mitigation, not a fix. Write amplification per shard is still proportional to that shard’s member set size; sharding lowers the chances that two writes conflict, but it doesn’t change the fact that adding one ID to the member set requires rewriting the entire array. It’s a cost I did not truly appreciate until we began experimenting with bitmaps: modulo sharding scatters consecutive product IDs by construction, which is exactly the access pattern that defeats Roaring’s run-length compression later on. The sharding scheme was solving gen 3’s concurrency but quietly working against gen 4 before we even designed gen 4.

The base design held up fine. What didn’t was three specific ways it broke down in production, and they’re the reason we started looking for something structurally different instead of patching this one again.

• Removals break incremental maintenance. Additions bump `updated_at`; deletions don’t. A staleness gate meant to skip redundant reconciles ended up skipping the ones cleaning up deleted products; one merchant accumulated ~148,000 zombie memberships before anyone noticed. Fix: every mutation site enqueues its own removal event; repair sweeps are the safety net, not the mechanism.
• A repair job that failed silently. A whole-facet set-diff blew its timeout and got SIGKILL’d on a 41-million-membership facet; never even marked failed. Fixed by chunking the diff per collection with checkpointing.
• The real tell. Drain jobs were 42% of all queue executions; every product write fans out into one write per collection, per facet, per value it touches.

The number that reframed the problem

We knew that we were storing a lot of duplicate data, but the scale of duplication was an afterthought. One merchant had 190,522 rollup rows built from only 906 distinct facet values across roughly 7,650 collections. That’s 210x duplication of the same handful of values, copied once per collection. Another merchant had 1.74 million rows from 6,219 values (279x). The collection dimension was the table. We’d built an architecture around storing the same value-membership information once per collection, when the actual information content was “which products have this value” and “which products are in this collection.” Two much smaller sets, intersected on demand.

There was already a clue in the schema: a global “all products with this value, merchant-wide” sidecar row which had been included for a different reason. It proved to be the solution, so we should keep it; remove the 210 copies and treat collections as sets in their own right. Instead of storing the counts, we should compute each one by taking the intersection.

The rewrite: Roaring bitmaps and the intersection model

This is what we built and measured on a branch seeded with production-scale data, not yet serving live reads.

Why we had to ask our database provider for this

Very few, if any, managed Postgres providers let you arbitrarily install extensions; they have a vetting process and tend to refuse support unless a large portion of their user base requires it.

We’d actually run into this before. Back in 2024, while we were still on RDS, we looked into getting pg_roaringbitmap added at all and found a long-standing open ticket for it; RDS didn’t have enough demand from other customers to prioritize building and maintaining it. We moved our production Postgres to PlanetScale in 2025, and the difference in how fast they move and how much care goes into what they ship has been genuinely impressive. So in June, once we had a concrete design that needed the 64-bit type, we sent them a formal request: add pg_roaringbitmap at a version that includes roaringbitmap64. The core of the argument was the write-amplification problem itself, illustrated as directly as we could:

-- Today: Postgres arrays are immutable. Adding one product ID
-- rewrites the entire array value and its TOAST chunks.
UPDATE facet_rollups
SET member_set = member_set || :productId
WHERE bucket = :bucket
  AND NOT (member_set @> ARRAY[:productId]::int8[]);

-- With pg_roaringbitmap: the bit flips in place.
UPDATE facet_rollups
SET members = rb_or(members, rb_build(ARRAY[:productId]))
WHERE bucket = :bucket;

SELECT rb_cardinality(members)
FROM facet_rollups WHERE bucket = :bucket;              -- facet count

SELECT rb_and_cardinality(members, :filterSet)
FROM facet_rollups WHERE bucket = :bucket;               -- filtered count

For the first statement, the cost is O(set size) whenever there is a single-product write; the whole member array, together with its TOAST chunks, has to be rewritten to add the ID. Sharding does reduce this cost but does not eliminate it, which is precisely the 42%-of-queue drain load mentioned in the previous section. As for the second statement, it is a bounded in-place bit flip regardless of how big the set becomes.

In the following weeks, we began using the 64-bit version in local containers to begin building generation 4 of our faceting system. PlanetScale began rolling it out across their fleet, and we got access on July 29th.

The model

Two tables. One roaringbitmap64 per facet value, merchant-wide (facet_vbm). One per collection, keyed by the Shopify collection ID rather than its handle (colt_bm). Every count becomes bitmap algebra instead of a stored, incrementally-maintained number:

-- Browse listing: count of a value within a collection
SELECT rb64_and_cardinality(value_bm, collection_bm)
FROM facet_vbm, colt_bm
WHERE facet_vbm.value_id = $1 AND colt_bm.collection_id = $2;

-- Search-filtered count: intersect against an eligible-IDs bitmap
-- built from the search candidate set
WITH scope AS MATERIALIZED (
  SELECT rb64_build_agg(product_id) AS scope_bm
  FROM search_candidates
  WHERE query_id = $1
)
SELECT fv.value_id, rb64_and_cardinality(fv.member_set, scope.scope_bm) AS count
FROM facet_vbm fv, scope
WHERE fv.facet_id = $2;

-- Disjunctive count: union the *other* selected values in this facet,
-- then intersect with the collection — dedup falls out of the algebra
WITH selected AS MATERIALIZED (
  SELECT rb64_or_agg(member_set) AS selected_bm
  FROM facet_vbm
  WHERE value_id = ANY($1)
)
SELECT rb64_and_cardinality(selected.selected_bm, collection_bm)
FROM selected, colt_bm
WHERE colt_bm.collection_id = $2;

For the first time, the functions for browsing and searching follow the same code path since in both cases the value’s bitmap is intersected with a scope bitmap, the scope being either a collection or a search result set; disjunctive counts combine the selected values with an AND operation against the collection and deduplication is a result of the algebra rather than requiring a COUNT(DISTINCT …) applied to unnested arrays.

The wins are structural, not just “bitmaps are smaller than arrays,” though they are that too:

• Collection membership changes never touch a facet row again; one bitmap update, done. The collection-leave drift class from the removals war story above cannot exist in this model, because there’s no per-collection facet row left to drift.
• Write volume drops by roughly the average number of collections a product belongs to. That 42%-of-queue drain load has somewhere real to go.
• member_count gets written as rb64_cardinality(member_set) in the same statement that updates the set. Counter-vs-set drift is eliminated by construction, closing the loop all the way back to the first trigger-counter design.

The measured details

There is no sharding and no use of GIN indexes. The prior design of sharding member sets by product ID modulo and spreading write load, which scatters consecutive IDs, totally defeats Roaring’s compression. Bitmap rows are found by primary key on a B-tree instead. There’s no GIN needed because there’s nothing left to index.

We found that TOAST needed real tuning. Roaring bitmap data doesn’t compress; pglz tries and bails on essentially every bitmap. We set STORAGE EXTERNAL to skip that futile attempt on every write, and raised toast_tuple_target to keep the typical 2–8 KB bitmap inline (most value bitmaps average around 61 bytes). fillfactor = 75 keeps drain UPDATEs doing HOT updates on the otherwise-unindexed bitmap column.

Unfortunately, rb64_runoptimize is a no-op on our data, 1.0x on every large bitmap we tested. Most merchants have heavily scattered Shopify IDs that never form the runs that optimization exploits.

One thing that surprised me is that three different structures of the same query with identical output have wildly different latency on Postgres 17.

-- (1) bare column reference: re-detoasted on every row of the AND
SELECT rb64_and_cardinality(facet_vbm.member_set, s.scope_bm) ...
-- 285ms, 286K buffer hits

-- (2) non-materialized CTE: inlined and re-executed per row
WITH scope AS (SELECT rb64_build_agg(id) AS scope_bm FROM eligible)
SELECT rb64_and_cardinality(facet_vbm.member_set, scope.scope_bm) ...
-- 43,399ms

-- (3) materialized CTE: computed once, referenced as a value
WITH scope AS MATERIALIZED (SELECT rb64_build_agg(id) AS scope_bm FROM eligible)
SELECT rb64_and_cardinality(facet_vbm.member_set, scope.scope_bm) ...
-- 84ms, 968 buffer hits

84ms, 285ms, or 43 seconds, depending on a keyword the planner is allowed to silently ignore in the middle spelling. All three return correct rows, which is exactly why this is dangerous; a functional test passes on all of them. We ended up writing an EXPLAIN-shape regression test specifically to catch this, since nothing else catches a 500x latency cliff hiding behind identical output.

Filter semi-joins showed the same shape of surprise: unnesting the bitmap into an array and hash-joining against eligible IDs ran in 5.5ms, versus 18.5ms for a per-row rb64_contains probe and 22.7ms for the legacy array’s @> containment check.

One more example from seeding: collection bitmaps need to be built as membership intersected with the live catalog, not raw membership rows. Raw membership carried forward stale product IDs long after deletion; on one merchant it doubled the stale-ID count, on another it produced a single bitmap over a megabyte for what should have been a small collection.

What the numbers say

Measured on a staging database seeded with production-identical data, and running on the same hardware with the same resources as production:

When I was first running the migration to scaffold the new tables and backfill, I expected it to take a while; our array rollups took about 6 minutes to build the first time, and the bitmaps took 45 seconds for the entire production database. Then when I diffed the freshly-seeded bitmaps against the live catalog, they matched exactly, and I found the current array rollups had already begun to drift. The design that finally stopped drifting is the one where drift became structurally impossible, not just something we got better at detecting.

What’s next

We’re still running performance testing before rolling this out to production, merchant by merchant, the same gradual approach we use for every rollup migration.

Three rewrites taught us that the failure mode to actually fear isn’t slow; it’s silently wrong. A facet count that quietly drifts until a merchant notices their newest arrivals aren’t showing up under a filter that should include them. Generation 4 didn’t happen because we got clever about compression. It happened because we kept being honest about what actually broke, generation after generation, until we landed on a model where that class of breakage isn’t cheaper to detect; it’s not possible in the first place.

None of this would have been possible without PlanetScale. We asked for a fairly niche extension with very few upvotes in June; their extensions team heard our case, saw our pain, and got it live across their fleet before the end of July. Big shout-out and thanks to Chris and Patrick ❤.

Four rewrites in three years, for a number most shoppers never consciously register. I think it was worth it and I’m so glad we don’t have to do a fifth.

Three years, four rewrites, and a Roaring bitmap was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

What the Script Does

Remote product links carry attribution in the URL itself.

Product Network ads look like normal product links, but the URL follows a specific pattern:

/products/{seo-handle}-{encoded-payload}-{signature}-remote

The payload is base64-decoded client-side into version, product ID, advertiser shop ID, ad response ID, and timestamp. The signature is parsed but not browser-validated; that’s presumably done server-side. Clean design: attribution travels with the link; no secondary lookup required.

Two analytics layers share one observation pass.

Products are bucketed into Remote, Collective-Static, and Organic origin types. Remote products emit ad-specific telemetry (rendered, impression, click, duplicate detection) plus standard product impression and click events. Organic products only get the latter. Ad attribution and storefront behavior analytics run through the same pipeline, but downstream consumers don’t have to understand ad state unless they need it.

ShopifyAnalytics.meta first, fetch second.

For organic products, instead of fetching /products/{handle}.js to resolve a product handle to an ID, the script first reads window.ShopifyAnalytics.meta.product and window.ShopifyAnalytics.meta.products. Shopify has already put the data on the page. Product pages have meta.product. Collection pages have meta.products. The script uses it directly and only falls back to a fetch if nothing’s there. The detail that made me look at our own code.

Performance guards run before observation, not during it.

A few things worth noting:

• Elements with a bounding box under 10x10px are filtered out before the IntersectionObserver sees them: hidden template nodes, zero-size utility links, and offscreen duplicates are gone.
• Product links are deduplicated by normalized URL before observation starts. There is one entry per unique product URL. Multiple links pointing to the same product get a single impression ID assigned upfront.
• A hard MAX_PROCESSED_LINKS cap prevents pathological storefronts from creating unbounded work.
• The MutationObserver only rescans when candidate-related attributes change or product nodes are inserted. It does not rescan on every mutation.

rendered and impression are separate events.

The script tracks DOM presence and viewport exposure independently. Shopify can distinguish what loaded from what was actually seen. This is useful for ad quality measurement and not something most tracking scripts do.

What We Found in Our Own Code

A few things we fixed after reading this:

Repeated array scans occurred on every event. Resolving which product a DOM element belonged to ran Array.find() against the full result set on every impression, hover, and click. We indexed results by handle, product ID, and request key on first access and cached the maps.

No element context caching existed. DOM parsing and state matching ran redundantly across impression, hover, and touch flows for the same element. We added a WeakMap cache keyed by element, cleared on each observer refresh so dynamic content stays fresh.

No minimum size filter existed. We weren’t filtering out zero-size or sub-10px elements before observing. We added the same getBoundingClientRect() check. This required updating JSDOM tests since default JSDOM boxes are zero-sized.

Observer set was not deduplicated. Product cards in Shopify themes typically include multiple elements that point to the same product: an image link, a title link, and a quick-add button. We were observing all of them. Now we build a deduplicated candidate set by normalized key (href, data-product-url, data-product-id) before handing anything to IntersectionObserver.

MutationObserver was firing on irrelevant mutations. Shopify storefronts mutate constantly: drawers, app embeds, inventory badges, quantity inputs. We were rescanning on all of it. The observer now checks whether any added node contains a product candidate or whether a candidate-related attribute changed before scheduling a refresh.

Product view fetching occurred when the data was already on the page using the same ShopifyAnalytics.meta pattern. We were fetching /products/{handle}.js as the default on product and collection pages. It’s now the fallback.

The script isn’t doing anything novel. IntersectionObserver for impressions, MutationObserver for dynamic content, and deduplication before observation are all established patterns. What’s notable is the consistency. Every optimization follows from the same principle: reduce work before it starts rather than handling it after.

Tearing Down Shopify’s Product Network Tracking Script was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

To observe this, open the network tab on a Shopify store using a recommendations or analytics app, add an item to the cart, and review the resulting network activity.

You’ll see the storefront’s /cart/add.js. Then, a beat later, you’ll see a /cart.js fired by the app. Sometimes two. Sometimes wrapped in a polling loop. The app is asking Shopify, “Okay, what’s in the cart now?” — a question the storefront’s own response already answered.

This pattern is widespread. Hundreds of the largest Shopify Apps and most session recording tools observe the add event and then issue a follow-up request to reconstruct the cart state. While functional, this approach is unnecessary, generates excess traffic, and makes your script the originator of cart requests rather than a passive observer.

Before detailing our solution, it is important to understand why most apps adopt this approach and the limitations of the alternatives.

How Most Apps Do It (And Why It Doesn’t Scale)

There are three common patterns for cart tracking in third-party Shopify apps. All three share the same flaw: they treat the cart as something to be queried, rather than something to be observed.

Pattern 1: Polling

The simplest approach. Set an interval, fetch `/cart.js` repeatedly, and diff the result.

let previousCart = null;

setInterval(async () => {
  const response = await fetch('/cart.js');
  const cart = await response.json();

  if (previousCart && cart.item_count !== previousCart.item_count) {
    handleCartChange(previousCart, cart);
  }

  previousCart = cart;
}, 2000);

This method works across all Shopify themes, requires no knowledge of storefront cart request implementation, and is simple to deploy. As a result, many apps use this approach by default.

Back in early 2025 I called this out publicly on Gorgias and Alia — both used to run polling intervals of 500ms to 2 seconds on storefronts across their install base. Both companies have since adopted the patching pattern we cover or use Shopify Web Pixels.

The problems:

• It hammers Shopify’s Storefront API rate limits. Shopify enforces a bucket-based rate limit on /cart.js calls. A 500ms polling interval generates 7,200 requests per hour per active session. On a busy storefront with hundreds of concurrent sessions, apps polling this aggressively can exhaust the rate limit bucket — and when that happens, the storefront’s own cart requests start getting 429’d. Shoppers hit “add to cart” and nothing happens. The app caused the outage.
• It’s laggy by design. Cart changes are only visible after the next tick, which means your add_to_cart event fires up to 2 seconds late.
• Diffing is fragile. Item count alone misses quantity changes on existing line items. Full deep-diffing cart objects means you’re now maintaining that logic indefinitely.
• It doesn’t give you the add context. You know the cart changed, but you’ve lost the original request, the product handle, the source surface, and any properties the storefront attached. Attribution at this point is a guess.

Pattern 2: PerformanceObserver

A more sophisticated approach. The PerformanceObserver API can observe completed network requests via PerformanceResourceTiming entries, which gives you URL, timing, and response size — without patching anything.

const observer = new PerformanceObserver((list) => {
  for (const entry of list.getEntries()) {
    if (entry.initiatorType === 'fetch' && entry.name.includes('/cart/add')) {
      // We know a cart add happened — but that's all we know
      handleCartAdd(entry);
    }
  }
});

observer.observe({ type: 'resource', buffered: true });

This method is non-invasive and does not modify fetch or XHR. Some analytics vendors use it as a ‘safe’ observation layer, considering it less risky than modifying prototypes.

The problems:

• You get timing metadata, not request or response bodies. PerformanceResourceTiming tells you a fetch to /cart/add.js completed in 340ms. It doesn’t tell you what was added, which variant, how much, or what the cart looks like now.
• So you still have to make a follow-up /cart.js call to find out what changed. The observation gives you a trigger, not the data.
• You can’t enrich the request. There’s no hook into the outbound payload; there is no way to inject a session ID or attribution token before the request leaves the browser.
• PerformanceObserver has gaps. Entries can be dropped under memory pressure. Buffering behavior varies by browser. It’s a telemetry API, not a reliable interception layer.

Pattern 3: Custom Event Listening

Some themes and apps dispatch custom DOM events when cart state changes — cart:updated, theme:cart:added, and similar. Listening to these is elegant when it works:

document.addEventListener('cart:updated', async (event) => {
  // Some themes pass cart data in event.detail
  const cart = event.detail?.cart ?? await fetch('/cart.js').then(r => r.json());
  handleCartUpdate(cart);
});

The problems:

• This is entirely theme-dependent. Dawn fires different events than Impulse. Custom themes fire none. There’s no standard.
• You’re at the mercy of the theme developer’s event naming and payload shape. If they change it, your tracking breaks silently.
• It still usually requires a follow-up fetch because most themes don’t include full cart data in the event payload.
• You still can’t touch the outbound request. No request enrichment, no line-item attribution.

The Right Approach: Patch the Primitives

All three patterns share a common flaw: they detect that an event occurred and then request the data separately. The original cart request, which contains all necessary information, is not utilized.

The solution is to intercept cart activity at the network layer. Layers’ cart tracking is based on patching the browser’s network primitives to observe existing cart traffic without generating additional requests.

Patching fetch and XHR

The interceptor patches both network primitives on initialization:

const originalFetch = window.fetch;

window.fetch = function (input, init) {
  const url = resolveUrl(input);

  if (isCartRoute(url) || isLayersRoute(url)) {
    return handleFetchIntercept(originalFetch, input, init, url);
  }

  return originalFetch.apply(this, arguments);
};

const originalOpen = XMLHttpRequest.prototype.open;
const originalSend = XMLHttpRequest.prototype.send;

XMLHttpRequest.prototype.open = function (method, url, ...rest) {
  this._layersUrl = resolveUrl(url);
  this._layersMethod = method;
  return originalOpen.apply(this, [method, url, ...rest]);
};

XMLHttpRequest.prototype.send = function (body) {
  if (isCartRoute(this._layersUrl)) {
    return handleXhrIntercept(this, body, originalSend);
  }
  return originalSend.apply(this, arguments);
};

The patch is deliberately narrow. isCartRoute matches a specific allowlist:

function isCartRoute(url) {
  if (!url) return false;
  const path = new URL(url, location.origin).pathname;
  return [
    '/cart/add', '/cart/add.js',
    '/cart/change', '/cart/change.js',
    '/cart/update', '/cart/update.js',
    '/cart.js',
  ].some(route => path.endsWith(route));
}

All other requests pass through unchanged. The script acts as a listener on a defined set of routes, not as a proxy.

The Part Most Apps Get Wrong: Payload Safety

Before getting into what we actually inject, it’s worth being explicit about something: patching fetch and XHR on a live Shopify storefront is dangerous if you don’t handle every possible request payload type. You are sitting in the critical path of every cart interaction in the store. If your enrichment code throws on an unexpected body format, the cart breaks. Not for you, for the shopper.

In early 2025, an app deployed a similar network patch across its portfolio of Shopify brands without fully accounting for the payload variants Shopify storefronts can send. The patch assumed JSON bodies. When it encountered a FormData request, which older themes and some native Shopify UI components still use, the enrichment code threw, the modified request was never sent, and cart adds silently failed. This rolled out to their entire install base before it was caught.

That’s the failure mode. Here’s what you actually have to handle.

The Shopify cart payload matrix

Shopify storefront cart requests can arrive in four distinct body formats depending on the theme, the Shopify UI component (Cart API, Section Rendering API, native Liquid forms), and whether the request was constructed by theme JS or a third-party app:

• JSON string → Modern themes, Ajax cart APIs, most app-generated requests
• FormData → Native Liquid <form> submits, older themes, some Shopify UI components
• URLSearchParams → Some older Ajax patterns, theme kit tooling
• null / undefined → GET /cart.js reads, some edge cases

The enrichment code must handle all four formats and, critically, pass through any unrecognized formats without error.

function enrichRequestBody(body, sessionId, attributionToken, url) {
  try {
    // FormData — mutate in place, it's passed by reference
    if (body instanceof FormData) {
      body.set('attributes[_layers_session_id]', sessionId);
      if (isAddRoute(url) && attributionToken) {
        body.set('properties[_layers_attribution]', attributionToken);
      }
      return body;
    }

    // URLSearchParams — same pattern as FormData
    if (body instanceof URLSearchParams) {
      body.set('attributes[_layers_session_id]', sessionId);
      if (isAddRoute(url) && attributionToken) {
        body.set('properties[_layers_attribution]', attributionToken);
      }
      return body;
    }

    // JSON string — parse, enrich, re-serialize
    if (typeof body === 'string' && body.trim().startsWith('{')) {
      const parsed = JSON.parse(body);
      parsed.attributes = {
        ...parsed.attributes,
        _layers_session_id: sessionId,
      };
      if (isAddRoute(url) && attributionToken && parsed.items) {
        parsed.items = parsed.items.map(item => ({
          ...item,
          properties: { ...item.properties, _layers_attribution: attributionToken },
        }));
      } else if (isAddRoute(url) && attributionToken && parsed.id) {
        // Single-item add shorthand
        parsed.properties = { ...parsed.properties, _layers_attribution: attributionToken };
      }
      return JSON.stringify(parsed);
    }

    // null, undefined, ReadableStream, Blob, ArrayBuffer, or anything else
    // — return completely untouched, no matter what
    return body;

  } catch (err) {
    // Enrichment failed — return the original body unmodified
    // The cart request must succeed even if we can't annotate it
    logger.debug('Cart body enrichment failed, passing through original', err);
    return body;
  }
}

Three things to notice:

The try/catch block encompasses the entire function, not just individual branches. If any error occurs, such as a malformed JSON body or unexpected FormData structure, the original body is returned unmodified. The cart request continues, ensuring that annotation is optional and cart functionality is preserved for the shopper.

Unknown body types are returned as-is. Formats such as ReadableStream, Blob, ArrayBuffer, or any future types from Shopify are not introspected, and no errors are thrown.

The JSON handling logic supports both single-item and array-item add formats. Shopify’s cart add API accepts both { id, quantity } and { items: […] } structures. Supporting only one format results in missed cart additions.

XHR body handling

XHR send() receives the same payload variety, but without the init wrapper — the body is passed directly:

XMLHttpRequest.prototype.send = function (body) {
  if (!isCartRoute(this._layersUrl)) {
    return originalSend.apply(this, arguments);
  }

  let enrichedBody = body;
  try {
    enrichedBody = enrichRequestBody(body, getSessionId(), resolveAttribution(), this._layersUrl);
  } catch (err) {
    // Enrichment failed entirely — fire original body
    logger.debug('XHR enrichment failed', err);
    enrichedBody = body;
  }

  return originalSend.call(this, enrichedBody);
};

Same principle: if enrichment fails at any level, originalSend fires with the original body. We never swallow the request itself.

Test against all four formats before shipping

If you are developing a similar solution, the following test matrix should be completed before deploying to production:

// 1. JSON string — modern themes
fetch('/cart/add.js', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ id: variantId, quantity: 1 }),
});

// 2. FormData — Liquid form submits
const fd = new FormData();
fd.append('id', variantId);
fd.append('quantity', 1);
fetch('/cart/add.js', { method: 'POST', body: fd });

// 3. URLSearchParams
const params = new URLSearchParams({ id: variantId, quantity: 1 });
fetch('/cart/add.js', { method: 'POST', body: params });

// 4. XHR with JSON
const xhr = new XMLHttpRequest();
xhr.open('POST', '/cart/add.js');
xhr.setRequestHeader('Content-Type', 'application/json');
xhr.send(JSON.stringify({ id: variantId, quantity: 1 }));

All four formats should add items to the cart successfully, regardless of enrichment success. Cart functionality must be preserved; annotation is optional.

Request-Side: Enriching What’s Already Being Sent

When the storefront fires a matching cart request, we modify the outbound payload before it leaves the browser. There are two things we inject: a session ID on all cart requests, and a line-item attribution token on add flows only.

Session stitching

Every cart payload format gets a _layers_session_id attribute injected at the cart level:

function injectSessionAttribute(body, sessionId) {
  if (body instanceof FormData || body instanceof URLSearchParams) {
    body.set('attributes[_layers_session_id]', sessionId);
    return body;
  }

  if (typeof body === 'string') {
    try {
      const parsed = JSON.parse(body);
      parsed.attributes = {
        ...parsed.attributes,
        _layers_session_id: sessionId,
      };
      return JSON.stringify(parsed);
    } catch {
      return body;
    }
  }

  return body;
}

This provides downstream systems with a stable identifier for associating cart events with a browser session, eliminating the need for additional requests.

Line-item attribution (add flows only)

For /cart/add requests specifically, we also stamp a _layers_attribution token at the line-item level:

function injectItemAttribution(item, attributionToken) {
  return {
    ...item,
    properties: {
      ...item.properties,
      _layers_attribution: attributionToken,
    },
  };
}

This process is not applied to /cart/update or /cart/change requests. Attribution is only added during add flows, as mutation flows do not provide meaningful attribution context. Enrichment is applied only where justified.

Attribution itself is resolved conservatively:

function resolveAttribution(item) {
  // 1. Already on the item — leave it alone
  if (item.properties?._layers_attribution) {
    return item.properties._layers_attribution;
  }

  // 2. Current PDP context matches
  const pdpContext = getCurrentPdpContext();
  if (pdpContext && pdpContext.variantId === item.id) {
    return pdpContext.attributionToken;
  }

  // 3. Last valid product interaction
  const lastInteraction = getLastProductInteraction();
  if (lastInteraction?.isValid() && lastInteraction.variantId === item.id) {
    return lastInteraction.attributionToken;
  }

  // 4. Skip rather than guess
  return null;
}

If product identity cannot be reliably resolved, the function returns null and attribution is omitted. Incorrect attribution is considered more detrimental than missing data.

Response-Side: Learning from What the Storefront Already Received

This is where the ‘stop making cart calls’ principle provides the most significant benefit.

Rather than issuing a /cart.js request after a cart mutation, the script listens to the response already generated by the storefront’s request.

The fetch interception pattern

The key implementation detail here is how we handle the response without disrupting the merchant’s promise chain:

function handleFetchIntercept(originalFetch, input, init, url) {
  // Modify the outbound request
  const modifiedInit = enrichRequest(init, url);

  // Fire the original fetch — return the promise immediately
  const fetchPromise = originalFetch.call(window, input, modifiedInit);

  // Process the response in a detached chain
  // Errors here cannot propagate to the merchant's await
  fetchPromise
    .then(response => response.clone())
    .then(cloned => cloned.json())
    .then(data => processCartResponse(data, url))
    .catch(err => {
      // Log internally, never throw
      logger.debug('Cart response processing failed', err);
    });

  // Return the original promise unmodified
  return fetchPromise;
}

The response is cloned before reading to avoid interfering with the storefront’s .then() handler. All processing occurs in a detached chain, ensuring that parsing failures do not result in unhandled rejections in the merchant’s error tools.

Add-to-cart responses

When a /cart/add succeeds, we use the response body to finalize the add_to_cart event:

function processAddResponse(responseData, originalRequestBody) {
  try {
    const items = responseData.items ?? [responseData];

    for (const item of items) {
      const product = resolveProduct(item.product_id, item.variant_id);
      const value = item.final_price ? item.final_price / 100 : null;

      emit('add_to_cart', {
        product_id: item.product_id,
        variant_id: item.variant_id,
        quantity: item.quantity,
        value,
        attribution: item.properties?._layers_attribution ?? null,
      });
    }
  } catch {
    // Fall back to request payload — event fires either way
    processAddFromRequest(originalRequestBody);
  }
}

If response parsing fails, the script falls back to the request data. The event is triggered in either case.

Cart state from mutation responses

GET /cart.js, /cart/update, and /cart/change responses update an in-memory productsInCart map:

function syncCartState(responseData) {
  if (!responseData?.items) return;

  productsInCart.clear();

  for (const item of responseData.items) {
    productsInCart.set(item.variant_id, {
      productId: item.product_id,
      variantId: item.variant_id,
      quantity: item.quantity,
      handle: item.handle,
    });
  }
}

This context is then used to exclude products in Layers block requests, filtering out items already in the cart, previously shown, or currently viewed. All information is obtained from existing storefront responses without additional requests.

Why Stack Trace Visibility Matters

A significant drawback of naively wrapping fetch is that the script can dominate merchant debugging sessions. If a developer investigates a cart error and the wrapper uses async/await throughout, the script appears first in stack traces.

The initial implementation looked like this:

// Before — our wrapper was in the call stack
window.fetch = async function (input, init) {
  const modifiedInit = await enrichRequest(init);
  const response = await originalFetch(input, modifiedInit);
  await processResponse(response.clone());
  return response;
};

Any error in processResponse could propagate, causing the script’s frame to appear in every stack trace involving a cart fetch. Merchant debugging tools would then flag the script.

The solution is to return the original promise immediately and perform all processing in a detached chain:

// After — we're not in the call stack for the merchant's await
window.fetch = function (input, init) {
  const modifiedInit = enrichRequestSync(init);
  const promise = originalFetch.call(window, input, modifiedInit);

  // Detached — errors here are ours, not the merchant's
  promise.then(r => r.clone()).then(r => r.json())
    .then(processResponse)
    .catch(logger.debug);

  return promise;
};

The merchant’s await fetch(…) now resolves as it would with native fetch. All processing occurs in a parallel chain, ensuring it does not interfere with the merchant’s code.

The Rules

Every decision above follows from a small set of constraints:

1. Don’t make extra cart calls to observe the cart. The storefront already made the call.
2. Enrich in place, don’t replay. Modify the outbound payload and listen for the inbound response.
3. Patch only known routes. Don’t become a general-purpose network proxy.
4. Add the minimum metadata. Session ID and attribution token, nothing else.
5. Never guess attribution. Skip rather than stamp the wrong product.
6. Treat add and mutation flows differently. Line-item attribution belongs only on adds.
7. Keep response processing detached. Our failures can’t break merchant cart flows.
8. Stay out of the stack trace. Developers debugging cart issues shouldn’t be reading our code.

The Broader Point

While observing network primitives is a well-established pattern, few Shopify apps apply it to cart tracking. Most continue to listen for events and then re-fetch to reconstruct state.

The optimal cart tracking script understands cart activity without generating additional cart requests. It observes rather than duplicates, enriches rather than replays, and remains adjacent to the merchant’s stack rather than interfering with it.

If a script issues cart requests to determine cart state, it likely already has the necessary information but is not observing the correct source.

Stop Making `/cart.js` Calls was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Search is easy to ship, but hard to know when you’ve broken it.

You can change a ranking model, deploy it, and have no idea whether it improved or worsened. Conversion rate takes days to move. Click-through rate is noisy. And asking someone to manually review results for hundreds of queries is the kind of thing that happens once, never gets maintained, and tells you nothing about what changed between this week and last.

When we started building Layers, we knew we needed a rigorous way to measure search quality that could keep pace with our rapid iteration on ranking models. What we built is an end-to-end evaluation loop that combines LLM judging, classical IR metrics, A/B experimentation, and our own analytics query language to close the feedback cycle between a model change and a reliable signal about whether it actually helped.

Why E-commerce Search Is Hard to Evaluate

Most search evaluation literature assumes you have relevance labels, which are human annotations that say “this result is good for this query.” At scale, that’s expensive and slow. User signals like clicks and purchases are the natural proxy, but they’re lagged, sparse for tail queries, and confounded by position bias.

E-commerce search has a few additional complications.

The catalog is the constraint. A query for “navy blue midi dress” might have zero relevant results, not because the search is broken, but because the merchant doesn’t carry it. A judge who doesn’t understand the catalog will score that as a failure. It’s not.

Intent is messy. A query like “gold” on a jewelry site means something completely different than “gold” on a workwear site. Branded terms, attribute-heavy queries, negation (“no leather”), and ambiguous intent all behave differently and need to be evaluated differently.

Head queries aren’t the whole story. The top 10 queries for any given store might drive 40% of search sessions, but the tail is enormous. A model optimized for head queries at the expense of tail quality is a common failure mode that aggregate metrics hide completely.

These constraints shaped every decision we made about how to build our evaluation system.

The Evaluation Pipeline

When I trigger an evaluation run, the system replays real user queries against the live search engine and has an LLM judge score the results. Here’s how that actually works.

Sourcing Queries

Queries come from one of two places. The first is real traffic: we sample the most frequent search terms from our analytics data over a configurable date window, ranging from 1 to 90 days, with a cap of 1,000 queries per run. The second is seeded sets, which are curated query lists we’ve pre-loaded for specific evaluation scenarios, such as testing attribute-heavy queries or branded searches in isolation.

Classifying Queries Before Judging

Before we send anything to the judge, every query gets classified along two axes.

The first is the volume tier, determined locally using frequency ratios relative to the top query in the set. Head queries sit at 10% or more of the maximum frequency. Torso queries range from 1% to 10%. Tail queries are everything below 1%.

The second is the semantic type, determined by an LLM in batches. Each query is labeled as branded or generic, along with any applicable modifier flags: negative intent, attribute-heavy, ambiguous intent, or synonym-heavy.

This classification matters because a ranking model can be excellent on head-branded queries and quietly terrible on tail attribute-heavy ones. Aggregate scores hide that. Bucket breakdowns reveal it.

Running the Search + LLM Judge

Each query runs through the actual search pipeline, the same code path a real user hits, and then the top 20 results get sent to an LLM judge running on AWS Bedrock.

The judge is multimodal. It pulls both text metadata (title, description, product type, tags) and product images from Shopify CDN thumbnails. Product imagery carries a signal that text doesn’t. A query for “casual” means something different visually than it does semantically, and a result that looks off-brand wildly is a worse result than a text-only judge would recognize.

The judge also receives catalog context: a description of the store, its product types, and its brands. This is what lets it distinguish “bad results” from “query outside this catalog.”

The judge scores each result set across six dimensions, all on a 0 to 100 scale. Relevance measures whether the results match the query topic. Intent measures whether the results match what the user intended to do. Attribute measures whether constraints like color, size, or material are respected. Brand measures whether branded queries return the correct brand. Negative measures whether there are clearly wrong results in the top positions. Diversity measures the appropriateness of the variety in the result set.

In addition to those six scores, the judge assigns per-product relevance grades on a 0–3 scale. Those grades are what feed the IR metrics.

IR Metrics

From the per-product grades, we compute four standard information retrieval metrics.

NDCG@10, or Normalized Discounted Cumulative Gain, is the primary one I watch. It measures not just whether relevant results appear, but whether they appear near the top, applying a logarithmic discount for lower positions. Ranking a great product third instead of first is a measurable loss, not a rounding error.

MRR, or Mean Reciprocal Rank, tells us how far down the list a user has to scroll to find the first genuinely relevant result. Recall@10 tells us how many of the relevant products in the catalog actually made it into the top 10. Estimated CTR@10 is a position-weighted click probability estimate based on known click curves.

Finalization and Report

Once all query jobs are complete, the system aggregates everything into a report. That includes average scores across all six judge dimensions, breakdowns by bucket (head, torso, tail, branded, generic, and modifier type), a histogram of score distributions, and the top and bottom-scoring queries. The bottom list is the most useful one.

There’s also an LLM-generated summary of strengths, weaknesses, and areas to investigate. That summary isn’t a replacement for reading the data. It’s a starting point, useful for surfacing patterns that aren’t obvious from aggregate numbers, like “tail attribute-heavy queries are underperforming because the model underweights exact keyword matches relative to semantic similarity.”

How the Ranking Model Is Defined

To understand what the evaluation is measuring, it helps to understand what it’s evaluating against.

Ranking models at Layers are defined as versioned JSON files. Each file declares a set of features, along with their weights, transforms, and normalization options. A simplified excerpt looks like this:

{
  "name": "default",
  "version": 1,
  "features": [
    { "feature": "text_similarity", "weight": 0.18 },
    { "feature": "image_similarity", "weight": 0.17 },
    { "feature": "keyword:title:bm25", "weight": 0.1, "normalize": "minmax" },
    { "feature": "product_metric:views_7d", "weight": 0.05, "transform": "sigmoid" },
    { "feature": "published_at", "weight": 0.1, "transform": "decay_days" }
  ],
  "options": { "normalization": "minmax", "combiner": "linear", "scoreFilterStd": 3.0 }
}

Features can include embeddings (text or image similarity), keyword signals, behavioral signals such as views, sales, and purchase rate across various time windows, or product properties such as recency. A model compiler takes these definitions and produces the final hybrid score.

Models compose into pipelines. A pipeline specifies the candidate generation strategy, which ranking model to apply, and an optional second-pass rerank model. That separation matters because candidate generation and ranking are distinct failure modes with different fixes.

A/B Experimentation

Changing a feature weight and running an evaluation tells you how the model performs against an LLM judge. That’s necessary but not sufficient. You also need to know what happens with real users.

Experiments at Layers are defined as JSON files alongside the models. Each experiment specifies a baseline model, a candidate model, a traffic split percentage, and which stores its active on. The assignment is deterministic based on the session ID, so a user sees consistent results across their session, and we can reproduce exactly which model served any given search request.

Every search request is logged with the experiment ID and experiment group fields, which means the evaluation and analytics systems are unified. You can measure LLM judge scores by experiment group and business outcomes by experiment group using the same query language.

LayersQL: Closing the Loop with Business Metrics

LLM judge scores and IR metrics tell you about search quality. Business metrics tell you about business impact. We built LayersQL, our own analytics query language derived from ShopifyQL, to make it easy to query search data without digging into the underlying schema.

A typical experiment analysis looks like this:

FROM search_text
SHOW SUM(total_sales), SUM(quantity_purchased), AVG(click_rate)
GROUP BY experiment_group
SINCE -30dFROM search_text

The syntax will feel familiar to anyone who has used ShopifyQL. Experiment ID and experiment group are first-class dimensions in every dataset, so any analytics query can be segmented by experiment arm without any extra work.

How It All Fits Together

The full loop looks like this. We define a ranking model in JSON and create an experiment that routes some percentage of traffic to it. The experiment assigner deterministically splits sessions between baseline and candidate, logging the assignment on every search request. We run an auto-evaluation, sourcing real queries from our analytics data, classifying them, executing the search pipeline, and having the LLM judge-score the results with full catalog context. The evaluation report provides NDCG@10, MRR, and six judge dimensions, broken down by query bucket. We then run a LayersQL query to check whether the candidate group is seeing different business outcomes in terms of revenue, units, and click rate. If both signals point in the same direction, we proceed with the experiment. If they diverge, we investigate why.

One feature worth calling out is the “reuse” evaluation mode. Instead of re-fetching queries, it re-evaluates the same query set from a previous run against a new model configuration. That makes comparison clean: same queries, different ranking, comparable scores. The comparison report compares them query-by-query, counting improvements, regressions, and unchanged queries based on score-delta thresholds.

What This Has Changed

Before we had this system, ranking model changes were essentially done in the dark. You’d make a change, watch the conversion rate for a week, and try to infer whether it helped or was just noise. The iteration loop was slow, and the signal was unreliable.

Now we can run an evaluation, get NDCG and judge dimension scores broken down by query type, compare them against a baseline, and have a reasonably confident signal before we touch live traffic. The experiments layer means we can ship to a traffic split first and graduate only when both the evaluation and business signals agree.

The multimodal judge has been the biggest surprise in practice. There are product results that look fine on paper, right category, matching keywords, that the judge correctly flags as wrong because the product image is obviously misaligned with what the query was asking for. Text-only evaluation would miss that entirely.

The bucket breakdowns have been the second-most valuable part. Tail query performance is invisible in aggregate metrics. Breaking results out by head, torso, and tail has surfaced model behaviors we would have shipped without noticing, including one case where a weight change that improved NDCG@10 overall was quietly degrading tail-branded queries by 12 points.

Search evaluation is one of those infrastructure investments that doesn’t feel urgent until you’ve made a bad deployment and spent a week figuring out why conversion dropped. We’re glad we built it early.

How We Evaluate Search Quality at Scale With LLM Judging and IR Metrics was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

PlanetScale Postgres offers a managed, branch-native database layer for your Postgres nodes. This enables branch-based deployments, global replication, and connection multiplexing. However, it introduces failure modes not found in standard Laravel and Postgres environments. This article outlines how to address three key production issues:

1. PgBouncer binding incompatibilities: Boolean values and prepared statements behave differently when routed through a connection pooler.
2. Replica lag and read/write routing: Retrying a failed read on the same replica that encountered an error is usually ineffective.
3. PlanetScale-specific lost connection signals: Laravel’s default detector does not recognize several of these errors.

The following code has been tested in production on a heavy OLTP workload.

Architecture Overview

Laravel app
    │
    ├── write connection  ──► PgBouncer ──► PlanetScale primary branch
    │
    └── read connection   ──► PgBouncer ──► PlanetScale replica

PlanetScale places PgBouncer in front of each branch. Your Laravel application connects to PgBouncer endpoints rather than directly to Postgres. By default, PgBouncer uses transaction pooling mode on PlanetScale, which means:

• Server-side prepared statements are not supported because each transaction receives a different backend connection.
• Emulated prepared statements (PDO-level) must be used instead.
• Type binding semantics change because PHP’s true and false values cannot be reliably passed as PDO::PARAM_BOOL. The pooler may route the bind call and the execute call to different backend connections.

Part 1: Fixing Boolean and Type Bindings for PgBouncer

Laravel’s default PostgresConnection uses PDO::PARAM_BOOL when binding PHP booleans. With PgBouncer transaction pooling, this is inconsistent because the backend receiving the bind may not execute the query.

To resolve this, convert booleans to strings at the PHP layer before they reach PDO, and bind them as PDO::PARAM_STR.

/**
 * Prepare the bindings for the query.
 *
 * Casts booleans to strings ('true'/'false') for PgBouncer compatibility.
 */
public function prepareBindings(array $bindings)
{
    $grammar = $this->getQueryGrammar();

    foreach ($bindings as $key => $value) {
        if ($value instanceof DateTimeInterface) {
            $bindings[$key] = $value->format($grammar->getDateFormat());
        } elseif (is_bool($value)) {
            $bindings[$key] = $value ? 'true' : 'false';  // <-- key fix
        } elseif (is_array($value)) {
            $bindings[$key] = json_encode($value);
        } elseif ($value instanceof \BackedEnum) {
            $bindings[$key] = $value->value;
        } elseif ($value instanceof \UnitEnum) {
            $bindings[$key] = $value->name;
        } elseif (is_object($value) && method_exists($value, '__toString')) {
            $bindings[$key] = (string) $value;
        } elseif (is_object($value)) {
            $bindings[$key] = json_encode($value);
        }
    }

    return $bindings;
}

prepareBindings runs before bindValues. By the time values reach bindValues, each boolean is already the string ‘true’ or ‘false’, so the match expression defaults to PDO::PARAM_STR:

public function bindValues($statement, $bindings)
{
    foreach ($bindings as $key => $value) {
        $statement->bindValue(
            is_string($key) ? $key : $key + 1,
            $value,
            match (true) {
                is_int($value)      => PDO::PARAM_INT,
                is_resource($value) => PDO::PARAM_LOB,
                $value === null     => PDO::PARAM_NULL,
                default             => PDO::PARAM_STR,  // booleans land here as strings
            },
        );
    }
}

A few other binding edge cases are handled above:

• Arrays are JSON-encoded, which Postgres accepts for jsonb columns and array casts in Eloquent.
• BackedEnum and UnitEnum values are unwrapped to their scalar forms to avoid errors related to object-to-string conversion.
• Objects that implement __toString are cast accordingly. Objects without this method are JSON-encoded as a fallback.

Enabling Emulated Prepared Statements

In config/database.php, set PDO::ATTR_EMULATE_PREPARES on the PlanetScale connection:

'pgsql' => [
    'driver'   => 'pgsql',
    'host'     => env('DB_HOST'),
    'port'     => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'options'  => [
        PDO::ATTR_EMULATE_PREPARES => true,
    ],
],

This configuration shifts prepared statement handling to PHP and PDO instead of the Postgres server. This is required for PgBouncer transaction pooling.

Part 2: PlanetScale-Specific Lost Connection Detection

Laravel includes LostConnectionDetector, which handles a set of known Postgres and MySQL error strings. PlanetScale’s proxy layer introduces additional failure messages that Laravel does not recognize by default:

• no primary available for branch — PlanetScale primary failover in progress
• no replica available for branch — Replica restarting or unhealthy
• no running members available for branch — All nodes for branch are unavailable
• failed to connect to upstream — PgBouncer can't reach the Postgres backend
• failed to send startup message — Startup handshake failure during reconnect
• failed to read startup message — Same, on the read side
• canceling statement due to conflict with recovery — Replica recovery conflict (hot standby)

The last message is especially important for replica workloads. It is a standard Postgres error that occurs when a read query conflicts with WAL replay on a hot standby replica, which is common during high write throughput.

Extending LostConnectionDetector:

namespace App\Database;

use Illuminate\Database\LostConnectionDetector as BaseLostConnectionDetector;
use Illuminate\Support\Str;
use Throwable;

class LostConnectionDetector extends BaseLostConnectionDetector
{
    public function causedByLostConnection(Throwable $e): bool
    {
        if (parent::causedByLostConnection($e)) {
            return true;
        }

        $message = $e->getMessage();

        return Str::contains($message, [
            'no primary available for branch',
            'no replica available for branch',
            'no running members available for branch',
            'failed to connect to upstream',
            'failed to send startup message',
            'failed to read startup message',
            'canceling statement due to conflict with recovery',
        ]);
    }
}

Register this in a service provider:

use App\Database\LostConnectionDetector;
use Illuminate\Database\Connection;

public function boot(): void
{
    Connection::resolveDetectorUsing(fn () => new LostConnectionDetector());
}

Part 3: Smarter Retry Logic for Replica Failures

Laravel’s default retry behavior on a lost connection is straightforward: reconnect and rerun the query on the same connection type. For a read that fails due to a conflict with recovery, this results in retrying on the same replica, which will likely fail again because the replica is still recovering.

The correct behavior is to fall back to the primary for that retry:

protected function tryAgainIfCausedByLostConnection(
    QueryException $e,
    $query,
    $bindings,
    Closure $callback
) {
    if (! $this->causedByLostConnection($e->getPrevious())) {
        throw $e;
    }

    $this->reconnect();

    // If this was a read query and we're in a request context,
    // retry on the primary instead of the replica.
    if (
        $this->latestReadWriteTypeUsed() !== 'read'
        || ! $this->shouldForcePrimaryFallbackForLostReadRetry()
    ) {
        return $this->runQueryCallback($query, $bindings, $callback);
    }

    $readOnWriteConnection = $this->readOnWriteConnection;
    $this->useWriteConnectionWhenReading(true);

    try {
        return $this->runQueryCallback($query, $bindings, $callback);
    } finally {
        $this->useWriteConnectionWhenReading($readOnWriteConnection);
    }
}

The finally block is essential because it restores the original readOnWriteConnection flag, regardless of whether the retry throws an exception. Without this, a failed retry would route all subsequent reads through the primary for the remainder of the request.

Why shouldForcePrimaryFallbackForLostReadRetry()?

protected function shouldForcePrimaryFallbackForLostReadRetry(): bool
{
    return request() !== null;
}

This guard is necessary because request() returns null in non-HTTP contexts such as queue workers, console commands, and scheduled jobs. In these cases, the request lifecycle does not manage connection state in the same way, and primary fallback may not be needed. Queue workers often use dedicated connection configurations.

For HTTP requests, this returns true, enabling the primary fallback on read retries.

Part 4: Wiring It Together

The custom connection class extends PostgresEnhancedConnection from tpetry/laravel-postgresql-enhanced, which adds Postgres-native features such as RETURNING, array operations, and full-text search helpers. All overrides integrate cleanly:

namespace App\Database;

use Closure;
use DateTimeInterface;
use Illuminate\Database\QueryException;
use PDO;
use Tpetry\PostgresqlEnhanced\PostgresEnhancedConnection;

class PostgresPGBouncerExtension extends PostgresEnhancedConnection
{
    // tryAgainIfCausedByLostConnection — retry on primary for failed reads
    // bindValues — PDO::PARAM_STR for booleans
    // prepareBindings — stringify booleans, JSON-encode arrays/objects
}

Register the custom connection driver in AppServiceProvider:

use App\Database\PostgresPGBouncerExtension;
use Illuminate\Database\Connection;

public function register(): void
{
    Connection::resolveConnectionUsing(
        'pgsql',
        fn ($connection, $database, $prefix, $config) =>
            new PostgresPGBouncerExtension($connection, $database, $prefix, $config)
    );
}

Part 5: Replica Lag and Sticky Sessions

Even with primary fallback on retry, replica lag presents a more subtle issue: a recently committed write may not be immediately visible on the replica. Laravel provides useWriteConnectionWhenReading() to force reads through the primary for the duration of a request, but you must determine when to use it.

A common approach is to track whether the current request has performed a write operation to the database:

// In a middleware or base controller
DB::afterSave(function () {
    DB::connection()->useWriteConnectionWhenReading(true);
});

Alternatively, you can explicitly wrap write-then-read operations:

DB::transaction(function () use ($order) {
    $order->save();
    // Any reads within this closure will use the write connection automatically,
    // since Laravel routes all queries inside a transaction to the primary.
});

// Back on the replica after the transaction closes.
$freshOrder = Order::find($order->id);

Inside a transaction, Laravel routes all queries to the primary. The lag issue arises after the transaction closes, when the next read returns to the replica. If you need immediately committed data in the same request, call useWriteConnectionWhenReading(true) after the transaction.

Summary

• PgBouncer breaks PDO::PARAM_BOOL bindings — Stringify booleans in prepareBindings, bind as PARAM_STR
• PlanetScale proxy errors not recognized as lost connections — Extend LostConnectionDetector with PlanetScale-specific strings
• Read retry after replica recovery conflict fails again — Override tryAgainIfCausedByLostConnection to use primary on first retry
• Post-write reads hit stale replica — Use useWriteConnectionWhenReading(true) after writes in latency-sensitive paths
• Queue/console context shouldn’t force primary fallback — Guard with request() !== null

The required changes are minimal: two classes and a service provider registration. Without them, PlanetScale Postgres in transaction-pooling mode will produce intermittent failures that are difficult to reproduce locally and are not visible in standard Postgres logs.

The Hidden Failure Modes of Laravel + PgBouncer + PlanetScale was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

At Layers, we regularly use the Shopify Admin GraphQL API to sync products, manage webhooks, poll bulk operations, and introspect metafields. To ensure reliable integration testing, we required a solution more robust than basic HTTP mocking. We built a fake Shopify GraphQL server that validates queries against the actual schema, calculates API cost, simulates stateful behavior across requests, and runs tests using the same query documents as production.

Why HTTP Mocking Wasn’t Enough

Our application is built on Laravel, a PHP framework with an integrated HTTP client and a testing utility, Http::fake(). This utility intercepts outgoing HTTP requests during tests and returns predefined responses, eliminating the need for real network calls. The standard approach is as follows:

Http::fake(['*.myshopify.com/*' => Http::response($fixture)]);

While effective for basic use cases, this approach has several significant limitations for Shopify integrations:

It does not validate queries. A fixture returns any provided JSON, regardless of the validity of the GraphQL query. Errors such as renamed fields, broken fragments, or invalid types may go undetected until production or, at best, staging.

It cannot enforce API cost. Shopify rejects queries exceeding 1,000 points, but a fixture does not account for cost and will return a successful response even for queries that would be throttled or rejected in production.

It cannot model stateful sequences. Bulk operations progress through states (CREATED to RUNNING to COMPLETED) across multiple polls. Webhooks created in one request need to be listable in the next. Fixtures do not compose; each Http::fake() call is independent.

It does not scale across a growing test suite. As the number of Shopify-related jobs, services, and edge cases increases, maintaining per-test fixture strings becomes a maintenance burden. Changes to query structure require updating every affected fixture.

The Query Library: bootstrap/graphql/

Our approach is based on organizing and managing query documents, rather than focusing solely on the fake server.

All Shopify GraphQL queries and mutations are stored in bootstrap/graphql/ as standalone .graphql files. These are the actual documents used by production API calls, not separate copies for tests. Each has a paired response JSON maintained on the test side:

bootstrap/graphql/
├── GetProduct.graphql
├── BulkOperationRunQuery.graphql
├── WebhookSubscriptionCreate.graphql
├── MetaobjectFieldsFragment.graphql
└── ...

tests/fixtures/graphql/
├── GetProductResponse.json
├── BulkOperationRunQueryResponse.json
├── WebhookSubscriptionCreateResponse.json
└── ...

.graphql files are part of the application and used for real API calls. .json files are used only in tests and contain realistic response structures, including GIDs, status values, and nested data that match Shopify’s responses. The fake server pairs these files by filename convention during initialization.

Schema Validation at Test Boot

We align all components with the Shopify Admin API schema, which is automatically downloaded from the Shopify introspection endpoint at the version specified in the environment:

SHOPIFY_API_VERSION=2026-01

php artisan shopify:sync-schema

(php artisan is Laravel’s CLI tool for running application commands — similar to rake in Rails or manage.py in Django.)

This pulls the current schema into tests/fixtures/shopify-admin-schema.graphql. When the test suite starts, the fake server loads every .graphql file from bootstrap/graphql/ and validates each one against the schema using DocumentValidator::validate() before any tests run. A schema mismatch, such as a deprecated field, renamed type, or changed enum value, causes the entire suite to fail immediately at boot, with a clear validation error indicating the problematic document.

As a result, updating SHOPIFY_API_VERSION in CI immediately identifies queries that require updates. This approach makes potential regressions explicit and prevents them from being discovered in production.

Architecture: Three Classes

With the query library as the data layer, the fake server is implemented using three dedicated classes.

ShopifyGraphqlFake: The Interceptor

This class intercepts all HTTP requests to *.myshopify.com/admin/api/*/graphql.json using Laravel’s Http::fake() and processes them as a real GraphQL API would. Upon initialization, it loads the schema and builds a webonyx/graphql-php Schema object.

Each incoming query during a test is parsed into an AST using GraphQL\Language\Parser::parse() and matched against the validated library. The response is shaped through a recursive shapeResponse() process that filters the paired JSON to include only the fields requested by the query, mirroring real GraphQL executor behavior. The result includes a populated extensions.cost block.

For rare cases where a test requires behavior not covered by the library, override() allows a test to register a custom resolver for a specific operation. Overrides take priority over library matches and are cleared between tests. In practice, they are used for scenarios such as testing pagination cursor logic, simulating deleted resources, or asserting on specific query arguments:

$capturedFirst = null;

$this->shopify()->override('query', 'product', function ($ctx) use (&$capturedFirst) {
    $capturedFirst = $ctx->variables['first'] ?? null;
    return [/* minimal valid response */];
});

$job->handle(app(ShopifySync::class), app(ProductService::class));

// Assert SyncProductVariants uses smaller page size when metafields are enabled
expect($capturedFirst)->toBe(10);

Error simulation is fully supported. Methods such as simulateAccessDenied(), simulateThrottle(), simulateFieldUnavailable(), and simulateEmptyResponse() enable testing of error-handling paths without additional library entries.

GraphqlCostCalculator: Shopify’s Cost Model in PHP

This class implements Shopify’s documented cost calculation rules:

• Scalar / Enum — 0 points
• Object — 1 point
• Connection — multiplied by first/last argument (default: 10 if neither is present but edges/nodes are selected)
• Mutation — base cost of 10
• Max per query — 1,000 points

The calculator traverses the AST recursively using calculateSelectionSetCost(), resolving variables, inline fragments, and named fragments. Connection sizes are multiplied as they nest. For example, a connection with first: 10 containing another with first: 25 results in 250 points for that subtree, not 35.

Each response includes the calculated cost in extensions.cost, matching the exact response structure of the Shopify API:

{
  "extensions": {
    "cost": {
      "requestedQueryCost": 52,
      "actualQueryCost": 52,
      "throttleStatus": {
        "maximumAvailable": 2000,
        "currentlyAvailable": 1948,
        "restoreRate": 100
      }
    }
  }
}

ResolverContext: Clean Interface for Resolvers

A context object is passed to every resolver, providing access to the operation type, field name, arguments, variables, selection set, fragments, and per-test state. It supports dot-notation reads and writes through $ctx->setState() and $ctx->getState().

This stateful design enables testing of bulk operation polling without requiring sleep calls or actual asynchronous execution:

$this->shopify()->setBulkOperationStatusSequence(
    $bulkOpId,
    ['RUNNING', 'RUNNING', 'COMPLETED']
);

Each successive poll to node(id: $bulkOpId) advances through the defined sequence. When the status reaches COMPLETED, the resolver automatically populates the download URL. This approach provides a realistic multi-request lifecycle for the job under test and requires only a single setup cal.l.

Cost Enforcement and the Leaky Bucket

We use two mechanisms to handle rate limiting in tests.

setEnforceMaxQueryCost(true) causes any query exceeding 1,000 points to return a MAX_COST_EXCEEDED error, mirroring the real Shopify API:

$this->shopify()->setEnforceMaxQueryCost(true);

expect(fn () => $this->shopifySync->executeGraphQlQuery(
    store: $this->store,
    query: $expensiveQuery,
))->toThrow(QueryException::class, 'MAX_COST_EXCEEDED');

Leaky bucket simulation tracks cumulative bucket state throughout a test, deducting points per query and restoring them over time. This enables testing of retry and backoff logic under realistic throttling conditions without real API calls:

$this->shopify()->setSimulateLeakyBucket(true);
$this->shopify()->configureLeakyBucket(
    maxPoints: 1000,
    restoreRate: 50,
    currentPoints: 0  // exhausted — next query throttles
);

expect(fn () => $this->shopifySync->executeGraphQlQuery(
    store: $this->store,
    query: 'query { productsCount { count } }',
))->toThrow(RateLimitException::class);

The Regression It Caught

The production sync metafields job uses MetaobjectFieldsFragment.graphql, one of the documents in bootstrap/graphql/. At some point during development, a references(first: 25) connection was added inside the fragment to resolve nested metaobject references. The query structure became:

metafields(first: 10) {
  references(first: 25) {
    ... on Metaobject {
      fields {
        references(first: 25) { ... }
      }
    }
  }
}

The cost calculation for this structure:10 (metafields)

10 (metafields)
  × 25 (references)
    × 25 (fields.references)
= 6,250 points

The limit is 1,000. Every invocation of this job against a real Shopify store would have been rejected.

Because MetaobjectFieldsFragment.graphql is a production document loaded and validated at test boot, the cost calculator detected this issue as soon as the fragment changed. The ShopifyGraphqlFake Query Cost Regression Tests describe block now permanently verifies both sides of the fix:

describe('ShopifyGraphqlFake Query Cost Regression Tests', function () {
    /**
     * Regression: MetaobjectFieldsFragment with nested references(first: 25)
     * exceeded the 1000 point limit in SyncProductMetafields.
     *
     * Cost: metafields(first: 10) × references(first: 25) × fields.references(first: 25)
     *     = 10 × 25 × 25 = 6,250 points (limit is 1,000)
     *
     * Fixed in commit e5ce83dd by removing nested references from the fragment.
     */
    test('old MetaobjectFieldsFragment with nested references exceeds 1000 points', function () {
        $this->shopify()->setEnforceMaxQueryCost(true);

        expect(fn () => $this->shopifySync->executeGraphQlQuery(
            store: $this->store,
            query: $oldQueryWithNestedReferences,
            variables: ['id' => 'gid://shopify/Product/123', 'first' => 10, 'keys' => []],
        ))->toThrow(QueryException::class, 'MAX_COST_EXCEEDED');
    });

    test('fixed MetaobjectFieldsFragment without nested references stays under 1000 points', function () {
        $cost = $this->shopify()->calculateQueryCost($fixedQuery, [
            'id' => 'gid://shopify/Product/123',
            'first' => 10,
            'keys' => [],
        ]);

        expect($cost['exceedsLimit'])->toBeFalse();
        expect($cost['requestedCost'])->toBeLessThanOrEqual(1000);
    });
});

The first test fails if deeply nested connections are reintroduced into the fragment. The second test confirms that the fix remains effective. Both tests run on every CI push against the current schema version.

What the Test Suite Actually Looks Like

With the query library in place, most feature tests that interact with Shopify require no fixture setup. The library manages the response structure, allowing tests to focus on the behavior being verified:

test('bulk operation status sequence progresses on each poll', function () {
    $bulkOpId = 'gid://shopify/BulkOperation/test123';
    $this->shopify()->setBulkOperationStatusSequence(
        $bulkOpId,
        ['RUNNING', 'RUNNING', 'COMPLETED']
    );

    // First poll
    $response = $this->shopifySync->executeGraphQlQuery(
        store: $this->store,
        query: 'query bulkop($id: ID!) {
            node(id: $id) { ... on BulkOperation { status url } }
        }',
        variables: ['id' => $bulkOpId],
    );
    expect($response['data']['node']['status'])->toBe('RUNNING');
    expect($response['data']['node']['url'])->toBeNull();

    // Third poll — COMPLETED with download URL populated automatically
    // ...
});

Error handling paths are similarly straightforward:

test('simulateThrottle returns throttle error', function () {
    $this->shopify()->simulateThrottle();

    expect(fn () => $this->shopifySync->executeGraphQlQuery(
        store: $this->store,
        query: 'query { productsCount { count } }',
    ))->toThrow(RateLimitException::class);
});

The fake server is instantiated in TestCase::setUp() , a base class lifecycle hook that runs before each test, and is available throughout the suite via $this->shopify(). There is no need for per-test HTTP setup, fixture strings in test bodies, or coupling between test logic and response structure.

Tradeoffs

This approach is not without cost. The paired response JSONs in tests/fixtures/graphql/ require maintenance. When a production query adds a field, the response JSON must also be updated. Boot-time validation makes failures explicit rather than silent, but manual updates are still required.

The cost model reimplements Shopify’s documented rules. It accurately detects relevant regressions such as nested connections, large first arguments, and mutation base costs, but does not capture every detail of Shopify’s internal cost engine. It is a high-confidence approximation rather than an exact match.

For our team, these tradeoffs are worthwhile. The alternative is maintaining a growing collection of per-test Http::fake() fixture strings that pass regardless of query validity or cost, does not scale and fails to catch critical issues.

Should we open-source and maintain these test fixtures?

We Built a Fake Shopify GraphQL Server. It Caught a Bug That Would Have Broken Production. was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

On February 27, we had a rough afternoon. Multiple waves of automated traffic hit our platform while several merchants were already in busy windows. We’d been mitigating it since mid-morning, but at 1:51 PM, the combined bot load and real customer traffic caused a 13-minute service interruption that affected a few tenants; our first broad service interruption in over two years.

During the postmortem, we traced a single chain of failure. First, a surge in background job traffic triggered a rush of worker activity. Next, the shared connection pool between our web app and workers became fully saturated by the workers. As a result, app requests were blocked, queued, and ultimately timed out for customers. The database itself handled the load, but the shared pool was the point of contention.

Lesson learned: separate your pools. That’s the single biggest takeaway from this incident. The fix was giving each consumer their own pool. Here’s how we did it.

Our setup

We run Layers on PlanetScale Postgres on an M-640 metal cluster in AWS us-east-1, with six replicas. It’s a high-throughput setup built for the query volumes that come with serving multiple large Shopify storefronts. That translates to hundreds of thousands of search queries, bulk-collection membership writes, and concurrent materialized-view refreshes.

Previously, both the app and workers connected through PlanetScale’s local PgBouncer on port 6432 as a single shared pool. At the time, we set the pool size to 300 max client connections, which was more than enough for routine activity and left a healthy buffer for spikes on busy days. In our setup, a single worker process maintains a client connection; each worker can process multiple jobs or requests, allowing us to comfortably scale to 295 workers (reserving a few connections for monitoring). This setup worked under normal conditions but became a bottleneck under intense pressure.

Why a shared pool breaks under load

PlanetScale runs PgBouncer in transaction pooling mode, which keeps a fixed number of server-side connections open to Postgres and multiplexes client connections across them. It’s efficient and scales well for OLTP workloads. But when one consumer saturates the pool, everything else waits.

Web requests are short and transactional, allowing connections to be released quickly. Queue workers hold connections longer; tasks like materialized view refreshes take seconds, and catalog sync jobs fire large writes in sequence. Workers also spike during high-traffic periods.

When workers surged on February 27, they filled the pool. The database itself was healthy the entire time. The bottleneck was entirely at the pooling layer.

The fix: one bouncer per consumer

PlanetScale lets you provision dedicated PgBouncer instances separately from the local default. Each one has its own isolated pool of server-side connections, its own name, and its own credentials. You can create as many as you need. While alternative mitigations like statement queuing or increasing the pool size were options, we chose multiple PgBouncers because they offered true isolation and avoided potential side effects like increased queuing delays or contention within a single shared pool. This approach gave us clearer operational boundaries and more predictable behavior during surges.

We created two:

• app-bouncer for all web application traffic
• worker-bouncer for all queue worker traffic

Both target our replica fleet. The connection format is straightforward: append the bouncer name to your database username (postgres.{branchId}|app-bouncer) and connect on port 6432. PlanetScale handles the rest.

With this in place, a worker surge saturating the worker-bouncer has no effect on the app-bouncer. The pools are separate. Workers and app traffic can spike independently without interfering with each other.

If you want to validate this isolation in your own environment, try running a simple load script or chaos test: simulate a heavy worker spike while tracking latency and error rates on app requests. This lets you verify that worker activity does not impact the web pool under stress, and gives your team confidence in the setup before peak traffic hits.

Because both bouncers target the full replica fleet, read queries are distributed across all six replica nodes rather than being pinned to a single one. This is a nice secondary benefit at our query volumes.

Before and after:

Previously, both the web app and queue workers shared a single local PgBouncer on port 6432 for reads, with writes going through the same pool to the primary. Now, the web app routes read through app-bouncer and workers through worker-bouncer; each is a dedicated replica pool on port 6432. Writes from workers and all migrations continue to use a direct connection on port 5432 to the primary, bypassing PgBouncer entirely, as they always should.

The failover behavior

Dedicated PgBouncers handle failover events more reliably than the local default. After February 27, this proved especially important to us.

With the local PgBouncer, a failover drops all client connections. Your application must detect these disconnects and attempt to reconnect, which can increase outage time during an incident.

With dedicated PgBouncers, failovers are handled using PgBouncer’s PAUSE command. During a failover, incoming transactions are queued inside the dedicated bouncer while the database transitions. Once Postgres comes back online, transactions automatically resume, eliminating dropped client connections and minimizing service disruption. From the application’s view, there’s a brief increase in latency but no connection loss.

For a cluster with six replicas where maintenance events aren’t rare, that behavior matters.

In short

If your app and queue workers share a connection pool, you have a coupling that works in normal conditions but becomes a liability under load. That’s a bad time to discover it. Take a moment today to audit your own pool setup before a bottleneck catches you by surprise.

Dedicated PgBouncers on PlanetScale provide isolated pools for each consumer. This lets them operate independently under stress, preventing one from impacting the other.

I wish we’d done this before February 27.

Using Multiple PgBouncers to Isolate Application and Worker Connections was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Vector search in PostgreSQL has come a long way, and pgvector makes it simple: insert embeddings, create an HNSW index, and run ANN queries. At least for a while.

As our system scaled across stores, regions, and product volumes, something became clear: HNSW is not “fire and forget.” Indexes age. They bloat. They fragment. They silently lose recall. When they degrade, search results degrade too. Sometimes this happens gradually, sometimes catastrophically.

For months, we assumed our approach was fine: rebuild indexes every night at 1 AM, after product imports, and after ingesting new embeddings.

It felt safe. It was absolutely not.

When Scheduled Rebuilds Turned Into a Problem

Our first attempt at maintaining HNSW indexes was a classic ops mistake: periodic work instead of condition-based work. Every night, every tenant, every index. No intelligence. Just execution.

And here’s what happened:

• We rebuilt indexes that had zero new writes — massive waste of compute + IO
• Large tenants saw daily index decay — recall dropped, latency increased
• Crash mid-rebuild left an index invalid but still present — PostgreSQL stopped using it entirely
• Replicas diverged from primaries — queries returned different ANN results per region

The worst case was the INVALID scenario. A configuration change required a Postgres restart. This restart happened during a CONCURRENT index creation. In Postgres, when a CONCURRENT index is being built, it is first added to the system catalog in an INVALID state. The system must then complete two full table scans to build and validate the index. If Postgres is restarted before these scans complete, the index remains present but stuck in an invalid or corrupted state. The result: an index that looks present but is unusable, and no one notices until search quality degrades or anomalies are detected.

That incident is what forced a rethink.

We didn’t need scheduled rebuilding. We needed index health monitoring and to rebuild only when necessary.

What Does “Healthy” Mean for an HNSW Index?

B-trees give you clear signals: bloat, dead tuples, page churn. HNSW is structure-heavy. It is a layered, navigable small-world graph. Its quality depends on:

• how many vectors have been reinserted or replaced
• how many nodes reference deleted or obsolete embeddings
• how well graph layers remain navigable under memory pressure
• whether the index still fits inside the buffer cache or spills to disk

There is no single metric like fragmentation percent.

So we built one.

We created a three-metric model that produces a single health score for every HNSW index:

• Dead tuple ratio — Signals vector churn + unpruned graph nodes
• Cache hit ratio — Indicates memory residency vs IO-bound index walks
• Bytes per vector (bloat heuristic) — Detects graph overgrowth + wasted structure

If an index inflates, loses cache locality, or accumulates dead references, search degrades. Not instantly but inevitably.

And critically, PostgreSQL already exposes everything you need to measure this.

Metric 1: Dead Tuple Ratio

Dead tuples happen when embeddings are rewritten, deleted, or replaced. In B-trees, that’s annoying. In HNSW, dead nodes remain in the graph like abandoned intersections. Search hops still cross them. They increase hop count. They widen the beam width. They reduce recall.

Even without deep internals, PostgreSQL gives a usable surface:

dead_tuple_ratio = n_dead_tup / n_live_tup

If that ratio climbs toward 10–15%, rebuilding pays measurable returns. Dead tuples are a silent killer. We use the following query in production.

SELECT
    relname AS table_name,
    n_dead_tup,
    n_live_tup,
    ROUND((n_dead_tup / GREATEST(n_live_tup, 1)::float) * 100, 2) AS dead_tuple_ratio_pct
FROM pg_stat_all_tables
WHERE relname = 'your_embedding_table_name';

Metric 2: Cache Hit Ratio

This one surprised us.

We always assumed index performance dropped because the index itself degraded.

But very often, performance dropped because the index no longer fit in memory.

When HNSW pages are evicted from shared_buffers, traversal becomes disk-bound. ANN is fast only when the graph fits in RAM. Once it doesn’t, vacuum and reindex are not just cleanup. They restore performance.

SELECT
    indexrelid::regclass AS index_name,
    idx_blks_hit,
    idx_blks_read,
    ROUND((idx_blks_hit * 100.0 / GREATEST(idx_blks_hit + idx_blks_read, 1)), 2) AS cache_hit_ratio_pct
FROM pg_statio_user_indexes
WHERE indexrelid::regclass::text LIKE '%hnsw%';

We treat anything below 90% hit ratio as a yellow flag. Below 75% is red.

Not because slow scans happen.

Because the planner may stop using the index altogether.

Metric 3: Bytes per Vector

This is our approximation of HNSW bloat.

We know the expected size of an embedding dimension: a 1024-float vector is about 4096 bytes of storage plus HNSW structural overhead. If observed bytes per vector drift from baseline, the index grows inefficiently.

WITH idx AS (
    SELECT c.relname AS index_name, pg_relation_size(c.oid) AS index_bytes
    FROM pg_class c
    JOIN pg_index i ON i.indexrelid = c.oid
    WHERE c.relname LIKE '%hnsw%'
)
SELECT
    index_name,
    pg_size_pretty(index_bytes) AS index_size,
    (
        index_bytes / NULLIF(
            (SELECT reltuples FROM pg_class WHERE relname = 'your_embedding_table_name'),
            0
        )
    ) AS bytes_per_vector
FROM idx;

Example symptom: We expect 5.0KB per vector and see 9.2KB per vector being stored. This equals bloat. Rebuild justified.

It’s not perfect, but it correlates strongly with real-world recall degradation.

The Health Score

Instead of binary rebuild vs no rebuild, we compute:

score = (dead_score * 0.4) + (cache_score * 0.4) + (bloat_score * 0.2)

Weighting rationale:

• Dead tuples = recall degradation → heaviest signal
• Cache residency = latency + planner reliability
• Bloat matters, but less across large embeddings

When a score dips below our threshold, rebuild is triggered only for that index.

We stopped rebuilding everything. We started rebuilding only what needed it.

Like the writing? Follow product development at Layers

Signal-driven health monitoring for HNSW indices w/ pgvector was originally published in Engineering @ Layers on Medium, where people are continuing the conversation by highlighting and responding to this story.