Database Optimization Commerce

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:

Step 2: Quick wins for WooCommerce (managed WordPress/WooCommerce)

Before touching database indexes directly, apply these WooCommerce-specific optimizations:

1. Install Redis Object Cache (free, wordpress.org):

• Your host must support Redis (most managed WordPress hosts — WP Engine, Kinsta, Cloudways — do)
• Install and activate the plugin; go to Settings → Redis and click Enable Object Cache
• This caches all WooCommerce database queries in memory, dramatically reducing repeat query times

2. Install WP-Optimize (free, wordpress.org):

• Go to WP-Optimize → Database and run Clean database to remove orphaned order meta, expired transients, and post revisions
• WooCommerce stores build up millions of rows of orphaned meta over time — regular cleanup is essential
• Schedule automatic cleanup weekly

3. Enable the WooCommerce HPOS (High-Performance Order Storage):

• Go to WooCommerce → Settings → Advanced → Features
• Enable High-Performance Order Storage — this moves orders from WP post tables to dedicated order tables with proper indexes
• Critical for stores with 10,000+ orders

4. Upgrade to a host with MySQL 8.0+ — older MySQL versions lack important index improvements; WP Engine, Kinsta, and Cloudways all run MySQL 8.0+

Step 3: PostgreSQL optimization for custom storefronts

---

Identify slow queries

-- Enable pg_stat_statements to find the worst offenders
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 slowest queries by total cumulative time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  calls,
  round((total_exec_time / sum(total_exec_time) OVER()) * 100, 2) AS pct_of_total,
  left(query, 200) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

-- Diagnose a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.id, p.name, p.price
FROM products p
JOIN product_categories pc ON pc.product_id = p.id
WHERE pc.category_id = 42
  AND p.price BETWEEN 1000 AND 5000
  AND p.status = 'active'
ORDER BY p.created_at DESC
LIMIT 24;
-- Look for "Seq Scan" on large tables — this means a missing index

Design indexes for product filtering

-- Partial index on active products only (smaller, faster)
CREATE INDEX CONCURRENTLY idx_products_status
  ON products (status) WHERE status = 'active';

CREATE INDEX CONCURRENTLY idx_products_price
  ON products (price) WHERE status = 'active';

-- Composite index for the most common filter combination
-- INCLUDE adds non-key columns for index-only scans (no table heap access)
CREATE INDEX CONCURRENTLY idx_products_listing
  ON products (status, brand_id, price, created_at DESC)
  INCLUDE (name, slug, thumbnail_url);

-- GIN index for flexible JSONB attribute filtering
-- Enables: attributes @> '{"color": "blue", "size": "M"}'
CREATE INDEX CONCURRENTLY idx_products_attributes
  ON products USING gin(attributes);

-- ALWAYS index foreign keys (PostgreSQL does NOT do this automatically)
CREATE INDEX CONCURRENTLY idx_product_categories_product_id
  ON product_categories (product_id);
CREATE INDEX CONCURRENTLY idx_order_lines_order_id
  ON order_lines (order_id);

Partition the orders table by date

-- Create orders table with range partitioning on created_at
CREATE TABLE orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL,
  status      TEXT NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Quarterly partitions
CREATE TABLE orders_2025_q1 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE orders_2025_q2 PARTITION OF orders
  FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
-- (continue for Q3, Q4, 2026...)

-- Indexes on the parent propagate to all partitions
CREATE INDEX CONCURRENTLY ON orders (customer_id, created_at DESC);
CREATE INDEX CONCURRENTLY ON orders (status, created_at DESC);

Route reads to replicas

// lib/database.js — two connection pools
import { Pool } from 'pg';

const primaryPool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });
const replicaPool = new Pool({ connectionString: process.env.DATABASE_REPLICA_URL, max: 50 });

