Re: Protect syscache from bloating with negative cache entries

Hello. Thank you for looking this.

At Wed, 12 Sep 2018 05:16:52 +0000, "Ideriha, Takeshi" <ideriha.takeshi@jp.fujitsu.com> wrote in
<4E72940DA2BF16479384A86D54D0988A6F197012@G01JPEXMBKW04>
> Hi, 
> 
> >Subject: Re: Protect syscache from bloating with negative cache entries
> >
> >Hello. The previous v4 patchset was just broken.
> 
> >Somehow the 0004 was merged into the 0003 and applying 0004 results in failure. I
> >removed 0004 part from the 0003 and rebased and repost it.
> 
> I have some questions about syscache and relcache pruning
> though they may be discussed at upper thread or out of point.
> 
> Can I confirm about catcache pruning?
> syscache_memory_target is the max figure per CatCache.
> (Any CatCache has the same max value.)
> So the total max size of catalog caches is estimated around or 
> slightly more than # of SysCache array times syscache_memory_target.

Right.

> If correct, I'm thinking writing down the above estimation to the document 
> would help db administrators with estimation of memory usage.
> Current description might lead misunderstanding that syscache_memory_target
> is the total size of catalog cache in my impression.

Honestly I'm not sure that is the right design. Howerver, I don't
think providing such formula to users helps users, since they
don't know exactly how many CatCaches and brothres live in their
server and it is a soft limit, and finally only few or just one
catalogs can reach the limit.

The current design based on the assumption that we would have
only one extremely-growable cache in one use case.

> Related to the above I just thought changing sysycache_memory_target per CatCache
> would make memory usage more efficient.

We could easily have per-cache settings in CatCache, but how do
we provide the knobs for them? I can guess only too much
solutions for that.

> Though I haven't checked if there's a case that each system catalog cache memory usage varies largely,
> pg_class cache might need more memory than others and others might need less.
> But it would be difficult for users to check each CatCache memory usage and tune it
> because right now postgresql hasn't provided a handy way to check them.

I supposed that this is used without such a means. Someone
suffers syscache bloat just can set this GUC to avoid the
bloat. End.

Apart from that, in the current patch, syscache_memory_target is
not exact at all in the first place to avoid overhead to count
the correct size. The major difference comes from the size of
cache tuple itself. But I came to think it is too much to omit.

As a *PoC*, in the attached patch (which applies to current
master), size of CTups are counted as the catcache size.

It also provides pg_catcache_size system view just to give a
rough idea of how such view looks. I'll consider more on that but
do you have any opinion on this?

=# select relid::regclass, indid::regclass, size from pg_syscache_sizes order by size desc;
          relid          |                   indid                   |  size  
-------------------------+-------------------------------------------+--------
 pg_class                | pg_class_oid_index                        | 131072
 pg_class                | pg_class_relname_nsp_index                | 131072
 pg_cast                 | pg_cast_source_target_index               |   5504
 pg_operator             | pg_operator_oprname_l_r_n_index           |   4096
 pg_statistic            | pg_statistic_relid_att_inh_index          |   2048
 pg_proc                 | pg_proc_proname_args_nsp_index            |   2048
..


> Another option is that users only specify the total memory target size and postgres 
> dynamically change each CatCache memory target size according to a certain metric.
> (, which still seems difficult and expensive to develop per benefit)
> What do you think about this?

Given that few caches bloat at once, it's effect is not so
different from the current design.

> As you commented here, guc variable syscache_memory_target and
> syscache_prune_min_age are used for both syscache and relcache (HTAB), right?

Right, just not to add knobs for unclear reasons. Since ...

> Do syscache and relcache have the similar amount of memory usage?

They may be different but would make not so much in the case of
cache bloat.

> If not, I'm thinking that introducing separate guc variable would be fine.
> So as syscache_prune_min_age.

I implemented that so that it is easily replaceable in case, but
I'm not sure separating them makes significant difference..

