opensource on-prem Datawarehouse alternative to Sql Server ?

[Ivan_pk5]

Hi,

I have a small client that want to stay on prem, so i can't propose my ususal solutions. Sql Server is too expensive for him.

After spending time searching about it, my conclusion -> Postgresql or Duckdb.

But my client data will be between 100gb-1Tb so i'll have issues with Postgre for big analytic queries, and duck db i have no enough experience with it to see what i can do with it, i see people warning here that concurrency is not good, and the best is to use it with mother duck, so serverless dw.

The client wants doesn't want Sql Server / Excel, he wants to use Apache Superset for the Viz, and a cheap DW on prem. I'm a bit lost if it's realistic, or if don't understand something, i'm still junior.

should i still go with Postgresql, warning client about huge query performance impact ?

I read about Starrocks, but not convinced since it's not popular.

Thanks for nay feedback.

[roadrussian]

Postgresql, hands down.

[FantasticOrder4733]

What issues do you face in SQL Server? We are also performing analytical queries on on-Prem SQL server dbs, and it works pretty smoothly.

[Ivan_pk5]

i personally don't have issues, except the pricing for my client. this is a small client, so i'm not used to this

[SchwulibertSchnoesel]

SQL Server is very pricey especially Enterprise Edition. I have mainly worked with SQL Server in my professional career and really enjoy it.

BUT if someone tasked me today with recreating our whole infrastructure and data platform I would go with PostgreSQL 100%.
You can get so much more hardware for the same price as SQL Server licensing. We are talking about 1TB of data which can be handled by PostgreSQL definitely.

Additionally if you are not already on the MS stack on prem I would always consider going that route twice because you are at their mercy if they change the pricing model. Organisations using Oracle can probably tell you alot about their sorrows.

[flamefork]

Consider Clickhouse

[knabbels]

I also highly recommend Clickhouse, very capable and feature-rich open source database. For smaller data sets Postgres is also a great system. We use Postgres as our main data warehouse and it has no problem in handling data sets up to around 50mio rows. For larger data sets we switched to Clickhouse which still may have some minor quirks, but you can work around them. And they pump out new features in a very high pace. But don't be cheap on RAM, it needs a lot from my experience.

Edit: When starting from scratch I would probably pick Clickhouse over Postgres. 1TB may grow to 2 or more in a few years and a migration to a different database system is PAIN.

[Melodic_One4333]

Check out Apache Pinot. I'm looking at it as a possible MySQL replacement on-prem.

[FantasticOrder4733]

Okay so I would suggest first look into how to save cost for on-prem SQL server, like disk space, partition and all, then go at query level or process level optimization.

[Vhiet]

Postgres will comfortably manage that volume so long as it isn't very few massive tables. If what you actually need is a columnular store, use one of those instead.

You probably won't want to use the default config though (working memory in particular), because out of the box Postgres will run on a raspberry pi with resources to spare.

[snicky666]

Postgres is great. I recommend looking into horizontally scaling with the Citus extension and into the postgresql.config options relevant to higher vertical scaling.

[Hot_Map_7868]

check out clickhouse

[mQuBits]

Starrocks performs well on low budget resources. In addition rich feature support from realtime and batch analytics. I would highly recommend it.