polar_ddl_manager

Prerequisites

select version();

Background information

In PostgreSQL, when you increase the length of a varchar column in a partitioned table, the indexes on the modified column are automatically rebuilt. This makes the ALTER TABLE statement inefficient. In addition, the ALTER TABLE statement requires an ACCESS EXCLUSIVE lock, which blocks other connections from accessing the table for a long time.

PolarDB for PostgreSQL and support the polar_ddl_manager extension. This extension provides functions to improve the performance of increasing the length of a varchar column in a partitioned table.

Usage

Install the extension

CREATE EXTENSION polar_ddl_manager;

Related functions

• polar_ddl_manager.alter_varchar_column_length_cmd

  This function generates a list of ALTER TABLE commands to increase the length of a varchar column. Use this function to check if the commands are correct before you execute the Data Definition Language (DDL) commands. The following table describes the parameters.

  Parameter	Description
  parent_table_name	The name of the partitioned table.
  parent_table_schema	The schema where the partitioned table resides.
  column_name	The name of the column whose length you want to increase.
  target_len	The target length after the increase.
  novalidate	Specifies whether to skip the partition constraint check. Valid values: true (default): Skips the partition constraint check. false: Does not skip the partition constraint check.

• polar_ddl_manager.alter_varchar_column_length

  This function retrieves the DDL commands from the polar_ddl_manager.alter_varchar_column_length_cmd function and automatically executes them to increase the length of the varchar column in the partitioned table. The process is as follows:

  1. Locks the partitioned table and its child tables, and performs a validity check.

  2. Detaches all subpartitions.

  3. Modifies the varchar column length of the partitioned table and its child tables.

  4. Attaches all subpartitions.

Usage example

CREATE TABLE t(a int, b varchar(10), c char(128)) PARTITION BY HASH (a);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES WITH (MODULUS 2, REMAINDER 1);
CREATE index ON t(b);
INSERT INTO t SELECT i, i FROM generate_series(1, 1000000) i;
-- Increase the length of column b without skipping the partition constraint check.
SELECT polar_ddl_manager.alter_varchar_column_length('t', 'public', 'b', 15, false);
-- Increase the length of column b and skip the partition constraint check.
SELECT polar_ddl_manager.alter_varchar_column_length('t', 'public', 'b', 20);

Uninstall the extension

DROP EXTENSION polar_ddl_manager;

ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT } [NOCHECK_CONSTRAINT]

Performance comparison

The table schema used for the test is as follows:

CREATE TABLE t(a int, b varchar(10), c char(128)) PARTITION BY HASH (a);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES WITH (MODULUS 2, REMAINDER 1);

The following table shows the running time in milliseconds (ms) for different data volumes in scenarios with and without indexes.