PolarDB:Cold data tiered storage

Benefits

When you enable tiered storage for cold data, the price per unit of storage is about 90% lower than that of an ESSD PL1 disk. For more information about pricing, see Billing rules for tiered storage of cold data.

The tiered storage feature for cold data in PolarDB for PostgreSQL provides the following benefits:

• Easy to use

  • SQL transparency: All SQL operations are transparent. You do not need to modify your code. Federated queries on OSS tables are supported. Data stored on OSS supports create, read, update, and delete (CRUD) operations.

  • Index transparency: You can set data archiving policies for objects such as indexes and materialized views. These operations are transparent.

• Highly flexible

  • Multiple tiered storage policies are supported. You can archive data by table, which includes indexes and materialized views, by partition, or by specified Large Object (LOB) fields. You can also combine these policies for flexible configuration.

• Excellent performance

  • The feature provides excellent query performance. It uses a three-layer caching design: logical object caching within User-Defined Functions (UDFs), shared page caching, and file persistence caching. This design reduces the number of access requests to OSS and minimizes the impact of read and write latency.

• Wide range of scenarios

  • It supports archiving general-purpose, spatiotemporal, and time series data. For example, you can archive data such as spatiotemporal trajectories and high-precision maps to significantly reduce storage costs.

• Secure and reliable

  • Cold data stored in OSS also supports backup and recovery. This reduces backup costs and ensures high availability (HA).

Applicability

How tiered storage works

Tiered storage results in cost savings because it uses the cost-effective OSS as a data storage option. PolarDB for PostgreSQL can also be used with Elastic Block Storage (EBS) and OSS to achieve automatic storage tiering for hot and cold data based on specific usage patterns. This way, PolarDB maintains transparency during SQL CRUD operations and minimizes performance degradation by leveraging a multi-level caching system. The tiered storage architecture is as follows:

Cold storage modes

Cold storage lets you dump data tables, indexes, or materialized views into OSS. It minimizes disk usage, thereby resulting in significant storage savings. After cold storage, all SQL statements that specify CRUD operations are transparent without additional modifications.

PolarDB supports the following cold storage modes:

• Store data within an entire table in OSS and retain indexes in cloud disks. This reduces storage costs while keeping access performance high.

• Store the columns of the LOB type and secondary columns in OSS.

• Store expired partitions in OSS and retain hot partitions in cloud disks. This is a typical tiered storage mode.

Scenarios

The access latency of OSS is hundreds of times higher than that of cloud disks. Consequently, once data is dumped to OSS, its access performance is reduced. However, there are still customers who require fairly high performance when querying or updating their cold storage data. To meet this requirement, PolarDB for PostgreSQL supports two types of storage tiering as follows:

• Dump expired partitions into OSS while retaining hot partitions in cloud disks. This minimizes the impact on the query performance and can reduce storage costs. For more information, see Cold storage of partitioned tables.

• Provide a materialized cache for frequently accessed and updated data in cloud disks and store the entirety of data in OSS. The lifecycle of data in the materialized cache is determined by their access frequency. This can achieve excellent performance and reduce storage costs. For more information, see Materialized cache for cold data.