Thanks for the opinion, I'll put consideration on this more.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index bee4afbe4e..6a00141fc9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1617,6 +1617,44 @@ include_dir 'conf.d'
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-syscache-memory-target" xreflabel="syscache_memory_target">
+      <term><varname>syscache_memory_target</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>syscache_memory_target</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the maximum amount of memory to which syscache is expanded
+        without pruning. The value defaults to 0, indicating that pruning is
+        always considered. After exceeding this size, syscache pruning is
+        considered according to
+        <xref linkend="guc-syscache-prune-min-age"/>. If you need to keep
+        certain amount of syscache entries with intermittent usage, try
+        increase this setting.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="guc-syscache-prune-min-age" xreflabel="syscache_prune_min_age">
+      <term><varname>syscache_prune_min_age</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>syscache_prune_min_age</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Specifies the minimum amount of unused time in seconds at which a
+        syscache entry is considered to be removed. -1 indicates that syscache
+        pruning is disabled at all. The value defaults to 600 seconds
+        (<literal>10 minutes</literal>). The syscache entries that are not
+        used for the duration can be removed to prevent syscache bloat. This
+        behavior is suppressed until the size of syscache exceeds
+        <xref linkend="guc-syscache-memory-target"/>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-max-stack-depth" xreflabel="max_stack_depth">
       <term><varname>max_stack_depth</varname> (<type>integer</type>)
       <indexterm>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 875be180fe..df4256466c 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -713,6 +713,9 @@ void
 SetCurrentStatementStartTimestamp(void)
 {
     stmtStartTimestamp = GetCurrentTimestamp();
+
+    /* Set this timestamp as aproximated current time */
+    SetCatCacheClock(stmtStartTimestamp);
 }
 
 /*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7251552419..1a1acd9bc7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -938,6 +938,11 @@ REVOKE ALL ON pg_subscription FROM public;
 GRANT SELECT (subdbid, subname, subowner, subenabled, subslotname, subpublications)
     ON pg_subscription TO public;
 
+-- XXXXXXXXXXXXXXXXXXXXXX
+CREATE VIEW pg_syscache_sizes AS
+  SELECT *
+  FROM pg_get_syscache_sizes();
+
 
 --
 -- We have a few function definitions in here, too.
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 5ddbf6eab1..aafdc4f8f2 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -71,9 +71,24 @@
 #define CACHE6_elog(a,b,c,d,e,f,g)
 #endif
 
+/*
+ * GUC variable to define the minimum size of hash to cosider entry eviction.
+ * This variable is shared among various cache mechanisms.
+ */
+int cache_memory_target = 0;
+
+/* GUC variable to define the minimum age of entries that will be cosidered to
+ * be evicted in seconds. This variable is shared among various cache
+ * mechanisms.
+ */
+int cache_prune_min_age = 600;
+
 /* Cache management header --- pointer is NULL until created */
 static CatCacheHeader *CacheHdr = NULL;
 
