"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)