5 ms·
> It’s borderline impossible to compare it against networked database management systems like MySQL or Postgres, because SQLite is a library that operates on a
by runeks 3y ago
> It’s borderline impossible to compare it against networked database management systems like MySQL or Postgres, because SQLite is a library that operates on a local file — it bypasses all the costs incurred by the network, layers of serialization and deserialization, authentication, authorization, and more.
Postgres can run locally, communicating via a Unix socket. You should try benchmarking this before stating that it's so much slower than SQLite.
- mschuster91 3y agoNo matter what, PostgreSQL can't (trivially) run in-memory only, and even Unix sockets has a performance cost compared to a library running in the same address space.
- charcircuit 3y agoIt should be trivial to set it up on a ramdisk and I see some guides on doing so when I search for "postgres ramdisk"
- mschuster91 3y agoPHP SQLite in-memory effort is `$db = new PDO('sqlite::memory');`. With postgreSQL, you'll at least have to: 1. create the ramdisk 2. mount the ramdisk, configure systemd to automount it 3. modify the systemd unit file for psql to depend on the ramdisk 4. initialize all the psql data structures in the ramdisk mount folder
- citrin_ru 3y agoEven with Unix socket there will be at least one additional copy across kernel - userpace boundary compare to SQLite (and likely at least one copy inside the kernel too from one socket buffer to another). Yes, a Unix socket has lower overhead than a TCP one, but still not free: kernel (filesystem) -> app with SQLite library kernel (filesystem) -> Postgres -> kernel -> app with Postgres client If Postgres reads data from own cache (not from disk) the chain will be one step shorter: Postgres -> kernel -> client, but the same true for SQLite own cache. If mmap is used (make sense for small data sets cached in RAM) then reading data by SQLite can be zero-copy [1]. Benchmark result highly depends on a use case - neither is better for all use cases but at least for some use cases SQLite is faster. [1] https://www.sqlite.org/mmap.html https://www.sqlite.org/mmap.html
- pjaoko 3y agoI understood "networked database" to mean a client-server DB architecture. So that Postgres for example is running locally, but communicating with a local server.
- usrbinbash 3y ago> communicating via a Unix socket. Which is already IPC, which already makes it slower than having the data never crossing process boundaries. No benchmark required.
- runeks 3y agoI don’t disagree with this. I disagree with whether these microseconds of difference matter when you’re serving a web page with milliseconds of latency.
- usrbinbash 3y agoThere are many applications beyond serving webpages, where the difference matters. Collecting and collating analytics data is just one example.
- bob1029 3y agoI've done the SQLite benchmark a few times. You can insert somewhere in the range of 10~20k rows per second if you are using NVMe (i.e. ~50uS per row). Requires some pragmas (i.e. WAL). This is 100% serialized throughput. No clue what Postgres would manage, but I suspect it would be about an order of magnitude higher latency in the happy case.
- runeks 3y ago> No clue what Postgres would manage, but I suspect it would be about an order of magnitude higher latency in the happy case. Unless you’re talking 1 versus 10 microseconds (or less), I don’t think Postgres will have an order of magnitude higher latency. And if we are talking this range, why would it matter for a web app where the client’s latency is almost certainly >1 millisecond?
- ripley12 3y agoBecause it changes the kinds of things you can build: https://www.sqlite.org/np1queryprob.html https://www.sqlite.org/np1queryprob.html With SQLite, it's often practical to issue several queries in situations where that would be too slow for a traditional client-server database.
- norir 3y agoI'm pretty sure sqlite is capable of significantly higher throughput that 10-20k rows per second depending on the workload. Inserts can be much faster if they are batched in large transactions and prepared statements are used to avoid sql parsing for each row. This only works of course if your workload can be batched.
- remram 3y agoSlight tangent: Is a UNIX socket faster than TCP over loopback?
- mfalcao 3y agoIn my experience they are 30 to 40% faster
- remram 3y agoWow that is significant!
- sgt 3y agoSomeone should benchmark that. Unix sockets in my mind should be faster as in theory you have less overhead. But the flow using TCP over loopback might have received many more optimizations over the last decades.
- mfalcao 3y agoHere's a quick benchmark with pgbench: $ pgbench -h localhost -p 5432 -b select-only -T 60 -c 10 -j 2 bench Password: pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1)) starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 10370147 number of failed transactions: 0 (0.000%) latency average = 0.058 ms initial connection time = 54.881 ms tps = 172993.008029 (without initial connection time) $ pgbench -h /var/run/postgresql -p 5432 -b select-only -T 60 -c 10 -j 2 bench pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1)) starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 maximum number of tries: 1 duration: 60 s number of transactions actually processed: 16890415 number of failed transactions: 0 (0.000%) latency average = 0.036 ms initial connection time = 7.186 ms tps = 281540.260418 (without initial connection time) YMMV depending on your workload, but Unix sockets should always be significantly faster.