QuickCart Stage 3: Choosing the Right Data Store for Each Job

Graph. A graph store keeps nodes and relationships and is quick at traversals such as "friends of friends". Verdict: not needed. "Shoppers who bought this also bought" is a co-purchase count computed in analytics, not a deep traversal. It would earn a place if QuickCart began hunting for connected clusters of accounts, such as fraud rings.

Choosing SQL versus NoSQL

"NoSQL scales, SQL does not" is a myth: a well-indexed relational database with replicas and a cache carries a great deal of load. Choose by access pattern. Ask, in order: Does a wrong answer cost money (then start relational)? Do you know every query in advance (then a query-shaped store is possible)? Is the shape stable? Is the database really the bottleneck (measure first)? Can the team operate it at 2 a.m.?

Figure 3. Choosing a store for a QuickCart data type

How to read this: take the first question that fits. Money comes first because it rules out the stores that give up transactions.

Making reads fast

Indexing: B-tree, LSM tree and inverted index

An index is an extra data structure that lets the database find rows without reading the whole table. Three structures cover most of QuickCart; storage engines goes deeper.

B-tree. A B-tree keeps keys sorted in a shallow, wide tree of pages. A lookup follows one branch per level to a leaf holding the key and a pointer to the row. Teaching example: if each page holds 100 keys, three levels reach 100 x 100 x 100 = 1,000,000 entries. Sorted leaves also make ranges and ORDER BY cheap. In PostgreSQL, btree is the default index type.

Composite index and column order. An index on several columns is sorted by the first, then the second within it, like a phone book sorted by surname then first name. PostgreSQL's multicolumn index documentation gives the rule: equality constraints on leading columns, plus an inequality on the first column without an equality, limit the portion of the index that is scanned. A store dashboard asks for its paid orders awaiting acceptance, oldest first:

CREATE INDEX orders_store_status_created
  ON orders (store_id, status, created_at);

SELECT id FROM orders
WHERE store_id = 17 AND status = 'paid'
ORDER BY created_at
LIMIT 50;

The two equalities narrow the scan to one corner of the index, where entries are already in created_at order, so no sort is needed. A query filtering only on status cannot use that corner, because the index is ordered by store_id first. (At the time of writing, PostgreSQL 18 can sometimes skip leading values, but the documentation says only when the leading column has few distinct values.) Put equality columns first, then the range or sort column.

The write cost. Every insert updates every index, and updates to indexed columns do too. Four indexes on orders means one table write plus four index writes per order, and each index uses memory and disk. On a busy table, use CREATE INDEX CONCURRENTLY: per the documentation it does not lock out writes, does two table scans, cannot run inside a transaction block, and can leave an invalid index if it fails.

LSM tree. A log-structured merge tree turns random writes into sequential ones. Writes go to a log for durability and to a sorted in-memory table (the memtable); when it fills, it is written out as an immutable sorted file (an SSTable). Reads check the memtable, then files from newest to oldest. Compaction later merges files and drops old versions. Worked example with courier pings, memtable flushing after three entries (illustrative):

Writes are cheap; the price is read and compaction work. Cassandra and RocksDB use this design.

Inverted index. A product row lists its words; an inverted index flips that and maps each word to the list of rows containing it, a posting list. Take three product names: 1 "oat milk 1l", 2 "whole milk 1l", 3 "oat biscuits".

Figure 4. An inverted index over three product names

How to read this: answering "oat milk" means intersecting two lists, which leaves product 1, without reading any product text.

PostgreSQL's GIN index is this structure: it stores (key, posting list) pairs and is commonly used for full-text search. Search engines add analysis and ranking on top.

Transactions and isolation

ACID and transaction isolation levels

A transaction groups statements into one all-or-nothing unit. ACID names its guarantees: atomicity (all or none), consistency (your declared rules hold), isolation (concurrent transactions do not interfere beyond a chosen level) and durability (committed data survives crashes). ACID consistency is about constraints, a different idea from the replica agreement in CAP and consistency. Isolation is the subtle one, and it is a dial.

