anarazel

A connection definitely has overhead in PG, but "5000 concurrent connections will already start to deadlock your Postgres server" is bogus. People completely routinely run with more connections.

Check the throughput graphs from this blog post from 2020 (for improvements I made to connection scalability):

https://techcommunity.microsoft.com/blog/adforpostgresql/imp...

That's for read-mostly work. If you do write very intensely, you're going to see more contention earlier. But that's way way worse with sqlite, due to its single writer model.

EDIT: Corrected year.

The usleep here isn't one that was intended to be taken frequently (and it's taken in a loop, so a too short sleep is ok). As it was introduced, it was intended to address a very short race that would have been very expensive to avoid altogether. Most of the waiting is intended to be done by waiting for a "transaction lock".

Unfortunately somebody decided that transaction locks don't need to be maintained when running as a hot standby. Which turns that code into a loop around the usleep...

(I do agree that sleep loops suck and almost always are a bad idea)

That depends on what you're hosting. Good luck if it's e.g. a web interface for a bunch of git repositories with a long history. You can't cache effectively because there's too many pages and generating each page isn't cheap.

He's still idling in a bunch of irc channels...

> There are known scenarios in the literature that will cause Postgres to lose data, which TigerBeetle can detect and recover from.

What are you referencing here?

The numbers in the post highlight an issue I have with Samsung consumer SSDs - they've slowed down FUA writes to an absurd degree.

        open_datasync                       249.578 ops/sec    4007 usecs/op
        fdatasync                           608.573 ops/sec    1643 usecs/op

open_datasync (i.e. O_DSYNC) ends up as FUA writes, fdatasync() as a plain write followed by a cache flush.

On just about anything else a single FUA write is either the same speed as a write + fdatasync, or considerably faster.

This is pretty annoying, as using O_DSYNC is a lot more suitable for concurrent WAL writes, but because Samsung SSDs are widespread, changing the default would regress performance substantially for a good number of users.

> So PostgreSQL evolved to the point that it has a stable API for extensibility?

Not across major versions, no. I seriously doubt we will ever make promises around that. It would hamper development way too much.

> Postgres offers strictly serializable isolation indeed, but IIUC it's basically a form of read locking so will tank performance.

Postgres' SSI [1] does not block reads. If you have lots of transactions reading and updating a lot of rows the granularity of tracking will become coarser to keep memory usage in bound though.

[1] https://drkp.net/papers/ssi-vldb12.pdf

FWIW, there actually are some ongoing efforts towards that - including several preparatory changes in PG 18. Still lots more work, but we are working towards it.

https://wiki.postgresql.org/wiki/Multithreading

It turns out to be the other way round, curiously. The bigger the reads (i.e. how much to read in one syscall) and the bigger the target area of the reads (how long before a target memory location is reused), the bigger the overhead of SMAP gets.

If interesting I can dig up the reproducer I had at some point.

From what I've seen a surprisingly large part of the overhead is due to SMAP when doing larger reads from the page cache - i.e. if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly.

On Intel it's also not hard to simply reach the per-core memory bandwidth with modern storage HW. This matters most prominently for writes by the checkpointing process, which needs to compute data checksums given the current postgres implementation (if enabled). But even for reads it can be a bottleneck, e.g. when prewarming the buffer pool after a restart.

> Do you think there's a possibility of Direct IO being adopted at some point in the future now that AIO is available?

Explicitly a goal.

You can turn it on today, with a bunch of caveats (via debug_io_direct=data). If you have the right workload - e.g. read only and lots of seqscans, bitmap index scans etc you can see rather substantial perf gains. But it'll suck in any cases in 18.

We need at least:

- AIO writes in checkpointer, bgwriter and backend buffer replacement (think bulk loading data with COPY)

- readahead support in a few more places, most crucially index range scan (works out ok today if the heap is correlated with the index, sucks badly otherwise)

EDIT: Formatting

FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease.

Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost.

It turns out, as some of the results in Lukas' post show, that the offload to the worker is often actually beneficial particularly when the data is in the kernel page cache - it parallelizes the memory copy from kernel to userspace and postgres' checksum computation. Particularly on Intel server CPUs, which have had pretty mediocre per-core memory bandwidth in the last ~ decade, memory bandwidth turns out to be a bottleneck for page cache access and checksum computations.

Edit: Fix negation

FWIW, there are prototype patches for an IOCP based io_method. We just couldn't get them into an acceptable state for PG 18. I barely survived getting in what we did...

They sped up things for a long time - but only when using unbuffered IO. The new thing with io_uring is that it also accelerates buffered IO. In the initial version it was all through kernel worker threads, but these days several filesystems have better paths for common cases.

Intrusive lists are often used to enqueued pre-existing structures onto lists. And often the same object can be in different lists at different times.

That's not realistically dealt with by the compiler re-organizing the struct layout.

Link to the option in https://news.ycombinator.com/item?id=43598217

> One gotcha about Postgres queries is that running queries do not get cancelled when the client disconnects

You can configure that these days, at least if the server is running on some common platforms:

https://www.postgresql.org/docs/current/runtime-config-conne...

FWIW, you can parametrize statements on postgres without preparing them.

> Today you should do the same trick as a Linux futex although you spell it differently in Windows. It's also optionally smaller, which is nice for this sort of job, a futex costs 4 bytes but in Windows we can spend just one byte.

I don't really care about narrower futexes, what I do wish Linux had was 8 byte futexes. It's at times hard to cram enough state into 4 bytes for more complicated things.

E.g. for postgres' buffer state it'd be very useful to be able to have buffer state bits, pin count, shared lockers and the head of the lock wait queue in one futex "protectable" unit.

