ApsaraDB RDS:Use the cache eviction feature

Prerequisites

Before you begin, ensure that:

• Your RDS instance runs PostgreSQL 11 or later

• If the feature is not yet available, update the minor engine version of your instance. For more information, see Update the minor engine version.

Background

Each time a PostgreSQL backend process accesses a table, it caches the table's metadata in process-local memory. This cache is never proactively released—it accumulates for the lifetime of the connection. In workloads with many persistent connections, this can exhaust available memory and trigger OOM errors.

Use cases

• SaaS applications that maintain large numbers of persistent database connections

Usage notes

• Some caches cannot be evicted to ensure the instance runs correctly. In extreme cases, the configured cache limit may not be fully enforced.

• Modifying the parameters described in this topic does not trigger an instance restart.

Key concepts

ApsaraDB RDS for PostgreSQL maintains three types of connection caches:

Configure cache eviction

-- Limit relcache to 1,000 user tables for the current connection
SET rds_relcache_max_cached_relations = 1000;

-- Remove the relcache limit for the current connection
SET rds_relcache_max_cached_relations = -1;

-- Limit syscache to 10,000 system table tuples for the current connection
SET rds_syscache_max_cached_tuples = 10000;

-- Remove the syscache limit for the current connection
SET rds_syscache_max_cached_tuples = -1;

Monitor cache usage per process

To monitor cache usage for each connection, install the rdsutils extension and enable the cache monitor parameter.

1. Install the extension:

   CREATE EXTENSION rdsutils;

2. In the ApsaraDB RDS console, set rds_enable_cache_monitor to on. You can modify the rds_enable_cache_monitor parameter only in the ApsaraDB RDS console. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

Enabling rds_enable_cache_monitor adds approximately 2 ms of overhead per SQL statement.

Parameters

Setting rds_relcache_max_cached_relations or rds_syscache_max_cached_tuples too low can degrade query performance. Size the limits based on the number of tables and fields your workload accesses. If your workload accesses many tables, use a larger value.