The SQL standard defines four levels. PostgreSQL's documentation (version 18 at the time of writing) says it implements three distinct ones:

Other databases differ; check the one you run.

The stock decrement. One unit of oat milk remains at store 17 and two shoppers check out together. A tempting design reads stock (it is 1), checks it is at least 1, and writes stock = 0. Under read committed both read 1 and both write 0, so QuickCart sells a unit it does not have: a lost update. The fix is one atomic statement:

UPDATE inventory
SET stock = stock - 1
WHERE store_id = 17 AND product_id = 9001 AND stock >= 1;

PostgreSQL reports UPDATE 1 if a row changed and UPDATE 0 if none matched. Per the documentation, under read committed a second updater waits for the first, and if the first commits, re-evaluates the WHERE clause against the new version of the row. The second statement sees stock = 0, matches nothing and returns UPDATE 0, so the app says the item is gone. Under repeatable read the second transaction fails with could not serialize access due to concurrent update and must be retried. The schema's CHECK (stock >= 0) is a last line of defence.

Figure 5. Two checkouts race for the last unit

How to read this: the row lock makes B wait, and the re-check after A commits is what stops the oversell. No application locking is needed.

Write skew needs more. Courier 7 may carry at most 3 active deliveries. Each assignment counts the active ones and inserts a row if the count is below 3:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM deliveries WHERE courier_id = 7 AND status = 'active';
-- application checks: count < 3
INSERT INTO deliveries (id, courier_id, order_id, status)
VALUES (5001, 7, 88001, 'active');
COMMIT;

Two such transactions insert different rows, so no row conflicts and, under repeatable read, both commit and the courier carries 4. Under serializable, PostgreSQL detects the dependency cycle and fails one with SQLSTATE 40001; the documentation requires code to rerun the whole transaction. An atomic update covers most rules; serializable is for rules that span rows. Work across databases is in distributed transactions.

Changing the data model safely

Data modeling and schema evolution

Data modeling means deciding which entities exist, how they relate and which rules hold. Work from queries and rules, not screens.

Schema evolution is the harder half, because the schema outlives any release. During a rolling deploy old and new application versions run together, so every change must be backward compatible.

The PostgreSQL ALTER TABLE guide says a constant default does not require updating every row, a volatile default does, and SET NOT NULL is checked immediately against existing data.

Expand and contract handles the dangerous changes in small, reversible steps. Example: rename orders.created_at to placed_at. A plain RENAME COLUMN is fast but breaks every old server still reading created_at.

Figure 6. Expand and contract migration

How to read this: the first steps only add and the last only removes, and the app works at every step. Any step can be paused or reversed until step 5.

-- 1 expand
ALTER TABLE orders ADD COLUMN placed_at timestamptz;

-- 3 backfill, one range at a time, until no rows remain
UPDATE orders
SET placed_at = created_at
WHERE placed_at IS NULL AND id BETWEEN 1 AND 10000;

-- 5 contract, after a full release with no code reading created_at
ALTER TABLE orders DROP COLUMN created_at;

Documents need the same care: store a schemaVersion, make readers tolerate unknown and missing fields, and upgrade old documents when next written.

Specialised stores

Object and blob storage

Object storage keeps immutable blobs under keys in a flat namespace behind an HTTP API; it is cheap, durable and built for large files. It is not a file system (no in-place edits) and not a database (no queries by content). Product images are the clear case. QuickCart stores bytes in a bucket under keys such as products/9001/a1/original.jpg and keeps only the key in the catalogue document. A new photo gets a new key, so CDN copies never need invalidating. Stores upload with a time-limited signed URL straight to the bucket (the photo sharing case study walks through it; CDN and object storage covers delivery); processing photos is Stage 7 work.

On consistency, Amazon S3 states that it provides strong read-after-write consistency for writes, overwrites, deletes and listings; other providers differ, so read yours. Lifecycle rules archive or delete old objects.

Search engines

