Problem RestatementProblem
Visa asked a database design question: design normalized tables for an e-commerce system (users, products, orders, order line items) that preserve the price the customer actually paid, even if the product's price changes later, and enforce useful constraints. Then write and optimize queries such as "total spend per user in the last 30 days" and "top 10 customers by spend this month".
Tables
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL
);
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
name TEXT NOT NULL,
current_price_cents INT NOT NULL CHECK (current_price_cents >= 0) -- can change any time
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL CHECK (status IN ('placed','paid','shipped','cancelled','refunded')),
total_cents INT NOT NULL CHECK (total_cents >= 0), -- sum of items at purchase time
currency CHAR(3) NOT NULL,
created_at TIMESTAMP NOT NULL
);
CREATE TABLE order_items (
order_id BIGINT REFERENCES orders(order_id),
line_no INT,
product_id BIGINT NOT NULL REFERENCES products(product_id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price_cents INT NOT NULL CHECK (unit_price_cents >= 0), -- price CHARGED, copied at purchase
PRIMARY KEY (order_id, line_no)
);order_items.unit_price_cents is a copy of the price at purchase time. We never compute past spend from products.current_price_cents, because that changes. (This is intentional denormalization for correctness of history.)
The Query
-- Total spend per user in the last 30 days (paid or shipped orders only)
SELECT o.user_id, SUM(o.total_cents) AS spend_cents
FROM orders o
WHERE o.created_at >= now() - INTERVAL '30 days'
AND o.status IN ('paid', 'shipped')
GROUP BY o.user_id
ORDER BY spend_cents DESC
LIMIT 10;
-- One user's recent spend (e.g., for a profile page)
SELECT COALESCE(SUM(total_cents), 0) FROM orders
WHERE user_id = $1 AND created_at >= now() - INTERVAL '30 days' AND status IN ('paid','shipped');Deep Dive — Indexing the spend queryDeep dive
The query is "how much has this user spent in the last 30 days", and the same table also has to answer "who are the top spenders this quarter". One table, two access patterns.
Filter without a supporting index
SELECT SUM(total_cents) FROM orders WHERE user_id = 42 AND created_at > now() - interval '30 days', with only a primary key on order_id.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
Q["One user's 30-day spend"] --> SEQ["Sequential scan of the orders table"]
SEQ --> ALL["Reads every order ever placed"]
ALL --> SLOW["Seconds, growing with total order count"]
ALL --> IO["The scan evicts everything useful from the buffer cache"]The cost is proportional to the whole table for an answer that concerns a handful of rows. It also damages everything else running at the same time, because the scan pushes hot pages out of memory.
A composite index on (user_id, created_at)
Now the database seeks directly to that user and walks only their recent orders.
The right index, and it leaves one avoidable cost: the index holds user_id and created_at, but the query also needs status and total_cents, so each matching entry causes a lookup back into the heap. For a customer with hundreds of orders that is hundreds of random reads to sum a column.
Cover the query, and partition by time
CREATE INDEX orders_user_recent ON orders (user_id, created_at)
INCLUDE (status, total_cents);%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
Q1["One user, last 30 days"] --> IDX["(user_id, created_at) INCLUDE (status, total_cents)"]
IDX --> ONLY["Index-only scan - never touches the table"]
Q2["Top spenders this quarter"] --> IDX2["(created_at) INCLUDE (user_id, status, total_cents)"]
IDX2 --> RECENT["Reads only recent entries"]
BIG["Very large table"] --> PART["Partition orders by month"]
PART --> PRUNE["Partition pruning - old months never opened"]- Including the summed columns turns it into an index-only scan: the answer comes entirely from the index, and the heap is never touched.
- A second index for the other direction. "Top spenders in a range" leads with
created_at, because the filter is on time and the grouping is on user. One index cannot lead with both columns. - Partition by month once the table is large. Pruning means a 30-day query opens one or two partitions instead of scanning an index spanning years, and dropping old data becomes a partition drop rather than a mass delete.
Then confirm with EXPLAIN ANALYZE rather than assuming. The thing to look for is an index-only scan with a low Rows Removed by Filter — an index that exists but is not being used, or is used with a heap fetch per row, is a common and invisible difference between these three rungs.
Extras
- Refunds: store refunds as separate rows (or negative adjustments) with their own dates, so spend = charges − refunds in the window.
- Currency: store the order currency, and convert with the rate at purchase time for reporting in one currency.
- Dashboards at scale: a daily summary table
user_daily_spend(user_id, day, spend_cents), updated incrementally. Then 30-day spend = the sum of ≤ 30 small rows. - Why
total_centson orders? It's a small, safe denormalization that avoids joining order_items for spend queries. Keep it consistent by computing it in the same transaction as the items.
Wrap-UpWrap-up
Use normalized users, products, orders and order_items tables with foreign keys and CHECK constraints, and copy the charged unit price into order_items (plus an order total) so history never changes when product prices do. Answer recent-spend queries from orders with a composite covering index on (user_id, created_at) (and a created_at index or monthly partitions for all-user rankings), verify with EXPLAIN, and add refund handling and daily summary tables as data grows.