Secondary Indexes at Scale

Local vs global secondary indexes

Base table partition key: USER#42
  ├── ORDER#2026-001  (sort key)
  └── ORDER#2026-002

GSI partition key: TRACKING#1Z999
  └── points to ORDER#2026-001 on USER#42

VaultCommerce's tracking lookup hits the GSI partition TRACKING#1Z999 directly — O(1) routing. Without it, scanning all user partitions is impossible at scale.

Write amplification

Every GSI row duplicates write traffic. Updating an order status writes the base table and each GSI projection:

order_update_cost:
  base_table: 1 WCU
  gsi_tracking: 1 WCU
  gsi_status_by_date: 1 WCU
  total: 3 WCU per status change

Design GSIs only for proven access patterns. VaultCommerce limits each table to three GSIs — each added only after query metrics justify the write tax.

Query fan-out and hot partitions

A poorly chosen GSI partition key concentrates traffic:

GSI key: STATUS#SHIPPED  ← millions of orders share one partition
Result: hot partition, throttling, 503s during peak shipping

VaultCommerce shards high-cardinality GSI keys with a composite: STATUS#SHIPPED#2026-03-15 buckets by ship date — trading one query for many parallel lookups merged in application code.

Postgres secondary indexes at scale

Distributed concerns apply even on single-node Postgres when tables reach hundreds of millions of rows:

-- Partial index: only active carts — smaller, faster
CREATE INDEX CONCURRENTLY idx_active_carts
  ON carts (user_id)
  WHERE status = 'active';

-- Covering index avoids heap lookups
CREATE INDEX idx_orders_user_covering
  ON orders (user_id, created_at DESC)
  INCLUDE (status, total);

CREATE INDEX CONCURRENTLY avoids blocking writes during index builds — mandatory for VaultCommerce's 400 GB orders table.

Index lifecycle discipline