Ask HN: How to optimise databases for latency rather than throughput? | Hacker News Reader

Ask HN: How to optimise databases for latency rather than throughput?

1 pointsonthonax5 comments

Databases are fast for querying large amounts of data, but terrible at fetching small amounts of data quickly.

Databases perform terribly on benchmarks for getting single rows from small tables. In Oracle, fetching a 100x2 matrix of integers takes 500us, while fetching 1000x2 matrix takes 1000us. I get similar (worse) performance in MySQL and Postgres.

This is hundreds of times slower than a key value store. It's crazy that databases are so slow in 2020. It massively influences how we build applications, and I don't understand why they have to be so bad at being key-value stores.

As far as I understand, modern databases are quite complex bits of software. For example, Oracle performs a lot of IPC between listener, writer and log processes. Postgres has a single process per connection that has to be synchronised with other processes.

What are some of the techniques that people on Hacker News have used to mitigate this problem?

The biggest influence on small query performance for RDBMSs is server<->client latency. I just checked, and a single simple key-value style lookup in postgres over a local unix-socket connection takes 0.025ms on average.

However, if I use pipelining, I see 1000 lookups performed in 4.725ms (i.e 0.0047ms per lookup).

If you test the same over an actual network, the performance difference is going to be much bigger, because it's not just context switch, but actual network delay.

You're never going to beat a dedicated KV store with a shared-nothing event listener architecture with a general purpose RDBMS. Transactional semantics imply some overhead, non-shared buffer pool isn't generally viable, ... But I don't think the difference is as big as you make it out to be.

Disclaimer: Postgres dev, so I'm certainly biased.

> However, if I use pipelining, I see 1000 lookups performed in 4.725ms (i.e 0.0047ms per lookup).

To be clear, that's over a single connection, not a parallel test.

Hm, maybe I need to run my tests again.

> https://github.com/oracle/python-cx_Oracle/issues/555#issue-...

This is what I saw for Oracle running on RDS. My ping was pretty stable: 500us.

What I found was that RDMSs are an order of magnitude slower even without the network latency.

I think it'd be good to put the different tests on a more comparable footing. E.g. comparing results of different services on hosts with different network latency isn't that meaningful. And the actual queries should be something roughly equivalent too.

Well that test ran on EC2 against RDS in the same region. But the results were reproducible locally using docker.

What I actually found, is that the timings given by explain plan were quite different from the reality, no matter how much I factored in network latency.

Which makes me think there’s a massive overhead in actually sending the data.

Reply on news.ycombinator.com