What Queries Your DynamoDB Schema Explicitly Doesn’t Support
When I posted the e-commerce orders pattern, a comment in the r/webdev thread cut to something important: “Great, but what can’t you do with this schema?”
It’s the right question, and most schema writeups - including mine - don’t answer it well. We show the access patterns the schema supports. We don’t show the ones it doesn’t.
That’s a problem, because every DynamoDB schema is a set of explicit bets. You’re betting that your pre-defined access patterns are the right ones. The queries you’re not supporting are the technical debt you’re taking on. If those queries ever become requirements, you’ll be adding GSIs or restructuring keys under pressure.
Writing down the unsupported queries before you ship is one of the most valuable things you can do for your future self.
Production issue
You're likely losing money on this in production.
A wrong partition key or missing GSI is a live cost problem. Get a DynamoDB schema review before your next deploy — async, fixed price, 5 business days.
Why DynamoDB forces this choice explicitly
In Postgres, you can add a WHERE clause for almost anything. You might add an index to make it fast, but the query is always possible. DynamoDB doesn’t work this way. A query without a matching index is either impossible or requires a full table scan - which at scale is expensive enough that it’s effectively impossible.
This isn’t a weakness. It’s the constraint that makes DynamoDB fast. The tradeoff is that you have to decide upfront which queries you care about. What feels like a limitation is actually forcing clarity that relational databases let you defer indefinitely.
The problem is that most teams treat this as implicit. They design for the access patterns they know about and silently ignore the ones they don’t. Then six months later, someone needs a query the schema doesn’t support and nobody knows whether it was a deliberate decision or just an oversight.
The format I use
For each entity in the schema, I keep an “unsupported queries” list alongside the access patterns. Here’s what it looks like for the e-commerce orders schema:
E-Commerce Orders - what this schema doesn’t support
| Unsupported Query | Why | If You Need It |
|---|---|---|
| Orders filtered by total amount | No index on total attribute | Add GSI with total as SK, or query by status and filter client-side |
| Orders by product (which orders contained product X) | OrderItems don’t have a GSI pointing to product | Add PRODUCT#<productId> GSI partition on OrderItem |
| Customer lifetime value across all orders | Requires aggregation across all orders for a customer | Compute and store on Customer record, update on each order |
| Orders modified in the last 7 days | No updatedAt index | Add GSI on updatedAt if this becomes an ops requirement |
| Average order value by month | Reporting aggregate | Stream to data warehouse; not a DynamoDB query |
| Orders containing a specific promotion code | No promotion code index | Add sparse GSI if this becomes a feature requirement |
None of these are design mistakes. They’re deliberate gaps - queries that either aren’t needed, can be served by a different data store, or can be computed and stored as denormalized values. But making them explicit means:
- A new engineer can see the reasoning instead of wondering why something seems hard
- When a requirement changes, you know immediately whether it’s a gap you anticipated or a schema problem
- You can revisit the list in code review and catch queries that were forgotten
The two categories of unsupported queries
Not all unsupported queries are equal. It helps to categorize them.
The first category is queries you’re deliberately not supporting. Reporting, analytics, aggregations - these are better served by a data warehouse fed by DynamoDB Streams. You’re not supporting them in DynamoDB because DynamoDB isn’t the right tool, not because of a schema limitation. Document this distinction explicitly: “not a DynamoDB query - handled in Redshift.” This is the same split I recommend in DynamoDB vs Postgres for e-commerce: OLTP in DynamoDB, analytics in a proper analytical store.
The second category is queries you might need later. Access patterns that aren’t current requirements but could become them. “Show all orders for a product” isn’t needed today, but if you build a product analytics dashboard it will be. Adding a sparse GSI now costs almost nothing if the query volume is low. Adding it later requires a table migration.
For the second category, I flag them with “watch this” in the unsupported query list. It’s a signal that the schema might need to evolve here, and a reminder to revisit when requirements change.
How this changes schema design conversations
When you put the unsupported queries next to the supported ones, schema review conversations change.
Instead of “does this schema support the access patterns?” you’re asking “are we comfortable with the access patterns this schema doesn’t support?” It’s a harder question, and it surfaces assumptions that would otherwise stay implicit until they become production incidents.
For the SaaS multi-tenant pattern, the unsupported queries include:
- List all users across all tenants (admin user search) - requires a cross-tenant GSI or a separate user directory
- Projects sorted by anything other than creation date - the sort key commits you to chronological ordering
- Tenants by plan type - the tenant list GSI sorts by name, not plan; add another GSI or filter client-side
These are all deliberate. The multi-tenant schema optimizes for tenant-scoped access, and cross-tenant queries are intentionally limited to the admin listing GSI. But “deliberate” only means something if you’ve actually thought it through and written it down.
A template
If you use the pattern blog post format - access patterns, table design, sample data - add an unsupported queries section before the ElectroDB code:
## What this schema doesn't support
| Query | Status | Notes |
|-------|--------|-------|
| [query] | Deliberate gap | [reason / alternative] |
| [query] | Watch this | [might become a requirement] |
| [query] | Out of scope | [handled elsewhere] |It takes ten minutes and saves hours of debugging later when someone wonders why a query is hard.
The singletable.dev visual designer puts access patterns and unsupported queries side by side - so you see the full shape of what your schema can and can’t do before it’s in production. Join the list if that sounds useful.
