An order system, modeled two ways

The best way to feel the difference between models is to build the same system twice. Take the order system's access patterns from access patterns, minus free-text search, which goes to a search index anyway. Model them once in Postgres and once in DynamoDB. Then compare the effort and the trade-offs.

The big idea

It is the same facts on two bookshelves. The relational shelf is sorted by subject, so any new question can be answered by looking across the shelf. The DynamoDB shelf is arranged for the questions you already know. It answers those very fast, and a new question may mean moving books.

Walking through it

Relational model. Customers, orders, order items and products are separate tables linked by keys.

Pattern 1 is two primary-key lookups. Pattern 2 uses the covering index from indexing strategy. Pattern 3 is one transaction. This sketch shows the transaction, with $id standing for the order ID from the insert above it.

BEGIN;
UPDATE products SET stock = stock - 2
  WHERE id = 42 AND stock >= 2;          -- 0 rows updated means out of stock: ROLLBACK
INSERT INTO orders (customer_id, status, total_cents) VALUES (7, 'PENDING_SHIPMENT', 5998)
  RETURNING id;
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents) VALUES ($id, 42, 2, 2999);
COMMIT;

DynamoDB model. You design the keys from the patterns.

• Pattern 2 is one query on the customer's partition, with ScanIndexForward = false and Limit = 20.
• Pattern 4 uses a sparse GSI. Only pending orders carry the index key. When the order ships, the attribute is removed, and the item drops out of the index.
• Pattern 3 uses a transaction with a condition that stock >= :qty. Transactions are limited to 100 items, and they cost roughly twice as much to write.
• Pattern 5 is not practical. You export to S3 and query with a warehouse.

Comparing them

DynamoDB is not worse. It is a different bargain. You give up query flexibility for predictable latency and easy scale. That suits a stable, high-volume access pattern. It is costly for a young product whose questions change every month.

Common mistakes

• Choosing DynamoDB before listing the patterns. You will rebuild the model when the first new question appears.
• Assuming Postgres cannot scale. A single tuned node with replicas and a cache goes further than most people expect.

Check yourself

1. Why is pattern 4 a sparse index in DynamoDB? Only pending orders carry the index key. When an order ships, the attribute is removed, so the item leaves the index.
2. What does DynamoDB give up for predictable latency? Query flexibility. New questions often need a new index and a backfill.

Key takeaways

• Build the same access patterns in both models to see the trade-off.
• Postgres handles new questions with indexes. DynamoDB handles known questions very fast.
• Transactions and conditional writes keep stock and orders consistent in both.
• Pick the model after the patterns, not before.

Further learning

For the DynamoDB design method in more depth, see query-first modeling.

For the decision rule, see databases and data modeling.