ApsaraDB RDS:Perform DDL operations to add fields to an ApsaraDB RDS for PostgreSQL instance

Background information

In most cases, you can add fields that do not have default values to your RDS instance within seconds. If your RDS instance runs PostgreSQL 11 or later, you can also add fields that have non-calculated default values within seconds. For more information, see PostgreSQL documentation.

You can execute the DDL statements that are used to add fields within seconds. However, blocks caused by locks may occur during the execution of the DDL statements. This issue occurs because of table lock contention caused by autovacuum or long-running transactions. In ApsaraDB RDS for PostgreSQL, the table lock required for the execution of the DDL statements that are used to add fields is an exclusive lock. The table lock suspends other read and write requests and the requests enter the waiting queue for the table lock until the DDL statements are successfully executed.

Solutions

Solution 1

Configure a transaction-level lock wait timeout period. After the lock wait times out, the DDL operation is automatically canceled. Examples:

BEGIN;

SET LOCAL lock_timeout = 500;  -- Set the lock wait timeout period to 500 milliseconds.
ALTER TABLE <Table name> ADD COLUMN <Column name> VARCHAR;  -- Add a field.

COMMIT; -- Commit the transaction.

Solution 2

Configure a transaction-level lock wait timeout period and allow the system to automatically and periodically perform a DDL operation until the operation is successful. Examples:

DO $$
DECLARE
    msg text;
BEGIN
    LOOP
        BEGIN
            PERFORM pg_sleep(1);
            SET LOCAL lock_timeout = 500;
            ALTER TABLE <Table name> ADD COLUMN <Column name> VARCHAR;  -- Add a field.
            EXIT;
        EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS msg = MESSAGE_TEXT;
            RAISE NOTICE 'failed, error: %s', msg;
        END;
    END LOOP;   
    RAISE NOTICE 'success!';
END;
$$;

(Optional) Terminate the connection that blocks a DDL operation

1. Query the process ID (PID) of the current connection.

   SELECT pg_backend_pid();

2. Perform a DDL operation to add a field to a table.

   ALTER TABLE <Table name> ADD COLUMN <Field name> VARCHAR; 

3. Query the process that blocks the DDL operation.

   SELECT pg_blocking_pids(<The PID obtained in step 1>);

4. Manually terminate the process that blocks the DDL operation.

   • Cancel the running process.

     SELECT pg_cancel_backend(<The PID obtained in step 3>);

   • Forcibly terminate the process.

     SELECT pg_terminate_backend(<The PID obtained in step 3>);

Stress testing results

sysbench is used to perform read-only stress testing (oltp_read_only) on the test table. The test duration is 300 seconds. Long-running transactions are executed on the test table. When you execute the DDL statements that are used to add fields during the test, the test results vary based on whether a lock wait timeout period is configured.

• If you do not specify lock_timeout and blocks are caused by locks when you execute the DDL statements to add fields, the read/write TPS of the RDS instance is 0.

• If you specify lock_timeout and blocks are caused by locks when you execute the DDL statements to add fields, the read/write TPS of the RDS instance is slightly affected. If you set lock_timeout to a large value, the read/write TPS of the RDS instance is significantly affected.

The following figure shows the TPS trend chart and the operations at each stage during the test.

References

• For more information about how to use sysbench to perform a performance test on your RDS instance, see ApsaraDB RDS for PostgreSQL.

• For more information about how to view the trends of the performance metrics of your RDS instance, see View enhanced monitoring.