Option 4: (Add a new column of type bigint for trade_logs with zero downtime)

Overview of the process

Add a new column to the table with type bigint. Copy over id values to id_new and then rename the new column to id (by renaming the existing id to id_old).

Steps:

• Add new column
• Copy data
• Update constraint
• Rename column
• Drop old data

Details of the process

Environment	Operating System	Disk type	Amount of data	Migration background time	Scheduled maintenance time
local	MAC	SSD	0.2 billion	2.5h	zero
fitzroy	RDS	SSD	3.5 billion	42h	zero

Note:

• To be safe, all steps should be performed when the database traffic is slow.
• Back up the schema for the table before performing the changes.

Prerequisites

Perform the following script in jump box server, then will create a run.sh.

vim run.sh

Copy the following script into run.sh and save it, then run all the sql commands that take a long time to execute with shell, for the purpose of recording the execution time and logging execution output.

#!/bin/bash

password=$1;
user_name='dbuser'
pid=$$
log_name=${2}_${pid}_`date "+%Y-%m-%d_%H_%M_%S"`
target_command=$3;

echo "log name is: ${log_name}"
echo "target command is: ${target_command}"
echo "pid is: ${pid}"
export PGPASSWORD=$password;
echo "begin time is: " `date "+%Y-%m-%d %H:%M:%S"` > ${log_name}.log;
if [ -n "${target_command}" ]; then
    echo "${target_command}" > temp.sql;
fi
nohup psql -a -d trade_db -h remote.server -U ${user_name} -f temp.sql >> ${log_name}.log 2>&1 &
wait $! && echo "successfully: end time is: " `date "+%Y-%m-%d %H:%M:%S"` >> ${log_name}.log || echo "fail: end time is: " `date "+%Y-%m-%d %H:%M:%S"` >> ${log_name}.log;

Add new column

• Add new nullable bigint column without a default specified for id column and allocation_log_id column. Add a trigger to update id to new_id. These two steps must be performed in a single transaction to avoid not assigning values to id_new when some insert requests hit the database before the trigger is added

begin work;
LOCK TABLE trade_logs IN EXCLUSIVE MODE;
-- 1s
ALTER TABLE trade_logs ADD COLUMN id_new BIGINT NULL;
-- 1s
ALTER TABLE trade_logs ADD COLUMN allocation_log_id_new BIGINT NULL;
-- 1s
DROP FUNCTION if exists trade_logs_insert_trigger_function();
-- 1s
create or replace FUNCTION trade_logs_insert_trigger_function() RETURNS TRIGGER as
$$
begin
    NEW.id_new = NEW.id;
    NEW.allocation_log_id_new = NEW.allocation_log_id;
    RETURN NEW;
END
$$
    language plpgsql; --1s

drop trigger if exists trade_logs_insert_trigger on trade_logs;

CREATE TRIGGER trade_logs_insert_trigger
    BEFORE insert or update
    ON trade_logs
    FOR EACH ROW WHEN ( NEW.id_new IS NULL )

EXECUTE PROCEDURE trade_logs_insert_trigger_function();
-- Commit trasaction work
commit work;

• After adding new columns, restart app to refresh rails's cache that can solve 'ActiveRecord::PreparedStatementCacheExpired' error.
• Build index on id_new concurrently. Supports building indexes without locking out writes. This method is invoked by specifying the option of . When this option is used, must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete. However, since it allows normal operations to continue while the index is built, this method is useful for adding new indexes in a production environment. Of course, the extra CPU and I/O load imposed by the index creation might slow other operations.

-- run with shell 1.5h
sh run.sh $password create_index_on_id_new "drop index if exists index_trade_logs_on_id_new;commit;CREATE UNIQUE INDEX CONCURRENTLY index_trade_logs_on_id_new ON trade_logs(id_new);select CURRENT_TIMESTAMP;" &

• Build index on allocation_log_id_new concurrently.

-- run with shell 1.5h
sh run.sh $password create_index_on_allocation_log_id_new "drop index if exists index_trade_logs_on_allocation_log_id_new;commit;CREATE INDEX CONCURRENTLY index_trade_logs_on_allocation_log_id_new ON trade_logs(allocation_log_id_new);select CURRENT_TIMESTAMP;" &

Copy data

• Perform VACUUM command to reclaim dead tuples before coping data from id to id_new, it will acquire a SHARE UPDATE EXCLUSIVE lock on trade_logs table.

-- Query dead tuples.
 SELECT relname, n_dead_tup FROM pg_stat_user_tables where relname='trade_logs';
-- Reclaim dead tuples, after reclaiming, perform the above command to check if dead tuples are empty.
-- run with shell 4h
sh run.sh $password vacuum_before_copying "VACUUM (VERBOSE, ANALYZE) trade_logs;select CURRENT_TIMESTAMP;" &

