Sorting a catalog by price took 9,663 ms. Same database, same hardware, three boring steps later: 18 ms. Here's the whole playbook — no exotic extensions, just Postgres used properly.
Context: we run a B2B toy marketplace — 389,000 products from 5,233 factories, full story with PageSpeed receipts here.
The crime scene
Classic marketplace schema drift: the price lived in a JSON field on the main products table, while all the storefront filters (category, stock, MOQ) hit a separate filter table. Every "sort by price" forced Postgres to join back, extract from JSONB, and sort 389k rows with nothing to lean on. EXPLAIN ANALYZE read like a horror novel: 9.6 seconds.
Step 1 — denormalize into the table that does the WHEREs
We copied price and moq into the filter table — the one already serving every storefront query. Yes, denormalization. No, it's not a sin: it's a trade you make consciously and document.
Step 2 — a trigger keeps the mirror honest
One AFTER UPDATE trigger on the source table syncs the mirror columns. No cron drift, no "eventual consistency" surprises, no application-level sync bugs at 3 a.m.
Step 3 — partial indexes for real query shapes
We didn't index the whole table. We indexed what the storefront actually asks: in-stock rows per category ordered by price. Partial indexes are smaller, hotter in cache, and Postgres actually picks them.
All migrations idempotent, built with CREATE INDEX CONCURRENTLY — zero downtime on a live store.
Numbers
- Catalog sort: 9,663 ms → 18 ms
- One factory's page (36,000 products from a single supplier): 2,270 ms → 5 ms
- Warm search over 389k products (FTS + trigram + pgvector cascade behind a Redis response cache): 6–15 ms
The iron rule that kept us honest
Every optimization ran against a control set of queries, and the top-20 results had to be byte-identical before and after. Speed bought by silently changing what customers see is not an optimization — it's a regression with good PR.
Verify, don't trust
Our search returns an open Server-Timing header on every request — open DevTools on opttoyschina.com and watch the stages yourself. Questions about the schema, the trigger, or the index shapes — comments are open.
Top comments (2)
The byte-identical control set is the best part of this optimization. I’d widen that invariant beyond the first 20 rows: include equal-price ties, NULL/invalid JSON prices, out-of-stock transitions, multiple pages, and concurrent updates. Add a deterministic tie-breaker such as
(price, product_id)to both the index and query; otherwise pagination can duplicate or skip products even when page one looks identical.The mirrored-column contract also needs coverage for every write path. An
AFTER UPDATEtrigger alone does not prove new inserts, deletes, bulk imports, disabled triggers, or backfills stay consistent. I’d add a reconciliation query/metric for source-versus-mirror mismatches, test it in CI, and run it after deployments/imports. For migration safety: backfill in bounded batches, create the index concurrently, validate plans with production-like parameter values, then enable the new read path behind a reversible flag. That keeps the 18 ms win without turning denormalization drift into a silent pricing bug.Appreciate this — you went straight for the real edges. I checked our actual implementation against each point.
Pagination determinism — you're right, and it's the important one. Our storefront sort orders by price (and moq) with no secondary key, so equal-price ties are non-deterministic and deep pagination can drift even when page one is byte-identical. Real latent bug, not a nitpick. Going in: a deterministic
(price, id)tie-breaker in both theORDER BYand the partial indexes — so it stays an index scan, no full sort — plus widening the control set past the top 20 to cover equal-price ties, NULL/placeholder prices, out-of-stock transitions, deep pages and concurrent updates.Mirror contract — partly already there, partly your point stands. One clarification: the post simplified it to "an AFTER UPDATE trigger," but the production trigger is actually
AFTER INSERT OR UPDATEof the mirrored columns, and the migration backfills on run — so inserts are covered. Where you're right: that still doesn't protect against bulk loads with triggers disabled, or a product row inserted before its filter row exists. So we're adding a source-vs-mirror reconciliation check (drift count + missing/orphan rows) as a metric and a CI test, run after deploys and imports — so drift can't turn into a silent pricing bug.Migration safety — mostly how we shipped it. Indexes were built with
CREATE INDEX CONCURRENTLY, the migration is idempotent and reversible (drop the mirror columns, trigger and indexes) and backfills on run. We're formalizing the rest — validating plans against production-like parameters and putting the new read path behind a reversible flag.Genuinely useful review — this is exactly the kind of comment that makes writing these up worth it.