Re: proposal: LISTEN *

On 11/19/15 5:32 PM, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>> On 11/19/15 4:21 PM, Tom Lane wrote:
>>> ... and then you gotta get the notifications to the clients, so it seems
>>> like this just leaves the performance question hanging.
>
>> I'm not sure which performance question you think is left hanging. If
>> every client is connected to postgres, you're waking up tens if not
>> hundreds of processes tens if not hundreds of times per second. Each of
>> them start a transaction, check which notifications are visible in the
>> queue from clog and friends, go through the tails of every other process
>> to see whether they should advance the tail pointer of the queue, commit
>> the transaction and go back to sleep only to be immediately woken up again.
>
>> If they're not connected to postgres directly, this kind of complex
>> processing only happens once, and then the notification server just
>> unconditionally serves all notifications to the clients based on a
>> simple map lookup. It should be trivial to see how the overhead is avoided.
>
> Meh. You've gotten rid of one single-point-of-contention by creating
> another one. Essentially, your argument why this is an improvement is
> that any random application developer using Postgres is smarter than
> the Postgres development team and can therefore produce something
> better-performing off the cuff.

It's not about who's smarter and who's not. All a notification server
has to do is wait for notifications incoming from one socket and make
sure they're delivered to the correct sockets in an epoll loop. There's
only one process being woken up, no need to synchronize with other
processes, no need to see where the pointers of other processes are, no
overhead of transaction visibility, transaction BEGIN, or transaction
COMMIT. The total amount of work done is trivially smaller.

> Which may indeed be true, but shall we
> say it's unproven?

Well it's not proof, but every time we moved an application from
LISTENing in postgres to the notification server our CPU usage halved.
The last time we did that (from ~100 connections to exactly 1), s_lock
went down from 30% to 0.16% in our CPU profiles, and our CPU usage is
only a fraction of what it used to be. And this is with the
notification server running on the same server with postgres, so it's
not cheating, either.

There's no way we could keep running postgres if all 400+ clients
interested in notifications had a LISTEN open in the database.

.m