Be careful with single-table design in AWS for Developers

Be careful with single-table design

I've watched teams move fast with DynamoDB single-table design, only to hit a wall six months later when their business logic evolved. What seemed like a clever optimization became a maintenance nightmare. Be careful with single-table design—it's genuinely powerful for specific, well-defined access patterns, but it comes with a hidden cost: inflexibility.

What single-table design actually is

Single-table design means storing all your application entities (users, orders, products, payments) in one DynamoDB table, differentiated by composite keys. You pack related data into items using overloaded attributes. The appeal is obvious: predictable costs, minimal cross-table queries, and blazing-fast retrieval if you know your access patterns.

A classic example: you might use a partition key like PK = USER#123 and sort keys like SK = ORDER#456 to retrieve all orders for a user. You'd also add a GSI (Global Secondary Index) with PK = ORDER#456 and SK = TIMESTAMP to query orders by date. One table, multiple views, minimal latency.

It works beautifully when you've mapped out your access patterns upfront and they don't change. In reality, they always change.

Great for well-known access patterns

Single-table design shines when you have crystallized, unchanging requirements. I've used it successfully in ride-hailing platforms where we knew exactly how drivers would search for trips, how dispatch would track active rides, and how passengers would check order status. These patterns were locked in by product and never shifted.

Before building the single-table schema, we ran a full access pattern analysis:

