Why DynamoDB Fails Most Real-World Apps

# Why DynamoDB Fails Most Real-World Apps ... **Brilliant KV at scale. Painful for most business queries.** I shipped a SaaS on DynamoDB. From start to scale during many years. Using DynamoDB as the primary store was one of my worst engineering calls. It feels great in week one: low cost, serverless, fast, safe, replicated, console out of the box. Then reality hits. Most business apps need flexible queries and evolving schemas. DynamoDB punishes both. … ## Two core flaws that sink product teams ### 1) Weak querying for real business needs Business apps rarely stop at “get by id.” They grow into multi-filter lists, admin dashboards, reports, exports, and “can we sort by X then Y?” asks. With DynamoDB: - You sort only within a partition. - Filters happen **after** item selection. - Cross-attribute predicates need GSIs, denormalized views, or both. - Every new dimension risks a backfill, a new GSI, or bespoke glue. … Add or remove a filter? Change the sort priority? Still trivial in SQL. **DynamoDB reality** You’ll end up with a GSI on `(status, created_at)` (maybe per tenant), another index or a composite key to slice by `country`, and you still can’t do a global sort by `created_at, total, order_id` across partitions. You fake it by: - Querying multiple indexes - Merging results in memory - Resorting client-side - Re-paginating manually - Handling holes/dupes across pages ``` // Sketch: 3 filters (status, country, time window) + multi-sort emulation ... ':status': { S: status }, ':since': { N: String(since) }, ':now': { N: String(Date.now()) }, ':country':{ S: 'FR' } ... ScanIndexForward: false, // created_at DESC Limit: 200 // overfetch to emulate secondary sort }).promise()); const merged = (await Promise.all(queries)).flatMap(r => r.Items); // Emulate ORDER BY created_at DESC, total DESC, order_id ASC merged.sort((a, b) => (Number(b.created_at.N) - Number(a.created_at.N)) || (Number(b.total.N) - Number(a.total.N)) || (a.order_id.S.localeCompare(b.order_id.S)) ); const page = merged.slice(0, 50); // manual pagination ``` … ### 2) Query vs Scan forces premature modeling and long-term rigidity This is stated quite hard, you should use Scan in production very carefully and prefer Query in hot paths - for cost and speed matters. DynamoDB makes you pick partition/sort keys and access patterns **upfront**. But real products don’t freeze their questions on day one. You end up: - Over-engineering single-table designs before you have traction - Backfilling GSIs when requirements change - Fighting hot partitions and throughput tuning - Paying in complexity every time you add a filter In an RDBMS, you add an index and move on. In DynamoDB, you plan a migration, tweak streams, write backfills, and hope you didn’t miss a denormalized projection. ## About AWS “workarounds” and their costs You’ll hear: “Keep DynamoDB for writes, then sync to something query-friendly.” - **OpenSearch sync:** $200-$1000 monthly cluster cost, index pipelines, mapping drift, cluster sizing, reindex pain, a new skillset to learn. Also another thing to break. - **RDS/Postgres sync:** At that point, why not just use Postgres first? Dual-write or stream-ingest adds failure modes and ops overhead. - **Athena/Glue/S3 sync:** Fine for batch analytics, not product queries. Latency, freshness, partitioning strategy, and scan-based pricing complicate everything. … - **Streams:** Mixed firehose. Every consumer re-implements routing + type logic. - **Monitoring:** Metrics blur across entity types. Hot keys and throttles are harder to triage. - **PITR/Backups/Restore:** You can’t easily restore “just Orders for tenant X.” It’s all intertwined.