ALTER TABLE in Greenplum 7: Avoiding Table Rewrite

GPDB7, Product Updates

ALTER TABLE in Greenplum 7: Avoiding Table Rewrite

Author | Huasong Fu

The ALTER TABLE commands are commonly used for operations like adding columns, changing the column data type, and many more. In many cases, such commands require the whole table to be rewritten while holding an exclusive lock on the table. For large tables this can be a very time-consuming process. As a result, it is often considered a problem in real life because all the other operations on the table (including even SELECT) could be blocked for a long time while the ALTER TABLE is rewriting the table. This problem has been discussed well in our previous blog Altered States: Greenplum Alter Table Command.

In Greenplum 7, we have made a few optimizations that help avoid whole table rewrite for many common ALTER TABLE commands, especially for the append-optimized tables. In this blog we will discuss about these changes.

1. Adding column (ALTER TABLE ... ADD COLUMN)

Thanks to an optimization introduced to PostgreSQL 11, Greenplum 7 (which is based on PostgreSQL 12) now does not require table rewrite if you add a column to a heap table, as long as the new column’s DEFAULT value is not volatile. Basically, from the perspective of existing rows in the table, the newly added column will be regarded as missing. When we read the existing rows, the missing columns will be filled with the DEFAULT value of the column. So we do not need to rewrite the existing rows at the time of ALTER TABLE. Here’s some example in Greenplum 7:

psql (12.12)
Type "help" for help.

-- create a heap table
postgres=# CREATE TABLE foo(a int);
CREATE TABLE
postgres=# INSERT INTO foo VALUES (1);
INSERT 0 1
postgres=# SELECT relfilenode FROM pg_class WHERE relname = 'foo';
 relfilenode
-------------
       16472
(1 row)

-- Add a column with non-volatile default value. No table rewrite (as relfilenode doesn't change).
postgres=# ALTER TABLE foo ADD COLUMN b int DEFAULT 10;
ALTER TABLE
postgres=# SELECT relfilenode FROM pg_class WHERE relname = 'foo';
 relfilenode
-------------
       16472
(1 row)

-- Add a column with volatile default value. There is a table rewrite.
postgres=# ALTER TABLE foo ADD COLUMN c int DEFAULT random() * 1000;
ALTER TABLE
postgres=# SELECT relfilenode FROM pg_class WHERE relname = 'foo';
 relfilenode
-------------
       16477
(1 row)

However, for append-optimized row-oriented (or AO) tables, there is a difficulty resulted from its different organization of table data: unlike heap table, AO table does not store how many columns a row has. It always assumes every column is present in the row. Therefore, the optimization could not be trivially applied for AO tables. We have resolved that issue by leveraging an internal “row number” that is unique to each row in an AO table. By recording the current largest row number at the time of ADD COLUMN, we essentially know which rows are missing that column. The rest of the logic remains the same as the heap optimization.

Ideally, the same optimization can be applied to append-optimized column-oriented (or AOCO, columnar etc.) tables. However, historically we have other optimization being applied which is to only write a single column data. This is already a big improvement than having to rewrite the entire table. Further optimizing away the single column write for AOCO is currently in the plan.

2. Changing column type (... ALTER COLUMN ... TYPE)

When we alter column type that is not binary coercible with the existing column type (i.e. we need to modify the column data in order for it to make sense to the new type), the entire table is going to be rewritten. This has been unfortunately the case for all access methods (heap, AO, AOCO). By nature, it is hard to avoid table rewrite for heap and AO tables because of their row-oriented data format. However, for AOCO tables, it is much easier to do something similar to what we did for ADD COLUMN for AOCO tables.

In Greenplum 7, we have implemented such an optimization for AOCO tables: we only rewrite the column that we are altering, but nothing else. Therefore, altering column type does not require table rewrite anymore which is a great improvement for wide tables (P.S. generated column is the same in this aspect). Here’s a simple example to see it work:

psql (12.12)
Type "help" for help.

-- comparing between heap and AOCO tables here
postgres=# CREATE TABLE heaptable(a int, b int);
CREATE TABLE
postgres=# CREATE TABLE cotable(a int, b int) USING ao_column;
CREATE TABLE
postgres=# select relname, relfilenode from pg_class where relname = 'heaptable' or relname = 'cotable';
  relname  | relfilenode
-----------+-------------
 heaptable |       33630
 cotable   |       33631
(2 rows)

-- Altering column type for a heap table. Requiring a table rewrite.
postgres=# ALTER TABLE heaptable ALTER COLUMN b TYPE text;
ALTER TABLE
-- Altering column type for a AOCO table. No table rewrite.
postgres=# ALTER TABLE cotable ALTER COLUMN b TYPE text;
ALTER TABLE

postgres=# select relname, relfilenode from pg_class where relname = 'heaptable' or relname = 'cotable';
  relname  | relfilenode
-----------+-------------
 cotable   |       33631
 heaptable |       33637
(2 rows)

3. Changing column encoding (storage) option (... ALTER COLUMN ... SET ENCODING)

Greenplum 7 has introduced a few new ALTER TABLE commands, including this ALTER COLUMN ... SET ENCODING command to alter the encoding option for AOCO tables. Since AOCO tables often have different encoding option (such as compression settings) for different columns, this command will come in handy when there is a need for fine-tuning each column in the table.

As you can imagine, it does not need table rewrite neither! All we need is just rewrite the column with whatever new encoding option that is intended for that column. For example:

postgres=# CREATE TABLE cotable(a int, 
                                b int ENCODING (compresstype=zlib,compresslevel=5)) 
                                USING ao_column;
