Performance under catalogue and transaction growth.
Hita is an e-commerce platform that saw inventory expand from 5,000 SKUs to over 150,000 items with deeply nested category hierarchies, requiring database optimization to prevent checkout slowdowns.
The Context
In early-stage e-commerce, relational models are clean: a products table, a variants table, an orders table, and simple SQL joins. As Hita added wholesale pricing tiers, multi-warehouse stock allocations, and complex category filters, the data schema multiplied in depth.
During regular browsing hours, performance appeared acceptable. But during promotion events and flash sales, p95 catalogue latency degraded from 180ms to over 2.4 seconds. Cart abandonment increased, and database CPU usage pinned at 98% with queries queuing up across connection pools.
The Engineering Problem
The data access pattern was mutating on catalogue queries.
The problem wasn't simply "more visitors." The issue was that every single page render executed a 7-table relational join: filtering products, resolving parent-child category trees, checking variant inventory across three warehouses, and evaluating custom customer discounts.
As the product count crossed 100,000 SKUs, PostgreSQL's query planner could no longer utilize simple indexes effectively. Nested index scans were thrashing database disk I/O, meaning even basic search requests forced the database into full sequential sweeps.
What Tenlyr Did
We eliminated relational multi-joins from customer read paths and restructured data projection layers:
Flattened Catalogue Read Projections
Constructed denormalized product view projections stored in high-performance memory, separating heavy customer catalogue browsing from transactional inventory writes.
Query Plan Tuning & Composite Indexes
Analyzed EXPLAIN ANALYZE traces across the top 20 query bottlenecks. Added composite indexes and filtered partial indexes, reducing sequential scan operations by 94%.
Surgical Cache Invalidation
Implemented targeted cache key invalidation tags so when a product variant's stock changes, only that specific SKU's inventory tag refreshes—avoiding wasteful full cache purges.
Atomic Checkout Reservations
Shifted real-time stock reservation at checkout to an atomic Redis token mechanism, preventing row-level deadlocks in PostgreSQL during simultaneous customer flash orders.
The Result
68% drop in p95 query latency; stable checkout under 10x promotional spikes.
Product listing pages dropped from 2,400ms to 78ms. Database CPU during high-traffic flash sale campaigns stabilized under 35%, ensuring zero checkout timeouts or cart drop-offs.
What We Learned: Not every query needs to execute against relational source-of-truth.
Relational databases are supreme at transactional consistency, but terrible at multi-join reads across 150,000 product variants. By separating read projections from write transactions, product teams can scale catalogue volume indefinitely without fearing query lockups.
Slow queries or database locks slowing your roadmap?
Tell us what's happening. We'll trace the query plans and connection bottlenecks with you.