Database migration without downtime

user_13774853 0
Viewed 315

In our organization we require to run a database migration on live site data. We want to add a column with default value in a table with around 1000 rows. Can you suggest any method so that we get zero or minimum downtime . We are using postgresql database and elixir phoenix app . Thanks.

PS : we want minimum down time not exact zero . Also we want to run migration using Ecto in elixir and not through script.

Also if you can tell expected time taken to run migration when we have default constraint set.

elixir database-migration alter-table postgresql database
1 Answers

In general ALTER TABLE requires exclusive lock on table but adding a column with default values can be very fast because only system catalog should be updated (and this action does not depend on the table size):

For example with PostgreSQL 12, I get:

# select count(*) from t;
  count  
---------
 1000000
(1 row)

Time: 60.003 ms
# begin;
BEGIN
Time: 0.096 ms
# alter table t add newcol int default 19;
ALTER TABLE
Time: 0.457 ms
# commit;
COMMIT
Time: 9.211 ms

You should be able to get very small downtime with PostgreSQL 11 or 12. With a lower version PG rewrites the table: but even in this case 1000 rows is very very small and should be also very fast.

user_107268500
Related