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

171–180 of 377 posts

Re: PostgreSQL reconsiders its process-based model

#171

Post by blinkingled »

chasil wrote: post #163
blinkingled wrote: post #156

Earlier quoted context omitted.

https://oracle-base.com/articles/12c/multithreaded-model-usi... Probably still requires the parameter to be set.

Contrast this to Microsoft SQL Server: $ systemctl status mssql-server ● mssql-server.service - Microsoft SQL Server Database Engine Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled) Active: active (running) since Mon 2023-06-19 15:48:05 CDT; 1min 18s ago Docs: https://docs.microsoft.com/en-us/sql/linux Main PID: 2125 (sqlservr) Tasks: 123 CGroup: /system.slice/mssql-ser…

Yeah multiprocess isn't Microsoft's style given how expensive creating processes is on Windows.

Oracle - never had a scalability issue on very big Linux, Solaris and HPUX systems though - they do it well in my experience.

Re: PostgreSQL reconsiders its process-based model

#172

Post by abalashov »

narrator wrote: post #17
lukeschlather wrote: post #9

This sounds like a problem that would border on the complexity of replacing the GIL in Ruby or Python. The performance benefits are obvious but it seems like the correctness problems would be myriad and a constant source of (unpleasant) surprises.

The correctness problem should be handled by a suite of automated tests which PostgreSQL has. If all tests pass, the application must work correctly. The project is too big, and has too many developers to make much progress without full test coverage. Where else would up-to-date documentation regarding the correct behavior of PostgreSQL exist? In some developers head? SQLite is pretty famous for there extreme approac…

> If all tests pass, the application must work correctly.

These are "famous last words" in many contexts, but when talking about difficult-to-reproduce parallelism issues, I just don't think it's a particularly applicable viewpoint at all. No disrespect. :)

Re: PostgreSQL reconsiders its process-based model

#173

Post by cogman10 »

niccl wrote: post #139
zeroimpl wrote: post #131

Earlier quoted context omitted.

However, it's already the case that if a postgres process crashes, the whole cluster gets restarted. I've occasionally seen this message: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a momen…

yes, but postmaster is still running to roll back the transaction. If you crash a single multi-threaded process, you may lose postmaster as well and then sadness would ensue

Transaction roll back is a part of the WAL. Databases write to the disk an intent to change things, what should be changed, and a "commit" of the change when finished so that all changes happen as a unit. If the DB process is interrupted during that log write then all changes associated with that transaction are rolled back.

Threaded vs process won't affect that.

Re: PostgreSQL reconsiders its process-based model

#174

Post by dan-robertson »

rburhum wrote: post #113

Sorry if I offend anybody, but this sounds like such a bad idea. I have been running various versions of postgres in production for 15 years with thousands of processes on super beefy machines, and I can tell you without a doubt that sometimes those processes crash - specially if you are running any of the extensions. Nevertheless, Postgres has 99% of the time proven to be resilient. The idea that a bad client can br…

Is the actual number you got 99%? Seems low to me but I don’t really know about Postgres. That’s 3 and a half days of downtime per year, or an hour and a half per week.

Re: PostgreSQL reconsiders its process-based model

#175

Post by anarazel »

niccl wrote: post #139
zeroimpl wrote: post #131

Earlier quoted context omitted.

However, it's already the case that if a postgres process crashes, the whole cluster gets restarted. I've occasionally seen this message: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a momen…

yes, but postmaster is still running to roll back the transaction. If you crash a single multi-threaded process, you may lose postmaster as well and then sadness would ensue

We would still have a separate process doing that part of postmaster's work.

Re: PostgreSQL reconsiders its process-based model

#176

Post by cogman10 »

loeg wrote: post #7
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…

Yeah. I think as a straightforward, easily correct transition from 2000 globals, a giant structure isn't an awful idea. It's not like the globals were organized before! You're just making the ambient state (awful as it is) explicit.

I think my bigger fear is around security. A process per connection keeps things pretty secure for that connection regardless of what the global variables are doing (somewhat hard to mess that up with no concurrency going on in a process).

Merge all that into one process with many threads and it becomes a nightmare problem to ensure some random addon didn't decide to change a global var mid processing (which causes wrong data to be read).

Re: PostgreSQL reconsiders its process-based model

#177

Post by anarazel »

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.

Postgres is already concurrent today. There's a lot of shared state between the processes (via shared memory).

Re: PostgreSQL reconsiders its process-based model

#178

Post by SigmundA »

wizofaus wrote: post #141
vbezhenar wrote: post #137

Earlier quoted context omitted.

Of course it will. That's better than continue working with damaged memory structures and unpredictable consequences. For database it's more important than ever. Imagine writing corrupted data because other thread went crazy.

You're implying that only an OS can provide memory separation between units of execution - at least in .NET AppDomains give you the same protection within a single process, so why couldn't postgres have its own such mechanism? I'd also think with a database engine shared state is not just in-memory - i.e. one process can potentially corrupt the behaviour of another by what it writes to disk, so moving to a single-pro…

No AppDomains are not as good as processes, I have tried to go that route before, you cannot stop unruly code reliably in an app domain (you must use thread.abort() which is not good) and memory can still leak in any native code used there.

The only reliable way to stop bad code like say an infinite loop is to run in another process even in .Net.

They also removed Appdomain in later versions of .Net because they had little benefit and weak protections compared to a a full process.

Re: PostgreSQL reconsiders its process-based model

#179

Post by orthoxerox »

jupp0r wrote: post #73

Please don't use mutable global state in your work. Global variables are universally bad and don't provide much of a benefit. The number of desirable architectural refactoring that I've witnessed turning into a muddy mess because of them is daunting. This is one more example of this.

You know what a database is, do you? It is the place where you store your mutable global state. You can't kick the can down the road forever, someone has to tackle the complexity of managing state.

Re: PostgreSQL reconsiders its process-based model

#180

Post by steve_adams_86 »

bb88 wrote: post #166
carstenhag wrote: post #126

Earlier quoted context omitted.

The person probably implied that Postgres should switch to another toolchain that guarantees more things at compile time, so probably Rust.

You can take a chunk of code and just rewrite it in Rust. You'll learn a lot quickly by this.

It’s sort of like the inverse of the Matrix when Neo learns kung fu. You realize that you actually don’t know how to program :)
Post reply on HN