+/* Timestamp used for any operation on caches. */
+TimestampTz    catcacheclock = 0;
+
 static inline HeapTuple SearchCatCacheInternal(CatCache *cache,
                        int nkeys,
                        Datum v1, Datum v2,
@@ -498,6 +513,7 @@ CatCacheRemoveCTup(CatCache *cache, CatCTup *ct)
         CatCacheFreeKeys(cache->cc_tupdesc, cache->cc_nkeys,
                          cache->cc_keyno, ct->keys);
 
+    cache->cc_tupsize -= ct->size;
     pfree(ct);
 
     --cache->cc_ntup;
@@ -849,6 +865,7 @@ InitCatCache(int id,
     cp->cc_nkeys = nkeys;
     for (i = 0; i < nkeys; ++i)
         cp->cc_keyno[i] = key[i];
+    cp->cc_tupsize = 0;
 
     /*
      * new cache is initialized as far as we can go for now. print some
@@ -866,9 +883,129 @@ InitCatCache(int id,
      */
     MemoryContextSwitchTo(oldcxt);
 
+    /* initilize catcache reference clock if haven't done yet */
+    if (catcacheclock == 0)
+        catcacheclock = GetCurrentTimestamp();
+
     return cp;
 }
 
+/*
+ * CatCacheCleanupOldEntries - Remove infrequently-used entries
+ *
+ * Catcache entries can be left alone for several reasons. We remove them if
+ * they are not accessed for a certain time to prevent catcache from
+ * bloating. The eviction is performed with the similar algorithm with buffer
+ * eviction using access counter. Entries that are accessed several times can
+ * live longer than those that have had no access in the same duration.
+ */
+static bool
+CatCacheCleanupOldEntries(CatCache *cp)
+{
+    int            i;
+    int            nremoved = 0;
+    size_t        hash_size;
+#ifdef CATCACHE_STATS
+    /* These variables are only for debugging purpose */
+    int            ntotal = 0;
+    /*
+     * nth element in nentries stores the number of cache entries that have
+     * lived unaccessed for corresponding multiple in ageclass of
+     * cache_prune_min_age. The index of nremoved_entry is the value of the
+     * clock-sweep counter, which takes from 0 up to 2.
+     */
+    double        ageclass[] = {0.05, 0.1, 1.0, 2.0, 3.0, 0.0};
+    int            nentries[] = {0, 0, 0, 0, 0, 0};
+    int            nremoved_entry[3] = {0, 0, 0};
+    int            j;
+#endif
+
+    /* Return immediately if no pruning is wanted */
+    if (cache_prune_min_age < 0)
+        return false;
+
+    /*
+     * Return without pruning if the size of the hash is below the target.
+     */
+    hash_size = cp->cc_nbuckets * sizeof(dlist_head);
+    if (hash_size + cp->cc_tupsize < (Size) cache_memory_target * 1024L)
+        return false;
+    
+    /* Search the whole hash for entries to remove */
+    for (i = 0; i < cp->cc_nbuckets; i++)
+    {
+        dlist_mutable_iter iter;
+
+        dlist_foreach_modify(iter, &cp->cc_bucket[i])
+        {
+            CatCTup    *ct = dlist_container(CatCTup, cache_elem, iter.cur);
+            long entry_age;
+            int us;
+
+
+            /*
+             * Calculate the duration from the time of the last access to the
+             * "current" time. Since catcacheclock is not advanced within a
+             * transaction, the entries that are accessed within the current
+             * transaction won't be pruned.
+             */
+            TimestampDifference(ct->lastaccess, catcacheclock, &entry_age, &us);
+
+#ifdef CATCACHE_STATS
+            /* count catcache entries for each age class */
+            ntotal++;
+            for (j = 0 ;
+                 ageclass[j] != 0.0 &&
+                     entry_age > cache_prune_min_age * ageclass[j] ;
+                 j++);
+            if (ageclass[j] == 0.0) j--;
+            nentries[j]++;
+#endif
+
+            /*
+             * Try to remove entries older than cache_prune_min_age seconds.
+             * Entries that are not accessed after last pruning are removed in
+             * that seconds, and that has been accessed several times are
+             * removed after leaving alone for up to three times of the
+             * duration. We don't try shrink buckets since pruning effectively
+             * caps catcache expansion in the long term.
+             */
+            if (entry_age > cache_prune_min_age)
+            {
+#ifdef CATCACHE_STATS
+                Assert (ct->naccess >= 0 && ct->naccess <= 2);
+                nremoved_entry[ct->naccess]++;
+#endif
+                if (ct->naccess > 0)
+                    ct->naccess--;
+                else
+                {
+                    if (!ct->c_list || ct->c_list->refcount == 0)
+                    {
+                        CatCacheRemoveCTup(cp, ct);
+                        nremoved++;
+                    }
+                }
+            }
+        }
+    }
+
+#ifdef CATCACHE_STATS
+    ereport(DEBUG1,
+            (errmsg ("removed %d/%d, age(-%.0fs:%d, -%.0fs:%d, *-%.0fs:%d, -%.0fs:%d, -%.0fs:%d) naccessed(0:%d, 1:%d,
2:%d)",
+                     nremoved, ntotal,
+                     ageclass[0] * cache_prune_min_age, nentries[0],
+                     ageclass[1] * cache_prune_min_age, nentries[1],
+                     ageclass[2] * cache_prune_min_age, nentries[2],
+                     ageclass[3] * cache_prune_min_age, nentries[3],
+                     ageclass[4] * cache_prune_min_age, nentries[4],
+                     nremoved_entry[0], nremoved_entry[1], nremoved_entry[2]),
+             errhidestmt(true)));
+#endif
+
+    return nremoved > 0;
+}
+
 /*
  * Enlarge a catcache, doubling the number of buckets.
  */
