14 ms·
PostgreSQL reconsiders its process-based model
- mihaic 3y agoI'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 real knowledge of the complexity of the code and wish the Postgres devs all the luck in the world to get this working smoothly.
- loeg 3y agoYeah. 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.
- mihaic 3y agoExactly, if you're now forced to put everything in one place you're forced to acknowledge and understand the complexity of your state, and might have incentives to simplify it.
- Sesse__ 3y agoHere's MySQL's all-session-globals-in-one-place-class: https://github.com/mysql/mysql-server/blob/8.0/sql/sql_class.h#L927 https://github.com/mysql/mysql-server/blob/8.0/sql/sql_class... I believe I can safely say that nobody acknowledges and understands the complexity of all state within that class, and that whatever incentives there may be to simplify it are not enough for that to actually happen. (It ends on line 4692)
- IshKebab 3y agoRight but that would still be true if they were globals instead. Putting all the globals in a class doesn't make any difference to how much state you have.
- Sesse__ 3y ago> Putting all the globals in a class doesn't make any difference to how much state you have. I didn't make any claims about the _amount_ of state. My claim was that “you're forced to acknowledge and understand the complexity of your state” (i.e., moving it all together in one place helps understanding the state) is plain-out wrong.
- IshKebab 3y agoIt's not wrong. Obviously putting it all in one place makes you consider just how much of it you have, rather than having it hidden away all over your code.
- stingraycharles 3y agoYes, it’s the most pragmatic and it’s only “awful” because it makes the actual problem visible. And would likely encourage slowly refactoring code to handle its state in a more sane way, until you’re only left with the really gnarly stuff, which shouldn’t be too much anymore and you can put them in individual thread local storages. It’s an easy transition path.
- cakoose 3y ago> I think as a straightforward, easily correct transition from 2000 globals, a giant structure isn't an awful idea. Agree. > It's not like the globals were organized before! Using a struct with 2000 fields loses some encapsulation. When a global is defined in a ".c" file (and not exported via a ".h" file), it can only be accessed in that one ".c" file, sort of like a "private" field in a class. Switching to a single struct would mean that all globals can be accessed by all code. There's probably a way to define things that allows you to regain some encapsulation, though. For example, some spin on the opaque type pattern: https://stackoverflow.com/a/29121847/163832 https://stackoverflow.com/a/29121847/163832
- pasc1878 3y agoNo that is what a static in a .c file is for. A plain global can be accessed from other compiled units - agreed with no .h entry it is my=uch more error prone e.g. you don't know the type but the variables name is exposed to other objects
- remexre 3y agoWouldn't those statics also be slated for removal with this change?
- junon 3y agoAt most they'd be determined to be read only constants that are inlined during constant folding. This includes most integral sized / typed scalar values that fit into registers for the most part, and nothing you've taken the address of either - those remain as static data.
- cakoose 3y agoI think there might be a terminology mix-up here. In C, a global variable with the `static` keyword is is still mutable. So it typically can't be constant-folded/inlined. The `static` modifier in that context just means that the symbol is not exported, so other ".c" files can't access it.
- cogman10 3y agoI 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).
- dfox 3y agoAll postgres processes run under the same system user and all the access checking happens completely in userspace.
- fdr 3y agoAccess checking, yes, but the scope of memory corruption does increase unavoidably, given the main thing the pgsql-hackers investigating threads want: one virtual memory context when toggling between concurrent work. Of course, there's a huge amount of shared space already, so a willful corruption can already do virtually anything. But, more is more.
- magicalhippo 3y agoWe did this with a project I worked on. I came on after the code was mature. While we didn't have 2000 globals, we did have a non-trivial amount, spread over about 300kLOC of C++. We started by just stuffing them into a "context" struct, and every function that accessed a global thus needed to take a context instance as a new parameter. This was tedious but easy. However the upside was that this highlighted poor architecture. Over time we refactored those bits and the main context struct shrunk significantly. The result was better and more modular code, and overall well worth the effort in our case, in my opinion.
- saulrh 3y agoAlso, even if a 2k-member structure is obnoxious, consider the alternative - having to think about and manage 2k global variables is probably even worse!
- megous 3y agoEach set of globals is in a module it relates to, not in some central file where everything has to be in one struct. If anything, it's probably easier to understand.
- paulddraper 3y ago> 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.
- 36364949thrw 3y ago> 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.
- datavirtue 3y agoYeah, and you will run headlong into other unforseen real world issues. You may never reach the performance goals.
- shepardrtc 3y agoI 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 writing them to an order book that used a custom data structure, and multiple other processes reading from that data structure. Ran well enough that trade decisions could be made in a few thousand nanoseconds on an average EC2 instance. Not sure what their latency requirements are, though I imagine they may need to be faster. Obviously mutexes are the bottleneck for them at this point, and while my idea might be a bit slower than a low-load situation, perhaps it would be faster when you start getting to higher load.
- hamandcheese 3y agoI think the Actor model is fine if you start there, but I can't imagine incrementally adopting it in a large, preexisting code base.
- ilyt 3y agoThat would most likely be several times slower than current model
- MuffinFlavored 3y ago> if the hierarchy inside them is well organized is this another way to say "in a 2000 member structure, only 10 have significant voting power"?
- Ankhers 3y agoThis statement is not about people, it is about a C struct.
- topspin 3y ago> 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 multithreading carries a large amount of risk. PostgreSQL developers will have to level up substantially to pull it off. A PostgreSQL endorsed "second-system" with the (likely impossible, but close enough that it wouldn't matter) goal of 100% client compatibility could be a better approach. Adopting a memory safe language would make this both tractable and attractive (to both developers and users.) The home truth is that any "new process model" effort would actually play out exactly this way, so why not be deliberate about it?
- atonse 3y agoWould 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?
- topspin 3y agoBoth, 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.
- nextaccountic 3y agoFrom 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
- topspin 3y agoAs does Oracle, and others. I'm aware. IPC, to me, includes the conventional shared memory resources (memory segments, locks, semaphores, condition variable, etc.) used by these systems: resources acquired by processes for the purpose of communication with other processes. I get it though. The most general concept of shared memory is not coupled to an OS "process." You made me question whether my concept of term IPC was valid, however. So what does one do when a question appears? Stop thinking immediately and consult a language model! Q: Is shared memory considered a form of interprocess communication? GPT-4: Yes, shared memory is indeed considered a form of interprocess communication (IPC). It's one of the several mechanisms provided by an operating system to allow processes to share and exchange data. ... Why does citing ChatGPT make me feel so ugly inside?
- comboy 3y agoI'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.
- sargun 3y agoWith 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.
- paulddraper 3y agoDepends 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.
- Diggsey 3y agoIt'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).
- ComputerGuru 3y ago> solvable without a full switch to a threaded model (eg. by having pooling built-in and session-state-aware). Yeeeeesssss, but solving that is solving the hardest part of switching to a threaded model. It requires the team to come terms with the global state and encapsulating session state in a non-global struct.
- FooBarWidget 3y agoI don't get it. How is a 2000-member structure any different from having 2000 global variables? How is maintaining the struct possibly harder than maintaining the globals? Refactoring globals to struct members is semantically nearly identical, it may as well just be a mechanical, cosmetic change, while also giving the possibility to move to a threaded architecture.
- ComputerGuru 3y agoBecause global variables can be confined to individual cpp files, exclusively visible in that compilation unit. It makes them far easier to reason with than hoisting them to the "global and globally visible" option if you just use a gargantuan struct. Which is why a more invasive refactor might be required.
- imtringued 3y agoJust use thread local variables. I abuse them for ridiculous things.
- ComputerGuru 3y agoYeah, I was really into that before there was even a cross-compiler/cross-platform syntax for declaring TLS values in C++ but have since “upgraded” to avoiding TLS altogether where possible. The quality of the implementations vary greatly from compiler and platform to compiler and platform, you run into weird issues with thread_at exit if they’re not primitive types, they run afoul of any fibers/coroutines/etc that have since become extremely prevalent, and a few other things.
- jeltz 3y agoThat is the plan for PostgreSQL.
- menaerus 3y agoThread locals are both blessing and a curse - the problem with them is that you have no lifetime control over such variables.
- hans_castorp 3y ago> I'm honestly surprised it took them so long to reach this conclusion. Oracle also uses a process model on Linux. At some point (I think starting with 12.x), it can now be configured on Linux to use a threaded model, but the default is still a process-per-connection model. Why does everybody think it's a bad thing in Postgres, but nobody thinks it's a bad thing in Oracle.
- patmorgan23 3y agoWell for one Postgress is open source and widely used. So anyone can pick it up and look at its internals, that's not the case for Oracle DB .
- rsaxvc 3y agoThis is how I made my fork of libtcc lock-free. Mainline has a lock so that all backends can use global variables, but only one instance can do codegen at a time. It was a giant refactoring Especially fun was when multiple compilation units used the same static variable name, but it all worked in the end.
- 72deluxe 3y agoOut of curiosity, where is this fork? Sounds very interesting.
- rsaxvc 3y agohttps://github.com/rsaxvc/tinycc-multithreaded https://github.com/rsaxvc/tinycc-multithreaded This is the multi-threaded compiler: https://github.com/rsaxvc/tcc-swarm https://github.com/rsaxvc/tcc-swarm With the multi-threaded tcc above it scales about as well as multiprocess. With mainline it doesn't scale well at all. So far I haven't gotten around to reusing anything across libtcc handles/instances, but would eventually like to share mmap()'d headers across instances, as well as cache include paths, and take invocation arguments through stdin one compilation unit per line.
- ExoticPearTree 3y agoI don't see the problem. All variables are either set in config or at runtime and then for every new query they are read and used by PostgreSQL (at least this is my understanding). Regarding the threading issue, I think you can do the connections part multithreaded instead of one process per connection and still use IPC between this and postmaster. Because of the way PostgreSQL currently works, seems feasible to move parts one by one into a threaded model and instead of tens/hundreds of processes you can have just a few and a lot of threads. Honestly, they should prototype it and see how it looks like and then decide on the way forward.
- Icathian 3y agoSome interesting discussion on this here also: https://news.ycombinator.com/item?id=36284487 https://news.ycombinator.com/item?id=36284487
- RcouF1uZ4gsC 3y agoThis would be one those places where a language like Rust would be helpful. In C/C++ with undefined behavior and crashes, process isolation makes a lot of sense to limit the blast radius. Rust borrow checker gives you at compile time a lot of the safety that you would rely on process isolation for.
- mattashii 3y agoYes, but note that the blast radius of a PostgreSQL process crash is already "the whole system reboots", so there are not a lot of differences between process- and thread-based PostgreSQL written in C. Rewriting in Rust would be interesting, but it would also probably be too invasive to make it worthwile at all - all code in PostgreSQL is C, while not all code in PostgreSQL interacts with the intrinsics of processes vs threads. Any rewrite to Rust would likely take several times more effort than a port to threads.
- megous 3y agoPostgreSQL process crash may also just be, one query fails.
- haburka 3y agoThat sounds like really hard programming. I’m glad I write react and get paid possibly much more.
- paulddraper 3y agoWe're glad you're writing React too :)
- Exuma 3y agoI feel this sort of undertaking could only be done by those programmers who truly value domain knowledge above all else (money, etc). I'm more of the entrepreneureal mind so I generally only learn as much as needed to do some task (even if it's very difficult), but just seeking information as a means to an end doesn't feel fulfilling to me. Of course many people DO find that, and its upon those people's shoulders that heroic things like this rest, and I'm very thankful to them.
- rbancroft 3y agoChanging something so fundamental seems like it should be a rewrite.
- baggy_trough 3y agoI hope they are conservative about this, because even the smartest and best programmers in the world cannot create bug free multithreaded code.
- jrott 3y agoThe fact that they are planning on doing this across multiple releases gives me hope that they'll be cautious with this.
- jerf 3y agoI mentally snarked to myself that "obviously they should rewrite it in Rust first". Then, after more thought, I'm not entirely sure that would be a bad approach. I say this not to advocate for actually rewriting it in Rust, but as a way of describing how difficult this is. I'm not actually sure rewriting the relevant bits of the system in Rust wouldn't be easier in the end, and obviously, that's really, really hard. This is really hard transition. I don't think multithread code quality should be measured in absolutes. There are things that are so difficult as to be effectively impossible, which is the lock-based approach that was dominant in the 90s, and convinced developers that it's just impossible difficult, but it's not multithreaded code that's impossibly difficult, it's lock-based multithreading. Other approaches range from doable to even not that hard once you learn the relevant techniques (Haskell's full immutability & Rust's borrow checker are both very solid), but of course even "not that hard" becomes a lot of bugs when scaled up to something like Postgres. But it's not like the current model is immune to that either.
- xwdv 3y agoNonsense, multithreaded code can be written as bug free as regular code. No need to fear.
- dboreham 3y agoThis is true. However, the blast radius may be smaller with a process model. Also recovering from a fatal error in one session could possibly be easier. I say this as a 30-year threading proponent.
- 3y ago
- lukeschlather 3y agoThis 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.
- narrator 3y agoThe 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 approach to testing including out of memory conditions, and other rare circumstances: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- lukeschlather 3y agoParallelism is often incredibly hard to write automated tests for, and this will most likely create parallelism issues that were not dreamed of by the authors of the test suite.
- abalashov 3y ago> 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. :)
- cactusfrog 3y agoThis 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.
- masklinn 3y ago
- deleted 3y ago[deleted]
- EGreg 3y agoI hope they don't do it. I've had a similar situation with PHP, where we had written quite a large engine (https://github.com/Qbix/Platform https://github.com/Qbix/Platform) with many features (https://qbix.com/features.pdf https://qbix.com/features.pdf) . It took advantage of the fact that PHP isolated each script and gave it its own global variables, etc. In fact, much of the request handling did stuff like this: Q_Request::requireFields(['a', 'b', 'c']); $uri = Q_Dispatcher::uri(); instead of stuff like this: $this->getContext()->request()->requireFields(['a', 'b', 'c']); $this->getContext()->dispatcher()->uri(); Over the last few years, I have run across many compelling things: amp reactPHP Swoole (native extension) Fibers (inside PHP itself) It seemed so cool! PHP could behave like Node! It would have an event loop and everything. Fibers were basically PHP's version of Swoole's coroutines, etc. etc. Then I realized... we would have to go through the entire code and redo how it all works. We'd also no longer benefit from PHP's process isolation. If one process crapped out or had a memory leak, it could take down everything else. There's a reason PHP still runs 80% of all web servers in the world (https://kinsta.com/blog/is-php-dead/ https://kinsta.com/blog/is-php-dead/) ... and one of the biggest is that commodity servers can host terrible PHP code and it's mostly isolated in little processes that finish "quickly" before they can wreak havoc on other processes or on long-running stuff. So now back to postgres. It's been praised for its rock-solid reliability and security. It's got so many features and the MVCC is very flexible. It seems to use a lot of global variables. They can spend their time on many other things, like making it byzantine-fault-tolerant, or something. The clincher for me was when I learned that php-fpm (which spins up processes which sleep when waiting for I/O) is only 50% slower than all those fancy things above. Sure, PHP with Swoole can outperform even Node.js, and can handle twice as many requests. But we'd rather focus on soo many other things we need to do :)
- zackmorris 3y agoI've been using PHP for decades and have found its isolated process model to be about the best around, certainly for any mainstream language. Also Symfony's Process component encapsulates most of the errata around process management in a cross-platform way: https://symfony.com/doc/current/components/process.html https://symfony.com/doc/current/components/process.html Going from a working process implementation to async/threads with shared memory is pretty much always a mistake IMHO, especially if it's only done for performance reasons. Any speed gains will be eclipsed by endless whack-a-mole bug fixes, until the code devolves into something unrecognizable. Especially when there are other approaches similar to map-reduce and scatter-gather arrays where data is processed in a distributed fashion and then joined into a final representation through mechanisms like copy-on-write, which are supported by very few languages outside of PHP and the functional programming world. The real problem here is the process spawning and context-switching overhead of all versions of Windows. I'd vote to scrap their process code in its entirety and write a new version based on atomic operations/lists/queues/buffers/rings with no locks and present an interface which emulates the previous poor behavior, then run it through something like a SAT solver to ensure that any errata that existing software depends on is still present. Then apps could opt to use the direct unix-style interface and skip the cruft, or refactor their code to use the new interface. Apple did something similar to this when OS X was released, built on a mostly POSIX Darwin, NextSTEP, Mach and BSD Unix. I have no idea how many times Microsoft has rewritten their process model or if they've succeeded in getting performance on par with their competitors (unlikely). Edit: I realized that the PHP philosophy may not make a lot of sense to people today. In the 90s, OS code was universally terrible, so for example the graphics libraries of Mac and Windows ran roughly 100 times slower than they should for various reasons, and developers wrote blitters to make it possible for games to run in real time. That was how I was introduced to programming. PHP encapsulated the lackluster OS calls in a cross-platform way, using existing keywords from popular languages to reduce the learning curve to maybe a day (unlike Perl/Ruby, which are weird in a way that can be fun but impractical to grok later). So it's best to think of PHP more like something like Unity, where the nonsense is abstracted and developers can get down to business. Even though it looks like Javascript with dollar signs on the variables. It's also more like the shell, where it tries to be as close as possible to bare-metal performance, even while restricted to the 100x interpreter slowdown of languages like Python. I find that PHP easily saturates the processor when doing things in a data-driven way by piping bytes around.
- tragomaskhalos 3y agoWorked 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 variable and referencing it by offset the whole time. Premature optimisation? Optimisation at all? Who knows, but there's a good reason coding standards typically militate against globals.
- ajkjk 3y agoThere'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.
- JdeBP 3y agoPer discussion on this very page, in the headlined article, and in the mailing list discussion it references, PostgreSQL is not in that category. It has lots of static storage duration variables, which do not necessarily have external linkage. Robert Haas pointed out in one message that an implementation pattern was to use things like file-scope static storage duration variables to provide session-local state for individual components. This is why they've been arguing against a single giant structure declared in "session.h" as an approach, as it requires every future addition to session state to touch the central core of the entire program. They want to keep the advantage of the fact that these variables are in fact not global. They are local; and the problem is rather that they have static storage duration and are not per-thread, and thus are not per-session in a thread-per-session model.
- TomMasz 3y agoThere are 2000 globals here, so more like a couple of shedloads. While this is something you'd sort of expect for a product that's been around 30+ years, it really seems like there's a lot of optimization that could happen and still stick with the process model.
- thecopy 3y agoThis feels like developers are bored and want a challenge.
- dboreham 3y agoIt's a multi-decade ask from many PG users and a serious pain point for many deployments.
- deleted 3y ago[deleted]
- ceeam 3y agoIs there any reason at all people use intrinsically bug-prone and broken multithreading mode instead of fork() and IPC apart from WinAPI having no proper fork?
- adwn 3y agoYes, and some of those reasons are even listed in the article.
- ceeam 3y agoTLB misses? They are just a detail of particular CPU implementation, and the architectures change. Also, aren't they per core and not per process? What would that solve then to switch to MT?
- adwn 3y ago> TLB misses? They are just a detail of particular CPU implementation, and the architectures change. TLBs are "just a detail" of roughly 100% of server, desktop, and mobile CPUs. > Also, aren't they per core and not per process? What would that solve then to switch to MT? TLB entries are per address space. Threads share an address space, processes do not.
- BoardsOfCanada 3y agoI recently looked through the source code of postgresql and every source files starts with a (really good) description of what the file is supposed to do, which made it really easy to get in to the code compared to other open source projects I've seen. So thanks for that.
- nonethewiser 3y agoUncle Bob hates this.
- Alekhine 3y agoI have no idea why that isn't standard practice in every codebase. I should be able to figure out your code without having to ask, or dig through issues or commit messages. Just tell me what it's for!
- SoylentOrange 3y agoBecause it takes a lot of time and because the comments can get outdated. I also want this for all my code bases. But do I always do this myself? No, especially on green field projects. I will sometimes go back and annotate them later.
- mhh__ 3y agoThey can get outdated but they usually don't. It's a good litmus test for if a file is too big / small if it's purpose is hard to nail down.
- akira2501 3y agoTrying to understand what I previously wrote and why I wrote it takes more time than I ever care to spend. I'd much rather have the comments, plus at this point, by making them a "first class" part of my code, I find them much easier to write and I find the narrative style I use incredibly useful in laying out a new structure but also in refactoring old ones.
- withinboredom 3y ago
- chasil 3y agoOracle has similar problems. On UNIX systems, Oracle uses a multi-process model, and you can see these: $ ps -ef | grep smon USER PID PPID STARTED TIME %CPU %MEM COMMAND oracle 22131 1 Mar 28 3:09 0.0 4.0 ora_smon_yourdb Windows forks processes about 100x slower than Linux, so Oracle runs threaded on that platform in one great big PID. Sybase was the first major database that fully adopted threads from an architectural perspective, and Microsoft SQL Server has certainly retained and improved on that model.
- EvanAnderson 3y ago> 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.
- devit 3y agoUse pgbouncer
- ethbr0 3y agoWas 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.
- ddorian43 3y ago> We're really talking about X-per-client as the primary reason to move away from processes, right? Many other things too. Like better sharing of caches. Lower overhead of thread instead of process. Etc. (read the thread)
- js4ever 3y agoFinally! This and a good multi master story and I'll finally start to love Postgres
- CodeWriter23 3y ago"no objections" <> "consent"
- ed25519FUUU 3y agoHave you ever tried to move a large organization forward in a certain direction? It’s really hard. At some point you have to make a decision.
- timcobb 3y agoNot in something like Postgres, I hope
- CodeWriter23 3y agoI have. What I've observed more is outside attackers with their own agenda use the "nobody objected because they were unprepared and unable to respond in the 2 minutes I gave them to object" as proof their agenda is supported.
- sargun 3y agoI'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.
- why-el 3y agowhat about support for Windows?
- nneonneo 3y agoPer the article, Postgres has many, many global variables, many of which track per-session state; much session state is “freed” via process exit rather than being explicitly cleaned up. Switching to CLONE_VM requires these problems to all be solved.
- chucky_z 3y agoI 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.
- levkk 3y agoPretty 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.
- BoardsOfCanada 3y agoFrom the article: > Tom Lane said: "I think this will be a disaster. There is far too much code that will get broken". He added later that the cost of this change would be "enormous", it would create "more than one security-grade bug", and that the benefits would not justify the cost.
- timcobb 3y agoYou can think of this as an opportunity to rewrite in Rust.
- tracker1 3y agoAfterPostgres
- krylon 3y agoMay I humbly suggest PostPostgres, or Post²gres?
- tomjakubowski 3y ago2Post2Furigres
- moi2388 3y ago
- uudecoded 3y agoI am going to go ahead and trust Tom Lane on this one, over someone who is working on "serverless Postgres". Godspeed to the forthcoming fork.
- aseipp 3y agoHeikki Linnakangas is one of the top Postgres contributors of all time, he isn't just "someone." The fact he's working for a startup on a fork (that already exists, which you can run right now on your local machine) doesn't warrant any snide dismissal. Robert Haas admitted that it would be a huge amount of work and that it would only be achievable by a small few people anyway, Heikki being among them. Anyway, I think there are definitely limits that are starting to appear with Postgres in some spots. This is probably one of the most difficult possible solutions to some of those problems, but even if they don't switch to a fully threaded model, being more CPU efficient, better connection handling, etc will all go a substantial way. Doing some of the really hard work is better than none of it, probably.
- avidphantasm 3y agoI wonder what AWS’s PostgreSQL-compatible Aurora looks like under the hood. Does it use threading, processes, both?
- shariat 3y agothis will be the beginning of the end of postgres
- eclipticplane 3y agoFor the record, I think this will be a disaster. There is far too much code that will get broken, largely silently, and much of it is not under our control. regards, tom lane (via https://lwn.net/ml/pgsql-hackers/4178104.1685978307@sss.pgh.pa.us/ https://lwn.net/ml/pgsql-hackers/4178104.1685978307@sss.pgh....) If Tom Lane says it will be a disaster, I believe it will be a disaster.
- idiomaticrust 3y agoHe is right. Such rewrites cause a lot of problems if your compiler doesn't help you with avoiding data races. But there is another way.
- hnarn 3y ago> But there is another way. Ok?
- mycall 3y agoMicrosoft SQL Server has SQLOS which is another way [0]. [0] https://www.thegeekdiary.com/what-is-sql-server-operating-system-sqlos/ https://www.thegeekdiary.com/what-is-sql-server-operating-sy...
- carstenhag 3y agoThe person probably implied that Postgres should switch to another toolchain that guarantees more things at compile time, so probably Rust.
- jupp0r 3y agoPlease 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.
- slashdev 3y agoThank you for sharing your ideological views, but this is not the appropriate venue for that. If you want to have a software _engineering_ discussion about the trade offs involved in sharing global mutable state, this is a good venue for that. All engineering is trade offs. As soon as you make blanket statements that X is always bad, you’ve transitioned into the realm of ideology. Now presumably you mean to say it’s almost always bad. But that really depends on the context. It may well be almost always bad in average software projects, but PostgreSQL is not your average software project. Databases are a different realm.
- refulgentis 3y agoGlobal mutable state being a poor choice in software architecture isn’t an ideology. There is no ideology that argues it is awesome. If you want to have a software _engineering_ discussion about the trade offs involved in sharing global mutable state, this is a good venue for that. All engineering is trade offs. As soon as you start telling people they’re making blanket statements that X is always bad, you’ve transitioned into the realm of nitpicking.
- megous 3y agoUsing globals is simpler, it's also pretty natural in event driven architectures. Passing everything via function arguments is welcome for library code, but there's little point to using it in application code. It just complicates things.
- jupp0r 3y agoThe problems it causes for Postgres are outlined in the article on LWN.
- formerly_proven 3y agongmi
- wielebny 3y agoHaving been using and administering a lot of PostgreSQL servers, I hope they don't lose any stability over this. I've seen (and reported) bugs that caused panics/segfaults in specific psql processes. Not just connections, also processes related to wal writing or replication. The way it's built right now, a child process can be just forced to quit and it does not affect other processes. Hopefully switching into thread won't force whole PostgreSQL to panic and shut down.
- vbezhenar 3y agoOf 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.
- wizofaus 3y agoYou'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-process model doesn't necessarily introduce problems that could never have existed previously (but, yes, would arguably make them more likely)
- SigmundA 3y agoNo 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.
- 3y ago
- mynonameaccount 3y ago"the benefits would not justify the cost". PostgreSQL, like any software, at some point in it's life need to be refactored. Why not refactor with a thread model. Of course there will be bugs. Of course it will be difficult. But I think it is a worthwhile endeavor. Doesn't sound like this will happen but a new project would be cool.
- timtom39 3y ago> like any software, at some point in it's life need to be refactored. This is simply not true for most software. Software has a product life cycle like everything else and major refactors/rewrites should be weighed carefully against cost/risk of the refactor. Many traditional engineering fields do much better at this analysis. Although, because I run a contracting shop, I have personally profited greatly by clients thinking this is true and being unable to convince them otherwise.
- smsm42 3y ago"Difficult" doesn't even begin to do it justice. Making a code which has 2k global variables and probably order of magnitude as many underlying assumptions (the code should know that now every time you touch X you may be influenced or influence all other threads that may touch X) is a gargantuan task, and will absolutely for sure involve many iterations which any sane person would never let anywhere near valuable data (and how long would it take until you'd consider it safe enough?). And making this all performant - given that shared-state code requires completely different approach to thinking about workload distribution, something that performs when running in isolated processes may very well get bogged down in locking or cache races hell when sharing the state - would be even harder. I am not doubting Postgres has some very smart people - much, much smarter than me, in any case - but I'd say it could be more practical to write new core from scratch than trying to "refactor" the core that organically grew for decades with assumptions of share-nothing model.
- djur 3y agoWhat you're talking about is a rewrite, not a refactor.
- gremlinsinc 3y ago
- 0xbadcafebee 3y agoSo compromise. Take the current process model, add threading and shared memory, with feature flags to limit number of processes and number of threads. Want to run an extension that isn't threadsafe? Run with 10 processes, 1 threads. Want to run high-performance? Run with 1 process, 10 threads. Afraid of "stability issues"? Run with 1 process, 1 thread. Will it be hard to do? Sure. Impossible? Not at all. Plan for it, give a very long runway, throw all your new features into the next major version branch, and tell people everything else is off the table for the next few years. If you're really sure threading is going to be increasingly necessary, better to start now than to wait until it's too late. But this idea of "oh it's hard", "oh it's dangerous", "too complicated", etc is bullshit. We've built fucking spaceships that visit other planets. We can make a database with threads that doesn't break. Otherwise we admit that basic software development using practices from the past 30 years is too much for us to figure out.
- newaccount74 3y agoA big advantage of the process-based model is its resilience against many classes of errors. If a bug in PostgreSQL (or in an extension) causes the server to crash, then only that process will crash. Postmaster will detect the child process termination, and send an error message to the client. The connection will be lost, but other connections will be unaffected. It's not foolproof (there are ways to bring the whole server down), but it does protect against many error conditions. It is possible to trap on some exceptions in a threaded environment, but cleaning up after eg. an attempted NULL pointer dereference is going to be very difficult or impossible.
- anarazel 3y agoWe would still have a separate supervisor process of we moved connections to threads.
- t43562 3y agoCalm down guys! Threading is tricky but they can rewrite it all in Rust so it'll be completely ok........ ;-)
- pjmlp 3y agoSo when we as a whole decided that multiprocessing is a much better approach from security and application stability point of view, they decide to go with threads?
- blinkingled 3y agoHorses for courses I guess - purely threaded vs purely MP both have different set of tradeoffs and shoehorning one over the other always fails some use cases. The article says they are also considering the possibility of having to keep both process and thread models indefinitely for this and other reasons. I know nothing of PG internals but I can see why process per connection model doesn't work for large machines and/or high number of connections. One way to do it would be to keep connection handling per thread and still keep multiprocess approach where it makes sense for security and doesn't add linear overheads.
- rburhum 3y agoSorry 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 bring the whole cluster down because it hit a bug sounds scary. Every try creating a spatial index on thousands/millions of records that have nasty overly complex or badly digitized geometries? Sadly, crashes are part of that workflow, and changing this from process to threading would mean all the other clients also crashing and cutting connections. This as a potential problem because I want to avoid context switching overhead or cache misses, no thanks.
- zeroimpl 3y agoHowever, 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 moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing
- niccl 3y agoyes, 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
- jtc331 3y agoIf you read the thread you’d see the discussion includes still having e.g. postmaster as a separate process.
- moonchrome 3y agoRestart on crash doesn't sound that difficult to do.
- jasonhansel 3y agoI'm rather surprised that their focus is on improving vertical scalability, rather than on adding more features for scaling Postgres horizontally.
- tracker1 3y agoIf you're more interested in horizontal scaling, you may want to look into CockroachDB, which has a Postgres compatible protocol, but still quite different. There are a lot more limitations with CDB over Pg though. With the changes suggested, I'm not sure it's the best idea from where Postgres is... if might be an opportunity to rewrite bits in Rust, but even then, there is a LOT that can go wrong. The use of shared memory is apparently already in place, and the separate process and inter-process communication isn't the most dangerous part... it's the presumption, variables and other contextual bits that are currently process globals that wouldn't be in the "after" version. The overall surface is just massive... That doesn't even get into plugin compatibility.
- ryanmargono 3y agoIsn't this the nature of relational databases (as per CAP)?
- bb88 3y agoThis reminds me of this poster: "You must be this tall..." https://bholley.net/blog/2015/must-be-this-tall-to-write-multi-threaded-code.html https://bholley.net/blog/2015/must-be-this-tall-to-write-mul... Back about a decade ago I was "auditing" someone else's threaded code. And couldn't figure it out. But he was the company's "golden child" so by default it must be working code because he wrote it. And then it started causing deadlocks in prod. "What do you want me to do about it? It's the golden child's code. He's not even gonna show up til 2pm today."
- wmf 3y agoThe thing is... multi-process with a bespoke shared memory system isn't better than multithreading; it's much worse.
- anarazel 3y agoI'm not sure if I'd judge it as harshly, but you have a good point: A lot of debugging / validation tooling understands threads, but not memory shared between processes.
- throwawaylinux 3y agoBy bespoke you mean using standard interfaces to create shared memory pools? They do roll some of their own locking primitives, but that's not particularly unusual in a large portable program (and quite likely what they wanted is/was not available in glibc or other standard libraries, at least when first written).
- __turbobrew__ 3y agoIn Linux, multi process with shared memory regions is basically just threads. The kernel doesn’t know anything about threads, it knows about processes and it lets you share memory regions between those processes if you so desire.
- paulddraper 3y agoIt kinda is though. The process barrier is better at enforcing careful deliberate interactions
- mastax 3y agoIt would be interesting to have something between threads and processes. I'll call them heavy-threads for sake of discussion. Like light-threads, heavy-threads would share the same process-security-boundary and therefore switching between them would be cheap. No need to flush TLB, I$, D$. Like processes, heavy-threads would have mostly-separate address spaces by default. Similar to forking a process, they could share read-only mappings for shared libraries, code, COW global variables, and explicitly defined shared writable memory regions. Like processes, heavy-threads would isolate failure states. A C++ exception, UNIX signal, segfault, etc. would kill only the heavy-thread responsible.
- dasyatidprime 3y agoSo what would be different between those and forked processes?
- ShroudedNight 3y agoI've been pondering / ruminating with this too; I've been somewhat surprised that few operating systems have played with reserving per-thread address space as thread-local storage, or requiring something akin to a 'far' pointer to access commonly-addressed shared memory.
- wbl 3y agoYou cannot COW and share the TLB state. The caches aren't flushed in process changes either: it's that the data is different so evictions happen.
- mattashii 3y ago> No need to flush TLB TLB isn't "flushed" so much as it is useless across different memory address spaces. Switching processes means switching address spaces, which means you have to switch the contents of the TLB to the new process' TLB entries, which eventually indeed flushes the TLB, but that is only over time, not necessarily the moment you switch processes. > Like processes, heavy-threads would have mostly-separate address spaces by default. This thus conflicts with the need to not flush TLBs. You can't not change TLB contents across address spaces.
- 3y ago
- waselighis 3y agoIt sounds to me like migrating to a fully multi-threaded architecture may not be worth the effort. Simply reducing the number of processes from thousands to hundreds would be a huge win and likely much more feasible than a complete re-architecture.
- papito 3y agoThis has Python 3 vibes.
- deleted 3y ago[deleted]
- gjvc 3y agosurely this is "Some guy reconsiders the process-based model of PostgreSQL"
- Icathian 3y agoUh. Heikki is definitely not just "some guy". Dude is one of the top contributors to Postgres.
- gjvc 3y agoHow does that make him immune to having dumb ideas? See, I'm judging the idea on merit. You're just defending your hero who has gone rogue.
- anarazel 3y agoHeikki is far from the only "senior" postgres contributor thinking that this is the right direction in the long term. > You're just defending your hero who has gone rogue. That doesn't sound like judging an idea on its merit, it sounds like judging a person for something you haven't analyzed yourself.
- gjvc 3y ago> Heikki is far from the only "senior" postgres contributor thinking that this is the right direction in the long term. sounds like groupthink
- Icathian 3y agoI must have missed all the nuanced judgment in your original post. Maybe you can quote some for me.
- gjvc 3y agonot rising to this sarcastic bait.
- cmrdporcupine 3y agoIt sounds like the specific concerns here are actually around buffer pool management performance in and around the TLB: "Once you have a significant number of connections we end up spending a *lot* of time in TLB misses, and that's inherent to the process model, because you can't share the TLB across processes. " Many of the comments here seem to be missing this and talking about CPU-boundedness generally and thread-per-request vs process etc models, but this seems orthogonal to that, and is actually quite specific about the VM subsystem and seems like a legitimate bottleneck with the approach Postgres has to take for buffer/page mgmt with the process model it has now. I'm no Postgres hacker (or a Linux kernel hacker), and I only did a 6 month stint doing DB internals, but it feels to me like perhaps the right answer here is that instead of Postgres getting deep down in the weeds refactoring and rewriting to a thread based model -- with all the risks in that that people have pointed out -- some assistance could be reached for by working on specific targeted patches in the Linux kernel? The addition of e.g. userfaultfd shows that there is room for innovation and acceptance of changes in and around kernel re: page management. Some new flags for mmap, shm_open, etc. to handle some specific targeted use cases to help Postgres out? Also wouldn't be the first time that people have done custom kernel patches or tuning parameters to crank performance out of a database.
- thesnide 3y agoExactly my thinking. If the problem is TLB evictions, why not improve these? PG isn't the only software that is hit by those. And if you carefully craft your CPU scheduling and address spaces mapping, you can reduce them by a lot. Yet rewrites are always easier and more sexy. At first.
- anarazel 3y agoThe TLB issue are more a hardware issue than a software/OS one. To my knowledge neither x86 nor arm provide a way to partially share TLB contents between processes/entities. Tlb entries can be tagged with a process context identifier, but that's an all or nothing thing. Either the entire address space is shared, or it's not. > Yet rewrites are always easier and more sexy. At first. Moving to threads would not at all be a rewrite.
- bob1029 3y agoI think an interesting point of comparison is the latest incarnation of SQL Server. You can't even point at 1 specific machine anymore with their hyperscale architecture. https://learn.microsoft.com/en-us/azure/azure-sql/database/hyperscale-architecture?view=azuresql https://learn.microsoft.com/en-us/azure/azure-sql/database/h...
- benlivengood 3y agoThis is interesting because Google just created AlloyDb[0] which is decidedly multiprocess for performance and switches out the storage layer from a read/write model to write+replicate + read-only model. The deep dive[1] has some details; the tl;dr: is that the main process only has to output Write Ahead Logs to the durable storage layer which minimizes transaction latency. The log processing service materializes postgres-compatible on-disk blocks that read-only replicas can read from, with a caching layer for block reads which sends cache invalidations from the LPS to read replicas. I'm not sure if similar benefits could be seen within a single machine; using network DMA or even rDMA to transfer bytes to and from remote machines also avoids TLB invalidation. There are some mentions in the mailing list of waiting for Linux to support shared page mappings between processes as a solution. I'm not exactly sure I understand the reasoning behind process separation as crash recovery; as far as I understand each connection is responsible for correctness and so if a process crashes there seems to be an assumption that the database can recover and keep working by killing that process but that seems like it risks silent data corruption; perhaps it's equivalently mitigated by separating materialization of blocks from the sync'd WAL in a separate process from the multithreaded connection process producing WAL entries? [0] https://cloud.google.com/alloydb https://cloud.google.com/alloydb [1] https://cloud.google.com/blog/products/databases/alloydb-for-postgresql-intelligent-scalable-storage https://cloud.google.com/blog/products/databases/alloydb-for...
- beebmam 3y agoWhy change postgres? Just fork it if you want to change something this fundamental.
- anarazel 3y agoBecause it makes it a lot easier to address some of postgres' weaknesses? This is a proposal by long time contributor to postgres, that a number of other long time contributors agree with (and others disagree with!). Why shouldn't have Heikki brought this up for discussion?
- MR4D 3y agoChanging the entire architecture of PG to suit the 0.1% of edge cases seems like a poor trade off. Are there a much larger percentage of users that really need this?
- doctor_eval 3y agoI know I’m probably being naive about this, but is it stupid to ask if there’s a way to make multi process work better on Linux - rather than “fixing” PG? I feel like the thread vs process thing is one of those pendulums/fads that comes and goes. I’d hate to see PG go down a rabbit hole only to discover the OS could be modified to make things go better. (I understand not all PG instances run on Linux, just using it as an example)
- chmod775 3y ago> I feel like the thread vs process thing is one of those pendulums/fads that comes and goes. In this context threads can be understood as processes that share the same address space and vice-versa processes as threads with separate address space. One gives you isolation, the other convenience and performance. Either can be desirable. What would you change about this?
- dikei 3y agoThat'll likely be an even bigger task, and harder to get into mainline kernel. Linux multi-process is already pretty efficient compared to Windows. However, multi-process is inherently less efficient than multi-thread due to more safety predicates / isolation guaranteed by the kernel, I feel lowering it might lead to more security issues, similar to how Hyper Threading triggered a bunch of issues with Intel Processors.
- doctor_eval 3y agoRight - yeah I was really just wondering if some of the safety predicates could be reduced when there is a relationship between processes, such as the mitigations against cache attacks. I think the cache misses caused by multi-process were one of the reasons given that it's slower than threading. But I don't understand why this is necessarily the case given that the shared memory and executable text ultimately refer to the same data. But I suppose this would need to work with processor affinity and other elements to prevent the cache being knocked around by non-PG processes, and I guess this is one place where it starts getting complicated. That said, please understand that I'm just being curious - I really don't know what I'm talking about, I haven't built a Linux kernel or dabbled in Unix internals in like 20 years, but thanks for replying :) Postgresql is my favourite open source project and I'm spooked by the threading naysayers.
- MichaelMoser123 3y agoi wonder if gpt-11 will be able to do this kind of project on its own...
- est 3y agoNot sure what's going on here, but one connection per process seems... ancient. Using threaded model is difficult, how about pre-fork? Some connections per process is a good improvement.
- anarazel 3y agoThe issue is costlier (runtime, complexity, memory) resource sharing, not the cost of the fork itself. Pre-forking isn't going to help with any of that.
- Too 3y agoIsn’t this why pgbouncer is so effective? Maybe it’s not the forking itself, but there is something about creating connections that is expensive, warranting such external connection poolers.
- sbr464 3y agoUnpopular idea: age limit on votes/contributions. We are in a unique time period and generation that has strong opinions based on a history that only exists within itself. At some point it needs to rewrite.
- georgewfraser 3y agoI wonder if it would be easier to create a C virtual machine that emulates all the OS interaction, then recompile Postgres and the extensions to run on this. Perhaps TruffleC would work? https://dl.acm.org/doi/10.1145/2647508.2647528 https://dl.acm.org/doi/10.1145/2647508.2647528
- anarazel 3y agoHard to believe that would provide any benefit without also causing massive slowdowns.
- jpgvm 3y agoA close to impossible task, if anyone can do it's probably Heikki though. Unfortunately I expect this to go the way of zheap et al. Fundamental design changes like this have just had such a rough time of succeeding thus far. I think for such a change to work it probably needs not just the support of Neon but also of say Microsoft (current stewards of Citus) that have larger engineering resources to throw at the problem and grind out all the bugs.
- anarazel 3y agoI know that at least people from EDB (Robert) and Microsoft (Thomas, me) are quite interested in eventually making this transition, it's not just Heikki. Personally I won't have a lot of cycles for the next release or two, but after that...
- jpgvm 3y agoThat gives me some faith, I hope everyone is able to come together to make it happen.
- rashidujang 3y agoHey I'm fairly new to the who's who in the PostgreSQL world, would you mind telling why Heikki might be able to pull this off?
- anarazel 3y agoNot who you asked, but: He is a longtime contributor who has written/resigned important parts of postgres (WAL format, concurrent WAL insertion, 2PC support, parts of SSI support, much more). And he is just a nice person to work with.
- rashidujang 3y agoCool! He seems like a powerhouse in this space - thank you for the answer
- winrid 3y agoAt the end of the day, this doesn't solve any problems. Small setups use postgres directly just fine, and large setups use pgbouncer, and having process isolation with extensions is a good thing and probably simplifies things a lot. My $0.02
- neilwilson 3y agoIt's always amazed me with databases why they don't go the other way. Create an operating system specifically for the database and make it so you boot the database. Databases seem to spend most of their time working around the operating system abstractions. So why not look at the OS, and streamline it for database use - dropping all the stuff a database will never need. That then is a completely separate project which is far easier to get started rather than shoehorning the database into an operating system thread model that is already a hack of the process model.
- dialogbox 3y agoI'm not sure what you mean by OS. If you mean a whole new kernel, it will take decades. They can support only small number of HW. If you mean a specialized linux distro, many companies does that already. I don't know how that can make it easier the process based / thread based problem.
- rossmohax 3y agoThis project could borrow a lot from unikernels. If they mandate running it as a VM, there is no HW to support.
- girvo 3y agoThat was/is part of the promise of the whole unikernel thing, no? https://mirage.io/ https://mirage.io/ or similar could then let you boot your database. That said, it's not really taken off from what I can tell, so I'm guessing there's more to it than that.
- anarazel 3y agoImo unikernels are a complicated solution in search of a problem, which turns out to not exist. There certainly are times OSs get in the way. But it's hard enough to write a good database, we don't need to maintain a third of an OS in addition.
- 3y ago
- c00lio 3y agoWhy should TLB flush performance ever be a problem on big machines? You can have one process per core with 128 or more cores, never flush any TLB if you pin those processes. And as it is a database, shoveling data from/to disk/SSD is your main concern anyways.
- scottlamb 3y agoPostgreSQL uses synchronous IO, so you won't saturate the CPU with one process (or thread) per core. That said, I think there have been efforts to use io_uring on Linux. I'm not sure how that would work with the process per connection model. Haven't been following it...
- c00lio 3y agoProblem with all kinds of asynchronous I/O is that your processes then need internal multiplexing, akin to what certain lightweight userspace thread models are doing. In the end, it might be harder to introduce than just using OS threads.
- anarazel 3y ago> That said, I think there have been efforts to use io_uring on Linux. I'm not sure how that would work with the process per connection model. Haven't been following it... There's some minor details that are easier with threads in that context, but on the whole it doesn't make much of a difference.
- scottlamb 3y agoI don't understand how it works with thread per connection either. io_uring is designed for systems that have a thread and ring per core, for you to give it a bunch of IO to do at once (batches and chains), and your threads to do other work in the meantime. The syscall cost is amortized or even (through IORING_SETUP_SQPOLL) eliminated. If your code is instead designed to be synchronous and thus can only do one IO at a time and needs a syscall to block on it, I don't think there's much if any benefit in using io_uring. Possibly they'd have a ring per connection and just get an advantage when there's parallel IO going on for a single query? or these per-connection processes wouldn't directly do IO but send it via IPC to some IO-handling thread/process? Not sure either of those models are actually an improvement over the status quo, but who knows.
- cryptica 3y agoSeems like a bad idea. Processes are more elegant and scalable than threads as they discourage the use of shared memory. Shared memory is often a bad idea. You end up with different threads competing and queuing up to access or write the same data (e.g. waiting on each other to acquire a lock with mutexes - This immediately disqualifies the system from becoming embarrassingly parallel) and it becomes the OS's problem to figure out when to allow which thread to access what memory... This is bad because the OS doesn't care about optimizing memory access for your specific use case. It will treat your 'high performance' database in the same way as it treats a run-of-the-mill Gimp desktop application.... With the process model, it encourages using separate memory for each process; this forces developers to think about things like memory consistency and availability and gives them more flexibility in terms of scalability across multiple CPU cores or even hosts. Processes are far better abstractions than threads for modeling concurrent systems since their logic is fundamentally the same regardless of whether they run across different CPU cores or different hosts. > The overhead of cross-process context switches is inherently higher than switching between threads in the same process I remember researching this a while back. It depends on the specific OS and hardware. It's not so straight forward and this is something which tends to change over time and the differences are usually insignificant anyway. Also, it's important not to conflate performance with scalability - These two characteristics are orthogonal at best and oftentimes conflicting. Oftentimes, to scale horizontally, a system needs to incur a performance penalty as additional work is required to route and coordinate actions across multiple CPUs or hosts. A scalable system can service a much larger number (or even sometimes theoretically unlimited) number of requests but it will typically perform worse than a non-scalable system if you judge it on a requests-per-CPU-core basis.
- glintik 3y agoMysql guys: Smart move, Postgres!
- nerpderp82 3y agoThey could host those currently subprocess inside Wasm environments. This would be largely a mechanical transformation. Even the current shm based architecture would stay intact.