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.
3 Answers
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 "singletonish" 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.
Yes, scanning 500k rows (depending on row size) is usually a bad idea in the NoSQL world. This is because NoSQL DBs (which may vary widely by product) are typically designed to have their data spread across multiple (commodity) instances. Therefore a table scan would end up reading from multiple (or all) instances, adding network time to query execution.
For what you're talking about a RDBMS (like PostgreSQL or SQL Server) should do just fine.
One thing to mention for DynamoDB scan, the data size and number of attributes also matters. You need some optimization on your record if scan is frequent, no matter what database you are choosing.
But yes it is not very fast to scan 500k rows overall, AWS has a benchmark that you can use to compare with SQL scans.
dd.