PostgreSQL reconsiders its process-based model • Hacker News Board

Re: PostgreSQL reconsiders its process-based model

#51

Post by nextaccountic » Mon, Jun 19, 2023, 5:44 PM UTC

topspin wrote: post #38

mihaic wrote: post #2

I'm honestly surprised it took them so long to reach this conclusion. > That idea quickly loses its appeal, though, when one considers trying to create and maintain a 2,000-member structure, so the project is unlikely to go this way. As repulsive as this might sound at first, I've seen structures of hundreds of fields work fine if the hierarchy inside them is well organized and they're not just flat. Still, I have no…

> I'm honestly surprised it took them so long to reach this conclusion. I'm not. You can get a long way with conventional IPC, and OS processes provide a lot of value. For most PostgreSQL instances the TLB flush penalty is at least 3rd or 4th on the list of performance concerns, far below prevailing storage and network bottlenecks. I share the concerns cited in this LWN story. Reworking this massive code base around…

From what I gather postgres isn't doing conventional IPC but instead it uses shared memory, which means the same mechanism threads use but with way higher complexity

Re: PostgreSQL reconsiders its process-based model

#52

Post by scolby33 » Mon, Jun 19, 2023, 5:47 PM UTC

erikpukinskis wrote: post #37

cactusfrog wrote: post #26

Earlier quoted context omitted.

This is different because there isn’t a whole ecosystem of packages that depend on access to a thread unsafe C API. Getting the GIL out of core Python isn’t too challenging. Getting all of the packages that depend on Python’s C API working is.

> there isn’t a whole ecosystem of packages that depend on access to a thread unsafe C API They mentioned a similar issue for Postgres extensions, no? > Haas, though, is not convinced that it would ever be possible to remove support for the process-based mode. Threads might not perform better for all use cases, or some important extensions may never gain support for running in threads.

I question how important an extension is if there’s not enough incentive to port it to the new paradigm, at least eventually.

Re: PostgreSQL reconsiders its process-based model

#53

Post by ethbr0 » Mon, Jun 19, 2023, 5:48 PM UTC

devit wrote: post #42

EvanAnderson wrote: post #40

Earlier quoted context omitted.

> Windows forks processes about 100x slower than Linux... I work with a Windows-based COTS webapp that uses Postgres w/o any connection pooling. It's nearly excruciating to use because it spins-up new Postgres processes for each page load. If not for the fact that the Postgres install is "turnkey" with the app I'd just move Postgres over to a Linux machine.

Use pgbouncer

Was curious about this as an architectural solution as well.

We're really talking about X-per-client as the primary reason to move away from processes, right?

So if you can get most of the benefit via pooling... why inherit the pain of porting?

Presumably latency jitter would be a difficult problem with pools, but it seems easier (and safer) than porting processes -> threads.

Disclaimer: High performance / low latency DB code is pretty far outside my wheelhouse.

Re: PostgreSQL reconsiders its process-based model

#54

Post by preordained » Mon, Jun 19, 2023, 5:50 PM UTC

xwdv wrote: post #33

baggy_trough wrote: post #8

I hope they are conservative about this, because even the smartest and best programmers in the world cannot create bug free multithreaded code.

Nonsense, multithreaded code can be written as bug free as regular code. No need to fear.

It can be. Anything can be. It is far more treacherous, though.

Re: PostgreSQL reconsiders its process-based model

#55

Post by 36364949thrw » Mon, Jun 19, 2023, 5:51 PM UTC

paulddraper wrote: post #21

mihaic wrote: post #2

I'm honestly surprised it took them so long to reach this conclusion. > That idea quickly loses its appeal, though, when one considers trying to create and maintain a 2,000-member structure, so the project is unlikely to go this way. As repulsive as this might sound at first, I've seen structures of hundreds of fields work fine if the hierarchy inside them is well organized and they're not just flat. Still, I have no…

> I'm honestly surprised it took them so long to reach this conclusion. On the contrary, it's been discussed for ages. But it's a huge change, with only modest advantages. I'm skeptical of the ROI to be honest. Not that is doesn't have value, but that it has more value than the effort.

> it's a huge change, with only modest advantages

+significant and unknown set of new problems, including new bugs.

