Storage engine
The AnalyticDB for PostgreSQL storage engine is built on PostgreSQL and delivers high scalability, high availability, and strong transaction processing capabilities. It supports six core capabilities that let you tune storage for performance, cost, and workload type.
Hybrid storage
The storage engine supports hybrid row-column storage. Choose the layout that best matches your access patterns:
| Store type | Best for | When to choose |
|---|---|---|
| Row store | Workloads with frequent INSERT, UPDATE, and DELETE operations, or point queries that retrieve all columns of a single record | Update-heavy workloads; frequent single-row lookups; transactions that touch many columns at once |
| Column store | Analytical (AP) queries that aggregate a small number of columns across large datasets | Read-heavy workloads; aggregation queries (SUM, AVG, COUNT) on a few columns; rarely updated data |
For mixed workloads, choose the store type that matches the dominant access pattern for each table. If a table is updated frequently, use row store. If a table is queried with column-selective aggregations, use column store.
Index types
Three index types are available to accelerate different query patterns:
| Index | Use when |
|---|---|
| B-tree index | Equality queries and range queries |
| Block range index (BRIN) | Range queries on naturally ordered columns (for example, timestamps), used in combination with sorting for rough set filtering |
| Generalized inverted index (GIN) | Full-text search and array containment queries |
Indexes are most effective for point queries and low-selectivity queries where only a small fraction of rows match the predicate.
Data compression
The storage engine supports three compression algorithms. Choose based on your CPU budget and scan performance requirements:
| Algorithm | Best for | When to choose |
|---|---|---|
| zlib | Archival data where storage cost matters more than query speed | Limited CPU available for decompression; cold data accessed infrequently |
| zstd | General-purpose workloads balancing compression ratio and I/O throughput | Default choice for most tables; good balance of compression and decompression performance |
| LZ4 | Frequently scanned tables where decompression latency is a bottleneck | Hot data with frequent full-table or large-range scans; CPU is constrained at scan time |
A higher compression ratio reduces storage costs directly. Faster decompression reduces I/O time during scans, which matters most for analytical workloads that read large volumes of data.
OSS external tables
OSS external tables let you store data in Object Storage Service (OSS) at a lower cost than local storage. Reads and writes on external tables use the same SQL syntax as local tables, so no application changes are required.
Use OSS external tables for historical or infrequently accessed data where query latency requirements are relaxed.
Data partitioning
Partitioning divides a table into segments and prunes irrelevant segments at query time, reducing I/O.
By time (most common): Partition by day, month, or year to limit scans to the relevant time window.
By value or range: Partition by a categorical or numeric column for workloads with predictable access patterns.
Partitioning also supports tiered storage: keep recent (hot) data in local hot storage and move older data to cold storage or OSS external tables. This lets you control costs without dropping historical data.
Sorting and rough set filtering
When you create a table, specify one or more sort columns. At query time, the engine applies min-max rough set filtering: each data block stores the minimum and maximum values of the sort columns, and the engine skips any block whose min-max range does not overlap the query predicate.
For example, if a table stores multiple years of data sorted by date and a query targets a short date range, the engine can skip most data blocks — scanning only the relevant blocks instead of the entire table.
Sorting also increases the compression ratio because values in the same block are more similar, which reduces storage costs.
Sort columns that appear frequently in WHERE clauses and range predicates — for example, a timestamp column used in date-range filters — benefit most from this optimization.