Re: Protect syscache from bloating with negative cache entries

On Thu, Mar 1, 2018 at 2:29 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> Right. Which might be very painful latency wise. And with poolers it's
> pretty easy to get into situations like that, without the app
> influencing it.

Really? I'm not sure I believe that. You're talking perhaps a few
milliseconds - maybe less - of additional latency on a connection
that's been idle for many minutes. You need to have a workload that
involves leaving connections idle for very long periods but has
extremely tight latency requirements when it does finally send a
query. I suppose such workloads exist, but I would not think them
common.

Anyway, I don't mind making the exact timeout a GUC (with 0 disabling
the feature altogether) if that addresses your concern, but in general
I think that it's reasonable to accept that a connection that's been
idle for a long time may have a little bit more latency than usual
when you start using it again. That could happen for other reasons
anyway -- e.g. the cache could have been flushed because of concurrent
DDL on the objects you were accessing, by a syscache reset caused by a
flood of temp objects being created, or by the operating system
deciding to page out some of your data, or by your data getting
evicted from the CPU caches, or by being scheduled onto a NUMA node
different than the one that contains its data. Operating systems have
been optimizing for the performance of relatively active processes
over ones that have been idle for a long time since the 1960s or
earlier, and I don't know of any reason why PostgreSQL shouldn't do
the same.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company