Best practice for really long migrations?

Phoenix Forum Questions / Help
, , ,
1

Hi there,

I need to add an index to a database table with already millions of entries in it. That might take a few minutes.

Any advice on how to approach this in production?
Do I need to increase any timeout limits?
Does creating the index lock the table or is there a way to allow selects/inserts while the index is building?

Thanks!

1 Like
2

Do it when overnight (or any time frame that usually is not that busy), perhaps schedule and announce a maintenance downtime.

At least PostgreSQL knows an option to not lock the table but advises to not use it ;):

CONCURRENTLY

When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it’s done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently.

I do not know how other DBMS handle creating an index, but I’d assume that a write-lock applies, at least.

1 Like
3

I wouldn’t say postgres advises against using concurrently; I’d say they advise to be aware of how it works.

1 Like
4

You could turn the site off via a .htaccess rule that redirects all requests to a page that says “Sorry we are currently updating the site and will be back up shortly” and then turn it back on once you have carried out the maintenance :slight_smile:

1 Like
5

Thanks for your advice everyone.

It turned out my main issue was the Kubernetes setup and running the migrations in the Dockerfile – which meant that migrations were run on ever node on startup, causing all kinds of trouble. I only migrate in one node now and all is good :slight_smile:

After this was fixed I simply ran it during a not-so-busy time.

3 Likes