Re: Threading in BGWorkers (!)

On 23.06.2020 06:38, Tom Lane wrote:
> James Sewell <james.sewell@jirotech.com> writes:
>> I was talking about PostgreSQL and threading on IRC the other day - which I
>> know is a frowned upon topic - and just wanted to frame the same questions
>> here and hopefully get a discussion going.
> I think the short answer about threading in bgworkers (or any other
> backend process) is "we don't support it; if you try it and it breaks,
> which it likely will, you get to keep both pieces".  I'm not sure that
> there's any merit in making small dents in that policy.  I suspect that
> at some point, somebody will try to move those goalposts a long way,
> but it will be a large and controversial patch.
>
> Why do you want threads in a bgworker anyway?  You could spawn multiple
> bgworkers, or you could dispatch the threaded work to a non-Postgres-ish
> process as PL/Java does.  The only advantage I can see of doing work in a
> process that's not at arm's-length is to have access to PG computational
> or IPC facilities, and none of that is likely to work safely in a threaded
> context.

Just an example of using threads in bgworker: right now I am working on 
FDW for RocksDB.
RocksDB LSM  implementation shows quite promising performance advantages 
comparing with classical Postgres B-Tree
(almost no degrade of insert speed with increasing number of records).

RocksDB is multithreded database. May be it is possible to port it to 
multiprocess model
but it will be very non trivial task. And even if such fork of RocksDB 
will be created,  somebody have to permanently back patch changes from 
main trunk.

Alternative solution is to launch multithreded RocksDB worker and let 
backends redirect requests to this worker.
Client-server architecture inside server:)

Using multithreading in bgworker is possible if you do not use any 
Postgres runtime inside thread procedures or do it in exclusive critical 
section.
It is not so convenient but possible. The most difficult thing from my 
point of view is error reporting.