Re: One process per session lack of sharing

On 12 July 2016 at 21:57, <AMatveev(at)bitec(dot)ru> wrote:

> Hi
>
> Is there any plan to implement "session per thread" or "shared
> sessions between thread"?
>

As has been noted by others, there isn't any such plan right now.

PostgreSQL isn't threaded. It uses a multi-processing
shared-nothing-by-default memory with explicit shared memory, plus
copy-on-write memory forking from the postmaster for initial backend state
(except on Windows, where we emulate that). It relies on processes being
cheap and light-weight.

This is a very poor fit with Java's thread-based shared-by-default model
with expensive heavyweight process startup and cheap threads. Process
forking doesn't clone all threads and the JVM has lots of worker threads,
so we can't start the JVM once in the postmaster then clone it with each
forked postgres backend. Plus the JVM just isn't designed to cope with that
and would surely get thoroughly confused when its file handles are cloned,
its process ID changes, etc. This is one of the reasons PL/Java has never
really taken off. We can mitigate the JVM startup costs a bit by preloading
the JVM libraries into the postmaster and using the JVM's base class
library preloading, but unless you're running trivial Java code you still
do a lot of work at each JVM start after the postgres backend forks.

> We have analyzed the ability to contribute pgSql to jvm bytecode
> compiler but with
> current thread model this idea is far from optimal.(Vm can be
> different of course.
> But currently we use oracle and jvm is important for us)
>

Yep, that's a real sticking point for a number of people.

The usual solution at this point is to move most of the work into an
application-server mid-layer. That moves work further away from the DB,
which has its own costs, and isn't something you're likely to be happy with
if you're looking at things like optimising PL/PgSQL with a bytecode
compiler. But it's the best we have right now.

> We have faced with some lack of sharing resources.
> So in our test memory usage per session:
> Oracle: about 5M
> MSSqlServer: about 4M
> postgreSql: about 160М
>

Yep, that sounds about right. Unfortunately.

You may be able to greatly reduce that cost if you can store your cached
compiled data in a shared memory segment created by your extension. This
will get a bit easier with the new dynamic shared memory infrastructure,
but it's going to be no fun at all to make that play with the JVM. You'll
probably need a lot of JNI.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services