CREATE TABLE
postgres=# select relfilenode from pg_class where relname = 'cotable';
 relfilenode
-------------
       33640
(1 row)

-- Alter the compression setting of column 'b' (to have smaller compression level). No table rewrite.
postgres=# ALTER TABLE cotable ALTER COLUMN b 
SET ENCODING (compresstype=rle_type,compresslevel=1);
ALTER TABLE
postgres=# select relfilenode from pg_class where relname = 'cotable';
 relfilenode
-------------
       33640
(1 row)

Note that, this is an AOCO-only operation so the optimization is not applicable to other access methods (heap and AO).

4. One table rewrite for ALL

Similar to changing column encoding option, in Greenplum 7 we have also supported changing table storage options for AO and AOCO tables (ALTER TABLE ... SET). Unlike previous cases discussed in this blog, it is almost certain that we have to rewrite the entire table because these storage options affect the entire table. But, if one tries to alter storage option to be the same as the table’s existing options (likely a mistake), there won’t be any table rewrite, e.g.:

postgres=# CREATE TABLE aotable(a int) USING ao_row WITH(compresstype=zlib,compresslevel=5);
CREATE TABLE
postgres=# select relfilenode from pg_class where relname = 'aotable';
 relfilenode
-------------
       33668
(1 row)

-- No rewrite since no real change in the options.
postgres=# ALTER TABLE aotable SET (compresstype=zlib,compresslevel=5);
ALTER TABLE
postgres=# select relfilenode from pg_class where relname = 'aotable';
 relfilenode
-------------
       33668
(1 row)

-- "compresslevel" changed, need a table rewrite. Note that 3 additional files 
-- are consumed for some internal auxiliary tables for the AO table.
postgres=# ALTER TABLE aotable SET (compresstype=zlib,compresslevel=6);
ALTER TABLE
postgres=# select relfilenode from pg_class where relname = 'aotable';
 relfilenode
-------------
       33672
(1 row)

It is advised that if there is a need to do multiple ALTER TABLE commands that all require table rewrite, one can combine them together in one single ALTER TABLE command (so each will be a subcommand). In that way, only one table rewrite is performed instead of multiple (similar notion was also mentioned in our previous post for GPDB6). Here’s an example:

postgres=# CREATE TABLE aotable(a int) USING ao_row;
CREATE TABLE

postgres=# select relfilenode from pg_class where relname = 'aotable';
 relfilenode
-------------
       33676
(1 row)

-- We are doing two ALTER TABLE commands here:
-- 1. Add a volatile column, and 
-- 2. change compression options.
-- Both require a table rewrite, but doing them together requires just one table rewrite (same as the previous example, 3 additional files are consumed for auxiliary tables).
postgres=# ALTER TABLE aotable ADD COLUMN b INT DEFAULT random() * 1000, SET (compresstype=zlib,compresslevel=5);
ALTER TABLE
postgres=# select relfilenode from pg_class where relname = 'aotable';
 relfilenode
-------------
       33680
(1 row)

Note that, however, currently we do not support altering access method and storage options in two ALTER TABLE subcommands. But the same can be done in one single ALTER TABLE ... SET ACCESS METHOD ... WITH ... command:

postgres=# CREATE TABLE foo (a int);
CREATE TABLE
postgres=# ALTER TABLE foo SET ACCESS METHOD ao_row WITH (compresstype=zlib,compresslevel=5);
ALTER TABLE

Summary

Similar to the one in our previous blog, here’s an updated table to summarize the table rewrite aspect for the ALTER TABLE commands discussed in the blog.

“Yes” indicates table will be rewritten.

“No” indicates it won’t.

Operation	AO Columnar (AOCO)	AO Row (AO)	Heap
DROP COLUMN	No	No	No
ALTER COLUMN TYPE [1]	No [2]	Yes	Yes
ADD COLUMN (w/ non-volative default [3])	No [2]	No	No
ADD COLUMN (w/ volative default)	No [2]	Yes	Yes
ALTER COLUMN SET ENCODING	No [2]	N/A	N/A
SET (<relation options>)[4]	Yes	Yes	Yes
SET ACCESS METHOD	Yes	Yes	Yes

[1] If the new type is not binary coercible with the old. Otherwise, no rewrite for all cases.

[2] But write/rewrite single column data.
[3] Including NULL.
[4] If the options differ from the current table. Otherwise, no rewrite for all cases.