Change the storage engine of heap tables or AO tables to Beam - AnalyticDB

AnalyticDB for PostgreSQL supports the following storage engines: Heap, Append-optimized (AO), and Beam. Each storage engine is suitable for specific business scenarios. If your business requirements change, you can change the storage engine of tables without the need to create tables and migrate data. For example, your business requirements change from high-concurrency write operations to high-concurrency read operations.

The Heap, AO, and Beam engines provide the following features:

Heap: supports online transaction processing (OLTP) services and concurrent update and delete operations. By default, the Heap engine does not support data compression or efficient range scanning. High storage costs are required to process large amounts of data. In most cases, random read operations require an extended period of time to complete and may result in I/O bottlenecks. The Heap engine is suitable for scenarios that involve high-concurrency write operations, large data volumes, and low query complexity.
AO: supports row-oriented storage and column-oriented storage but does not support primary keys. The AO engine is suitable for scenarios that involve high-concurrency read operations, large numbers of write operations, and long-term stored but infrequently accessed datasets.
Beam: a hybrid row-column storage engine that is developed in-house by Alibaba Cloud. The Beam engine supports data compression and primary keys and uses column pruning and zone mapping to improve filter capabilities. The Beam engine uses compression algorithms to reduce data storage costs and significantly reduces the number of disk I/O operations and improves I/O utilization. The Beam engine is suitable for scenarios that require high-performance real-time write operations and high-throughput batch imports.

Supported versions

AnalyticDB for PostgreSQL V7.0 instances of V7.0.6.2 or later.

Note

For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor version of an instance. If your AnalyticDB for PostgreSQL instance does not meet the preceding requirements, we recommend that you update the minor version of the instance. For more information, see UpgradeDBVersion.

Usage notes

When you change the storage engine of a table, the table is locked. You cannot perform read or write operations on the table. The amount of time for which the table is locked varies based on the amount of data in the table. We recommend that you change the storage engine during off-peak hours. If a table remains unlocked for an extended period of time, submit a ticket.

Syntax

ALTER TABLE table_name SET ACCESS METHOD access_method

Parameters

access_method: the new storage engine that you want to use. Valid values: heap, beam, ao_row, and ao_column.

Examples

Change the storage engine of a standard table

In this example, a heap table named testtable is used.

CREATE TABLE testtable (i int) USING heap;
INSERT INTO testtable SELECT * FROM generate_series(1,10000);

Change the storage engine of the table to Beam.
```
ALTER TABLE testtable SET ACCESS METHOD beam;
```
Change the storage engine of the table to Heap.
```
ALTER TABLE testtable SET ACCESS METHOD heap;
```
Change the storage engine of the table to Beam and specify the compression algorithm and the compression level for the Beam table.
```
SET beam_default_storage_options = 'compresstype=auto, compresslevel=5';
ALTER TABLE testtable SET ACCESS METHOD beam;
```
Note
- For information about the compression algorithm and the compression level, see the "Specify a compression algorithm" section of the Beam usage topic.
- The configurations for the compression algorithm and the compression level are session-level settings and may slow down data read and write operations.

Change the storage engine of a partitioned table

In this example, a partitioned table named am_partitioned is used.

CREATE TABLE am_partitioned(x INT, y INT)
PARTITION BY HASH (x) USING heap;
CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 0);
CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 1);

Change the storage engine of all partitions in the partitioned table to Beam.
```
ALTER TABLE am_partitioned SET ACCESS METHOD beam;
```
Change the storage engine of a single partition in the partitioned table to Beam.
```
ALTER TABLE am_partitioned_1 SET ACCESS METHOD beam;
```
Change the storage engine of all partitions in the partitioned table to Heap.
```
ALTER TABLE am_partitioned SET ACCESS METHOD heap;
```