SQL背景开发者咨询DynamoDB二级索引的灵活性问题

Hey there! Since you’re coming from an SQL background, let’s break down how DynamoDB’s secondary indexes can supercharge your data operations with your book catalog schema. Your core key choice (Author as partition key, Title as sort key) is solid for direct lookups of a specific author’s book, but secondary indexes unlock way more query patterns without sacrificing performance.

First: A Quick Primer on DynamoDB Index Types #

Unlike SQL’s flexible secondary indexes, DynamoDB has two distinct types—each with specific use cases tailored to its key-based design:

• Global Secondary Indexes (GSIs): Independent of the main table’s partition key. Think of these as "standalone mini-tables" that let you query across all authors/books using entirely different keys.
• Local Secondary Indexes (LSIs): Share the main table’s partition key (Author in your case) but use a different sort key. Perfect for sorting/filtering within a single author’s books.

Global Secondary Indexes (GSIs): Uncrossing Query Silos #

Let’s map your schema’s fields to practical GSIs that solve common query needs:

1. Query by Publisher (e.g., "Show me all books from Tor Books") #

Your main table’s key doesn’t include Publisher, so a direct query would require a full table scan (slow and expensive). Instead, build a GSI:

• GSI Key Schema: Publisher (partition key) + Title (sort key)
• Projection: Choose ALL to include all book fields, or INCLUDE if you only need specific fields like Author and Tags to save storage/write costs.

This lets you run a fast, efficient Query operation targeting Publisher = 'Tor Books' and get results sorted by title—no scan needed.

2. Query by Tag (e.g., "Show me all SciFi books") #

Your Tags field is an array, which needs a little extra thought. DynamoDB doesn’t natively index array elements, so you have two options:

Option A: Multi-Entry GSI (Best for Performance)

When inserting a book with multiple tags, write one entry to the GSI per tag. For example, Old Man’s War would create two GSI entries: one for Tag = 'DeepSpace' and one for Tag = 'SciFi'.

• GSI Key Schema: Tag (partition key) + Author (sort key)
• Projection: KEYS_ONLY (just Author and Title) if you only need to look up the main table entries, or ALL if you want tag results with full book details.

This lets you run a lightning-fast Query for Tag = 'SciFi' and get all matching books sorted by author.

Option B: Project Tags and Use Filtering (Simpler, Less Efficient)

If you don’t want to handle multi-entry writes, build a GSI that projects the Tags field (e.g., using your main table’s key + projected Tags). Then run a Scan on the GSI with a CONTAINS filter for the tag you want. Note: This works but is slower than the multi-entry approach, especially as your catalog grows.

Local Secondary Indexes (LSIs): Sorting Within an Author’s Books #

Since LSIs share the main table’s partition key (Author), they’re ideal for queries like "Show me all John Scalzi’s books sorted by publisher"—something you’d do with an ORDER BY in SQL, but optimized for DynamoDB’s key model.

Build an LSI with:

• LSI Key Schema: Author (partition key, same as main table) + Publisher (sort key)
• Projection: ALL to include all book fields.

Now you can query Author = 'John Scalzi' on this LSI and get his books sorted alphabetically by publisher—no post-query sorting required, and it’s faster than filtering the main table.

Key Things to Remember (From SQL to DynamoDB) #

• Indexes are Materialized Views: Every write to the main table triggers a write to each index, so more indexes mean higher write costs. Only build indexes for query patterns you actually use.
• Projection Matters: Use KEYS_ONLY or INCLUDE instead of ALL whenever possible to reduce storage and write overhead.
• Avoid Over-Scanning: GSIs let you replace full table scans with targeted queries—always prioritize query operations over scans when using indexes.

内容的提问来源于stack exchange，提问作者Impostor Syndrome