Comment by film42
2 days ago
A thread per connection is a almost always the correct decision for performance, but by choosing a process per connection, postgres is able to let you load whatever sketchy extensions you want. Worst case you crash the process, not the database. It would be nice if you could strike a balance so a segfaul in the extension only crashes a small percentage of connections, not the whole thing.
That’s not true for Postgres however: due to its usage of a shared memory pool, whenever a subprocess is terminated unexpectedly, Postgres will kill all other processes and enter recovery mode, replaying the WAL, during which time it will not accept connection requests.
It does this because it can’t possibly know whether the dying process did bad things to the shared memory pool.
You are correct! TIL, and thank you. Connection processes get a SIGQUIT, shared buffers cleared, and WAL replayed, but postmaster stays alive. It's effectively an online restart.
I’d be very curious to hear what an online restart approach for sketchy extensions crashing a shared thread per connection process might look like. This is more a question for the author of the project but I’m curious if they have plans in that direction.
1 reply →
What's online about this restart? Just that the tcp port keeps listening? I assume all running transaction will have to be aborted, right? And connections dropped?
1 reply →
If it's a choice between performance and being able to "safely" run sketchy extensions, I'd rather have performance.
A mixture of threads and processes that can be used to match processors, disk I/O, and network interfaces.
A very long time ago, there was once a feature called "Data Blades" which tanked a commercial database vendor. A badly behaving blade could bring down the entire database. Most anyone who has been working on databases for a few decades remembers this and makes a point of either not introducing these sorts of features or making use of processes over threads.
I have not looked at the code referenced in the mentioned project, but thus far I haven't seen a model that could craft a complete SQL parser on its own.
There are a number of problems, and design decisions, that a developer decides on when writing a database that I don't see any current models… just because you have the ingredients does not mean that the stew is edible.
Informix. Michael Stonebraker, to his credit, learned a lot along the way and revised his thinking about database technology and capability after believing that a single engine could be good at everything.
> A very long time ago, there was once a feature called "Data Blades" which tanked a commercial database vendor.
I have no idea what this is and a web search turned up Harbor Freight woodworking tools.
8 replies →
While PG's behavior doesn't guarantee a lack of data corruption, "an extension crashed, all bets are off, tear everything down" is going to give you a much better fighting chance against data corruption vs the alternative.
In the age of vibe-generated code, I promise you're gonna want the safety.
So load them up in read replicas
3 replies →
What about just not installing sketchy extensions?
1 reply →
You could fix probably the sketchy extension issue with WASM.
Limit, not fix.
Do you mean that you don't run sketchy extensions and therefore this doesn't affect you, or that you're ok with data loss due to extension failures?
What about extensions that are not sketchy? Lots of good ones out there.
People are assuming the extensions can't also be rewritten to be good.
Presumably they'd be fine running in a threaded context.
3 replies →
Especially since all those sketchy extensions can be rewritten in rust over a weekend and have their bugs fixed as well.
But it isn't.
Threads does not offer any major performance advantage, performance of processes vs threads is virtually the same. The reason the PostgreSQL project is moving towards threads is to make development easier.
> Threads does not offer any major performance advantage
This is very not true. When it comes to parallel queries, a process model adds a ton of overhead. You can't pass pointers between processes because the address space is different. This adds a ton of overhead in a bunch of different places. For example when doing a parallel hash join, Postgres will have each worker build a local hash table. Then it will take all the tuples out of the local hash table and copy them through shared memory to the leader who will then construct a new hash table. This duplicates a lot of work as you have to hash the tuples multiple times.
A lot of getting to Clickhouse level performance was making better use of parallelism.
18 replies →
MSSQL can handle 32k open connections no need to run a pooler in front of it, can PG do 32k connections and a process for each?
MSSQL shares cached query plans between connections including jitted code, PG cannot do that and the changes needed to make the plans cross process portable would be extensive while sharing between threads is just normal code sharing between threads.
unless you're spawning them for new connections.
1 reply →
Doesn't postgres (rightly) have a cow if a process has a disorderly shutdown (at least while in a write transaction) because there's shared memory between the processes?
Some see a 30 year old system and think "outdated", I see a 30 year old system and think "time tested."
Clearly a process per connection is more stable and that's what I'm using.
It's unclear what problem such optimizations are solving anyway, with the old way you could only support a million concurrent users with a single server? Are we missing out on supporting ten million concurrent users with 2 servers instead of 10? Ostensibly reducing the minimum db hardware opex for a 10B$ company from 10k$/month to 2k$/month?
A million users? Hell, I'd bet 99.999% of live postgres databases in existence serve less than 5 users on average. Even among products that actually make a profit, I bet 99.9% of them serve less than 100 customers a day. We hooligans on hacker news manage the 0.1% of databases, and in my newfound consulting life, I'm hoping to never support one of those again.
> Clearly a process per connection is more stable
Even if those processes share most of their memory, and are written in a notoriously memory-unsafe language?
A significant performance improvement can well be the difference between being able to run the entire database on one beefy server, and having to shard. And that has a huge cost in terms of complexity and thus reliability and development time.
Is that a serious issue? Wouldn't it just restart the split second later? Or does it take a long time to start?
(Or I guess it would get stuck in a doom loop or something?)
I'm not informed of the Postgres's internals, but, maybe, that can be solved by grouping threads into different processes depending on which set of extensions they request.
A more modern way to do this might be to support WebAssembly plugins.
The extensions might need to be rewritten, but hey, we have AI for that now, so why not :-)
An OS thread per connection can be fine for performance if you don't have to scale your connections, but if you don't need to scale connections why have connections at all? Databases are even more performant when you eliminate connection overhead entirely.
Thread pools