Pgpool-II Configuration Parameters - enable_shared_relcache

person holding black and white electronic device

In the previous post, I explained how to control the client connections by using reserved_connections parameter. In this post I would like to introduce how to use shared relation cache to improve performance.

Shared relation cache is added in Pgpool-II 4.1. Before explaining this feature, let's have a look at what relation cache of Pgpool-II is.

What is relation cache?

If Pgpool-II receives a client query, Pgpool-II will parse the query and extract the table name, then access PostgreSQL system catalogs to get the relation information. Pgpool-II obtains the following information from PostgreSQL system catalogs:

  • whether a table included in the query is a temporary table or not
  • whether a table included in the query is an unlogged table or not
  • whether a function included in the query is "immutable" or not (if a function is used in the query and query cache mode is enabled)

If the table included in the query is a temporary table or an unlogged table, then Pgpool-II must send the query to the primary PostgreSQL.

To reuse the information obtained from system catalogs, Pgpool-II stores the information in local cache. If the same object (e.g. table, function) appears in the subsequent queries, Pgpool-II fetches information from the local cache instead of accessing system catalogs.

Each child process stores the local relation cache in process private memory and other child processes can not access the process local relation cache. If a different process is assigned to a client request, the process has to access PostgreSQL system catalogs, even if same table information is stored in other child process's local relation cache. If a large value is set to num_init_children, Pgpool-II accesses PostgreSQL system catalog as many times as the value of num_init_children, and it may increase the overhead.

Shared relation cache feature introduced in Pgpool-II 4.1 can resolve this problem.

Shared relation cache

Pgpool-II can create relation cache in shared memory by enabling enable_shared_relcache parameter. If a child process has created a relation cache entry in the shared memory, other child processes can retrieve the result by accessing the shared memory. This feature is pretty effective and it is recommended to enable this feature. 

Configure the following parameter to enable shared relation cache. Default is on.

enable_shared_relcache = on

Let me explain how shared relation cache works in Pgpool-II.

  1. If the table/function information is not found in the local relation cache, then Pgpool-II checks the shared relation cache.
  2. If the table/function information is found in the shared relation cache, Pgpool-II copies the information to local cache.
  3. If it is not found in the shared relation cache, Pgpool-II accesses PostgreSQL system catalogs and stores the result in both local relation cache and shared relation cache.


Popular posts from this blog

Query Load Balancing in Pgpool-II

Monitoring PostgreSQL Cluster via Pgpool-II with Prometheus

Connection Pooling in Pgpool-II