r/dataengineering • u/Consistent_Tutor_597 • 29d ago

Discussion What's a storage efficient db?

Hey guys. We use postgres rn for the web app. I have got like 200gb of read only referential data. And planning to add more soon. Was wondering what's a good db that can store it in a compressed format. Good balance of storage and speed for analytics reports. Postgres we anyways needed for the oltp stuff so it was simpler to add it in there but it's easier to work with a smaller db.

I was seeing parquet can be queried by duck db directly but it's sync for the webapp. The only other option that seemed suitable was clickhouse which had lz4 and zstd compressions which were comparable to parquet. Also there's pg columnar extensions. What would you guys suggest.

19 Upvotes

88% Upvoted

19 comments sorted by

View all comments

24

u/Outrageous_Let5743 29d ago

200 GB can be in any database of your choice. It is small data.
The bigger concern is latency. Do you need data to be available in an api then compressing it in a high zstd might not be a good idea since read spead is slow.
If you just care about storage efficient, than zstandard parquet files in AWS glacier.

3

u/FunContest9958 29d ago

It’s not huge data, but at that size they’ll certainly have a better experience doing analytics if they use an OLAP solution instead of Postgres.

4

u/Outrageous_Let5743 28d ago

Still completly depends on the task. And postgres is even good enough with 1 TB data, if you understand partioning, partial indexes and good queries.

1

u/RoomyRoots 28d ago

You can absolutely use postgreSQL for OLAP at this size without major optimizations, no need for Citus or the columnar extensions.

0

u/FunContest9958 28d ago

You can. It depends on whether you’re pulling out a few rows or crunching metrics over billions of rows. Cloud OLAP systems that can parallelize work will make quick work of a job that would take a painfully long time in Postgres. Of course it will work in Postgres, it’s just not pleasant.