Hacker News Reader

Comment by runeks | Hacker News Reader

> 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.

View on HN

Even 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

No 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.

It should be trivial to set it up on a ramdisk and I see some guides on doing so when I search for "postgres ramdisk"

PHP 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

I'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.

> 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?

Because it changes the kinds of things you can build: 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.

I'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.

> 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.

I 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.

There are many applications beyond serving webpages, where the difference matters. Collecting and collating analytics data is just one example.

Slight tangent: Is a UNIX socket faster than TCP over loopback?

In my experience they are 30 to 40% faster

Wow that is significant!

Someone 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.

Here'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.

Very interesting! Thank you for the effort.

I understood "networked database" to mean a client-server DB architecture. So that Postgres for example is running locally, but communicating with a local server.

Reply on news.ycombinator.com