Shared Relation Cache

 System catalogs?

Pgpool-II needs to access PostgreSQL's system catalogs whenever it recognizes tables in user's query.  For example, Pgpool-II has to know whether the table in question is a temporary table or not. If it's a temporary table, then the query using the temporary table must be routed to the primary PostgreSQL, rather than one of standby PostgreSQL servers because  PostgreSQL does not allow to create temporary tables on standby servers. Another use case is converting the table name to OID (Object Identifier). OIDs are unique keys for objects managed in PostgreSQL's system catalogs.

Same thing can be said to functions. Details of functions, for instance whether they are "immutable" or not, is important information since it affects the decision on which the query result using the function should be cached or not when query cache feature is enabled.

Local query cache for system catalogs

Sometimes Pgpool-II needs to issue up to as many as 10 queries to the system catalog when it sees a table or function for the first time. Fortunately Pgpool-II does not wast the query results. They are stored in local cache (wee call it "Relation Cache" or "Relcache"), and next time it sees the object in the same or different queries, it extracts info from the local cache . So far so good.

Problem is, the local cache is stored in private memory in Pgpool-II' s child process. For each new session from Pgpool-II clients, different child process is assigned for the session. So even if single table is used in queries, Pgpool-II continues to access system catalogs until the table's info gets filled  in all the local caches.

Shared relation cache

How to mitigate the problem? One of the solutions would be sharing the relation cache info among Pgpool-II processes. This way,  once one of the processes accesses the system catalogs and obtains the info, other processes do not need to access the system catalogs any more. The cache shared by the processes is called "shared relation cache".

How to implement it?

But how to implement it? Fortunately Pgpool-II already has shared query cache. Why can't we store the shared relation cache on it? Here's the idea:

•  If the table/function info is not in the local relation cache, check the shared relation cache.
• If it  is not in the shared relation cache, access the system catalogs and store the info in the local cache. Also copy the info to the shared relation cache,
• If the table/function info is already in the shared relation cache, just copy the info to the local cache. 

You might wonder why there are two kinds of cache: local one and shared one. The reason is locking. Since local cache is never be accessed by multiple processes, it does not need any locking, while the shared relation cache can be accessed by multiple processes, it must be guarded by locking, and this could be a serious bottle neck if there are many processes.

 Cache invalidation

Any cache needs to be invalidated someday.  In the current implementation the cache invalidation is based on timeout. The timeout value can be specified using "relcache_expire" parameter, which controls the local cache timeout as well.

Is it faster?

Is shared relation cache is faster? Well, it depends on use cases.  If there are very small number of tables or functions, overhead of new shared relation cache will not give advantages. However there are many tables/functions, definitely it wins. This is the reason why Pgpool-II has a switch (enabled_shared_relcache) to enable or disable the feature.

To demonstrate the case when the feature wins, I created  1, 2, 4, 8, 16, 32, 64, 128 and 256 tables (all tables are empty), and accessed them using pgbench. pgbench's option is as follows:

pgbench  -C -T 30 -c 30 -n -f script_file

pgbench ran 3 times for each session and I used the average of the numbers.
The script file includes 1-128 SELECTs to access each table.

The blue line (Speed = 1) is the base line, i.e. when the feature is disabled. The red line is when the feature is enabled. As you can see as the number of tables increases, performance increases as well, up to 32 tables. As the number of tables increases, performance is getting lower but still the performance when shared relcache being on is superior than off.

The result may differ according to the workload: if the SELECT is heavy, then the effect may be weaken because the longer access time of SELECT hides the effect of shared relache.

When it will be available?

The feature is already comitted into Pgpool-II version 4.1, which is supposed to be released around September 2019.  So stay tuned!