AnalyticDB for PostgreSQL provides compute nodes for local storage and supports row stores and column stores. AnalyticDB for PostgreSQL also supports hard disk drives (HDDs) and non-volatile memory such as solid state drives (SSDs). A single node can provide a maximum storage capacity of 10 TB. To meet requirements in various scenarios, AnalyticDB for PostgreSQL provides rich storage features such as compressed storage, Object Storage Service (OSS) external tables, shared storage, and tiered storage.
Compressed storage
AnalyticDB for PostgreSQL allows you to use column stores for compressed storage. When you create tables, you can specify whether to compress data for append-optimized (AO) or append optimized column oriented (AOCO) tables. By default, AnalyticDB for PostgreSQL uses the zstd compression algorithm. AnalyticDB for PostgreSQL also supports the zlib and LZ4 compression algorithms.
Compressed storage provides the following features:
- Reduces the size of files. Compressed tables can reduce the size of files. If you use the zstd compression algorithm, you can reduce file size by approximately 75%. For example, 1 GB of raw data requires only about 250 MB physical storage capacity. If you use the LZ4 compression algorithm, you can reduce file size by approximately 60%. For example, 1 GB of raw data requires only about 400 MB physical storage capacity. You can use compressed storage to reduce file size by 60% to 75%.
- Enhances query performance. To query the data that is stored in compressed tables, you must use algorithms to decompress the data. The saved storage capacity can reduce I/O bandwidth. Therefore, the query performance is improved in scenarios where I/O speeds are low such as when HDDs are used. However, query performance is degraded at high I/O speeds. The decompression speed of the LZ4 algorithm is approximately triple that of the zstd algorithm. In scenarios that require high query performance, we recommend that you use the LZ4 algorithm.
External tables
AnalyticDB for PostgreSQL allows you to use external tables to access OSS and Hadoop. OSS is a storage service provided by Alibaba Cloud. OSS allows you to store large amounts of data in a cost-effective and highly secure and reliable manner to meet storage requirements in various scenarios.
You can use external tables to access OSS, perform data analysis on data that is stored in OSS, and export data from AnalyticDB for PostgreSQL to OSS. The supported data formats include ORC, parquet, CSV, and JSON. External tables can be partitioned. You can perform operations to push down some filter conditions. You can also use external tables to access MaxCompute.
Tiered storage
In many analysis scenarios, data requires timeliness. The more recent data is more frequently accessed, whereas the less recent data is less frequently accessed. AnalyticDB for PostgreSQL can archive partial low-timeliness data based on your specifications and replace a high-cost local storage medium with a low-cost medium such as OSS that provides low access speeds. For example, you can configure the data generated three months ago to be automatically archived to OSS to reduce storage costs. You can reduce storage costs without compromising query performance by using tiered storage.
Shared storage
Database File System (DBFS) is a cloud native shared file storage service that is provided by Alibaba Cloud. AnalyticDB for PostgreSQL deeply integrates with DBFS to provide services based on the DBFS storage media. Compared with the services based on local storage, the DBFS-based services implement I/O acceleration, multi-point reads and writes, and data backup by using the high availability and ultimate I/O performance of DBFS. This reduces storage costs and improves storage performance and reliability.