There are also ABA style issues that would be easier to defend against with a wider futex.

> That Craig person, the OP in the thread. Imagine doing all that work, having other people say, hey, that's an issue; and then all the people saying "so what" or "nothing can be done"

For context - Craig's opinion won out, and what he was suggesting (crash-restart and perform recovery) is what postgres has been doing for many years (with an option to revert back to retrying, but I haven't seen anybody toggle that).

> As such I'm occasionally bemused at the sort-of monoculture here around Postgres, where if Postgres doesn't have it, it may as well not exist.

FWIW, I, as a medium-long term PG developer, are also regularly ... bemused by that attitude. We do some stuff well, but we also do a lot of shit not so well, and PG is succeeding despite that, not because of.

> Relatedly, the other interesting thing is the chatter about fsync. I know on Windows that's not the mechanism that's used, and out of curiosity I looked deeper into what MS-SQL does on Linux, and indeed they were able to get significant improvement by leveraging similar mechanisms to ensure the data is hardened to disk without a separate flush (see https://news.ycombinator.com/item?id=43443703). They contributed to kernel 4.18 to make it happen.

Case in point about "we also do a lot of shit not so well" - you can actually get WAL writes utilizing FUA out of postgres, but it's advisable only under somewhat limited circumstances:

Most filesystems are only going to use FUA writes with O_DIRECT. The problem is that for streaming replication PG currently reads back the WAL from the filesystem. So from a performance POV it's not great to use FUA writes, because that then triggers read IO. And some filesystems have, uhm, somewhat odd behaviour if you mix buffered and unbuffered IO.

Another fun angle around this is that some SSDs have *absurdly* slow FUA writes. Many Samsung SSDs, in particular, have FUA write performance 2-3x slower than their already bad whole-cache-flush performance - and it's not even just client and prosumer drives, it's some of the more lightweight enterprise-y drives too.

Edit: fights with HN formatting.

It works even with that setting at zero! Just requires a bit more concurrency.

Concurrent commits can group commit with one WAL flush (i.e. one fsync/fdatasync).

Correct. Async commit transactions add their changes to the WAL the same way normal transactions do. The only relevant change is that the WAL is not synchronously flushed to disk before COMMIT completes.

Hah, I found something similar in gcc a few years back. Affecting postgres' expression evaluation interpreter:

https://gcc.gnu.org/bugzilla/show_bug.cgi?id=71785

I have two Google accounts, one in the us, one in Germany. So far that has divided to get around this for the apps I encountered it. But I'm not a heavy phone user...

> I will try this on my own hardware as well

FWIW, my results corresponded to:

  cpupower frequency-set --governor powersave && cpupower idle-set -E

  cpupower frequency-set --governor performance && cpupower idle-set -E

  cpupower frequency-set --governor performance && cpupower idle-set -D0

It's perhaps worth pointing out that -D0 sometimes hurts performance, by reducing the boost potential of individual cores, due to the higher baseline temp & power usage.

> Maybe for completeness, what CPU type is this on?

This was a 2x Xeon Gold 5215. But I've reproduced this on newer Intel and AMD server CPUs too.

> (though I'm far from running into performance limitations on the old laptop that I use for hosting various projects, it could still be something to tune when I run some big task with lots of queries)

If you're run larger queries or queries at a higher frequency (i.e. client on the same host instead of via network, or the client uses pipelining), the problem doesn't typically manifest to a significant degree.

> Intermittent workloads on the order of 2 milliseconds, you mean?

Yea. Most of the cases I was looking at were with postgres, with fully cached simple queries. Each taking << 10ms. The problem is more extreme if the client takes some time to actually process the result or there is network latency, but even without it's rather noticeable.

> Turning it off would, to me, only make sense if you want a server to handle thousands of fast requests per second, but those requests don't come in for periods of, say, 50 ms at a time and so the CPU scales back.

I see regressions at periods well below 50ms, but yea, that's the shape of it.

E.g. a postgres client running 1000 QPS over a single TCP connection from a different server, connected via switched 10Gbit Ethernet (ping RTT 0.030ms), has the following client side visible per-query latencies:

  powersave, idle enabled: 0.392 ms
  performance, idle enabled: 0.295 ms
  performance, idle disabled: 0.163 ms

If I make that same 1 client go full tilt, instead of limiting it to 1000 QPS:

  powersave, idle enabled: 0.141 ms
  performance, idle enabled: 0.107 ms
  performance, idle disabled: 0.090 ms

I'd call that a significant performance change.

> if you have that many short requests coming in, the CPU would simply never scale back if it's reasonably constant.

Indeed, that's what makes the whole issue so pernicious. One of the ways I saw this was when folks moved postgres to more powerful servers and got worse performance due to frequency/idle handling. The reason being that it made it more likely that cores were idle long enough to clock down.

On the same setup as above, if I instead have 800 client connections going full tilt, there's no meaningful difference between powersave/performance and idle enabled/disabled.

It performs terrible if you have an intermittent workload. Like e.g. a request response workload where request processing is cheap (so that the time to increase the frequency matters). I've seen cases it's a more than 2x request latency increase.

It can be pretty annoying, because it means that systems can perform better under higher load and that you get drastically different latency depending on whether a request is scheduled on a core that just processed another request (already at high freq) or one that was idle.

And because the frequency control isn't fun enough, this behavior also exists with cpu idle states. Even at high frequency Linux can enter idle states...

I've debugged several cases where this set of issues has caused unintuitive behavior. E.g.

a) switching to a more powerful servers drastically increased latency

b) optimized code resulting in higher latency / lower throughout because that provided enough idle cycles for a deeper idle time between requests

c) slightly increased IO latency leading to significantly worse overall performance, due to the IO getting long though to clock down