ApsaraDB RDS:RDS PostgreSQL Buffer Pool Extension (BPE)

How it works

General ESSDs (Enterprise SSDs) use a three-tier storage architecture with high-speed cache disks as an intermediate layer between memory and ESSD storage. The I/O acceleration feature directs temporary data to this cache layer.

Without I/O acceleration, all data — including temporary tables and files generated during query execution — is stored on cloud disks. With I/O acceleration enabled, the query executor separates data by type:

• Non-temporary data (regular tables, views): read from cloud disks into memory for processing, as usual.

• Temporary data (temporary tables and files): read and written directly on cache disks.

ApsaraDB RDS for PostgreSQL uses the rds_temp_tablespace tablespace to represent the cache disk layer. Temporary objects — those created without an explicit tablespace assignment — are automatically placed in rds_temp_tablespace once you set the temp_tablespaces parameter.

Use cases

I/O acceleration is most effective when queries generate large volumes of temporary data that exceed available memory:

• Sorting, grouping, aggregation, and joins: operations that spill intermediate results to disk.

• Recursive queries using common table expressions (CTEs): CTEs that materialize large intermediate result sets.

• Ad hoc queries without supporting indexes: complex filters or aggregations that require full scans.

• Analytic queries on large tables or multiple tables: reporting and analytics workloads with high I/O demands.

• Workloads that use temporary tables: any application logic that explicitly creates temporary tables.

Prerequisites

Before you enable I/O acceleration, make sure your instance meets all of the following requirements:

The instance must also reside in one of the following regions and zones:

Enable I/O acceleration

Option 1: Enable when creating an instance (recommended)

When creating an RDS instance, configure the parameters to meet the prerequisites listed above, then turn on I/O Acceleration.

SET temp_tablespace TO 'rds_temp_tablespace';

-- Create a regular table
CREATE TABLE test_table (di INT);

-- Create a temporary table
CREATE TEMPORARY TABLE test_temp_table (id INT);

-- Query which tablespace each table uses
SELECT
    c.relname AS table_name,
    COALESCE(t.spcname, 'pg_default') AS tablespace_name
FROM
    pg_class c
JOIN
    pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN
    pg_tablespace t ON c.reltablespace = t.oid
WHERE
    c.relkind = 'r'
    AND c.relname IN ('test_table', 'test_temp_table');

    table_name    |   tablespace_name
------------------+---------------------
 test_table       | pg_default
 test_temp_table  | rds_temp_tablespace
(2 rows)

Usage notes

• Cache disk space is allocated based on your instance type. If the cache fills up, the error ERROR: could not write to file "pg_tblspc/xxxx": No space left on device is reported for statements that write temporary data. SQL statements operating on non-temporary data are not affected.

• Data in rds_temp_tablespace is not durable. It is lost during instance spec changes or other data migration operations.

Troubleshooting

What's next

• For all storage types supported by ApsaraDB RDS for PostgreSQL, see Storage types.

• For an overview of general ESSDs and their three-tier architecture, see General ESSDs.

• If your workloads have unpredictable peak IOPS demands, consider enabling I/O burst in addition to I/O acceleration.