Introduction
We are introducing a capability to alter the storage characteristics of an already populated table with the ALTER TABLE command in GPDB 7. This means that users can now go from a heap table to an AO or AOCO table (or any manner of combinations of the above). Further, this extends to regular tables as well as partitioned tables. We are also supporting the capability to change the storage options (reloptions) of the table, meaning a user can change the compression settings of the table, blocksize etc.
Motivation
This feature will help users change their table’s storage characteristics dynamically with changes in the access pattern of their tables. As an example, partitions carrying time-series data are perfect candidates for this feature. Older and colder partitions will benefit from the AO format whereas newer, hotter and frequently updated partitions could be heap. Similarly, one could crank up the compression settings of older partitions that won’t be seeing frequent updates. One could also determine that the access pattern of a table is chiefly dealing with a subset of columns – in which case the table could be turned into a columnar table.
Today, if a user wants to change the storage characteristics of a table, they have to dump the data somewhere, drop, re-CREATE the table and then reload the table. If there were any indexes they would have to be recreated too. One could CREATE TABLE LIKE the new table, which would create many of the associated objects, given the user exhaustively enumerates them in the INCLUDING clause. However that doesn’t really solve the problem of dependent objects – if there are dependent objects such as views (or worse views on top of views), all of those objects will have to be dropped, before the table can be dropped. Sometimes CASCADE can be too far-reaching, especially if there are complex depenencies and dependencies have to be carefully examined. Furthermore, if we are dealing with partitioned tables, one would need to drop, create and then reattach the partition. Multi-step processes like these are not only inconvenient, but leave room for partial states.
ALTER TABLE will remove all of this complexity and will offer a seamless experience.
How it works?
In essence, this is no different from other ALTER TABLE operations that rewrite the table. For instance, adding a column to a heap/row-oriented AO table requires a table rewrite. Under the hood, we grab an AccessExclusiveLock for the duration of the entire operation, rewrite the catalog and then rewrite the entire table. As an additional benefit, any dead rows in the original table are left on the side of the road.
Seeing it in action! Changing the AM
In GPDB7, we now define “table access methods (AM)” which is the term used to tell us whether our table is a heap, AO or AOCO table.
In this example, we start off with a heap table and modify its storage and storage options. Modifying a heap table to an AO table constitutes a change of it’s access method from heap to ao_row. Similarly, for an AOCO table, the access method is ao_column.
CREATE TABLE foo(i int);
INSERT INTO foo SELECT generate_series(1, 10000);
\d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Distributed by: (i)
Access method: heap
ALTER TABLE foo SET ACCESS METHOD ao_row;
\d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (i)
Access method: ao_row
ALTER TABLE foo SET ACCESS METHOD ao_column;
\d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
i | integer | | | | plain | | none | 0 | 32768 |
Checksum: t
Distributed by: (i)
Access method: ao_column
Changing storage options
We can achieve this with the ALTER TABLE SET command. Here, we change the table’s compression settings.
ALTER TABLE foo SET (compresslevel=9, compresstype=zlib);
\d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Compression Type: zlib
Compression Level: 9
Block Size: 32768
Checksum: t
Distributed by: (i)
Access method: ao_row
Options: compresslevel=9, compresstype=zlib
For AOCO tables, we can also change the storage options on a per-column basis:
CREATE TABLE foo(i int, j int) USING ao_column;
ALTER TABLE foo ALTER COLUMN i SET ENCODING (compresslevel=7, compresstype=zstd),
ALTER COLUMN j SET ENCODING (compresslevel=2);
\d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
i | integer | | | | plain | | zstd | 7 | 32768 |
j | integer | | | | plain | | zlib | 2 | 32768 |
Checksum: t
Distributed by: (i)
Access method: ao_column
Alternative syntax
We also support legacy GP style, similar to CREATE TABLE.
ALTER TABLE foo SET WITH (appendonly=true, orientation=row);
postgres=# \d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (i)
Access method: ao_row
Partitioned tables
In this section, we will apply the above to partitioned tables. Lets take a table sales that represents the last 8 months of sales in 2022.
CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (date) ( START (date '2022-01-01') INCLUSIVE END (date '2022-08-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
Now, we realize that data for the month of Mar is not going to be frequently updated. So, we can transform it into an AO table. Other sibling partitions will be unaffected.
ALTER TABLE sales_1_prt_3 SET ACCESS METHOD ao_row;
\d+ sales_1_prt_3
Table "public.sales_1_prt_3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Partition of: sales FOR VALUES FROM ('2022-03-01') TO ('2022-04-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-03-01'::date) AND (date < '2022-04-01'::date))
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row
Changing the AM and storage options at the same time
Now, we want to do the same for Jan but we want to go one better by compressing it more aggressively, as we predict that updates to it will be at an even lower frequency.
ALTER TABLE sales_1_prt_1 SET ACCESS METHOD ao_row
WITH (compresslevel=9, compresstype=zlib);
\d+ sales_1_prt_1
Table "public.sales_1_prt_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Partition of: sales FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-01-01'::date) AND (date < '2022-02-01'::date))
Compression Type: zlib
Compression Level: 9
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row
Options: compresslevel=9, compresstype=zlib
Changing the AM for the entire partition hierarchy
Once we are into 2023, we may feel that the sales table for 2022 is not going to see much action, in which case we might want to transform all of its partitions into AO. We can do so with just 1 command - by issuing the ALTER command from the root sales partition!
\d+
List of relations
Schema | Name | Type | Owner | Storage | Size | Description
--------+---------------+-------------------+---------+---------+---------+-------------
public | sales | partitioned table | pivotal | heap | 0 bytes |
public | sales_1_prt_1 | table | pivotal | heap | 96 kB |
public | sales_1_prt_2 | table | pivotal | heap | 96 kB |
public | sales_1_prt_3 | table | pivotal | heap | 96 kB |
public | sales_1_prt_4 | table | pivotal | heap | 96 kB |
public | sales_1_prt_5 | table | pivotal | heap | 96 kB |
public | sales_1_prt_6 | table | pivotal | heap | 96 kB |
public | sales_1_prt_7 | table | pivotal | heap | 96 kB |
(8 rows)
ALTER TABLE sales SET ACCESS METHOD ao_row;
\d+
List of relations
Schema | Name | Type | Owner | Storage | Size | Description
--------+---------------+-------------------+---------+-------------+---------+-------------
public | sales | partitioned table | pivotal | append only | 0 bytes |
public | sales_1_prt_1 | table | pivotal | append only | 225 kB |
public | sales_1_prt_2 | table | pivotal | append only | 225 kB |
public | sales_1_prt_3 | table | pivotal | append only | 225 kB |
public | sales_1_prt_4 | table | pivotal | append only | 225 kB |
public | sales_1_prt_5 | table | pivotal | append only | 225 kB |
public | sales_1_prt_6 | table | pivotal | append only | 225 kB |
public | sales_1_prt_7 | table | pivotal | append only | 225 kB |
(8 rows)
Also, we could conceive that new partitions representing later months will be added to sales, and they would see frequent updates. These partitions should be heap. But wait: we don’t want to touch the existing partitions - they should remain AO, as they won’t be updated. How can we achieve this? Enter the ONLY clause!
ALTER TABLE ONLY sales SET ACCESS METHOD heap;
Now, any new partition that will get attached will be a heap table! Also, existing partitions remain untouched!
ALTER TABLE sales ADD PARTITION aug
START (date '2022-08-01') INCLUSIVE
END (date '2022-09-01') EXCLUSIVE;
\d+
List of relations
Schema | Name | Type | Owner | Storage | Size | Description
--------+-----------------+-------------------+---------+-------------+---------+-------------
public | sales | partitioned table | pivotal | heap | 0 bytes |
public | sales_1_prt_1 | table | pivotal | append only | 225 kB |
public | sales_1_prt_2 | table | pivotal | append only | 225 kB |
public | sales_1_prt_3 | table | pivotal | append only | 225 kB |
public | sales_1_prt_4 | table | pivotal | append only | 225 kB |
public | sales_1_prt_5 | table | pivotal | append only | 225 kB |
public | sales_1_prt_6 | table | pivotal | append only | 225 kB |
public | sales_1_prt_7 | table | pivotal | append only | 225 kB |
public | sales_1_prt_aug | table | pivotal | heap | 0 bytes |
(9 rows)
Changing storage options for the entire partition hierarchy
If we feel like an entire partition hierarchy (or subpartition hierarchy) has gone cold, we can jack up the compression settings for the entire hierarchy! We can do this by using the same syntax as before, and directly apply it to the partition (or subpartition) root.
ALTER TABLE sales SET (compresslevel=9);
\d+ sales
Partitioned table "public.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Partition key: RANGE (date)
Partitions: sales_1_prt_1 FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'),
sales_1_prt_2 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
sales_1_prt_3 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
sales_1_prt_4 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'),
sales_1_prt_5 FOR VALUES FROM ('2022-05-01') TO ('2022-06-01'),
sales_1_prt_6 FOR VALUES FROM ('2022-06-01') TO ('2022-07-01'),
sales_1_prt_7 FOR VALUES FROM ('2022-07-01') TO ('2022-08-01')
Distributed by: (id)
Access method: ao_row
Options: compresslevel=9
\d+ sales_1_prt_1
Table "public.sales_1_prt_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Partition of: sales FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-01-01'::date) AND (date < '2022-02-01'::date))
Compression Type: zlib
Compression Level: 9
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row
Options: compresslevel=9
...
We also support the ONLY clause, which means if we wanted to apply our storage parameters to future partitions only (without affecting existing partitions) we could:
ALTER TABLE ONLY sales SET (compresslevel=3);
-- root has been changed
\d+ sales
Partitioned table "public.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Partition key: RANGE (date)
Partitions: sales_1_prt_1 FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'),
sales_1_prt_2 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
sales_1_prt_3 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
sales_1_prt_4 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'),
sales_1_prt_5 FOR VALUES FROM ('2022-05-01') TO ('2022-06-01'),
sales_1_prt_6 FOR VALUES FROM ('2022-06-01') TO ('2022-07-01'),
sales_1_prt_7 FOR VALUES FROM ('2022-07-01') TO ('2022-08-01'),
sales_1_prt_aug FOR VALUES FROM ('2022-08-01') TO ('2022-09-01')
Distributed by: (id)
Access method: ao_row
Options: compresslevel=3
-- example of existing partition remaining unchanged
\d+ sales_1_prt_1
Table "public.sales_1_prt_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Partition of: sales FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-01-01'::date) AND (date < '2022-02-01'::date))
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row
-- new partition will inherit the setting specified with ONLY
ALTER TABLE sales ADD PARTITION aug
START (date '2022-08-01') INCLUSIVE
END (date '2022-09-01') EXCLUSIVE;
\d+ sales_1_prt_aug
Table "public.sales_1_prt_aug"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
date | date | | | | plain | |
amt | numeric(10,2) | | | | main | |
Partition of: sales FOR VALUES FROM ('2022-08-01') TO ('2022-09-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-08-01'::date) AND (date < '2022-09-01'::date))
Compression Type: zlib
Compression Level: 3
Block Size: 32768
Checksum: t
Distributed by: (id)
Access method: ao_row
Options: compresslevel=3
Column level storage options for AOCO tables can be changed similarly throughout the partition hierarchy.
ALTER TABLE sales ALTER COLUMN id SET ENCODING (compresslevel=7, compresstype=zstd);
\d+ sales
Partitioned table "public.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
id | integer | | | | plain | | zstd | 7 | 32768 |
date | date | | | | plain | | none | 0 | 32768 |
amt | numeric(10,2) | | | | main | | none | 0 | 32768 |
Partition key: RANGE (date)
Partitions: sales_1_prt_1 FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'),
sales_1_prt_2 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
sales_1_prt_3 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
sales_1_prt_4 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'),
sales_1_prt_5 FOR VALUES FROM ('2022-05-01') TO ('2022-06-01'),
sales_1_prt_6 FOR VALUES FROM ('2022-06-01') TO ('2022-07-01'),
sales_1_prt_7 FOR VALUES FROM ('2022-07-01') TO ('2022-08-01')
Distributed by: (id)
Access method: ao_column
\d+ sales_1_prt_1
Table "public.sales_1_prt_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
id | integer | | | | plain | | zstd | 7 | 32768 |
date | date | | | | plain | | none | 0 | 32768 |
amt | numeric(10,2) | | | | main | | none | 0 | 32768 |
Partition of: sales FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-01-01'::date) AND (date < '2022-02-01'::date))
Checksum: t
Distributed by: (id)
Access method: ao_column
We also support the ONLY clause, which behaves in the same manner as table-level options.
ALTER TABLE ONLY sales ALTER COLUMN id SET ENCODING (compresslevel=7, compresstype=zstd);
-- root has been changed
\d+ sales
Partitioned table "public.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
id | integer | | | | plain | | zstd | 7 | 32768 |
date | date | | | | plain | | none | 0 | 32768 |
amt | numeric(10,2) | | | | main | | none | 0 | 32768 |
Partition key: RANGE (date)
Partitions: sales_1_prt_1 FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'),
sales_1_prt_2 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
sales_1_prt_3 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
sales_1_prt_4 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'),
sales_1_prt_5 FOR VALUES FROM ('2022-05-01') TO ('2022-06-01'),
sales_1_prt_6 FOR VALUES FROM ('2022-06-01') TO ('2022-07-01'),
sales_1_prt_7 FOR VALUES FROM ('2022-07-01') TO ('2022-08-01')
Distributed by: (id)
Access method: ao_column
-- example of existing partition remaining unchanged
\d+ sales_1_prt_1
Table "public.sales_1_prt_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
id | integer | | | | plain | | none | 0 | 32768 |
date | date | | | | plain | | none | 0 | 32768 |
amt | numeric(10,2) | | | | main | | none | 0 | 32768 |
Partition of: sales FOR VALUES FROM ('2022-01-01') TO ('2022-02-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-01-01'::date) AND (date < '2022-02-01'::date))
Checksum: t
Distributed by: (id)
Access method: ao_column
-- new partition will inherit the setting specified with ONLY
ALTER TABLE sales ADD PARTITION aug
START (date '2022-08-01') INCLUSIVE
END (date '2022-09-01') EXCLUSIVE;
\d+ sales_1_prt_aug
Table "public.sales_1_prt_aug"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------------+-----------+----------+---------+---------+--------------+------------------+-------------------+------------+-------------
id | integer | | | | plain | | zstd | 7 | 32768 |
date | date | | | | plain | | none | 0 | 32768 |
amt | numeric(10,2) | | | | main | | none | 0 | 32768 |
Partition of: sales FOR VALUES FROM ('2022-08-01') TO ('2022-09-01')
Partition constraint: ((date IS NOT NULL) AND (date >= '2022-08-01'::date) AND (date < '2022-09-01'::date))
Checksum: t
Distributed by: (id)
Access method: ao_column
Powerful combinations
Wait, there’s more! What if you wanted to change the access method or storage options and add/drop a column at the same time? All this at the cost of 1 rewrite?! Since all of our new subcommands belong to the ALTER family, we can combine commands like:
ALTER TABLE foo SET ACCESS METHOD ao_row, ADD column j int;
\d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
j | integer | | | | plain | |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (i)
Access method: ao_row
ALTER TABLE foo SET (compresslevel=7), ADD COLUMN k int;
\d+ foo
Table "public.foo"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
i | integer | | | | plain | |
j | integer | | | | plain | |
k | integer | | | | plain | |
Compression Type: zlib
Compression Level: 7
Block Size: 32768
Checksum: t
Distributed by: (i)
Access method: ao_row
Options: compresslevel=7