@@ -1282,6 +1419,11 @@ SearchCatCacheInternal(CatCache *cache,
          */
         dlist_move_head(bucket, &ct->cache_elem);
 
+        /* Update access information for pruning */
+        if (ct->naccess < 2)
+            ct->naccess++;
+        ct->lastaccess = catcacheclock;
+
         /*
          * If it's a positive entry, bump its refcount and return it. If it's
          * negative, we can report failure to the caller.
@@ -1813,7 +1955,6 @@ ReleaseCatCacheList(CatCList *list)
         CatCacheRemoveCList(list->my_cache, list);
 }
 
-
 /*
  * CatalogCacheCreateEntry
  *        Create a new CatCTup entry, copying the given HeapTuple and other
@@ -1827,11 +1968,13 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
     CatCTup    *ct;
     HeapTuple    dtp;
     MemoryContext oldcxt;
+    int            tupsize = 0;
 
     /* negative entries have no tuple associated */
     if (ntp)
     {
         int            i;
+        int            tupsize;
 
         Assert(!negative);
 
@@ -1850,13 +1993,14 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
         /* Allocate memory for CatCTup and the cached tuple in one go */
         oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
 
-        ct = (CatCTup *) palloc(sizeof(CatCTup) +
-                                MAXIMUM_ALIGNOF + dtp->t_len);
+        tupsize = sizeof(CatCTup) +    MAXIMUM_ALIGNOF + dtp->t_len;
+        ct = (CatCTup *) palloc(tupsize);
         ct->tuple.t_len = dtp->t_len;
         ct->tuple.t_self = dtp->t_self;
         ct->tuple.t_tableOid = dtp->t_tableOid;
         ct->tuple.t_data = (HeapTupleHeader)
             MAXALIGN(((char *) ct) + sizeof(CatCTup));
+        ct->size = tupsize;
         /* copy tuple contents */
         memcpy((char *) ct->tuple.t_data,
                (const char *) dtp->t_data,
@@ -1884,8 +2028,8 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
     {
         Assert(negative);
         oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
-        ct = (CatCTup *) palloc(sizeof(CatCTup));
-
+        tupsize = sizeof(CatCTup);
+        ct = (CatCTup *) palloc(tupsize);
         /*
          * Store keys - they'll point into separately allocated memory if not
          * by-value.
@@ -1906,17 +2050,24 @@ CatalogCacheCreateEntry(CatCache *cache, HeapTuple ntp, Datum *arguments,
     ct->dead = false;
     ct->negative = negative;
     ct->hash_value = hashValue;
+    ct->naccess = 0;
+    ct->lastaccess = catcacheclock;
+    ct->size = tupsize;
 
     dlist_push_head(&cache->cc_bucket[hashIndex], &ct->cache_elem);
 
     cache->cc_ntup++;
     CacheHdr->ch_ntup++;
+    cache->cc_tupsize += tupsize;
 
     /*
-     * If the hash table has become too full, enlarge the buckets array. Quite
-     * arbitrarily, we enlarge when fill factor > 2.
+     * If the hash table has become too full, try cleanup by removing
+     * infrequently used entries to make a room for the new entry. If it
+     * failed, enlarge the bucket array instead.  Quite arbitrarily, we try
+     * this when fill factor > 2.
      */
-    if (cache->cc_ntup > cache->cc_nbuckets * 2)
+    if (cache->cc_ntup > cache->cc_nbuckets * 2 &&
+        !CatCacheCleanupOldEntries(cache))
         RehashCatCache(cache);
 
     return ct;
@@ -2118,3 +2269,9 @@ PrintCatCacheListLeakWarning(CatCList *list)
          list->my_cache->cc_relname, list->my_cache->id,
          list, list->refcount);
 }
+
+int
+CatCacheGetSize(CatCache *cache)
+{
+    return cache->cc_tupsize + cache->cc_nbuckets * sizeof(dlist_head);
+}
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 7271b5880b..490cb8ec8a 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -63,12 +63,14 @@
 #include "storage/lmgr.h"
 #include "tcop/pquery.h"
 #include "tcop/utility.h"
+#include "utils/catcache.h"
 #include "utils/inval.h"
 #include "utils/memutils.h"
 #include "utils/resowner_private.h"
 #include "utils/rls.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
+#include "utils/timestamp.h"
 
 
 /*
@@ -86,6 +88,12 @@
  * guarantee to save a CachedPlanSource without error.
  */
 static CachedPlanSource *first_saved_plan = NULL;
+static CachedPlanSource *last_saved_plan = NULL;
+static int                 num_saved_plans = 0;
+static TimestampTz         oldest_saved_plan = 0;
+
+/* GUC variables */
+int                         min_cached_plans = 1000;
 
 static void ReleaseGenericPlan(CachedPlanSource *plansource);
 static List *RevalidateCachedQuery(CachedPlanSource *plansource,
@@ -105,6 +113,7 @@ static TupleDesc PlanCacheComputeResultDesc(List *stmt_list);
 static void PlanCacheRelCallback(Datum arg, Oid relid);
 static void PlanCacheFuncCallback(Datum arg, int cacheid, uint32 hashvalue);
 static void PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue);
+static void PruneCachedPlan(void);
 
 /* GUC parameter */
 int    plan_cache_mode;
@@ -210,6 +219,8 @@ CreateCachedPlan(RawStmt *raw_parse_tree,
     plansource->generic_cost = -1;
     plansource->total_custom_cost = 0;
     plansource->num_custom_plans = 0;
+    plansource->last_access = GetCatCacheClock();
+    
 
     MemoryContextSwitchTo(oldcxt);
 
@@ -425,6 +436,28 @@ CompleteCachedPlan(CachedPlanSource *plansource,
     plansource->is_valid = true;
 }
 
+/* moves the plansource to the first in the list */
+static inline void
+MovePlansourceToFirst(CachedPlanSource *plansource)
+{
+    if (first_saved_plan != plansource)
+    {
+        /* delink this element */
+        if (plansource->next_saved)
+            plansource->next_saved->prev_saved = plansource->prev_saved;
+        if (plansource->prev_saved)
+            plansource->prev_saved->next_saved = plansource->next_saved;
+        if (last_saved_plan == plansource)
+            last_saved_plan = plansource->prev_saved;
+
+        /* insert at the beginning */
+        first_saved_plan->prev_saved = plansource;
+        plansource->next_saved = first_saved_plan;
+        plansource->prev_saved = NULL;
+        first_saved_plan = plansource;
+    }
+}
+
 /*
  * SaveCachedPlan: save a cached plan permanently
  *
@@ -472,6 +505,11 @@ SaveCachedPlan(CachedPlanSource *plansource)
      * Add the entry to the global list of cached plans.
      */
     plansource->next_saved = first_saved_plan;
+    if (first_saved_plan)
+        first_saved_plan->prev_saved = plansource;
+    else
+        last_saved_plan = plansource;
+    plansource->prev_saved = NULL;
     first_saved_plan = plansource;
 
     plansource->is_saved = true;
@@ -494,7 +532,11 @@ DropCachedPlan(CachedPlanSource *plansource)
     if (plansource->is_saved)
     {
         if (first_saved_plan == plansource)
+        {
             first_saved_plan = plansource->next_saved;
+            if (first_saved_plan)
+                first_saved_plan->prev_saved = NULL;
+        }
         else
         {
             CachedPlanSource *psrc;
@@ -504,10 +546,19 @@ DropCachedPlan(CachedPlanSource *plansource)
                 if (psrc->next_saved == plansource)
                 {
                     psrc->next_saved = plansource->next_saved;
+                    if (psrc->next_saved)
+                        psrc->next_saved->prev_saved = psrc;
                     break;
                 }
             }
         }
+
+        if (last_saved_plan == plansource)
+        {
+            last_saved_plan = plansource->prev_saved;
+            if (last_saved_plan)
+                last_saved_plan->next_saved = NULL;
+        }
         plansource->is_saved = false;
     }
 
@@ -539,6 +590,13 @@ ReleaseGenericPlan(CachedPlanSource *plansource)
         Assert(plan->magic == CACHEDPLAN_MAGIC);
         plansource->gplan = NULL;
         ReleaseCachedPlan(plan, false);
+
+        /* decrement "saved plans" counter */
+        if (plansource->is_saved)
+        {
+            Assert (num_saved_plans > 0);
+            num_saved_plans--;
+        }
     }
 }
 
