// work / database administration

E-commerce database performance tuning

A three-week performance engagement that took the checkout's slowest query from 2.4 seconds to 180 milliseconds at the 95th percentile, without new hardware.

Client
Online marketplace
Industry
E-commerce
Year
2024
Role
Database administrator
Duration
3 weeks
Close-up of network cables and server rack hardware
Photo: @tvick on Unsplash

Context

A marketplace with a few thousand orders a day had a familiar problem: the site was fine until a promotion ran, then checkout crawled and the database hit 100% CPU. The plan on the table was to double the RDS instance size. The engineering lead wanted a second opinion before paying for it.

The problem

Find out what was actually slow, fix it safely on a live system, and prove the improvement with numbers rather than impressions.

Approach

Baseline. The first two days were measurement only. I enabled pg_stat_statements and auto_explain for slow queries, captured a week of workload including a promotion, and built a Grafana dashboard for p50, p95 and p99 latency of the twenty most expensive queries. The report that came out of this was short: ten queries accounted for 83% of total database time, and three of them were in the checkout path.

The checkout query. The worst offender joined orders, order lines, inventory and promotions and was doing a sequential scan on the orders table because the filter used a function on a timestamp column. A composite index on (customer_id, created_at) and moving the function to the parameter side took it from 2.4 seconds to 180 milliseconds at the 95th percentile. That one change removed most of the CPU problem.

The rest of the top ten. Eleven index changes in total: six new composite indexes, three unused indexes dropped (they were slowing writes for nothing), two rewritten as partial indexes. Four queries were rewritten, mostly to stop fetching columns and rows the application threw away.

Partitioning. The orders ledger had five years of rows and every report scanned all of them. Monthly range partitioning with the last 13 months on fast storage made the reporting queries cheap and made archiving a matter of detaching a partition.

Pooling. The application opened a new connection per request. pgBouncer in transaction mode cut connection churn and removed a class of “too many connections” errors during spikes.

Every change was tested on a copy, applied in a maintenance window with a rollback script ready, and measured against the baseline the following day.

Outcome

The next promotion ran with database CPU peaking at 36% where it had been pinned at 100%. The checkout query at the 95th percentile dropped from 2.4 seconds to 180 milliseconds. The instance upgrade was cancelled. The dashboards and alert thresholds stayed, so the team now sees a regression the day it ships rather than the day of the next sale.

Need something like this?

This is the kind of work I do under database administration. Tell me about your version of the problem.