Database Optimization — Commerce

Overview

E-commerce databases face distinct query patterns: high-cardinality product filtering (category + price + attributes), session-scoped cart lookups, write-heavy order creation, and read-heavy catalog browsing that must scale to concurrent users. This skill covers identifying slow queries, designing effective indexes for product filtering, partitioning order tables, and routing read traffic to replicas.

When to Use This Skill

• When product listing pages are slow due to unindexed filter combinations (category + price + brand)
• When checkout throughput is limited by order insertion latency
• When read load on the primary database is causing write latency to increase
• When a slow query log reveals queries doing sequential scans on large tables
• When planning a database schema for a new custom e-commerce platform

Core Instructions

Step 1: Determine your situation

Database optimization applies primarily to self-hosted setups. Understand your constraints first: