SQL vs NoSQL: Fetching All records in DynamoDB vs SQL Database I am designing database for a project which requires frequent querying of all the records. The total number of records will be less than 500K. One of the databases I am considering is AWS DynamoDB where scan operation meant for this is very inefficient. What I fail to understand is whether it is inefficient in a SQL based database or there is one better than other for frequent calls for fetching all records.
Scan operations aren't usually optimal but 500,000 records isn't something too crazy for the standard RDBMS. For example, I've seen Microsoft SQL Server serve up 1 million records on a decently wide table (about 100 columns) in a few seconds on a pretty basic server (4 core, 32 GB of RAM).
I'm not an expert on NoSQL databases or DynamoDB but I've researched into its use cases in the past and my understanding is it's most performant when caching smaller amounts of data, and recalling smaller &quot;singletonish&quot; datasets, such as paging product information on a catalog, or a specific user's settings. So I would say NoSQL databases (or at least DynamoDB) is likely not great for scanning large amounts of data, but a modern RDBMS should be able to handle scanning a few million records on basic architecture these days.