3 ms·

No benchmark = no download. Plenty of these SQLite web front ends exist, including more enticing single static binaries in go, and they are always far too slow

by geenat 3y ago

No benchmark = no download. Plenty of these SQLite web front ends exist, including more enticing single static binaries in go, and they are always far too slow to use in anything but toy projects. Should be standard to bench against a standard SQLite integration (on modern cpu with nvme). Even PocketBase, for as nice as the UI is, an order of magnitude slower than SQLite directly. Here's some rough numbers I've found using SELECT * FROM user LIMIT 1; per second ( 1x / 10x / 100x ) SQLite 68,000 / 50,000 / 4,000 SQLite WAL2 (3x Database files) 67,000 / 52,000 / 8,000 Pocketbase (CURL) 15,000 / 2,300 / 234 Pocketbase (Direct) 62,000 / 29,000 / 3,900 ws4sqlite (CURL) 20,000 / 2,600 / 255

geenat 3y ago
Btw ws4sqlite rewrite in rust that's allegedly faster: https://github.com/proofrock/sqliterg https://github.com/proofrock/sqliterg
- geenat 3y ago
  Ran the same test, same server (Ubuntu 22.04 + 32 thread 7950X + Gen 4 NVME + 64GB DDR5) -- nearly zero difference sqliterg vs ws4sqlite. sqliterg (CURL) 20,000 / 2,500 / 237 For those wondering about write performance...its poor except for direct SQLite. If you're write heavy, look elsewhere, or use SQLite directly (preferably with multiple databases and WAL2). INSERT INTO users (id) VALUES (..); per second ( 1x / 10x / 100x ) SQLite WAL 11,000 / 3,000 / 300 SQLite WAL2 14,500 / 3,000 / 760 SQLite WAL2 (3x Database files) 29,000 / 6,000 / 1,400 sqliterg (CURL) 1,750 / 181 / blocks indefinitely
jddj 3y ago
Curious to see how much it is a factor, did you run the same with/without cgo?
f731d3a6-1e22 3y ago
Go calls to C are slow https://vancan1ty.com/blog/post/52 https://vancan1ty.com/blog/post/52
sesm 3y ago
I think this one is more for development or test endpoints. So installation from NPM is big plus, so that I can add it to devDependencies, have sources in node_modules and patch them with patch-package.
randomwebdev 3y ago
Hi, PocketBase author here. Keep in mind that PocketBase do a lot more than just executing a raw DB query. We perform data validation, normalization, serialization, enriching, auto fail-retry to handle additional SQLITE_BUSY errors, etc. All of this comes with some cost and will always have an effect when doing microbenchmarks like this. The performance would also depend on what version of PocketBase did you try (before or after v0.10), whether you used CGO or the pure Go driver, etc. For a benchmark closer to "real world" scenarios tested on various servers you can check the results from https://github.com/pocketbase/benchmarks https://github.com/pocketbase/benchmarks. There is definitely room for improvements (I haven't done any detailed profiling yet) but the current performance is "good enough" for the purposes the applications PocketBase is intended for (I've shared some numbers regarding a PocketBase app on production in https://github.com/pocketbase/pocketbase/discussions/4254 https://github.com/pocketbase/pocketbase/discussions/4254). Hope the above helps.
- b-lee 3y ago
  Man, thanks for building PocketBase. It's very pleasant to use!
diggan 3y ago
> and they are always far too slow to use in anything but toy projects Or, not every project need the absolute best performance, sometimes good enough is simply good enough?
mattgreenrocks 3y ago
I've had similar ideas recently. If I have an SPA querying a moderately-sized static dataset, then something like this is appealing. I can stand up a container on Fly, store the SQLite DB on the volume, and serve directly from there without needing to worry about # of reads I involve (w/ something like Turso) or all the ops complexity of a real DB.
runlaszlorun 3y ago
> No benchmark = no download. Perhaps it's because I'm particularly snarky pre-morning coffee, but this doesn't come across as the welcoming attitude open source is supposed to be and comes across as downright entitled. Especially as the first thing to lead with. Maybe you didn't quite mean it that way? I'm taking a wild guess to say that you don't have a screaming need to run 'SELECT * FROM user LIMIT 1;' tens of thousands of times per second on SQLite. And if you did, I'm guessing you prob would be able to write your own API in front of it. Am I wrong here? Kudos to the authors of this tool. It looks great and I'd love to try it out on a recent project.
- geenat 3y ago
  Who really wants to build on top of something just to hit an immediate performance cliff? How far you can vertically scale on one server before requiring a split or shard in your data is why it's a big deal. A) App servers are easy to scale (stateless, add hardware). B) Database servers are hard to scale (requires changing logic). If you're immediately hitting B) you're probably screwed.