export const db = {
  // Writes and anything requiring freshness — primary
  async write(sql, params = []) {
    const result = await primaryPool.query(sql, params);
    return result.rows;
  },
  // Catalog reads — replica (slight staleness is acceptable)
  async read(sql, params = []) {
    const result = await replicaPool.query(sql, params);
    return result.rows;
  },
  // Transactions — always primary
  async transaction(fn) {
    const client = await primaryPool.connect();
    try {
      await client.query('BEGIN');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (e) {
      await client.query('ROLLBACK');
      throw e;
    } finally {
      client.release();
    }
  },
};

Route reads correctly:

• Catalog pages, product search, order history → db.read() (replica)
• Cart operations, checkout, inventory decrement → db.write() or db.transaction() (primary)

Use keyset pagination (never OFFSET for large catalogs)

-- OFFSET 10000 reads and discards 10,000 rows — slow at scale
-- Use keyset pagination instead: pass the last row's cursor values

-- First page
SELECT id, name, price, created_at FROM products
WHERE status = 'active'
ORDER BY created_at DESC, id DESC
LIMIT 24;

-- Next page (pass last row's created_at and id as cursor)
SELECT id, name, price, created_at FROM products
WHERE status = 'active'
  AND (created_at, id) < ('2025-03-01T12:00:00Z', 'uuid-of-last-row')
ORDER BY created_at DESC, id DESC
LIMIT 24;

Best Practices

• Use `EXPLAIN (ANALYZE, BUFFERS)` to validate index usage — EXPLAIN alone shows estimates; ANALYZE runs the query and shows actuals; "Seq Scan" on a large table means a missing index
• Create indexes `CONCURRENTLY` — without CONCURRENTLY, index creation locks the table for writes; always use it in production
• Index all foreign keys — PostgreSQL does not auto-index foreign keys; customer_id, order_id, and product_id in join tables must be explicitly indexed
• Set `work_mem` carefully — increasing work_mem speeds up sorting but multiplies with connection count; benchmark before raising it
• Run `VACUUM ANALYZE` regularly — table bloat from dead tuples slows all queries; configure autovacuum aggressively on high-write tables like carts and sessions

Common Pitfalls

Related Skills

• @flash-sale-scaling
• @monitoring-alerting-commerce
• @ecommerce-caching
• @load-testing-commerce

Orders Table Scaling and Sales Dashboard Optimization

Problem/Feature Description

A fast-growing e-commerce company has an orders table that started three years ago and now contains over 50 million rows. Simple queries like "get all orders for customer X from the last 90 days" take 12+ seconds because the planner has to scan the entire table. Meanwhile, the analytics team runs dashboard queries every few minutes to compute revenue by product category — these live aggregation queries take 30–45 seconds each and are hammering the database.

The engineering lead wants to solve both problems with a major schema overhaul: partition the orders table so PostgreSQL can skip irrelevant data automatically, and pre-compute the analytics aggregations so the dashboard reads a summary instead of scanning all orders live. The team also wants the partition management process to be automated so a new quarterly partition gets created without manual SQL every three months.

Your job is to produce a SQL migration script that transforms the system, plus a brief explanation document.

Output Specification

Produce two files:

1. migration_orders_overhaul.sql — A SQL script that:

• Defines the new partitioned orders table structure.
• Creates the necessary quarterly partitions (create at least four — for the current year).
• Creates a stored procedure to automate future quarterly partition creation.
• Adds appropriate indexes on the partitioned table.
• Creates a materialized view that aggregates daily sales revenue by category.
• Includes the command to refresh that materialized view in a non-blocking way.

2. design_notes.md — A short document (bullet points are fine) explaining:

• How to ensure the query planner skips irrelevant partitions when querying recent orders.
• How to validate that an index is actually being used after the migration (include the specific SQL syntax to use).
• How often the materialized view should be refreshed and via what mechanism.

Input Files

The following existing schema is provided. Extract it before beginning.

=============== FILE: inputs/existing_orders.sql =============== -- Current monolithic orders table (no partitioning) CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), customer_id UUID NOT NULL, status TEXT NOT NULL, -- 'pending', 'processing', 'completed', 'cancelled' total_cents INTEGER NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() );

