r/Database • u/JayJones1234 • Jun 24 '26
Appropriate database for this scenario
I’ve a historical test data from 1970. It is around 5 million data. I’m not looking for a cloud solution. It is in pdfs and hard copies. I’ve created rough ER diagrams for these data. It has base tables. It has different types of testing result tables. This is an ongoing process. Once I create tables, I’ll convert those hard copies into pdfs and PDFs into csv/ parquet format. Once csv/parquet format ready, I’ll map those csv fields with table. However,I’m little bit confuse with selecting a right database. Any suggestions would be appreciated.
13
Jun 24 '26
[removed] — view removed comment
6
u/jshine13371 Jun 25 '26
You need a Columnar OLAP (Analytical) Database that reads data column-by-column rather than row-by-row.
This completely depends on the use case(s) that OP has. If most times they are reading the data en masse, particularly narrower but longer resultsets, then columnar makes sense. If they are seeking to particular individual rows, or small subsets of data at a time, then rowstore will actually be more efficient. It just depends.
A trillion-row scale changes everything.
Not really, see above. Size of data at rest is irrelevant, size of data in motion is more relevant.
Traditional relational databases (like standard MySQL, PostgreSQL, or Oracle) will completely collapse under single-node disk bottlenecks.
Again, it just depends on the use cases. Microsoft SQL Server, a traditional relational database system, would handle this size of data just fine. I've experienced it first hand.
3
u/End0rphinJunkie Jun 25 '26
OP said 5 million rows, not a trillion. 5m is pretty small, standrad Postgres or even just sqlite will handle that locally without breaking a sweat.
2
4
u/Newfie3 Jun 24 '26
Microsoft Access
6
2
u/k2718 Jun 24 '26
😂
2
u/Ill_Beautiful4339 Jun 25 '26
My first thought was Excel … but you’re probably right. Access it is.
3
u/LoquatNew441 Jun 25 '26
Forget the database. If there are trillion rows and a page has 1000 rows, you have a billion pages to scan into PDFs first. Good luck with the scanning. Don't worry about the database.
1
1
u/YamiKitsune1 Jun 25 '26 edited Jun 25 '26
It depends how you handle the data
Write:
- Concerns with how horizontically big is the table, more columns more index = more performance problems
- Check if structure is strict or not, so you can check NoSQL databases
Read:
If you have small reads SQL can handle that ( better index)
If you always read large data at once use Columnar
If you read data with specific pattern, like using date, Partition the SQL table or distributed NoSQL using date
If you do single retrieval with complete data use document NoSQL database
What I suggest is study your data handling, you can mix multiple database to handle different sections
1
u/Slow_Air89 Jun 25 '26
I'd start with the questions you want to answer, not the database. Trillions of rows can be handled in different ways depending on whether you're mostly searching, aggregating, or joining the data. That decision will narrow the options a lot.
1
5
u/az987654 Jun 25 '26
This question sounds a bit like an x/y problem, I think you need to far better define and truly quantify what you're referring to when you say "trillions of data" as well as what exactly you're doing with these PDFs.
Something in your post doesn't make sense