• Perform ANALYZE command in order to collect statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

-- run with shell 1m
sh run.sh $password analyze_before_copying "ANALYZE trade_logs;select CURRENT_TIMESTAMP;" &

• Record min id value and max id value. Example: latest negative max id value is -12340

select max(id) as latest_negative_max_id 
from trade_logs
where id < 0; --latest negative max id value

• Copy data from id to id_new in background mode. The reason we choose increments of 100,000 is mainly because it performed better in terms of incremental speed, disk utilization and cleanup between transactions(Need to be verified in AWS env again).

-- 1s
DROP PROCEDURE if exists syn_data(int8);
-- 1s
create or replace PROCEDURE syn_data(in_latest_negative_max_id int8)
as
$$
DECLARE
    counter int8;
    min_id int8;
    max_id int8;
    step int8 := 100000;
begin
    select min(id) into min_id from trade_logs;
    select max(id) into max_id from trade_logs;
    RAISE  NOTICE 'begin time is: %',CURRENT_TIMESTAMP;
    RAISE NOTICE 'latest negative max id value is: %',in_latest_negative_max_id;
    RAISE NOTICE 'min id value is: %',min_id;
    RAISE NOTICE 'max id value is: %',max_id;

    counter:=min_id;
    while counter <= in_latest_negative_max_id
        loop
            begin
                update trade_logs
                set id_new=id,
                    allocation_log_id_new=allocation_log_id
                where id < 0
                  and id >= counter
                  and id < counter + step;
                commit;
            end;
            counter := counter + step;
            RAISE NOTICE 'current datetime: % counter: %',CURRENT_TIMESTAMP,counter;
        end loop;

    counter:=0;
    while counter <= max_id
        loop
            begin
                update trade_logs
                set id_new=id,
                    allocation_log_id_new=allocation_log_id
                where id >= 0
                  and id >= counter
                  and id < counter + step;
                commit;
            end;
            counter := counter + step;
            RAISE NOTICE 'current datetime: % counter: %',CURRENT_TIMESTAMP,counter;
        end loop;
     RAISE NOTICE 'end time is: %',CURRENT_TIMESTAMP;
END
$$
    language plpgsql;
    
-- run with shell 31h
sh run.sh $password syn_data "call syn_data(-12345);select CURRENT_TIMESTAMP;" &

• After finishing coping the data, then checking for not null values on the column id_new before dropping primary key constraints on id, in order to avoid some data may not be copied successfully

-- 1s
DROP PROCEDURE if exists check_null_and_fixed();
create or replace PROCEDURE check_null_and_fixed()
as
$$
DECLARE
    i RECORD;
begin
    FOR i IN select * from trade_logs tl where tl.id_new is null
        loop
            begin
                RAISE NOTICE '% id is for null id_new', i.id;
                update trade_logs
                set id_new=i.id,
                    allocation_log_id_new=i.allocation_log_id
                where id = i.id;
                commit;
            end;
        end loop;
END
$$
    language plpgsql;

-- run with shell 1.5h
sh run.sh $password check_null_and_fixed "call check_null_and_fixed();select CURRENT_TIMESTAMP;" &

Update constraint

• Once the backfill was done, it was time to change the primary key. By itself this is a relatively instantaneous operation, but it requires that the new column have a NOT NULL constraint. Marking a column as NOT NULL used to require an exclusive lock and table scan, but since PostgreSQL 12.0 the constraint can be added without a scan if the database can prove the column cannot contain NULLs. The best way to do this is with a CHECK constraint, and these can be created and then validated concurrently.

-- 1s
ALTER TABLE trade_logs ADD CONSTRAINT id_new_not_null CHECK (id_new IS NOT NULL) NOT VALID;
-- run with shell 1h
sh run.sh $password validate_not_null_constraint "ALTER TABLE trade_logs VALIDATE CONSTRAINT id_new_not_null;select CURRENT_TIMESTAMP;" &
-- 1s
ALTER TABLE trade_logs ALTER COLUMN id_new SET NOT NULL;
-- 1s
ALTER TABLE trade_logs DROP CONSTRAINT id_new_not_null;

• Add Foreign Key constraints to allocation_log_id . it acquires the ACCESS SHARE and SHARE ROW EXCLUSIVE locks. With NOT VALID, the ADD CONSTRAINT command does not scan the table and can be committed immediately.

-- 1s
ALTER TABLE trade_logs ADD CONSTRAINT trade_logs_allocation_log_id_fk_new FOREIGN KEY (allocation_log_id_new) REFERENCES trade_logs (id_new) not valid;

