Re: Let's make PostgreSQL multi-threaded

On 06.06.2023 12:07 AM, Jonah H. Harris wrote:
> On Mon, Jun 5, 2023 at 8:18 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
<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.