A search engine answers "which documents match these words, best first?" It uses the inverted index above plus analysis on both sides: break text into tokens, lowercase them, optionally reduce words to stems, so "Milks" matches "milk". The Elasticsearch text analysis overview describes tokenization and normalization at index time and query time. Matches are scored, with schemes such as BM25 that favour rare terms, and combined with filters. QuickCart's rules:

• The index is a derived copy built from the catalogue and inventory, never the source of truth, and can be rebuilt.
• It is eventually consistent: changes arrive seconds later. Checkout reads price and stock from PostgreSQL.
• Honest alternative: PostgreSQL full-text search with a GIN index would serve 100,000 products. A separate engine pays off when ranking, typo tolerance and typeahead matter.

Relevance, typeahead and keeping the index in sync are Stage 7 topics; see search and indexing.

Time-series databases

A time-series database stores timestamped measurements that are mostly appended, rarely updated and queried by time range. QuickCart has two sources: courier location pings and metrics.

Teaching arithmetic (illustrative): 10,000 couriers online, one ping every 5 seconds, is 10,000 / 5 = 2,000 writes per second. At 50 bytes a ping that is 100 KB per second, about 8.6 GB per day. A transactional database can store that, but its pages and backups would fill with a stream no one updates. What these stores do (see storage engines):

• Partition by time, so old data goes as whole partitions. PostgreSQL's partitioning documentation notes that dropping a partition is far faster than a bulk delete and avoids its vacuum cost.
• Compress regular timestamps and similar neighbouring values.
• Downsample: keep raw pings for 7 days (about 60 GB at the rate above) and one point per minute for longer.
• Limit cardinality. Each distinct label combination is its own series. Labelling metrics by courier_id is bounded by the courier count (10,000 here); labelling by order_id grows without limit.

The live map needs the latest position per courier, a different question from history: serve it from one overwritten key per courier in the Stage 2 cache.

Data warehouses and data lakes

QuickCart's questions about history ("revenue by store by week", "late deliveries by district") scan millions of rows and read few columns. This is OLAP, the opposite of the transactional OLTP work in the primary. A data warehouse is an analytical database, usually column-oriented, loaded with cleaned, modelled data and queried in SQL. A data lake is cheap object storage holding raw files in any format, with structure applied when read (schema-on-read) rather than when loaded (schema-on-write). Warehouses fit dashboards and repeatable reports; lakes fit exploration and messy data, but mistakes surface late, at read time.

A warehouse is usually a star schema: a fact table of events (fact_order_items) surrounded by dimension tables (dim_product, dim_store, dim_date).

ETL versus ELT. ETL (extract, transform, load) cleans data in a separate step before loading. ELT loads raw data first and transforms it inside the warehouse. ELT keeps the raw copy, so a corrected transformation can be re-run; ETL still fits when data must be filtered or masked before it lands.

Figure 7. QuickCart's analytics path (ELT)

How to read this: data is copied out of the operational stores on a schedule, kept raw in object storage, then transformed inside the warehouse. Analysts never touch the primary.

When does QuickCart need one? When heavy reports slow checkout even on replicas, when questions span several systems, or when years of history should not sit in operational stores. Before that, a replica is enough. Near-real-time pipelines are Stage 7.

Decisions and trade-offs

In practice: QuickCart at this stage

The situation

At one million monthly active users, product search takes over a second at peak (illustrative), store managers add free-text attributes every week, and a Friday sales report slows checkout. Courier pings are a large share of the primary's writes.

Starting architecture

Figure 1: one PostgreSQL primary with replicas, a cache and a CDN, a products table with dozens of mostly empty columns, and image bytes in a table.

Options considered

The decision

Rollout and verification

1. Images. Copy bytes to object storage, store keys, point the CDN origin at the bucket, then delete the database copies after a backup check.
2. Catalogue in shadow mode. Write to both stores, compare reads from the new one with the old path and log differences, then switch reads at 5, 25, 50 and 100 percent.
3. Search. Build the index from the catalogue, compare results for the top queries, then switch.
4. Pings. Dual-write for a week, check counts match, then stop writing to PostgreSQL.
5. Warehouse. Load nightly and reconcile one day of order totals.

