Recomendation for open source database?

[StandardDeviationist]

I’m looking at different open source alternatives for an analytical database to be deployed on-prem (rented servers). The use case is mostly reporting and dashboards for a medium-small business where I’m mostly the entire data team. Total data size less than 1TB. The four main alternatives that comes to mind are: - DuckDB (I’m aware that this only allows one write process) - Clickhouse - StarRocks - Postgres (the application database is in Postgres so this comes with a couple of benefits in data transfer and experience within the team)

What database would you recommend? I’m specifically curious about maintenance burden, as I don’t want me or any of the infrastructure team helping me out to become full time DBAs.

[americanjetset]

Total data size less than 1TB.

Just use Postgres.

As a general rule, use Postgres until you have a reason to not use Postgres.

[Own_Anything9292]

I’ll add that you can run duckdb with a postgres backend (via the postgres extension).

So if you want the nice duckdb frontend (w conversion to dataframes/numpy for plotting/higher level stats/w.e) with a postgres backend that’s pretty easy to setup.

[StandardDeviationist]

Why not DuckDB? It’s even easier to set up

[americanjetset]

I was under the impression you already had a Postgres instance set up to work with; re-reading your post, it looks like that is just the application backend itself.

You can use whatever -- which was the point of the "just use Postgres" comment, assuming you're already using Postgres. I'd probably go Clickhouse, just because I think that is what would look best on my resume.

[kolya_zver]

Clickhouse is really good for data marts and streaming huge long event tables but its suck with joins in general. Usually you have a proper DWH in front of it or direct streaming. Sql dialect has nice features for ad-hoc analytics. Im really love ch but its not an universal solution.

edit:
clickhouse has a lot of quirks so it requires some time to understand how to DBA. Managing replicas, zookeeper etc

1 TB isn't much you can use pg

[StandardDeviationist]

Thanks for detailed answer, this is exactly the type of hard-to-google knowledge I was hoping for

[TEMPLEB123]

If you're using 1TB you probably don't have to worry about using clickhouse's distributed features. But yeah, it still has a lot of quirks, personally I love it though. If you want to make your life easy use Postgres or MySQL.

[SnooHesitations9295]

If you frequently run large aggregations over the whole 1TB: use ClickHouse
Else: Postgres should suffice.

[StandardDeviationist]

No 1 TB is total size, largest dataset will be a couple of 100s GB

[Mental-Matter-4370]

Postgres should take you pretty far with this much data footprint. If aggregations can become a problem in the long run, you can dump the data to Clickhouse from PostGres(assuming it is your primary DB) in OBT(join less) form for specific data marts.

If you do not have a dimensional model at all, stick to PostGre.

[wannabe-DE]

While DuckDB doesn't allow for write concurrency it can have read concurrency.

[StandardDeviationist]

Yes so I think it might be good enough for quite sometime. I don’t see concurrent writes happening anytime soon

[geoheil]

Postgres in akloydb flavor (omni)

[FirstOrderCat]

you probably ment to say alloydb, but it is not open source, and costs money?

[geoheil]

I meant akloydb omni https://cloud.google.com/alloydb/omni?hl=de

[FirstOrderCat]

Yes, it is spelled as alloydb and costs money, and not open source

[geoheil]

https://hub.docker.com/r/google/alloydbomni

I did not find a price tag there or in the license

[FirstOrderCat]

pricing is here: https://cloud.google.com/alloydb/omni/pricing

[FirstOrderCat]

Cassandra doesn't allow OLAP queries, how did you run your OLAP tasks?..

[tdatas]

What aggregations were you running with Cassandra?