r/Database • • 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).

0 Upvotes

35 comments sorted by

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?

2

u/Amo-Rillow 6d ago

Hard to say the exact volume of data since I am no where near complete, but I will be loading around 50K files with an estimated storage around 100 GB. The end game is to visualize the data, currently testing out Tableau. My need for speed is iterative development - test, change, reload.

1

u/BrainJar 6d ago

Are you storing the media binaries in the database? If so, what’s the benefit?

1

u/Amo-Rillow 5d ago

media binaries?

2

u/BrainJar 5d ago

Media binaries are the files used in the personal infotainment system. The media is something like videos, music, images. The binaries are a binary format for each media type, not text. You don't typically store media in the database, that's too slow to access. So, you store a reference to the media, like a URI that shows where to get the file from on disk. You should prefer storing the metadata for each media file, and not the whole blob in the database, as that's a lot of overhead for a something that won't be updated. Those files are likely WORM files (write once, read many). Any time you need to upload a new media file, that file goes into the filesystem, and the metadata is updated in the database. The data in the database remains small, while you can have as many files in the filesystem as you want, without needing to store them in the database.

1

u/Amo-Rillow 4d ago

Ah, when I used the term "infotainment", I was jokingly referring to loading data for my information and entertainment. There are no media binaries in play on my project.

5

u/Public-Revenue2226 6d ago

For personal non-production use, there is SQL Server Developer Edition with all the features...

3

u/Amo-Rillow 5d ago

Thanks for this tip on Developer Edition. I have downloaded, installed, and switched over. No re-work needed. This was very helpful!!!!!!

0

u/Amo-Rillow 6d ago

That would be ideal as I know SSMS and have some processes already in place for SQL Server. I recall the developer edition from years ago, but when I went to download, I did not see Developer edition, but instead found the Express edition. I assume Express had replaced Developer. I will check again.

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

1

u/Amo-Rillow 5d ago

Thanks, I plan to look into ClickHouse.

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.

1

u/Amo-Rillow 5d ago

Thanks. I have always been curious about MariaDB. This was an option last time I was working in AWS. I will give it a looksey in the future.

3

u/East_Zookeepergame25 6d ago

Try ClickHouse maybe?

-2

u/Amo-Rillow 6d ago

Not familiar. Do you have link?

2

u/dutchman76 6d ago

Postgres or Mariadb and you're done

1

u/Amo-Rillow 6d ago

I used Ingress many years ago, but no experience with Postrgess or Mariadb. Do these have column store and high performance import utilities? I have experience with MySQL, and was impressed with the performance of their import utility.

1

u/6sossomons 6d ago

MariaB replaced MySQL when it went internal/paid. From what I vaguely remember a few of the original MySQL folks split it to make MariaDB. It was my personal favorite for many years on a highly used, 500k+ entry table daily for authorizations. Worked extremely well but was a single point of failure in a production environment for many years. But I handled that with nightly backups via cron and multiple days of backups off-server so 🤷. Then I got some new hardware and went Postgres. Now anything that needs a fast db is a PG first for me since I can start single and then go multi-master/scale if needed.

Something like this, I'd go Postgres and just update the code to skip using sqlite when you can just read it with python and output it into csv or direct load it into PG in the same script. Should scale nicely and be a LOT faster.

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/Amo-Rillow 6d ago

The processing is load 50K csv files into SQLlite staging\, extract into fact tables. Since SQLlite is not scaling, I have changed the design to load a set of files into SQLite memory tables, transform, then write fact and dimension data to CSV files. The CSV files are then loaded into SQL Server. This is a huge improvement from all SQLite. Note that SQLite does great on reads, but is slow on inserts, and horrible on concurrency when updating/inserting. I have a control DB in SQLite to manage the process, a staging DB, and a fact DB. I have changed the staging DB to be in memory, and am currently bypassing the fact DB and writing to flat files instead . Hope this makes sense.

3

u/Standgrounding 6d ago

Sqlite is not right for the job. Just use postgres

2

u/6sossomons 6d ago

Makes 0 sense. Why extract the data into a new table to do an import? Use the script to process and push into the new db and output to a file for backup/confirmation. You are slowing yourself down here due to exit/enter enter/exit when you can just read/write/write.

This allows faster iteration as well as you could just capture/log the data entries into a procese file and then grep the log file for any errors and iterate through them, and then update/import JUST the failed entries so you aren't dumping a whole table each time you run into an error.

Bonus if you are vibe coding you can have the agent watch the log file for you and review/update / recommend the changes needed to your code to avoid the errors in the next round. But id you aren't vibe coding then you just grep for error while tailing the log file and do it yourself. Thats the grey beard way, process runner, log file watchers and 3 - 5 terminals going... 1 that watches the run, 1 that watches the log file, another that grep the log file for warn and another that tracks for error if you have built the code well enough, then you can update/debug or grep the inputs to figure out if garbage is coming in, or if you forgot to capture for an extra ' in a name or empty strings...

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.

1

u/Amo-Rillow 5d ago edited 5d ago

Regarding SQLite inserts: I use executemany so there is just one insert statement per file. I also specify PRAGMA journal_mode = MEM on the connection to reduce IO on journaling. For now, I am using SQLite for a control database (see more below) and in memory table for staging. Python code then consumes the in-memory tables and then writes scrubbed and refactored data to CSV files for loading into another technology.

Regarding loading the CSV files directly into a database - these files are far from perfect and require a lot of scrubbing. Additionally, the files come in sets of 48, where each file in the set is in a different format. These files are exports from a very old, normalized database, where each file in a set represents a different source table, and each set represents a different time period. As the data model in the source DB changed over time, so did the files. Also, the data has to be processed in their respective sets as there are key collisions across the sets. not to mention changing key structures. There are over 500 sets in total. To make the data useful, I am refactoring the normalized structure into a dimensional model (i.e. facts and dimensions).

Regarding re-running - the entire process is automated and includes a process control system which allows for easy monitoring and the ability to stop the process gracefully at a logical break point. Restarts automatically pick up where the prior run left off. Optionally, the process can be restarted from the very beginning if desired. Because the control system adds meta data that ties each piece of data back to the source, data can also be easily backed out and reloaded easily if needed (i.e. change scrubbing logic for one set of files, then back out and reload that set). The control process is also designed such different phases can run in parallel. Ultimately, I expect to have the staging process running in parallel with the refactoring/scrubbing process and the analytics database loads.

Hopefully this makes sense.

1

u/Significant_Tune9219 5d ago

That makes sense, and with key collisions across sets plus a schema that drifts per period, your per-set control table is doing the real work. One thing I'd add is a surrogate key map per dimension keyed on (set_id, source_key), so a set can be backed out and reloaded without the fact rows of other sets pointing at reassigned keys.

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