Option 1: Change the datatype of column directly

Overview of the process

Change the datatype by using ALTER COLUMN.

Risks:

• Acquires an access exclusive lock which can be problematic for big tables.
• Causes table rewrite where Postgres rewrites every single row of the table, which is a very expensive operation (both in terms of disk I/O and wall clock time).

Steps:

• Run alter table to change datatype from int to bigint

Details of the process

Environment

Operating System	Disk type	Amount of data	Maintenance time
MAC Big Sur	SSD	0.5 billion	1h3m

• Run alter table to change datatype from int to bigint

-- 1 h 3 m 56 s 372 ms alter table trade_logs alter column id type bigint using id::bigint;