> Services that require ad hoc query access. Though it’s possible to use external relational frameworks to implement entity relationships across DynamoDB tables, these are generally cumbersome.
> Online analytical processing (OLAP)/data warehouse implementations. These types of applications generally require distribution and the joining of fact and dimension tables that inherently provide a normalized (relational) view of your data.
> Binary large object (BLOB) storage. DynamoDB can store binary items up to 400 KB, but DynamoDB is not generally suited to storing documents or images. A better architectural pattern for this implementation is to store pointers to Amazon S3 objects in a DynamoDB table.
I said moving the workload to DynamoDB, not doing the same query pattern with DynamoDB ... :-)
For each of these workloads you switch the query pattern to accommodate for DynamoDB and provide a solution for your workload and/or app. That is actually the secret of NoSQL. You do the work upfront.
So your reading of the recommendations is correct on the surface but incorrect on the fundamental usage aspect of it. Lets have a look at each:
> Services that require ad hoc query access. Though it’s possible to use external relational frameworks to implement entity relationships across DynamoDB tables, these are generally cumbersome.
A: Dont do this. Don't do ad hoc query access. Define a series of access patterns and lay out your data to support them. You dont get ad hoc query access
for Netflix, Amazon or your Airline Travel website...
> Online analytical processing (OLAP)/data warehouse implementations. These types of applications generally require distribution and the joining of fact and dimension tables that inherently provide a normalized (relational) view of your data.
These are not most SQL Patterns. This is OLAP and was always or should be done with MPP systems, not with your relational database like your SQLServer or your Oracle. So this is going on an edge...
> Binary large object (BLOB) storage. DynamoDB can store binary items up to 400 KB, but DynamoDB is not generally suited to storing documents or images. A better architectural pattern for this implementation is to store pointers to Amazon S3 objects in a DynamoDB table.
Your Relational Database will not store BLOBs more efficiently than S3 anyway....
DynamoDB can't even represent SELECT * FROM items LIMIT 25 OFFSET 100. It's just not designed for that. It's not meant to be a relational DB replacement.
How would you do it? Assume we want proper pagination, and not rewrite the app for cursor based "Load more" style pagination. Why? Because the React Admin provider API insists. https://github.com/marmelab/react-admin/issues/1510
Your example itself shows why you would not want to do this. The database still has to read through all the rows up to the OFFSET point...
You are not supposed to do the same query patterns. My argument is that you can substitute your relational database, by changing the app and the layout of the data to match the proper patterns for DynamoDB.
If you place arbitrary restrictions upon it then of course it won't fit your model. Dynamo can handle pagination easily by passing in the last seen ID in the request. We used IDs that were lexigraphically sortable by time which has some nice properties such as never losing your position due to extra items being inserted in the middle of your query. Offset/limit aren't fun to work with.
You can go further by using the streams feature to dump your data into an analytical database for your querying needs.
DynamoDB lacks consistency (in the ACID sense, it has been it in the CAP sense which is more related to Atomicity in ACID), so you can move an SQL workload to DynamoDB by moving the consistency parts out of the DB into the code of any application that hits the DB.
That's an article about isolation, not consistency; and even there, both the RDBMS’s relevant to thus discussion (those on Aurora Serverless that it is suggested in this subthread be replaced with DynamoDB) supported serializable isolation even in their current versions at the time of that article a decade ago.
So your post is both irrelevant as a response to mine about consistency and more generally irrelevant to the entire discussion here.
The article is about ACID, both consistency and isolation not just about consistency and you can't isolate them (pun intended). The underlying of the discussion was not on consistency, instead on the possibility of using DynamoDB where you would use a typical relational engine.
From the article:
"The textbook definition of ACID Isolation is serializability (e.g., Architecture of a Database System, Section 6.2), which states that the outcome of executing a set of transactions should be equivalent to some serial execution of those transactions. This means that each transaction gets to operate on the database as if it were running by itself, which ensures database correctness, or consistency."
" This means that each transaction gets to operate on the database as if it were running by itself, which ensures database correctness, or consistency. A database with serializability (“I” in ACID), provides arbitrary read/write transactions and guarantees consistency (“C” in ACID), or correctness, of the database. Without serializability, ACID, particularly consistency, is generally not guaranteed"
I am of course ignoring the Consistency you are certainly aware that exist in DynamoDB with Strong Consistency and DynamoDB Transactions.
Moving consistency management outside of DynamoDB as you propose doesn't circumvent the CAP theorem's limitations.
> Some unsuitable workloads for DynamoDB include:
> Services that require ad hoc query access. Though it’s possible to use external relational frameworks to implement entity relationships across DynamoDB tables, these are generally cumbersome.
> Online analytical processing (OLAP)/data warehouse implementations. These types of applications generally require distribution and the joining of fact and dimension tables that inherently provide a normalized (relational) view of your data.
> Binary large object (BLOB) storage. DynamoDB can store binary items up to 400 KB, but DynamoDB is not generally suited to storing documents or images. A better architectural pattern for this implementation is to store pointers to Amazon S3 objects in a DynamoDB table.