Option 2: Create a new table like old table schema
Overview of the process
Create a new table with the same schema butid with type bigint and use INSERT INTO.
Risks:
- Bloating the disk usage on the database server.
- Bloating the CPU and memory usage on the database server during the copy data
Steps:
- Create a table with the same schema as old_table except for id and allocation_log_id and drop all Foreign Key constraints that reference id in the new table.
- Create a trigger, when the data of the source table is modified, it is used to synchronize the records of the source table to the new table in real time
- Copy data from the source data table to the new table.
- Once the background migration completed, we can start maintenance mode
- Drop trigger
- Rename the source data table to the old table, rename the new table to the source table name.
- Add back Foreign Key constraints to reference id.
- Add back the sequence to id.
- Drop function
- Drop old table
Details of the process
Environment
| Operating System | Disk type | Amount of data | Background time | Maintenance time |
|---|---|---|---|---|
| MAC Big Sur | SSD | 0.5 billion | 4h | 10m |
- Create a table with the same schema as old_table except for id and allocation_log_id and drop all Foreign Key constraints that reference id in the new table.
-- 1s
drop table if exists trade_logs_new;
-- 1s
create table trade_logs_new
(
id bigint not null
constraint trade_logs_new_pkey
primary key,
trade_id integer
constraint fk_rails_7a0ba7830b
references trades,
account_id integer
constraint fk_rails_f946cfeeba
references accounts,
amount bigint,
is_credit boolean,
tax_rate varchar,
description varchar(1000),
created_at timestamp,
updated_at timestamp,
type varchar not null,
allocation_log_id bigint
)
with (autovacuum_analyze_scale_factor = 0.0, autovacuum_analyze_threshold = 2000000);
create index index_trade_logs_new_on_account_id
on trade_logs_new (account_id);
create index index_trade_logs_new_on_account_id_amount
on trade_logs_new (account_id, amount);
create index index_trade_logs_new_on_account_id_trade_id
on trade_logs_new (account_id, trade_id);
create index index_trade_logs_new_on_allocation_log_id
on trade_logs_new (allocation_log_id);
create index index_trade_logs_new_on_trade_id
on trade_logs_new (trade_id);
- Create a trigger, when the data of the source table is modified, it is used to synchronize the records of the source table to the new table in real time
-- 1s
DROP FUNCTION if exists trade_logs_trigger_function();
-- 1s
create or replace FUNCTION trade_logs_trigger_function() RETURNS TRIGGER as
$$
DECLARE
begin
begin
case TG_OP
when 'INSERT'
then insert into trade_logs_new(id, trade_id, account_id, amount, is_credit, tax_rate,
description, created_at, updated_at, type, allocation_log_id)
values (new.id, new.trade_id, new.account_id, new.amount, new.is_credit, new.tax_rate,
new.description, new.created_at, new.updated_at, new.type, new.allocation_log_id);
when 'UPDATE' then update trade_logs_new
set id=NEW.id,
trade_id=new.trade_id,
account_id=new.account_id,
amount=new.amount,
is_credit=new.is_credit,
tax_rate=new.tax_rate,
description=new.description,
created_at=new.created_at,
updated_at=new.updated_at,
type=new.type,
allocation_log_id=new.allocation_log_id
where id = old.id;
when 'DELETE' then delete from trade_logs_new where id = OLD.id;
else return NULL;
end case;
return NULL;
end;
END
$$
language plpgsql;
-- 1s
drop trigger if exists trade_logs_trigger on trade_logs;
-- 1s
CREATE TRIGGER trade_logs_trigger
AFTER INSERT OR DELETE OR UPDATE
ON trade_logs
FOR EACH ROW
EXECUTE PROCEDURE trade_logs_trigger_function();
- Copy data from the source data table to the new table.
-- 1s
DROP PROCEDURE if exists syn_table_data();
-- 1s
create or replace PROCEDURE syn_table_data()
as
$$
DECLARE
counter int8;
step int8:=999;
min_id int8;
max_id int8;
begin
select min(id) into min_id from trade_logs;
select max(id) into max_id from trade_logs;
RAISE NOTICE 'min id value is: %',min_id;
RAISE NOTICE 'max id value is: %',max_id;
counter := min_id;
while counter <= max_id
loop
begin
if counter + step > max_id then
insert into trade_logs_new(id, trade_id, account_id, amount, is_credit, tax_rate,
description, created_at, updated_at, type, allocation_log_id)
select *
from trade_logs
where id >= counter
and id <= max_id;
else
insert into trade_logs_new(id, trade_id, account_id, amount, is_credit, tax_rate,
description, created_at, updated_at, type, allocation_log_id)
select *
from trade_logs
where id >= counter
and id < counter+step;
end if;
end;
commit;
RAISE NOTICE 'counter: %',counter;
counter := counter + step;
end loop;
END
$$
language plpgsql;
-- 4 h 10 m 0 s 714 ms
call syn_table_data();
-- verify synchronized result
-- select max(id) from trade_logs;
-- select max(id) from trade_logs_new;
-- select count(1) from trade_logs;
-- select count(1) from trade_logs_new;
Once the background migration completed, we can start maintenance mode.
- Drop trigger
-- 1s
drop trigger if exists trade_logs_trigger on trade_logs;
- Rename the source data table to the old table, rename the new table to the source table name.
-- 1s
alter table trade_logs rename to trade_logs_old;
-- 1s
alter table trade_logs_new rename to trade_logs;
- Add back Foreign Key constraints to reference id.
-- 10 m 18 s 793 ms
ALTER TABLE trade_logs ADD CONSTRAINT fk_rails_bca3a2c7d2 FOREIGN KEY (allocation_log_id) REFERENCES trade_logs (id);
- Add back the sequence to id.
-- 1s
ALTER TABLE trade_logs_old ALTER COLUMN id drop default;
-- 1s
ALTER TABLE trade_logs ALTER COLUMN id SET DEFAULT NEXTVAL('trade_logs_id_seq'::regclass);
-- 1s
alter sequence trade_logs_id_seq owned by trade_logs.id;
- Drop function
-- 1s
DROP PROCEDURE if exists syn_table_data();
-- 1s
DROP FUNCTION if exists trade_logs_trigger_function();
- Drop old table
-- 1s
drop table trade_logs_old;
