undefined | Better HN

0 pointsklabb33y ago0 comments

Postgres over localhost is ~10x slower (as in RTT) than SQLite, I think mainly due to serialization and perhaps context switches?

https://youtu.be/XcAYkriuQ1o

If you (more commonly) run on a different host, you’re looking more at 20x-50x and this is assuming you’re in the same region.

To even get close to the SQLite level of throughput you’d have to stagger/pipeline your requests over a number of concurrent connections (proportional to the earlier x).

You’ll eventually succeed at doing just that, with multiple machines. Congratulations, but now you have to also consider the N+1 problem, even for small Ns. In SQLite, you can afford much more back-and-forth with simple queries.

0 comments

10000truths3y ago

The benchmark in the video seems flawed:

* He’s comparing an in-memory SQLite DB against a non-temp table in Postgres. Of course the RTT is lower when you don’t have to interact with the filesystem or flush to disk in the critical path.

* He’s connecting to Postgres over a loopback interface, instead of using a UNIX socket. Of course the RTT is higher when requests and responses needlessly traverse the TCP/IP stack.

klabb3OP3y ago

> He’s comparing an in-memory SQLite DB against a non-temp table in Postgres.

That’d surprise me, where does he say that? (Even if he doesn’t it’s important to iron out though, so thanks for being critical!) The ~18us sound reasonable from my own experiments, and that’s with flush to file (using wal mode).

EDIT: I think I ran my benchmarks with synchronous=normal. Not sure what he did.

> He’s connecting to Postgres over a loopback interface, instead of using a UNIX socket.

I agree unix sockets would be better for same host setup, but still, the more representative number for “typical use” would be networked, no?

Also, for fairness: SQLite setups come with downtime, that’s hard to avoid. Worth mentioning..

10000truths3y ago

> That’d surprise me, where does he say that? (Even if he doesn’t it’s important to iron out though, so thanks for being critical!) The ~18us sound reasonable from my own experiments, and that’s with flush to file (using wal mode).

I was looking at the benchmark results at 13:27, but now I see there's another set of benchmark results shown at 29:13. He doesn't link to a repo for that second one, but in-memory vs persistent doesn't matter as much when measuring reads if the results are cached in memory.

> I agree unix sockets would be better for same host setup, but still, the more representative number for "typical use" would be networked, no?

If we're going by what's "representative", then a benchmark isn't useful, because Postgres and SQLite have dissimilar use cases. If you want to compare their performance, it only makes sense to eliminate confounding variables, of which the TCP/IP stack is significant.

klabb3OP3y ago

> He doesn't link to a repo for that second one, but in-memory vs persistent doesn't matter as much when measuring reads if the results are cached in memory.

No doubt that we deserve reproducible benchmarks and this could use more details. As for caching that’d be the same for Postgres in theory.

> If we're going by what's "representative", then a benchmark isn't useful, because Postgres and SQLite have dissimilar use cases.

I agree and disagree! We already know Postgres is performant enough for a majority of server-side use cases. Ben is trying to show what you can expect from SQLite if you use it for those cases, which is novel to most people. SQLite doesn’t have the same amount of features, and partly because of that drawback, the extremely low latency (in a typical setup) is a redeeming factor. I think it’s not misleading to say SQLite has significantly lower app->db _latency_ than networked databases. Unix socket is a more fair microbench yea, but at least to me that’s not as useful because the Postgres setup wouldn’t be same-host.

ricardobeat3y ago

Loopback interfaces in Linux bypass the network stack (and maybe in windows server as well?).

withinboredom3y ago

I believe you still have TCP serialization (including packet fragmentation) in a loopback, but not in a unix socket. I could be wrong though.

fulafel3y ago

The relative performance difference of no-op queries (RTT) is unlikely to have a high impact on application performance since they're so quick in absolute terms.

jbverschoor3y ago

What about postgresql using a file socket

j / k navigate · click thread line to collapse

0 comments

10000truths3y ago

The benchmark in the video seems flawed:

* He’s comparing an in-memory SQLite DB against a non-temp table in Postgres. Of course the RTT is lower when you don’t have to interact with the filesystem or flush to disk in the critical path.

* He’s connecting to Postgres over a loopback interface, instead of using a UNIX socket. Of course the RTT is higher when requests and responses needlessly traverse the TCP/IP stack.

klabb3OP3y ago

> He’s comparing an in-memory SQLite DB against a non-temp table in Postgres.

EDIT: I think I ran my benchmarks with synchronous=normal. Not sure what he did.

> He’s connecting to Postgres over a loopback interface, instead of using a UNIX socket.

I agree unix sockets would be better for same host setup, but still, the more representative number for “typical use” would be networked, no?

Also, for fairness: SQLite setups come with downtime, that’s hard to avoid. Worth mentioning..

10000truths3y ago

> I agree unix sockets would be better for same host setup, but still, the more representative number for "typical use" would be networked, no?

klabb3OP3y ago

> He doesn't link to a repo for that second one, but in-memory vs persistent doesn't matter as much when measuring reads if the results are cached in memory.

No doubt that we deserve reproducible benchmarks and this could use more details. As for caching that’d be the same for Postgres in theory.

> If we're going by what's "representative", then a benchmark isn't useful, because Postgres and SQLite have dissimilar use cases.

ricardobeat3y ago

Loopback interfaces in Linux bypass the network stack (and maybe in windows server as well?).

withinboredom3y ago

I believe you still have TCP serialization (including packet fragmentation) in a loopback, but not in a unix socket. I could be wrong though.

fulafel3y ago

The relative performance difference of no-op queries (RTT) is unlikely to have a high impact on application performance since they're so quick in absolute terms.

jbverschoor3y ago

What about postgresql using a file socket

j / k navigate · click thread line to collapse