@@ -1156,6 +1214,17 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
     if (useResOwner && !plansource->is_saved)
         elog(ERROR, "cannot apply ResourceOwner to non-saved cached plan");
 
+    /*
+     * set last-accessed timestamp and move this plan to the first of the list
+     */
+    if (plansource->is_saved)
+    {
+        plansource->last_access = GetCatCacheClock();
+
+        /* move this plan to the first of the list */
+        MovePlansourceToFirst(plansource);
+    }
+
     /* Make sure the querytree list is valid and we have parse-time locks */
     qlist = RevalidateCachedQuery(plansource, queryEnv);
 
@@ -1164,6 +1233,11 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
 
     if (!customplan)
     {
+        /* Prune cached plans if needed */
+        if (plansource->is_saved &&
+            min_cached_plans >= 0 && num_saved_plans > min_cached_plans)
+                PruneCachedPlan();
+
         if (CheckCachedPlan(plansource))
         {
             /* We want a generic plan, and we already have a valid one */
@@ -1176,6 +1250,11 @@ GetCachedPlan(CachedPlanSource *plansource, ParamListInfo boundParams,
             plan = BuildCachedPlan(plansource, qlist, NULL, queryEnv);
             /* Just make real sure plansource->gplan is clear */
             ReleaseGenericPlan(plansource);
+
+            /* count this new saved plan */
+            if (plansource->is_saved)
+                num_saved_plans++;
+
             /* Link the new generic plan into the plansource */
             plansource->gplan = plan;
             plan->refcount++;
@@ -1864,6 +1943,90 @@ PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue)
     ResetPlanCache();
 }
 
+/*
+ * PrunePlanCache: removes generic plan of "old" saved plans.
+ */
+static void
+PruneCachedPlan(void)
+{
+    CachedPlanSource *plansource;
+    TimestampTz          currclock = GetCatCacheClock();
+    long              age;
+    int                  us;
+    int                  nremoved = 0;
+
+    /* do nothing if not wanted */
+    if (cache_prune_min_age < 0 || num_saved_plans <= min_cached_plans)
+        return;
+
+    /* Fast check for oldest cache */
+    if (oldest_saved_plan > 0)
+    {
+        TimestampDifference(oldest_saved_plan, currclock, &age, &us);
+        if (age < cache_prune_min_age)
+            return;
+    }        
+
+    /* last plan is the oldest. */
+    for (plansource = last_saved_plan; plansource; plansource = plansource->prev_saved)
+    {
+        long    plan_age;
+        int        us;
+
+        Assert(plansource->magic == CACHEDPLANSOURCE_MAGIC);
+
+        /* we want to prune no more plans */
+        if (num_saved_plans <= min_cached_plans)
+            break;
+
+        /*
+         * No work if it already doesn't have gplan and move it to the
+         * beginning so that we don't see it at the next time
+         */
+        if (!plansource->gplan)
+            continue;
+
+        /*
+         * Check age for pruning. Can exit immediately when finding a
+         * not-older element.
+         */
+        TimestampDifference(plansource->last_access, currclock, &plan_age, &us);
+        if (plan_age <= cache_prune_min_age)
+        {
+            /* this entry is the next oldest */
+            oldest_saved_plan = plansource->last_access;
+            break;
+        }
+
+        /*
+         * Here, remove generic plans of this plansrouceif it is not actually
+         * used and move it to the beginning of the list. Just update
+         * last_access and move it to the beginning if the plan is used.
+         */
+        if (plansource->gplan->refcount <= 1)
+        {
+            ReleaseGenericPlan(plansource);
+            nremoved++;
+        }
+
+        plansource->last_access = currclock;
+    }
+
+    /* move the "removed" plansrouces altogehter to the beginning of the list */
+    if (plansource != last_saved_plan && plansource)
+    {
+        plansource->next_saved->prev_saved = NULL;
+        first_saved_plan->prev_saved = last_saved_plan;
+         last_saved_plan->next_saved = first_saved_plan;
+        first_saved_plan = plansource->next_saved;
+        plansource->next_saved = NULL;
+        last_saved_plan = plansource;
+    }
+
+    if (nremoved > 0)
+        elog(DEBUG1, "plancache removed %d/%d", nremoved, num_saved_plans);
+}
+
 /*
  * ResetPlanCache: invalidate all cached plans.
  */
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index 2b381782a3..9cdb75afb8 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -73,9 +73,14 @@
 #include "catalog/pg_ts_template.h"
 #include "catalog/pg_type.h"
 #include "catalog/pg_user_mapping.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "nodes/execnodes.h"
 #include "utils/rel.h"
 #include "utils/catcache.h"
 #include "utils/syscache.h"
+#include "utils/tuplestore.h"
+#include "utils/fmgrprotos.h"
 
 
 /*---------------------------------------------------------------------------
@@ -1530,6 +1535,64 @@ RelationSupportsSysCache(Oid relid)
 }
 
 
+/*
+ * rough size of this syscache
+ */
+Datum
+pg_get_syscache_sizes(PG_FUNCTION_ARGS)
+{
+#define PG_GET_SYSCACHE_SIZE 3
+    ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+    TupleDesc    tupdesc;
+    Tuplestorestate *tupstore;
+    MemoryContext per_query_ctx;
+    MemoryContext oldcontext;
+    int    cacheId;
+
+    if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+        ereport(ERROR,
+                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                 errmsg("set-valued function called in context that cannot accept a set")));
+    if (!(rsinfo->allowedModes & SFRM_Materialize))
+        ereport(ERROR,
+                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                 errmsg("materialize mode required, but it is not " \
+                        "allowed in this context")));
+
+    /* Build a tuple descriptor for our result type */
+    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+        elog(ERROR, "return type must be a row type");
+    
+    per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+    oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+    tupstore = tuplestore_begin_heap(true, false, work_mem);
+    rsinfo->returnMode = SFRM_Materialize;
+    rsinfo->setResult = tupstore;
+    rsinfo->setDesc = tupdesc;
+
+    MemoryContextSwitchTo(oldcontext);
+
+    for (cacheId = 0 ; cacheId < SysCacheSize ; cacheId++)
+    {
+        Datum values[PG_GET_SYSCACHE_SIZE];
+        bool nulls[PG_GET_SYSCACHE_SIZE];
+        int i;
+
+        memset(nulls, 0, sizeof(nulls));
+
+        i = 0;
+        values[i++] = cacheinfo[cacheId].reloid;
+        values[i++] = cacheinfo[cacheId].indoid;
+        values[i++] = Int64GetDatum(CatCacheGetSize(SysCache[cacheId]));
+        tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+    }
+
+    tuplestore_donestoring(tupstore);
+
+    return (Datum) 0;
+}
+
 /*
  * OID comparator for pg_qsort
  */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 0625eff219..3154574f62 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -79,6 +79,7 @@
 #include "tsearch/ts_cache.h"
 #include "utils/builtins.h"
 #include "utils/bytea.h"
