RE: Protect syscache from bloating with negative cache entries

From: 'Bruce Momjian' [mailto:bruce@momjian.us]
> I think, in general, smaller is better, as long as making something
> smaller doesn't remove data that is frequently accessed.  Having a timer
> to expire only old entries seems like it accomplished this goal.
> 
> Having a minimum size and not taking it to zero size makes sense if we
> know we will need certain entries like pg_class in the next query.
> However, if the session is idle for hours, we should just probably
> remove everything, so maybe the minimum doesn't make sense --- just
> remove everything.

That's another interesting idea.  A somewhat relevant feature is Oracle's "ALTER SYSTEM FLUSH SHARED_POOL".  It flushes
alldictionary cache, library cache, and SQL plan entries.  The purpose is different: not to release memory, but to
defragmentthe shared memory.
 


> I don't think other DBMSs are a good model since they have a reputation
> for requiring a lot of tuning --- tuning that we have often automated.

Yeah, I agree that PostgreSQL is easier to use in many aspects.

On the other hand, although I hesitate to say this (please don't get upset...), I feel PostgreSQL is a bit too loose
aboutmemory usage.  To my memory, PostgreSQL crashed OS due to OOM in our user environments:
 

* Creating and dropping temp tables repeatedly in a stored PL/pgSQL function.  This results in infinite
CacheMemoryContextbloat.  This is referred to at the beginning of this mail thread.
 
Oracle and MySQL can limit the size of the dictionary cache.

* Each pair of SAVEPOINT/RELEASE leaves 8KB of CurTransactionContext.  The customer used psqlODBC to run a batch app,
whichran millions of SQL statements in a transaction.  psqlODBC wraps each SQL statement with SAVEPOINT and RELEASE by
default.
I guess this is what caused the crash of AWS Aurora in last year's Amazon Prime Day.

* Setting a large value to work_mem, and then run many concurrent large queries.
Oracle can limit the total size of all sessions' memory with PGA_AGGREGATE_TARGET parameter.


We all have to manage things within resource constraints.  The DBA wants to make sure the server doesn't overuse memory
toavoid crash or slowdown due to swapping.  Oracle does it, and another open source database, MySQL, does it too.
PostgreSQLdoes it with shared_buffers, wal_buffers, and work_mem (within a single session).  Then, I thought it's
naturalto do it with catcache/relcache/plancache.
 


Regards
Takayuki Tsunakawa