Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL reconsiders its process-based model

lwn.net

Post reply on HN
Print view View original

61–70 of 377 posts

Re: PostgreSQL reconsiders its process-based model

#61

Post by Diggsey »

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.

It's not just CPU - memory usage is also higher. In particular, idle connections still consume signficant memory, and this is why PostgreSQL has so much lower connection limits than eg. MySQL. Pooling can help in some cases, but pooling also breaks some important PostgreSQL features (like prepared statements...) since poolers generally can't preserve session state. Other features (eg. notify) are just incompatible with pooling. And pooling cannot help with connections that are idle but inside a transaction.

That said, many of these things are solvable without a full switch to a threaded model (eg. by having pooling built-in and session-state-aware).

Re: PostgreSQL reconsiders its process-based model

#62

Post by PhilipRoman »

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'm assuming you're referring to formally proven programs. If that's the case, do you have any pointers?

Aside from the trivial while(!transactionSucceeded){retry()} loop, I have trouble proving the correctness of my programs when the number of threads is not small and finite.

Re: PostgreSQL reconsiders its process-based model

#63

Post by johannes1234321 »

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.

The code already is multithreaded. They have shared state just across multiple processes instead of threads within a process.

They might even reduce complexity that way.

Re: PostgreSQL reconsiders its process-based model

#64

Post by ajkjk »

tragomaskhalos wrote: post #19

Worked on a codebase which was separate processes, each of which has a shedload of global variables. It was a nightmare working out what was going on, not helped by the fact that there was no naming convention for the globals, plus they were not declared in a single place. I believe their use was a performance move, ie having the linker pin a var to a specific memory location rather than copying it to the stack as a…

There's something to be said for globals whose access is well-managed, though.

IMO: if the variable is _truly_ global, i.e. code all over the codebase cares about it, then it should just be global instead of pretending like it's not with some fancy architecture.

The tricky part is reacting to changes to a global variable. Writing a bunch of "on update" logic leads to madness. The ideal solution is for there to be some sort of one-directional flow for updates, like when a React component tree is re-rendered... but that's very hard to build in an application that doesn't start out using a library like React in the first place.

Re: PostgreSQL reconsiders its process-based model

#65

Post by ajkjk »

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 is just harder.

Re: PostgreSQL reconsiders its process-based model

#66

Post by chucky_z »

I wish they would do some kind of easy shared storage instead, or in addition too. This sounds like an odd solution, however I’ve scaled pgsql since 9 on very, very large machines and doing 1 pgsql cluster per physical socket ended up doing near-linear scaling even on 100+ total core machines with TB+ of memory.

The challenge with this setup is that you need to do 1 writer and multiple reader clusters so you end up doing localhost replication which is super weird. If that requirement was somehow removed that’d be awesome for scaling really huge clusters.

Re: PostgreSQL reconsiders its process-based model

#67

Post by hyperman1 »

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.

I see postgres become CPU bound regularly: Lots of hash joins, copy from or to CSV, index or materialized view rebuild. Postgis eats CPU. Tds_fdw tends to spend a lot of time doing charset conversion, more than actually networking to mssql.

I was surprised when starting with postgres. Then again, I have smaller databases (A few TB) and the cache hit ratio tends to be about 95%. Combine that with SSDs, and it becomes understandable.

Even so, I am wary of this change. Postgres is very reliable, and I have no problem throwing some extra hardware to it in return. But these people have proven they know what they are doing, so I'll go with their opinion.

Re: PostgreSQL reconsiders its process-based model

#68

Post by levkk »

Pretty sure Tom Lane said this will be a disaster in that same pgsql-hackers thread. Not entirely sure what benefits the multi-threaded model will have when you can easily saturate the entire CPU with just 128 connections and a pooler. So I doubt there is consensus or even strong desire from the community to undertake this boil the ocean project.

On the other hand, having the ability to shut down and cleanup the entire memory space of a single connection by just disconnecting is really nice, especially if you have extensions that do interesting things.

Re: PostgreSQL reconsiders its process-based model

#69

Post by topspin »

atonse wrote: post #49
topspin wrote: post #38

Earlier quoted context omitted.

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

Would this basically be a new front end? Like the part that handles sockets and input? Or more if a rewrite of subsystems? Like the query planner or storage engine etc?

Both, I'd imagine.

With regard to client compatibility there are related precedents for this already; the PostgreSQL wire protocol has emerged as a de facto standard. Cockroachdb and ClickHouse are two examples that come to mind.

Post reply on HN