What lightweight database should I use for large CSV files on Windows 11?
Asked by
MellowPine42
I have a hobby project that collects statistics from several sources while I experiment with automating horse-racing predictions in PowerShell. The data is currently stored in CSV files, and the two main files are about 634 MB and 557 MB, with more than 500,000 rows overall. I upload the files to cloud storage when they change and archive them as compressed files each month, but I'm worried about eventually losing or mishandling the data. What database or storage format would be a good migration target on Windows 11—ideally something lightweight, reliable, and low-maintenance?
Answers
SQL Server Express is another reasonable choice and can ingest files of this size. It is free and uses the full SQL Server engine, but it has a database-size limit and involves more ongoing service and configuration than SQLite. Whichever option you choose, keep the original CSVs and make separate backups rather than relying on a single synchronized copy.
Reply to this answer
If you want something more scalable and are interested in learning server tools, PostgreSQL in a container is a solid option. It handles large datasets well and gives you a powerful query engine, but it’s probably more infrastructure than you need for a single-user hobby project. SQLite should be the simplest starting point, and you can move to PostgreSQL later if the project grows.
Reply to this answer
Several options would work, including SQL Server Express, PostgreSQL, MySQL, MariaDB, and SQLite. For a personal Windows project where you want minimal resource use and maintenance, I’d choose SQLite. The server-based databases are better if you need multiple clients, concurrent users, or more advanced database services, but they also require more setup and administration.
Reply to this answer
You might not need a traditional SQL database at all. Since this sounds primarily like historical data analysis rather than transactional work, Parquet is worth investigating. It stores data by column, is usually much more efficient than CSV for analytical queries, and can be a straightforward next step if your source data is JSON and you mainly need compact, queryable archives.
MellowPine42: I hadn’t considered Parquet before. Since I’m already converting JSON to CSV, I’ll compare it with SQLite before deciding.
Reply to this answer
SQLite is probably the best fit here. It’s lightweight, open source, serverless, portable, and doesn’t need a background service or a separate server. A database file can be copied and backed up easily, and it should handle files of this size without trouble. The PSSQLite PowerShell module can help with importing the CSV data, and a desktop tool such as SQLiteStudio can make the initial import easier.
MellowPine42: That portability is a strong selling point. I’ll take a look at PSSQLite and SQLiteStudio.
Reply to this answer
Add your answer
Answers are reviewed before publication.
MellowPine42: The extra maintenance makes SQLite more appealing for this project, but I’ll keep SQL Server Express in mind. I’ll also make sure the originals are backed up independently.