Where possible go with simple but abstracted cloud storage, cloud tables and then a cloud db that is managed. We use Azure mostly right now but our storage system works across Azure storage, Amazon S3, Google Cloud and others. For tables, Azure Tables mainly. For database with filtering/paging better performant and ACID compliant cloud db, CosmosDB currently which is a dream with the differing apis (SQL, Mongo, Cassandra, Tables style). The more you can avoid vendor or dev-lockin the better so simple formats/messaging/routing and abstracted specifics/implementations.
When you store data in storage or a cloud db the scaling is "infinite" and you can also snapshot or backup to anther one, you never worry about data.
The front ends and APIs are mostly repos pushed to app/web services and everything else in data storage. Super simple and anywhere you need some special service that can be serverless or a dedicated setup, like maybe a RDBMS, chat server, network server or WebRTC/socket endpoint that interacts with the simple side. These managed as well if possible, though not always. Additionally, build cheap and horizontal scaling on web/real-time servers. Vertical scaling and sharding is for suckers.
Side note: CosmosDB is like a combination of NoSQL, document databases and GraphQL and it is ACID compliant and you can do REST or SQL, it can even wrap MongoDB and Cassandra and make them ACID compliant. It really feels like the best way. Not many have all that and ACID compliant. Not even Amazon Redshift has that, DynamoDB does if specified. Google Firestore does if specified. I used to be big on RDMBS Oracle then MSSQL then PostgreSQL and those are great for backing/reporting etc but CosmosDB combines all the power of RDMBS, NoSQL, document databases, and ACID compliant with little worry about scale. It is vendor lockin to Azure, which you can route around with platform abstraction, but currently can't be beat. As you got that clean API layer you could change later but best way is limited/clean and if possible, non breaking change API layers/signatures.
Really do not share your experience with CosmosDB, some of it's attributes that currently make me miserable include...
- Scaling is not infinite, it's up to 20gb per partition key (1), which can't be changed after document creation.
- One set of global indices, no equivalent to DynamoDB's secondary indices.
- Still can't run their docker container on mac (2) natively.
- Weird SQL-like dialect that's required for all but the simplest queries. JOINs are spectacularly awkward.
- Tooling is horrific. Based on the previous point, no existing tooling works for it (and nobody is building tooling for a DB with such minimal market share). For example, I needed to manually deleted 30 or so documents/rows yesterday - only way to achieve this is with 30 separate click-to-deletes in their UI.
- Minimises analytics options. There exist a plethora of business intelligence type tools that will happily sit on top of most common DBs. None of them like CosmosDB. So you're stuck with synapse link or whatever MS calls it now.
Overall it seems to combine the worst aspects of both RDS and document stores, with the worst aspects of both traditional and serverless infrastructure.
The partition key limitation is something you can work out with smart partitioning and horizontal scaling. We do the same already with storage/tables to prevent large data blocks at a smaller limit even for speed/lookups/map.
Tooling could be improved and will be, it is fairly new still and the Azure Cosmos DB Emulator is not bad.
There is a CosmosDB Synapse setup that allows more analytics/intel on top like you said but same with other NoSQL, takes a bit to get worked in.
I actually like the flexibility of query types and that they include SQL as it makes it a bit more standard and somewhat less vendor lockin. You can use other types as well Mongo/Cassandra/Tables syntax. For filtering the SQL side isn't bad but most of what we do is flat/associative and not heavily normalized. For most of our data we are very cache heavy as well to reduce db hits and retries.
ACID compliance is huge and there are some design considerations.
Managed and no need to backup with snapshots. PG still has manage concerns with size, logs, access, scale etc. Nothing you have to do with cloud storage. I do love me some PostgreSQL but use it less and less except for reporting or heavily filtered needs. Horizontal over vertical for comfort and simplicity.
Where possible go with simple but abstracted cloud storage, cloud tables and then a cloud db that is managed. We use Azure mostly right now but our storage system works across Azure storage, Amazon S3, Google Cloud and others. For tables, Azure Tables mainly. For database with filtering/paging better performant and ACID compliant cloud db, CosmosDB currently which is a dream with the differing apis (SQL, Mongo, Cassandra, Tables style). The more you can avoid vendor or dev-lockin the better so simple formats/messaging/routing and abstracted specifics/implementations.
When you store data in storage or a cloud db the scaling is "infinite" and you can also snapshot or backup to anther one, you never worry about data.
The front ends and APIs are mostly repos pushed to app/web services and everything else in data storage. Super simple and anywhere you need some special service that can be serverless or a dedicated setup, like maybe a RDBMS, chat server, network server or WebRTC/socket endpoint that interacts with the simple side. These managed as well if possible, though not always. Additionally, build cheap and horizontal scaling on web/real-time servers. Vertical scaling and sharding is for suckers.
Side note: CosmosDB is like a combination of NoSQL, document databases and GraphQL and it is ACID compliant and you can do REST or SQL, it can even wrap MongoDB and Cassandra and make them ACID compliant. It really feels like the best way. Not many have all that and ACID compliant. Not even Amazon Redshift has that, DynamoDB does if specified. Google Firestore does if specified. I used to be big on RDMBS Oracle then MSSQL then PostgreSQL and those are great for backing/reporting etc but CosmosDB combines all the power of RDMBS, NoSQL, document databases, and ACID compliant with little worry about scale. It is vendor lockin to Azure, which you can route around with platform abstraction, but currently can't be beat. As you got that clean API layer you could change later but best way is limited/clean and if possible, non breaking change API layers/signatures.