ApsaraDB RDS:RDS PostgreSQL Data Archiving

Overview

RDS PostgreSQL accesses OSS using tablespaces and external file systems, allowing you to move data between ESSDs and OSS using DDL statements. When data archiving is enabled, the instance adds a cold storage directory /cold-data outside the main data directory and creates a corresponding tablespace named rds_oss. Data in the rds_oss tablespace resides in OSS, while data in the pg_default tablespace remains on ESSDs.

After enabling data archiving for RDS PostgreSQL, run the following DDL statements to move data between ESSDs and OSS (applies to standard tables and child tables of partitioned tables). Handle table and index data separately during the transfer.

-- Archive
ALTER TABLE $tableName SET tablespace rds_oss;
ALTER INDEX $indexName SET tablespace rds_oss;
-- Retrieve
ALTER TABLE $tableName SET tablespace pg_default;
ALTER INDEX $indexName SET tablespace pg_default;

Benefits

With data archiving enabled, you can leverage OSS’s massive scale, security, low cost, and high reliability to achieve near-zero-cost, unlimited-capacity archival storage in RDS PostgreSQL for infrequently accessed data. Compared to PL1 ESSDs, storage costs drop by 80%.

Applicability

• Engine: RDS PostgreSQL

• Major version: 12 or later

• Minor version: 20240530 or later. To upgrade the minor engine version, see Upgrade minor engine version.

  Note

  For major version 15, data archiving is supported starting from minor version 20240229.

• Edition: Basic Edition, high-availability series

• Instance type: Standard Edition, Yitian Edition

• Storage type: Premium ESSD

  Note

  If the storage class is enterprise SSD, you can change from enterprise SSD to Premium ESSD.

• Billing method: subscription, pay-as-you-go

• Instance type: at least 8 CPU cores

• Region: Not supported in South Korea (Seoul), UAE (Dubai), or UK (London).

Limits

• Enabling data archiving affects backups and certain features:

  • Backup within seconds is not supported.

  • Cross-region backup is not supported.

  • During database or table recovery, only non-archived data in the selected database can be restored.

  • Downloaded backup sets contain only non-archived data.

  Note

  For instances with data archiving enabled, backup and restore operations take longer than usual because archived data must also be recovered.

• Before the system archives table or index data to OSS storage, you must perform data cleanup. The storage performance of OSS is significantly lower than that of ESSD, which results in low autovacuum efficiency in OSS tablespaces. To prevent prolonged autovacuum operations from negatively affecting the performance of the entire instance, the design requires that all pages of archived tables being transferred in be set to all_visible and all_frozen to avoid triggering autovacuum in the archived tablespace. To clean up archived data, we recommend that you use the following methods:

  VACUUM (FREEZE) table_name;

  Add the VERBOSE option to print detailed cleanup information, which helps identify issues and achieve complete cleanup.

  • If long-running transactions or replication slots exist, some transaction IDs may need to be retained, making full cleanup impossible.

  • Different RDS PostgreSQL versions use different optimization strategies. Use verbose output to analyze and specify parameters for complete cleanup. For example:

    VACUUM (FREEZE, INDEX_CLEANUP on) table_name;

    • For RDS PostgreSQL 14 and later, set PROCESS_TOAST to enable TOAST table cleanup.

    • For RDS PostgreSQL 16 and later, set PROCESS_MAIN to enable main table cleanup.

  For more information about VACUUM, see VACUUM.

Notes

• Run VACUUM on the target table before performing data archiving.

• Due to OSS access latency, querying cold data tables incurs higher latency (around 10 ms) compared to ESSD storage.

  Note

  If a table archived to OSS appears to respond quickly, this is likely due to caching in the database Buffer Pool or the operating system Page Cache.

• OSS reads consume memory and network bandwidth from the ECS instance. Adjust the speed and frequency of data transfers and queries accordingly.

Billing

Enable and Disable Data Archiving

Enable Data Archiving

1. Go to the RDS Instances page. Select a region at the top, then click the target instance ID.

2. On the Basic Information tab of the instance details page, click Basic Information, then toggle on Configure General ESSD to enable the Data Archiving feature.

   Important

   Enabling OSS data archiving causes a transient disconnection of about 30 seconds. Perform this operation during off-peak hours and ensure your application has an automatic reconnection mechanism.

Disable Data Archiving

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

2. On the Basic Information tab of the instance details page, click Basic Information, then toggle off Configure General ESSD to disable the Data Archiving feature.

   Important

   • Disabling OSS data archiving is not a lossless operation and causes a transient disconnection of about 30 seconds.

   • You cannot disable OSS data archiving if any archived table data exists in OSS. First, use DDL to move archived tables back to online storage or drop them before disabling the feature.

View Archived Data

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

2. In the navigation pane on the left, click Cold Storage Management.

3. On the Archive Layer Settings page, view archived data.

   The page shows a data size of 360.00 KB. After selecting a target database from the Select Database dropdown, the table below displays archived information including Database Name, Table Name, Tablespace, and Table Schema Size.

References

• For other storage types supported by RDS PostgreSQL, see Storage Types.

• For more information about Premium ESSDs, see Premium ESSD.

• If your workload fluctuates significantly with frequent peaks, enable the I/O performance burst feature for Premium ESSDs. This allows IOPS to exceed the maximum limit during peak periods, meeting sudden demand. For more information, see I/O Performance Burst.

• When facing database disk I/O bottlenecks, the Buffer Pool Extension (BPE) feature of Premium ESSDs can significantly improve I/O performance under heavy or frequent read/write workloads. For more information, see Buffer Pool Extension (BPE).

Related APIs