Finding the right database software

#1

Hello I’m thinking of buying the 16gb variant of rocks 5b.
I’m primarily going to be using it as a database for personal queries. I’m new to databases and I’m currently researching many options.

I would have 100s of millions of objects stored in the following format: {a:12345678, b:123456789000, c:123, d: 23, e: true} . I will be querying from the entire db something like this : query all the c values where b is between 123456789000 to 123456899000. Or query everything where b is between 123456789000 to 123456899000.

I won’t be doing any database manipulation. Only db reads.

I would be querying 100,000 to a million objects at a time. Query frequency would probably max out at 2-5 queries per second. But on average, probably 1 query per second. I read that sqlite / sqlite3 are quite lightweight so I would like to test it out. If you have any other suggestions, please add here. And finally will the sbc be powerful enough to handle these requests ?

#2

It should be able to handle it, but I would question the EMMC speed. Maybe if database was stored on M.2 nvme drive it would be ok. After a while the EMMC speeds drop significantly.

#3

Thanks for your reply. Yeah I plan on getting nvme m2 drive. So do you think the queries will be done in sub milliseconds no problem?

#4

I am not knowledgeble in any way to suggest/say anything but if you could share your findings, that would be of immense help to the community. thanks in advance.

#5

from what you’d mentioned, just make sure you create proper index over the column you will put in the where clause, and it should be fine. no “database manipulation” means no index update so you only need to do it once.

#6

How would I know the index number when querying? I would be querying the object where b is between x and y. So put index over column b?
Are you saying that the index numbers will be used internally by sqlite? Cause how would I know the index number? Also do you think sub ms queries will be possible with rock pi 5? Or will it the query be backlogged if I do like 5 queries per second? Thanks

#7

I will once I buy one.

#8

You should learn basic database operations in this case, can’t explain much in details here.

[I would be querying the object where b is between x and y. So put index over column b] => yes
[sub ms queries] => even on a regular server it is hard to achieve, since normally I/O are in ms range; in this case maybe you should consider in-memory DB, if your data can fit into memory completely.
[query be backlogged if I do like 5 queries per second] = > since your DB is read only, it should not be an issue at all. just make sure you open a connection read-only, which should give hint to the database engine