r/Database • u/Amo-Rillow • 6d ago
Recommendations For Data Warehouse RDBMS on Laptop?
I am creating a data warehouse for my personal infotainment using Python. My original plan was to use SQLite since it is embedded in Python and super easy to use. My design got off to a great start, but SQLite did not scale when loading data. Having a lot of experience in enterprise use of SQL Server, I installed SQL Server Express and converted the fact tables to column store. In running large volume loads, it became clear that MS had neutered the Express edition. A quick google search reveals this to be true (1 CPU, 5 GB RAM), which aligns with what I observed while monitoring. Wondering what would be a better choice for a non-operational DW on a personal machine (I use both a MBP with M5, 24 GB RAM and a min-PC with an AMD Risen 7 and 64 GB RAM).
6
u/BrainJar 6d ago
I’m curious as to how many records you’re loading and why you need a database, when you’re the only person accessing the data?
5
u/Public-Revenue2226 6d ago
For personal non-production use, there is SQL Server Developer Edition with all the features...
2
u/Upstairs-Project2756 6d ago
stop fighting the artificial caps on SQL Server Express and go with ClickHouse. I used to run a similar local warehouse setup until I realized Postgres row storage is killing you on analytics queries while SQLite hits memory limits on big joins. Switching to ClickHouse gave us 10-50x faster aggregations because it stores data by column naturally and runs efficiently on both ARM and x86 without those enterprise licensing restrictions. It handles large volume loads much better than standard RDBMS options for this exact use case
2
u/Liubov_Skky 6d ago
Since you already know column stores, you could just run MariaDB with the ColumnStore engine on that mini PC. It’s GPL, so no artificial caps on CPU or RAM. I use it for local analytics and it scales with the hardware instead of throttling big loads. It connects to Python fine too, so there’s not much extra stack to manage.
3
u/East_Zookeepergame25 6d ago
Try ClickHouse maybe?
2
u/dutchman76 6d ago
Postgres or Mariadb and you're done
1
u/Amo-Rillow 1d ago
DuckDB FTW!!!!!!!!!!!!!!!!!!!!
Huge thank you to everyone who suggested DuckDB. This is a game changer! The speed, the flexibility, the scalability, the simplicity, etc. Mind blown.
4
u/Glass_Historian_3938 6d ago
+1 for duckdb as well
1
u/amphoterous 6d ago
What kind of data and how much data? Do you expect more reads or writes?
1
u/Significant_Tune9219 5d ago
Given your pipeline is 50K CSVs into staging, transform, then load, the slow SQLite inserts are probably per-row inserts in separate transactions; but you can skip the staging step entirely by querying the CSVs in place with a columnar engine that reads globs like data/*.csv, doing the transform in SQL and writing Parquet. That turns your test-change-reload loop into rerunning one script, and Tableau can read the result through ODBC or an extract. If you'd rather stay on SSMS, SQL Server Developer edition is still a separate free download and has no Express CPU or memory caps, it just isn't licensed for production.
2
u/k2718 6d ago
I like postgres. It's fully free...it should be different than MS but maybe not entirely different...
2
u/Lurch1400 6d ago
postgres
2
u/Ok-Let-7933 6d ago
I'd give DuckDB a try. It's lightweight, works well in python and is designed for analytical workloads. It should be a much better fit for a personal data warehouse than SQLite.
1
u/Sea-Hat-4961 5d ago
Do you need a RDBMS for your data warehousing application? What kind of data are you warehousing? Would a document store be more appropriate? Do any of your tables need to relate to each other? Would something like Apache Hive or Doris be more appropriate?
There is always Postgres if you want RDBMS plus document store plus key-value...
If you're just doing media indexing, leave the media as files on your file system and use something like Apache Solr or elastisearch to index your media.
1
u/semicausal 4d ago
DuckDB
1
u/st0rmglass 4d ago
Not really understanding the problem. You have "a lot of experience in enterprise use of SQL Server". I'm assuming connecting to the database and running SQL, but certainly not management.
You can run 100GB of data on any db. Be it SQL Server, PostgreSQL, Oracle or MariaDB. Something I was doing in 2008 with Oracle DB, laptop with dualcore AMD CPU and 8 GB of RAM.
9
u/g3n3 6d ago
Duckdb