Watch checkout p99 latency, primary CPU, index lag and a daily order-count reconciliation.

The 2 a.m. failure

A catalogue deploy turns the nutrition attribute from a string into an object. At 02:00 the search indexer, which expects a string, throws on every such document and stops. Index lag climbs, new products and edits stop appearing, and a withdrawn product stays searchable.

An alert on index lag above a few minutes pages the on-call. The first three checks: the indexer error log (one parse error repeating on one field), the lag graph (flat, then rising from the deploy time) and the schemaVersion distribution in the catalogue (a new value appearing). Mitigation: roll back the catalogue change if the old shape can be restored; otherwise deploy an indexer that accepts both shapes, set failing documents aside and let it catch up. Correctness held, because checkout reads price and stock from PostgreSQL and rejects a withdrawn item. Follow-ups: tolerant readers, a schema check in CI before catalogue deploys, and dead-letter queues, introduced in Stage 5.

Architecture after this stage

Figure 8. QuickCart architecture after Stage 3 (new stores highlighted)

How to read this: orange borders mark what this stage added. The CDN, app servers and primary database are from earlier stages; the load balancer, cache and replicas are unchanged and left out for clarity.

QuickCart's polyglot data map

What QuickCart looks like now

The next stage, scaling writes, shards the one component that must stay relational: orders.

Architect's notes

• Start from the question, not the product. Fill in the five properties before anyone names a technology.
• Keep money in one transactional store. Everything else may be stale; payments and stock may not.
• Mark which copy is the truth. Test that every derived copy can be rebuilt.
• Cross-store consistency is yours now. Plan reconciliation jobs that check ids match across stores.

Common mistakes

• Read-then-write stock checks. They oversell under concurrency; use one atomic conditional update.
• Serializable without retries. Serialization failures are normal; rerun the transaction.
• Wrong column order in a composite index. Equality columns first, then range or sort.
• Renaming a column in one step. Old servers still running break; expand and contract.
• Choosing NoSQL because "it scales". Without a measured need you give up transactions.

Key takeaways

• Choose a store by shape, access pattern, consistency need, volume and lifetime.
• Normalize first. Copy only a historical fact or a measured hot path.
• Four NoSQL families, four jobs. QuickCart uses key-value (already) and document stores, and does not need wide-column or graph yet.
• B-trees serve lookups and ranges, LSM trees serve heavy appends, inverted indexes serve word search. Every index costs writes.
• Composite index order matters. Equality columns first, then the range or sort column.
• Use an atomic conditional update for stock. Read committed re-checks the condition after waiting.
• Write skew needs serializable or explicit locking, and code that retries.

Practice exercises

Exercise 1 (30 minutes). Add courier ratings (a shopper rates a delivery 1 to 5 with a comment). Write the normalized tables with keys and constraints, and say where a denormalized average would help and how you would keep it right.

Exercise 2 (45 minutes). For "all orders for shopper 77 in the last 30 days, newest first", write the composite index. Name one query from this lesson it helps and one it does not, and say why.

Exercise 3 (1 hour). In PostgreSQL, create inventory with one row at stock 1. In two sessions run the read-then-write version, then the atomic update, then the atomic update under REPEATABLE READ. Write down what each session sees.

Exercise 4 (1 hour). Plan the expand and contract migration that moves users.email into a user_emails table. List each step and its rollback.

Related reading

• QuickCart Stage 1: the brief and Stage 2: scale reads
• QuickCart Stage 4: scale writes and Stage 7: search, notifications and media
• System Design: Databases Basics
• System Design: Storage Engines
• System Design: Search and Indexing
• System Design: CDN and Object Storage
• System Design: CAP and Consistency
• System Design: Distributed Transactions

Official documentation

• PostgreSQL: Transaction Isolation
• PostgreSQL: Multicolumn Indexes
• PostgreSQL: CREATE INDEX
• PostgreSQL: Modifying Tables (ALTER TABLE)
• Amazon S3: data consistency model
• MongoDB: Data Modeling