My dream was having a database like AWS DynamoDB, but with analytical capabilities built-in the API.
If your workload fits the model well (mostly OLTP, but occasionally some with heavier aggregations, etc.), it would be awesome.
Example: TiDB
From an OLTP standpoint, the limitations to query the data are actually amazing, because it forces you to think about the right questions from the beginning.
On more flexible DBMS like Postgres, it's very very easy to shoot yourself in the foot.
I find it easier to understand and take into consideration the DynamoBD constraints (explicit) than SQL shortcomings (implicit).
For many apps thinking that you can have the right questions from the beginning is delusional.
Strong disagree, I think you might be approaching this from a traditional angle where analytics questions are also questions of the system. In DynamoDB you only focus on access patterns you need for system to work real-time, without bothering with analytics (as stated above). Any question you want to ask on data outside of the operational code is out of the scope.
The issue is the pairing.