CREATE TABLE order_lines ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL REFERENCES orders(id), product_id UUID NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL );

CREATE TABLE product_categories ( product_id UUID NOT NULL, category_id UUID NOT NULL, PRIMARY KEY (product_id, category_id) );

CREATE TABLE categories ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL );

Slow Product Listing Page — Index Migration

Problem/Feature Description

An online outdoor gear retailer has a PostgreSQL database with a products table (roughly 2 million rows), a product_categories join table, and variable per-product specs stored alongside the main row. The engineering team has been receiving complaints that the product listing and search pages are taking 4–8 seconds to load, especially when shoppers filter by category, price range, brand, and attributes like material or weight.

The backend developer who built the schema created the base tables but left indexing for later. Now "later" has arrived and the team needs a migration script that adds the right indexes so that filtered product listing queries, full-text search, and attribute-based filtering all use index scans instead of sequential scans. The migration must run against a live production database without locking tables, so it must not block incoming writes at any point.

Output Specification

Produce a single SQL migration file named migration_add_indexes.sql that adds all the indexes the team needs. The file should:

• Include a brief comment above each index explaining what query pattern it supports.
• Cover all the slow query patterns described: category+price filtering, brand/status combinations, full-text search on product name and description, and attribute containment queries.
• Also include an example SELECT query at the bottom of the file (in a comment) demonstrating how to paginate through the product listing efficiently at scale — imagine the catalog has been live for two years and the listing page is on page 500.

Input Files

The following schema is provided as a starting point. Extract it before beginning.

=============== FILE: inputs/schema.sql =============== -- Existing schema (no indexes beyond PKs)

CREATE TABLE products ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, description TEXT, slug TEXT NOT NULL UNIQUE, thumbnail_url TEXT, price INTEGER NOT NULL, -- cents brand_id UUID NOT NULL, status TEXT NOT NULL DEFAULT 'active', -- 'active', 'draft', 'archived' attributes JSONB DEFAULT '{}', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() );

CREATE TABLE brands ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL );

CREATE TABLE categories ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL );

CREATE TABLE product_categories ( product_id UUID NOT NULL REFERENCES products(id), category_id UUID NOT NULL REFERENCES categories(id), PRIMARY KEY (product_id, category_id) );

Database Access Layer for Scaled E-commerce API

Problem/Feature Description

A Node.js/TypeScript e-commerce API has reached a point where the primary PostgreSQL database is showing increased write latency during peak traffic. The infrastructure team has provisioned a read replica, and now the application needs to be updated to route read traffic to the replica. The team wants a clean, reusable database module that handles both the primary and replica pools and makes it easy for application code to choose the right connection.

A junior engineer wrote a draft module that just uses a single pool pointed at the primary, and the tech lead needs it replaced with a proper dual-pool implementation. The module must expose a consistent interface so existing call sites can migrate incrementally: transactional operations stay on the primary, catalog browsing and order history queries move to the replica. One exception the tech lead flagged explicitly: the shopping cart must always read from the primary because even a small replication lag can cause confusing UX (items appearing to disappear from the cart).

Output Specification

Produce a TypeScript file lib/database.ts implementing the database access module. The file should:

• Export a db object with at minimum three methods: one for transactions, one for replica reads, and one for primary writes/reads.
• Use environment variables for connection strings.
• Include a short README.md (in the same output directory) that documents which method to use for: product listing queries, cart reads, order placement, and order history — with one-line justifications for each.

Input Files

The following starter file is provided as context. Extract it before beginning.

=============== FILE: inputs/database_v1.ts =============== // Current single-pool implementation — needs to be replaced import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10, });

export const db = { async query<T = any>(sql: string, params: any[] = []): Promise<T[]> { const result = await pool.query(sql, params); return result.rows; }, };