I am using event sourcing with cqrs and that means i also utilise projections, which the db itself can replicate less efficiently as a "view" or virtual table.
So one can technically create a projection/view that is tailor-made for a query that needs to display some data. Of course it is no often possible to retrieve all the data with a single select command.
So joins and multiple queries are simply inherent to complexity of data we store nowadays.
Anyway, years ago, i have moved to a db model where every entity is stored as a blob in a dedicated column and every additional column, beside id, is indexed. So there is no wasted space and a ton of columns that only hold data but are not used for filtering. I can run data-efficient queries that yield a list of ids of blobs to load or the blobs themselves and then i extract any necessary data out of those blobs(entities) on the application level. So the database us purely a blob store + few fast indices.
I'm sure it does work well if the database is perfectly tailored to your app. I'm skeptical that it can handle every new use case that might come up -- i.e. if you need to query for something hidden in a blob. Sounds precisely like NoSQL. It works until it doesn't.
I use it on gethly.com and previously i used the same approach on an internal application for domain registrar that handles the EPP. So far no issues. Also using those blobs allows me to easily add encryption layer on application level.
I see: "Gethly is a paywalled hosting and sales platform for digital content creators.
Sell online courses, license downloadable content, receive donations or build communities accessible via paid memberships." Are all the online courses and downloadable content and analytics and payments in the CQRS system, or are you using it for only some subset?
The entire system is event-sourced. I wrote few articles about the technical aspect of the platform on the blog https://gethly.com/blog
At this time, there are 12 services that make up the entire application. Event-sourcing is what allows infinite scaling and CQRS.
ES is the pinnacle of technology in the web sector, but it comes with a lot of overhead and time to market is significantly slowed down. So it is not something every project can or should consider implementing.
So one can technically create a projection/view that is tailor-made for a query that needs to display some data. Of course it is no often possible to retrieve all the data with a single select command.
So joins and multiple queries are simply inherent to complexity of data we store nowadays.
Anyway, years ago, i have moved to a db model where every entity is stored as a blob in a dedicated column and every additional column, beside id, is indexed. So there is no wasted space and a ton of columns that only hold data but are not used for filtering. I can run data-efficient queries that yield a list of ids of blobs to load or the blobs themselves and then i extract any necessary data out of those blobs(entities) on the application level. So the database us purely a blob store + few fast indices.