Re:Many small queries are efficient in SQLite
by elAhmo on Tuesday February 23, 2021 @04:46PM
(#26243327)
I stumbled upon a similar issue last week.
I was building a small Rails project that has mostly reads, but here and there it has inserts that can have 1000+ entries with related models. Things seem fine when clicking around, but when I used jMeter to test what is the capability of the server, I found it terrible with simultaneous requests.
Adding two workers made things even worse, and this is with ~10% writes and the remaining going to reads.
I quickly changed the db to postgres for comparison purposes, and from 3-4 requests per second with sqlite, it jumped to 60-70 as it did scale linearly with the number of workers.
I am by no means expert in database optimisations, but out of the box this behaviour was somewhat limiting the usability of sqlite for a webapp.
I was building a small Rails project that has mostly reads, but here and there it has inserts that can have 1000+ entries with related models. Things seem fine when clicking around, but when I used jMeter to test what is the capability of the server, I found it terrible with simultaneous requests.
Adding two workers made things even worse, and this is with ~10% writes and the remaining going to reads.
I quickly changed the db to postgres for comparison purposes, and from 3-4 requests per second with sqlite, it jumped to 60-70 as it did scale linearly with the number of workers.
I am by no means expert in database optimisations, but out of the box this behaviour was somewhat limiting the usability of sqlite for a webapp.