Comment by khurs
3 hours ago
"As the datasets become bigger and bigger,"
I think the better way is to use Postgresql for new data and routinely archive off older data to data warehouse type database, to keep the Postgres one small.
(Many companies also now use a RDBMS alongside either a KV database or document store in main app)
Which document store do you prefer? And do you use it via Postgres using a foreign wrapper?
Two most recent companies:
The first was using SQL Server alongside CouchDB, an older company. CRUD Data into SQL, documents into Couch. (used separately, document Url in Couch stored in SQL and brought in by front end)
The second, startup with funding, was using Azure SQL Server (and complaining of the cost) and using Azure Cosmo (Key Value NoSQL) alongside it. Same relationship, no link between the two, it's the front end that drew data from both.
Just look up the pro and cons of each database, some are insert only, some are distributed, some are faster read then write etc. LLM will give you a quick comparison.
Also: https://db-engines.com/en/ranking