This is not about the detailed comparison between SQL and NOSQL. There are a lot of articles online already regarding this topic.
This is about my experience with SQL and NOSQL.
I primarily used Oracle, Mysql and some DB2. And I am mainly using Aurora SQL database and DynamoDB NOSQL nowadays.
What I learned:
Even though a lot of online talks mentioned using one single table approach with DynamoDB for microservices, it is not easy to do, especially if a microservice has relatively complex domain and goes through a lot of changes.
DynamoDB case insensitive search is not straightforward; it takes extra effort compared to SQL. When data needs to be encrypted and searched, be careful about the data management (for example, use lower case, use hash).
DynamoDB throttling can be a pain depending on how you manage it, or whether your company is willing to pay more to avoid throttling.
DynamoDB global secondary indexes could become expensive if you have too many.
Some DynamoDB tricks mentioned online, for example, using some fake hashkey to enforce unique key constraints, using the same column to store many different types of data, could become a nightmare for application maintenance. Not many developers will be able to understand the code easily.
DynamoDB transaction apis could provide great value to developers that come from the SQL world. But the transaction APIs are more expensive and relatively slow.
Even with DynamoDB, you may still need to manage some kind of relationships between entities, which basically is similar to what you would do with SQL DB.
DynamoDB is really a dummy map. Some things you normally can do with SQL DB, for example, create timestamp, SEQUENCE, becomes a burden in application code.
DynamoDB stream is actually a great feature. But when integrating with lambdas, some duplication processing could happen in some cases.
SQL database is not bad when it comes to handling large volume. Facebook mysql use is a good example. It takes good design, tuning, and maybe even customization.
