Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 5, 2023 at 8:18 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

For 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.

While I've long been in favor of a multi-threaded implementation, now in my old age, I tend to agree with Tom. I'd be interested in Konstantin's thoughts (and PostgresPro's experience) of multi-threaded vs. internal pooling with the current process-based model. I recall looking at and playing with Konstantin's implementations of both, which were impressive. Yes, the latter doesn't solve the same issues, but many real-world ones where multi-threaded is argued. Personally, I think there would be not only a significant amount of time spent dealing with in-the-field stability regressions before a multi-threaded implementation matures, but it would also increase the learning curve for anyone trying to start with internals development.

--

Jonah H. Harris

Let me share my experience with porting Postgres to threads (by the way - repository is still alive - https://github.com/postgrespro/postgresql.pthreads
but I have not keep it in sync with recent versions of Postgres).

1. Solving the problem with static variables was not so difficult as I expected - thanks to TLS and its support in modern compilers.
So the only thing we should do is to add some special modified to variable declaration:

-static int     MyLockNo = 0;
-static bool holdingAllLocks = false;
+static session_local int       MyLockNo = 0;
+static session_local bool holdingAllLocks = false;

But there are about 2k such variables which storage class has to be changed.
This is one of the reasons why I do not agree with the proposal to define some session context, place all session specific variables in such context and pass it everywhere. It will be very inconvenient to maintain structure with 2k fields and adding new field to this struct each time you need some non-local variable. Even i it can be hide in some macros like DEF_SESSION_VAR(type, name).
Also it requires changing of all Postgres code working with this variables, not just declarations.
So patch will be 100x times more and almost any line of Postgres code has to be changed.
And I do not see any reasons for it except portability and avoid dependecy on compiler.
Implementation of TLS is quite efficient (at least at x86) - there is special register pointing to TLS area, so access TLS variable is not more expensive than static variable.

2. Performance improvement from switching to threads was not so large (~10%). But please notice that I have not changed ny Postgres sync primitives.
(But still not sure that using for example pthead_rwlock instead of our own LWLock will cause some gains in performance)

3. Multithreading significantly simplify concurrent query execution and interaction between workers.
Right now with dynamic shared memory stuff  we can support work with varying size data in shared memory but
in mutithreaded program it can be done much easier.

4. Multuthreaded model  opens a way for fixing many existed Postgres problems: lack of shared catalog and prepared statements cache, changing page pool size (shared buffers) in runtime, ...

5. During this porting I had most of troubles with the following components: GUCs,  signals, handling errors and file descriptor cache. File descriptor cache really becomes bottleneck because now all backends and competing for file descriptors which number is usually limited by 1024 (without editing system configuration). Protecting it with mutex cause significant degrade of performance. So I have to maintain thread-local cache.

6. It is not clear how to support external extensions.

7. It will be hard to support non-multithreaded PL languages (like python), but for example support of Java will be more natural and efficient.

I do not think that development of multithreaded application is more  complex or requires large "learning curve".
When you deal with parallel execution you should be careful in any case.
The advantage of process model is that there is much clear distinction between shared and private variables.
Concerning debugging and profiling - it is more convenient with multithreading in some cases and less convenient in other.
But programmers are living with threads for more than 30 years so now most tools are supporting threads at least not worse than processes.
And for many developers now working with threads is more natural and convenient.


OOM and local backend memory consumption seems to be one of the main challenges for multithreadig model:
right now some queries can cause high consumption of memory. work_mem is just a hint and real memory consumption can be much higher.
Even if it doesn't cause OOM, still not all of the allocated memory is returned to OS after query completion and increase memory fragmentation.
Right now restart of single backend suffering from memory fragmentation eliminates this problem. But if will be impossible for multhreaded Postgres.


So? as I see from this thread, most of authoritative members of Postgres community are still very pessimistic (or conservative:)
about making  Postgres  multi-threaded. And it is really huge work which will cause significant code discrepancy. It significantly complicates
backpatching and support of external extension. It can not be done without support and approval by most of committers. This is why this work was stalled in PgPro.

My personal opinion is that Postgres almost reaches its "limit of evolution" or is close to it.
Making some major changes such as multithreading, undo log, columnar store with vector executor
requires so much changes and cause so many conflicts with existed code that it will be easier to develop new system from scratch
rather than trying to plugin new approach in old architecture. May be I wrong. It can be my personal fault that I was not able to bring multithread Postgres, builtin connection pooler, vectorized executor, libpq compression and other my PRs to commit.
I have a filling that it is not possible to merge in mainstream something non-trivial, affecting Postgres core without interest and help of several
committers. Fro the other hand  presence of such Postgres forks as TimescaleDB, OrioleDB, GreenPlum demonstrates that Postgres still has high potential for extension.