• Validate the table by using the VALIDATE CONSTRAINT option, it acquires the ACCESS SHARE and SHARE UPDATE EXCLUSIVE locks, it will verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates.

-- run with shell 3h
sh run.sh $password validate_foreign_key_constraint "ALTER TABLE trade_logs VALIDATE CONSTRAINT trade_logs_allocation_log_id_fk_new;select CURRENT_TIMESTAMP;" &

Rename column

• Perform the rename step in a single transaction and lock the TALBE in EXCLUSIVE mode to avoid id_new being set to null when the rename step is performed.

-- Stop writing to the new column, takes an exclusive table lock
begin work;
LOCK TABLE trade_logs IN  EXCLUSIVE MODE;


-- Remove the trigger `trade_logs_insert_trigger`, before removing the trigger make sure  that no queries access the`trade_logs` table, untill the following steps are completed.
drop trigger if exists trade_logs_insert_trigger on trade_logs;

-- Rename column `id` to `id_old`.
ALTER TABLE trade_logs RENAME COLUMN id TO id_old;

-- Rename column `id_new` to `id`.
ALTER TABLE trade_logs RENAME COLUMN id_new TO id;

-- Rename column `allocation_log_id` to `allocation_log_id_old`.
ALTER TABLE trade_logs RENAME COLUMN allocation_log_id TO allocation_log_id_old;

-- Rename column `allocation_log_id_new` to `allocation_log_id`.
ALTER TABLE trade_logs RENAME COLUMN allocation_log_id_new TO allocation_log_id;

--Remove Foreign Keys from the `allocation_log_id` column, make sure the name of the foreign key to be deleted is correct

-- alter table trade_logs drop constraint fk_rails_bca3a2c7d2;
alter table trade_logs drop constraint trade_logs_allocation_log_id_fk;

-- Remove the Primary Key constraint on `id`.
ALTER TABLE trade_logs DROP CONSTRAINT trade_logs_pkey;

-- Rename the foreign key `trade_logs_allocation_log_id_fk_new` to `trade_logs_allocation_log_id_fk`
alter table trade_logs rename constraint trade_logs_allocation_log_id_fk_new to trade_logs_allocation_log_id_fk;

-- Rename the 'index_trade_logs_on_allocation_log_id' to 'index_trade_logs_on_allocation_log_id_old'
alter index index_trade_logs_on_allocation_log_id rename to index_trade_logs_on_allocation_log_id_old;

-- Rename the 'index_trade_logs_on_allocation_log_id_new' to 'index_trade_logs_on_allocation_log_id'
alter index index_trade_logs_on_allocation_log_id_new rename to index_trade_logs_on_allocation_log_id;

-- Add primary key constraint with using the unique index built concurrently.
ALTER INDEX index_trade_logs_on_id_new RENAME TO trade_logs_pkey;
ALTER TABLE trade_logs ADD CONSTRAINT trade_logs_pkey PRIMARY KEY USING INDEX trade_logs_pkey;

-- Add back the sequence to `id`.
ALTER TABLE trade_logs ALTER COLUMN id_old drop default;
ALTER TABLE trade_logs ALTER COLUMN id SET DEFAULT NEXTVAL('trade_logs_id_seq'::regclass);
alter sequence trade_logs_id_seq owned by trade_logs.id;

-- Drop not null constraint on id_old
ALTER TABLE trade_logs ALTER COLUMN id_old DROP NOT NULL;

-- Commit trasaction work
commit work;

Reset start value of trade_logs_id_seq sequece.

select max(id) from trade_logs;
ALTER SEQUENCE trade_logs_id_seq RESTART WITH <MAX_ID+1>;

Drop old data

• Drop column id_old

-- 1m
ALTER TABLE trade_logs DROP COLUMN id_old;

• Drop column allocation_log_id_old

-- 1m
ALTER TABLE trade_logs  DROP COLUMN allocation_log_id_old;

• Drop procedures no longer used

-- 1s
DROP PROCEDURE if exists syn_data(int8, int8);
-- 1s
DROP procedure if exists check_null_and_fixed();
-- 1s
DROP FUNCTION if exists trade_logs_insert_trigger_function();

• Restart app to refresh rails cache that could solve 'columns not exists' errors.

Reclaim dead tuples

• Vacuum table again to reclaim dead tuples that was created by performing coping data.

-- Query dead tuples.
 SELECT relname, n_dead_tup FROM pg_stat_user_tables where relname='trade_logs';
-- Reclaim dead tuples, after relaiming, perform the above command to check if dead tuples are empty.
sh run.sh $password vacuum_after_finishing "VACUUM (VERBOSE, ANALYZE) trade_logs;" &