How to change a table to a Beam table - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL supports three storage engines: Heap, Append-Optimized (AO), and Beam. Each engine is designed for different workload patterns. You can change the storage engine of an existing table in place, without creating a new table or migrating data manually.

Storage engine comparison

The following table compares the key characteristics of each storage engine to help you determine which engine best fits your workload.

Feature	Heap	AO (Append-Optimized)	Beam
Architecture	Row-oriented	Row-oriented or column-oriented	Hybrid row-column (developed in-house by Alibaba Cloud)
Primary key support	Yes	No	Yes
Data compression	Not supported by default	Supported	Supported (uses compression algorithms to reduce storage costs)
Concurrent updates and deletes	Yes	Yes	Yes
Range scanning	Not efficient by default	-	Uses column pruning and zone mapping for improved filter capabilities
I/O performance	Random reads may take extended time and cause I/O bottlenecks	-	Significantly reduces disk I/O operations and improves I/O utilization
Storage costs	High for large data volumes	-	Lower (due to compression)
Best suited for	OLTP services, high-concurrency writes, large data volumes, low query complexity	High-concurrency reads, large write volumes, long-term stored but infrequently accessed datasets	High-performance real-time writes and high-throughput batch imports

Prerequisites

Your instance runs AnalyticDB for PostgreSQL V7.0 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 version requirement, 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 during the conversion. The lock duration depends on the amount of data in the table. We recommend that you change the storage engine during off-peak hours. If a table remains locked for an extended period of time, submit a ticket.

Syntax

ALTER TABLE table_name SET ACCESS METHOD access_method

Parameters

Parameter	Description
`table_name`	The name of the table whose storage engine you want to change.
`access_method`	The new storage engine. 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 the table and insert sample data.

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

Change the storage engine to Beam.

   ALTER TABLE testtable SET ACCESS METHOD beam;

Change the storage engine back to Heap.

   ALTER TABLE testtable SET ACCESS METHOD heap;

Change the storage engine to Beam with a specific compression algorithm and compression level.
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.
```
   SET beam_default_storage_options = 'compresstype=auto, compresslevel=5';
   ALTER TABLE testtable SET ACCESS METHOD beam;
```

Change the storage engine of a partitioned table

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

Create the partitioned table with two partitions.

   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 to Beam.

   ALTER TABLE am_partitioned_1 SET ACCESS METHOD beam;

Change the storage engine of all partitions back to Heap.
```
   ALTER TABLE am_partitioned SET ACCESS METHOD heap;
```