pg-pool: there is no indication when pool becomes idle

[pkit]

Currently there is no event emitted when pool has some idle connections.
Which makes it impossible to efficiently manage the pool queue.
You need to poll pool.idleCount (or some other counts) all the time.
I have added this.emit('idle') in release which seems to work

....
this._idle.set(client.processID, new IdleItem(client, tid))
this.emit('idle')
....

Is there a problem with that?

[vitaly-t]

to efficiently manage the pool queue

The current pool implements standard pool architecture, where there is no practical need to know how many idle connections it contains.

If you think differently, then please clarify what kind of efficient pool management you are trying to implement, that you need idle connections count.

[pkit]

Any kind of back-pressure. Right now the pool queue will grow indefinitely if infinite stream of queries is attached to it.

[vitaly-t]

Sounds like you are trying to do it inside an HTTP service - am I right? If so, then you are not doing right. Back pressure monitoring in this case needs to be applied on a level above, at HTTP, and not on the database layer. There are plenty of back-pressure monitoring libraries and solutions for HTTP requests. And you do not do that on the database level, it is a level far too late for that.

[pkit]

I'm not sure I follow, how can I apply any back-pressure algorithm if I have no idea how busy the pool is?
And we are not talking about DB level. We are in nodejs right now. One end is getting queries over HTTP (for example) the other end needs to execute these queries.
Obviously there is a different queue at the HTTP end (an implementation of a fair queue GPS) but it needs some indication when it can pass more data to the pg-pool end.

@pkit you don't need to know how many are idle, you stack up as many queries as you want and it will handle them when connections are available, I think you need to explain better what you are trying to do

If you are adding queries faster than you can process them then you should set a bigger pool size or add more servers/cpu's etc

[pkit]

@mslatt I'm not sure I follow either, why should I accommodate for any possible load instead of throttling, delaying requests?
What if I need priorities? Like some requests should be delayed because of more urgent ones?

[pkit]

I knida understand the sentiment: if you need something more than a simple pool we provide - write your own.
Did that already, thank you very much. :)
But it's kinda hard to keep up with node-postgres because the internal interfaces kinda change . On the other hand some strange things go in. And I understand the sentiment here too: people shouldn't write their ovn load balancers and failover code just for a simple cases like this. But again, what's wrong with adding another simple case?

Are you sure you understand how the pool works? You don't need to throttle anything, stack up queries and the pool handles it for you. If you have priority queries then you can create a 2nd pool just for high priority queries. Have you actually tried any of this or are you just theorizing?

[pkit]

stack up queries and the pool handles it for you

Unless there is a bug in the calling code and it creates too much queries and DoS'es everything.
Multi-user database means that people/apps will DoS each other routinely.
And to implement safeguards against it you must know if the pool is too busy.
Or if you want to do some fine tuned handling like "active connection per user" you will need to implement a new pool, which will break on node-postgres bugfixes.

If you have priority queries then you can create a 2nd pool just for high priority queries.

There are multiple priorities, do you understand how weighted fair queueing works?

You are not making any sense.......

Unless there is a bug in the calling code and it creates too much queries

then you should probably fix the bug making too many queries

know if the pool is too busy

then you should be checking pool.waitingCount to see how many pending queries there are, not checking for when the pool has idle connections like your issue is about

need to implement a new pool, which will break on node-postgres bugfixes

you don't need to implement your own pool, the current pool works fine

There are multiple priorities

if you are creating more queries than you can process then you need more processing power, otherwise 2 or 3 pools could handle different priorities

Again have you actually tried any of this or are you here to just confuse everyone? Your issue is titled "There is no indication when pool becomes idle". You don't need to know when the pool becomes idle

[pkit]

then you should probably fix the bug making too many queries

Multi-user environment. Apps are made by users (even if it's the same enterprise), platform has no direct control.

then you should be checking pool.waitingCount to see how many pending queries there are

Polling, inefficient. pool.waitingCount === 0 -> idle, btw.

you don't need to implement your own pool, the current pool works fine

It doesn't, for any use case other than "one app connecting to one DB over a bulk of connections with the same usage pattern". Even for simple cases like "failover to different instance" hacks need to be implemented, as I've mentioned. Currently pool works well only for the simplest usage case possible.

if you are creating more queries than you can process then you need more processing power

No. I need to throttle these queries and maybe deny service to one app that's misbehaving, it shouldn't impact all other users.

Again have you actually tried any of this or are you here to just confuse everyone?

I have explained an actual use case already: multi-user enterprise platform where apps cannot be directly controlled. It's not theoretical. And issues like: "we accidentally created a query per each mouse move" are not that uncommon.
And no, "create a pool per app" doesn't work either because apps run on front end, the backend is shared for performance reasons (shared backend can efficiently share resources by multiplexing users over the same connections).

Ok no one knows what your actual problem is, you are spamming this issue about needing "indication when pool becomes idle" which is ridiculous. Maybe figure out what you need then come back here and explain it

These issues are for library bugs, not to help you figure out how to manage "multi-user apps that cannot be directly controlled" or whatever that means

[pkit]

I'm not sure again. I never asked how to design my application. It's you two who started all of the de-rail here. My simple question was in the OP.