+#include "utils/catcache.h"
 #include "utils/guc_tables.h"
 #include "utils/float.h"
 #include "utils/memutils.h"
@@ -2113,6 +2114,38 @@ static struct config_int ConfigureNamesInt[] =
         NULL, NULL, NULL
     },
 
+    {
+        {"cache_memory_target", PGC_USERSET, RESOURCES_MEM,
+            gettext_noop("Sets the minimum syscache size to keep."),
+            gettext_noop("Cache is not pruned before exceeding this size."),
+            GUC_UNIT_KB
+        },
+        &cache_memory_target,
+        0, 0, MAX_KILOBYTES,
+        NULL, NULL, NULL
+    },
+
+    {
+        {"cache_prune_min_age", PGC_USERSET, RESOURCES_MEM,
+            gettext_noop("Sets the minimum unused duration of cache entries before removal."),
+            gettext_noop("Cache entries that live unused for longer than this seconds are considered to be
removed."),
+            GUC_UNIT_S
+        },
+        &cache_prune_min_age,
+        600, -1, INT_MAX,
+        NULL, NULL, NULL
+    },
+
+    {
+        {"min_cached_plans", PGC_USERSET, RESOURCES_MEM,
+            gettext_noop("Sets the minimum number of cached plans kept on memory."),
+            gettext_noop("Timeout invalidation of plancache is not activated until the number of plancaches reaches
thisvalue. -1 means timeout invalidation is always active.")
 
+        },
+        &min_cached_plans,
+        1000, -1, INT_MAX,
+        NULL, NULL, NULL
+    },
+
     /*
      * We use the hopefully-safely-small value of 100kB as the compiled-in
      * default for max_stack_depth.  InitializeGUCOptions will increase it if
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 7486d20a34..917d7cb5cf 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -126,6 +126,8 @@
 #work_mem = 4MB                # min 64kB
 #maintenance_work_mem = 64MB        # min 1MB
 #autovacuum_work_mem = -1        # min 1MB, or -1 to use maintenance_work_mem
+#cache_memory_target = 0kB    # in kB
+#cache_prune_min_age = 600s    # -1 disables pruning
 #max_stack_depth = 2MB            # min 100kB
 #dynamic_shared_memory_type = posix    # the default is the first option
                     # supported by the operating system:
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 860571440a..c0bfcc9f70 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9800,6 +9800,15 @@
   proargmodes => '{o,o,o,o,o,o,o,o,o,o,o}',
   proargnames =>
'{slot_name,plugin,slot_type,datoid,temporary,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn}',
   prosrc => 'pg_get_replication_slots' },
+{ oid => '3423',
+  descr => 'syscache size',
+  proname => 'pg_get_syscache_sizes', prorows => '100', proisstrict => 'f',
+  proretset => 't', provolatile => 'v', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{oid,oid,int8}',
+  proargmodes => '{o,o,o}',
+  proargnames => '{relid,indid,size}',
+  prosrc => 'pg_get_syscache_sizes' },
 { oid => '3786', descr => 'set up a logical replication slot',
   proname => 'pg_create_logical_replication_slot', provolatile => 'v',
   proparallel => 'u', prorettype => 'record', proargtypes => 'name name bool',
diff --git a/src/include/utils/catcache.h b/src/include/utils/catcache.h
index 7b22f9c7bc..9c326d6af6 100644
--- a/src/include/utils/catcache.h
+++ b/src/include/utils/catcache.h
@@ -22,6 +22,7 @@
 
 #include "access/htup.h"
 #include "access/skey.h"
+#include "datatype/timestamp.h"
 #include "lib/ilist.h"
 #include "utils/relcache.h"
 
@@ -61,6 +62,7 @@ typedef struct catcache
     slist_node    cc_next;        /* list link */
     ScanKeyData cc_skey[CATCACHE_MAXKEYS];    /* precomputed key info for heap
                                              * scans */
+    int            cc_tupsize;        /* total amount of catcache tuples */
 
     /*
      * Keep these at the end, so that compiling catcache.c with CATCACHE_STATS
@@ -119,7 +121,9 @@ typedef struct catctup
     bool        dead;            /* dead but not yet removed? */
     bool        negative;        /* negative cache entry? */
     HeapTupleData tuple;        /* tuple management header */
-
+    int            naccess;        /* # of access to this entry, up to 2  */
+    TimestampTz    lastaccess;        /* approx. timestamp of the last usage */
+    int            size;            /* palloc'ed size off this tuple */
     /*
      * The tuple may also be a member of at most one CatCList.  (If a single
      * catcache is list-searched with varying numbers of keys, we may have to
@@ -189,6 +193,28 @@ typedef struct catcacheheader
 /* this extern duplicates utils/memutils.h... */
 extern PGDLLIMPORT MemoryContext CacheMemoryContext;
 
+/* for guc.c, not PGDLLPMPORT'ed */
+extern int cache_prune_min_age;
+extern int cache_memory_target;
+
+/* to use as access timestamp of catcache entries */
+extern TimestampTz catcacheclock;
+
+/*
+ * SetCatCacheClock - set timestamp for catcache access record
+ */
+static inline void
+SetCatCacheClock(TimestampTz ts)
+{
+    catcacheclock = ts;
+}
+
+static inline TimestampTz
+GetCatCacheClock(void)
+{
+    return catcacheclock;
+}
+
 extern void CreateCacheMemoryContext(void);
 
 extern CatCache *InitCatCache(int id, Oid reloid, Oid indexoid,
@@ -227,5 +253,6 @@ extern void PrepareToInvalidateCacheTuple(Relation relation,
 
 extern void PrintCatCacheLeakWarning(HeapTuple tuple);
 extern void PrintCatCacheListLeakWarning(CatCList *list);
+extern int CatCacheGetSize(CatCache *cache);
 
 #endif                            /* CATCACHE_H */
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 5fc7903a06..338b3470b7 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -110,11 +110,13 @@ typedef struct CachedPlanSource
     bool        is_valid;        /* is the query_list currently valid? */
     int            generation;        /* increments each time we create a plan */
     /* If CachedPlanSource has been saved, it is a member of a global list */
-    struct CachedPlanSource *next_saved;    /* list link, if so */
+    struct CachedPlanSource *prev_saved;    /* list prev link, if so */
+    struct CachedPlanSource *next_saved;    /* list next link, if so */
     /* State kept to help decide whether to use custom or generic plans: */
     double        generic_cost;    /* cost of generic plan, or -1 if not known */
     double        total_custom_cost;    /* total cost of custom plans so far */
     int            num_custom_plans;    /* number of plans included in total */
+    TimestampTz    last_access;    /* timestamp of the last usage */
 } CachedPlanSource;
 
 /*
@@ -143,6 +145,9 @@ typedef struct CachedPlan
     MemoryContext context;        /* context containing this CachedPlan */
 } CachedPlan;
 
+/* GUC variables */
+extern int min_cached_plans;
+extern int plancache_prune_min_age;
 
 extern void InitPlanCache(void);
 extern void ResetPlanCache(void);