• Driver: find all active rides assigned to me (PK = DRIVER#ID, SK = RIDE#TIMESTAMP)
• Passenger: find my trip history (PK = PASSENGER#ID, SK = TRIP#TIMESTAMP)
• Dispatch: find all unassigned rides in a region (GSI with PK = REGION#ZONE, SK = CREATED_AT)
• Analytics: query completed rides by date (GSI with PK = STATUS#COMPLETED, SK = ENDED_AT)

Each query mapped to a specific key structure. We added exactly four indexes. Provisioning was straightforward, and cost predictability was excellent because we knew our traffic shape.

The performance was also undeniable. A driver opening the app and fetching active rides hit one DynamoDB item fetch with a begin_with query. No joins, no aggregation, just a direct partition key lookup. Latency stayed under 50ms at the 95th percentile.

But here's the catch: this only worked because we'd been running this product for three years. The access patterns were battle-tested and stable. If we'd built the single table in month one, we'd have suffered badly.

Hard to change when requirements shift

Three months into a trading platform build, product decided that traders needed to see not just their open positions, but a "my portfolio performance vs. market benchmark" view. That benchmark rolled up positions across multiple asset classes and time windows. Nobody had anticipated this.

We'd already committed to single-table design with positions, orders, and fills all normalized into one table. The partition key was TRADER#ID and sort key was POSITION#SYMBOL#EXPIRY. Adding the benchmark view meant we'd need to either:

1. Scan the entire positions table and aggregate in application code (slow, expensive)
2. Create three new GSIs to support different aggregation windows (complex, harder to maintain)
3. Denormalize benchmark data into every position item (storage bloat, difficult syncing)
4. Bite the bullet and refactor to multiple tables (painful, risky)

We went with option 3 halfway, which created inconsistency bugs. Benchmark data got stale. It taught me a hard lesson: single-table design optimizes for known queries at the cost of flexibility.

The real problem emerges when you need to support an access pattern that doesn't fit your existing key structure. Adding a GSI is cheap in DynamoDB, but there's a limit. You can have up to 20 GSIs per table, and after five or six, your schema becomes a tangle of assumptions. New team members struggle to understand why keys are structured this way. When the next requirement lands, no one wants to touch it.

I've also seen teams try to "future-proof" single tables by over-engineering the schema from day one. They add 12 GSIs with different PKs and SKs, "just in case." Then you're paying for 12 indexes you don't use, burning RCU (read capacity units) on maintenance writes, and the codebase becomes impossible to reason about.

Multiple tables are fine

Normalize. Seriously. Multiple tables won't kill your performance if you design them right.

I built a CRM platform that started with the assumption we'd need everything in one table for speed. Within two sprints, I split it into five: Contacts, Companies, Activities, Deals, and Notes. Each table had a clear, single responsibility. No more contorted key structures.

The payoff was immediate:

• New developers understood the schema in minutes, not hours
• Adding a "notes search" feature was trivial—just add a GSI on the Notes table
• Capacity planning became simpler; each table had predictable traffic
• Testing was easier; mocking one table is cleaner than mocking complex key hierarchies

Yes, retrieving a contact and their related activities now requires two queries instead of one. But the second query is a simple GSI lookup: PK = CONTACT#ID, SK begins_with ACTIVITY#. It's fast, and you can batch them with BatchGetItem if needed. The latency difference is milliseconds, but the operational clarity is enormous.

For a SaaS platform with moderate scale, I'd choose multiple, well-normalized tables over a convoluted single table 9 times out of 10. You trade microseconds of latency for weeks of debugging time saved.

That said, there's a middle ground: use single-table design only for high-frequency, latency-critical entities (like active session data or real-time notifications), and use separate tables for everything else. Best of both worlds.

Use relational databases for ad-hoc queries

Here's something people don't say enough: DynamoDB isn't built for ad-hoc queries. It's not flexible like SQL. If your product roadmap includes exploratory analytics, compliance reports, or customer segmentation queries that change month-to-month, put that data in PostgreSQL or Aurora.

I worked on a ride-hailing platform where operations needed to run queries like "show me all drivers who completed more than 20 rides in Q3 with a 4.8+ rating and are active in zones 1-5." With single-table DynamoDB, that's a nightmare. You'd scan the entire drivers table and filter in application code—painful and expensive at scale.

With PostgreSQL, that query takes seconds:

SELECT driver_id, COUNT(rides) as completed_rides, AVG(rating) as avg_rating
FROM rides
WHERE completed_at BETWEEN '2024-07-01' AND '2024-09-30'
  AND driver_id IN (SELECT id FROM drivers WHERE current_zone IN (1,2,3,4,5) AND active = true)
GROUP BY driver_id
HAVING COUNT(rides) > 20 AND AVG(rating) >= 4.8
ORDER BY completed_rides DESC;

The architecture that worked best for us was:

• DynamoDB: Active operational data (current rides, driver location, real-time notifications)
• PostgreSQL: Historical data, reference tables, and anything analytics-focused
• Kafka or Lambda functions: Stream events from DynamoDB to PostgreSQL for analytics

This separation of concerns means your DynamoDB tables stay lean and predictable, while your relational database handles the queries that shift and evolve.

Common mistakes

Overengineering the schema to support "future" access patterns: You end up with 15 GSIs and no one knows which keys to use. Start with what you know, add indexes as requirements land.

Assuming network latency is always the bottleneck: It's often not. Application complexity, debugging time, and onboarding new developers usually matter more. A 50ms two-table query beats a 5ms one-table query that took you three weeks to build correctly.

Using single-table design for soft deletes, multi-status workflows, or entities with optional relationships: These patterns are messy in DynamoDB. You end up with sparse attributes and confusing key logic. A normalized schema handles them cleanly.

Not documenting your key structure: Your team will hate you. Write it down. Include examples. Show the sort key prefix for each entity type. Add comments in your code.

Forgetting that DynamoDB costs scale with GSIs: Every write hits every GSI. If you have six GSIs, a single item update costs 7x the RCU. That adds up.

Key takeaways

• Single-table design is genuinely powerful for locked-in, high-frequency access patterns, but it's an optimization, not a default
• Most teams overestimate their ability to predict future requirements; build for change with multiple tables instead
• Multiple DynamoDB tables with clear responsibilities are easier to understand, test, and evolve than a convoluted single table
• Use PostgreSQL or Aurora for queries that need flexibility; stream data from DynamoDB if you need both fast operational reads and ad-hoc analytics
• If you do use single-table design, document the key structure meticulously and limit yourself to five GSIs maximum
• Measure actual latency impact before choosing a complex schema for theoretical performance; the cost of debugging often exceeds the cost of an extra network round-trip