This reminds me of the time they lifted entire streets in Chicago by 14 feet to address new urban requirements. Chicago, we can safely assume, did not have the option of just starting a brand new city a few miles away.

The interesting question here is should a system design that works quite well upto a certain scale be abandoned in order to extend its market reach.

Re: PostgreSQL reconsiders its process-based model

#56

Post by sargun » Mon, Jun 19, 2023, 5:53 PM UTC

comboy wrote: post #48

mihaic wrote: post #2

I'm honestly surprised it took them so long to reach this conclusion. > That idea quickly loses its appeal, though, when one considers trying to create and maintain a 2,000-member structure, so the project is unlikely to go this way. As repulsive as this might sound at first, I've seen structures of hundreds of fields work fine if the hierarchy inside them is well organized and they're not just flat. Still, I have no…

I've never really been limited by CPU when running postgres (few TB instances). The bottleneck is always IO. Do others have different experience? Plus there's elegance and a feeling of being in control when you know query is associated with specific process which you can deal with and monitor just like any other process. But I'm very much clueless about internals, so this is a question rather than an opinion.

With modern SSDs that can push 1M IOPs+, you can get into a situation where I/O latency starts to become a problem, but in my experience, they far outpace what the CPU can do. Even the I/O stack can be optimized further in some of these cases, but often it comes with the trade off of shifting more work into the CPU.

Re: PostgreSQL reconsiders its process-based model

#57

Post by hamandcheese » Mon, Jun 19, 2023, 5:53 PM UTC

shepardrtc wrote: post #29

mihaic wrote: post #2

I'm honestly surprised it took them so long to reach this conclusion. > That idea quickly loses its appeal, though, when one considers trying to create and maintain a 2,000-member structure, so the project is unlikely to go this way. As repulsive as this might sound at first, I've seen structures of hundreds of fields work fine if the hierarchy inside them is well organized and they're not just flat. Still, I have no…

I think this is a situation where a message-passing Actor-based model would do well. Maybe pass variable updates to a single writer process/thread through channels or a queue. Years ago I wrote an algorithmic trader in Python (and Cython for the hotspots) using Multiprocessing and I was able to get away with a lot using that approach. I had one process receiving websocket updates from the exchange, another process wr…

I think the Actor model is fine if you start there, but I can't imagine incrementally adopting it in a large, preexisting code base.

Re: PostgreSQL reconsiders its process-based model

#58

Post by taeric » Mon, Jun 19, 2023, 5:53 PM UTC

xwdv wrote: post #33

baggy_trough wrote: post #8

I hope they are conservative about this, because even the smartest and best programmers in the world cannot create bug free multithreaded code.

Nonsense, multithreaded code can be written as bug free as regular code. No need to fear.

I think the point is that some mistakes in process based code are not realized as the bugs that they will be in threaded code?

Re: PostgreSQL reconsiders its process-based model

#59

Post by paulddraper » Mon, Jun 19, 2023, 5:57 PM UTC

comboy wrote: post #48

mihaic wrote: post #2

I'm honestly surprised it took them so long to reach this conclusion. > That idea quickly loses its appeal, though, when one considers trying to create and maintain a 2,000-member structure, so the project is unlikely to go this way. As repulsive as this might sound at first, I've seen structures of hundreds of fields work fine if the hierarchy inside them is well organized and they're not just flat. Still, I have no…

I've never really been limited by CPU when running postgres (few TB instances). The bottleneck is always IO. Do others have different experience? Plus there's elegance and a feeling of being in control when you know query is associated with specific process which you can deal with and monitor just like any other process. But I'm very much clueless about internals, so this is a question rather than an opinion.

Depends on your queries.

If you push a lot of work into the database including JSON and have a lot of buffer memory...CPU can easily be limiting.

Re: PostgreSQL reconsiders its process-based model

#60

Post by sargun » Mon, Jun 19, 2023, 5:58 PM UTC

I'm curious if they can take advantage of vfork / CLONE_VM, to get the benefits of sharing memory and lower overhead context switches, with the trade of still getting benefits from the scheduler, and sysadmin-friendliness.

The other thing that might be interesting is FUTEX_SWAP / UMCG. Although it doesn't remove the overhead induced by context switches entirely (specifically, you would still deal with TLB misses), you can avoid dealing with things like speculative execution exploit mitigations.