Hybrid storage in Yandex MPP Analytics for PostgreSQL

Yandex MPP Analytics for PostgreSQL clusters support hybrid storage: some data can be stored in cluster storage and some in hybrid storage. By default, data is stored in cluster storage on disks of the selected type. Rarely used data meant to be stored for a long time can be moved from a cluster storage to a Yandex Object Storage service bucket in a hybrid storage. This will make data storage less costly.

You can enable hybrid storage in Yandex MPP Analytics for PostgreSQL clusters when creating or updating a cluster.

Hybrid storage scope of useHybrid storage scope of use

You can use hybrid storage only for append-optimized tables. When data is migrated between cluster storage and hybrid storage, whole tables are transferred. Tables are processed using the Yezzey extension by Yandex Cloud.

The data of append-optimized tables is stored as a set of segment files, compressed and encrypted, in an Object Storage service bucket. The number of segment files depends on the number of segments in the cluster and on the table structure.

I/O management when using hybrid storageI/O management when using hybrid storage

When running SQL queries against append-optimized tables, there are many requests to segment files in storage. SQL query execution time depends on how efficiently you schedule I/O requests to segment files. If you do not use I/O request scheduling, storage performance is impaired, RAM consumption and SQL query execution time increase. Using the scheduler allows you to protect the cluster from performance degradation when executing SQL queries that involve processing massive amounts of data.

When data is stored in cluster storage, I/O request scheduling is performed by the operating system on the cluster hosts.

When data is stored in hybrid storage, the operating system cannot schedule I/O requests to the Object Storage service bucket. Therefore, to avoid performance degradation, Yandex MPP Analytics for PostgreSQL clusters use YProxy by Yandex Cloud to schedule such requests. Even if the table is in a hybrid storage, the use of YProxy minimizes the impact on SQL query execution time.

When accessing hybrid storage data, throughput is limited to 1 GB per second from a single segment host, regardless of the number of segments per host. For example, if you have 8 segment hosts in your cluster, the maximum throughput for hybrid storage data for the whole cluster will not exceed 8 GB per second.

Learn more about hybrid storage architecture from this Habr article. The article also provides performance tests for different types of storage.

Hybrid storage cleanupHybrid storage cleanup

For AO and AOCO tables in a hybrid storage, the VACUUM and VACUUM FULL operations are performed the same way as for similar tables in the cluster storage. However, hybrid storage data not being affected by the cleanup process, the following procedure is performed to automatically delete unnecessary segment files:

1. When you run the VACUUM and VACUUM FULL operations for hybrid storage tables, the list of segment files in hybrid storage gets updated. Only the files containing the current table rows remain in the list.
2. Yezzey saves the names of unlisted files and the time of the operation in a service table. These files become candidates for deletion.
3. Independently of the cleanup operations, Yezzey checks the entries in this service table on a weekly basis. If a file record is created earlier than the oldest automatic backup, the file gets deleted from the Object Storage service bucket to the recycle bin, and its record gets removed from the service table. After a week's time, the file gets deleted from the recycle bin.

This procedure ensures that files required for recovery from a hybrid storage cluster backup are not deleted from hybrid storage.

Use casesUse cases

• Exporting Greenplum® data to a hybrid storage in Yandex Object Storage

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.