Solution (single-table, user-partitioned):
Primary table keys
- PK (partition key): user_id
- SK (sort key): composed type prefix + qualifier, e.g.:
- PROFILE (single item): SK="PROFILE"
- Activity items: SK="ACTIVITY#<ISO8601-timestamp>#<activity_id>"
- User items (that have tags): SK="ITEM#<tag>#<item_id>"
How each access pattern is served
- Get user profile by user_id
- Use GetItem with PK=user_id and SK="PROFILE". Very cheap and strongly consistent if needed.
- List a user's recent activities ordered by timestamp
- Query table with PK=user_id and KeyConditionExpression SK begins_with "ACTIVITY#"; results are returned ordered by SK. If you store timestamp as ISO8601 or inverted-timestamp in the SK, ordering is natural (newest-first if using inverted ts). Alternatively add an LSI:
- LSI_activity: PK=user_id (same), LSI_SK=activity_ts (number or ISO string) — Query PK=user_id, order by LSI_SK DESC for efficient time-ordered reads and range queries.
- Query a user's items by tag
- Create a GSI to support tag+user queries:
- GSI1 PK: user_tag (compose "USER#<user_id>#TAG#<tag>")
- GSI1 SK: item_id (or created_at)
- When writing an item record, include attribute user_tag="USER#<user_id>#TAG#<tag>" for each tag (for multi-tag items write multiple records or maintain an item->tags mapping). Query GSI1 with user_tag to get all items for that user/tag.
Design notes and reasoning
- Single-table with user_id as PK keeps user-scoped access localized to one partition key, simplifying consistency and reducing cross-partition queries.
- SK composition lets you store heterogeneous items (profile, activities, items) in one table; prefixes make filtering and short KeyConditionExpressions efficient.
- LSI is used when you need alternate sorted access within the same partition (activity timestamp ordering). LSI only available for same PK which fits "per user" ordering.
- GSI is used to support an access pattern that cannot be satisfied by the base PK/SK (query by user+tag). Using a composite GSI partition key (user_tag) makes the query a single-partition GSI read.
Capacity / operational considerations (SRE perspective)
- If traffic is unpredictable or bursty, use On-Demand mode to avoid throttling and simplify ops. For predictable steady traffic, use Provisioned with autoscaling targets (target utilization ~70%) to control cost.
- Estimate RCU/WCU from expected QPS and item size. For Query-heavy patterns consider eventually consistent reads where acceptable to halve RCU cost.
- Hot-user risk: if a few users generate high load, enable write sharding for hot partitions (e.g., suffix user_id with modulo shards) or use DAX/Edge cache for read-heavy hot partitions.
- Monitor CloudWatch metrics: ThrottledRequests, ConsumedCapacity, UserErrors, ConditionalCheckFailedRequests; set alerts and autoscaling policies.
- Use exponential backoff and jitter in clients, and item-level TTL for activity retention to limit storage growth.
- Consider global secondary index capacity too (GSIs consume separate throughput in provisioned mode).
Edge cases
- Multi-tag items: choose whether to duplicate GSI entries per tag or maintain a tag set and filter (filtering costs reads).
- LSI limit: max 5 LSIs per table; plan schema accordingly.
- Large result pagination: Query with Limit and LastEvaluatedKey; consider fetching pages asynchronously if UI requires it.