by geenat 3y ago

geenat 3y ago

Btw ws4sqlite rewrite in rust that's allegedly faster: https://github.com/proofrock/sqliterg https://github.com/proofrock/sqliterg

geenat 3y ago
Ran the same test, same server (Ubuntu 22.04 + 32 thread 7950X + Gen 4 NVME + 64GB DDR5) -- nearly zero difference sqliterg vs ws4sqlite. sqliterg (CURL) 20,000 / 2,500 / 237 For those wondering about write performance...its poor except for direct SQLite. If you're write heavy, look elsewhere, or use SQLite directly (preferably with multiple databases and WAL2). INSERT INTO users (id) VALUES (..); per second ( 1x / 10x / 100x ) SQLite WAL 11,000 / 3,000 / 300 SQLite WAL2 14,500 / 3,000 / 760 SQLite WAL2 (3x Database files) 29,000 / 6,000 / 1,400 sqliterg (CURL) 1,750 / 181 / blocks indefinitely

jddj 3y ago

Curious to see how much it is a factor, did you run the same with/without cgo?

f731d3a6-1e22 3y ago

Go calls to C are slow https://vancan1ty.com/blog/post/52 https://vancan1ty.com/blog/post/52

sesm 3y ago

I think this one is more for development or test endpoints. So installation from NPM is big plus, so that I can add it to devDependencies, have sources in node_modules and patch them with patch-package.

randomwebdev 3y ago

Hi, PocketBase author here. Keep in mind that PocketBase do a lot more than just executing a raw DB query. We perform data validation, normalization, serialization, enriching, auto fail-retry to handle additional SQLITE_BUSY errors, etc. All of this comes with some cost and will always have an effect when doing microbenchmarks like this. The performance would also depend on what version of PocketBase did you try (before or after v0.10), whether you used CGO or the pure Go driver, etc. For a benchmark closer to "real world" scenarios tested on various servers you can check the results from https://github.com/pocketbase/benchmarks https://github.com/pocketbase/benchmarks. There is definitely room for improvements (I haven't done any detailed profiling yet) but the current performance is "good enough" for the purposes the applications PocketBase is intended for (I've shared some numbers regarding a PocketBase app on production in https://github.com/pocketbase/pocketbase/discussions/4254 https://github.com/pocketbase/pocketbase/discussions/4254). Hope the above helps.

b-lee 3y ago
Man, thanks for building PocketBase. It's very pleasant to use!

diggan 3y ago

> and they are always far too slow to use in anything but toy projects Or, not every project need the absolute best performance, sometimes good enough is simply good enough?

mattgreenrocks 3y ago

I've had similar ideas recently. If I have an SPA querying a moderately-sized static dataset, then something like this is appealing. I can stand up a container on Fly, store the SQLite DB on the volume, and serve directly from there without needing to worry about # of reads I involve (w/ something like Turso) or all the ops complexity of a real DB.

runlaszlorun 3y ago

> No benchmark = no download. Perhaps it's because I'm particularly snarky pre-morning coffee, but this doesn't come across as the welcoming attitude open source is supposed to be and comes across as downright entitled. Especially as the first thing to lead with. Maybe you didn't quite mean it that way? I'm taking a wild guess to say that you don't have a screaming need to run 'SELECT * FROM user LIMIT 1;' tens of thousands of times per second on SQLite. And if you did, I'm guessing you prob would be able to write your own API in front of it. Am I wrong here? Kudos to the authors of this tool. It looks great and I'd love to try it out on a recent project.

geenat 3y ago
Who really wants to build on top of something just to hit an immediate performance cliff? How far you can vertically scale on one server before requiring a split or shard in your data is why it's a big deal. A) App servers are easy to scale (stateless, add hardware). B) Database servers are hard to scale (requires changing logic). If you're immediately hitting B) you're probably screwed.