[aeyes]

The migration has to rewrite the whole table, bigint needs 8 bytes so you have to make room for that.

I have done several such primary key migrations on tables with 500M+ records, they took anywhere from 30 to 120 minutes depending on the amount of columns and indexes. If you have foreign keys it can be even longer.

Edit: But there is another option which is logical replication. Change the type on your logical replica, then switch over. This way the downtime can be reduced to minutes.

[semiquaver]

In practice the only option that I’ve seen work for very large teams and very large relational databases is online schema change tools like https://github.com/shayonj/pg-osc and https://github.com/github/gh-ost (the latter developed for GitHub’s monolith). It’s just too difficult to model what migrations will cause problems under load. Using a binlog/shadowtable approach for all migrations mostly obviates the problem.

[scient]

Rails migrations really fail to be viable at scale. And its not really because of the migrations in Rails, its because changes in PostgreSQL get very very expensive. Things have gotten better in PG 15, but its still not quite there yet.

I've ran into an integer PK being almost exhausted in values twice now. In one of these cases I used pg-osc to rewrite a 8TB table on disk without downtime over a period of a week to fix the issue. In the other case it was about 6TB within 2 days. Its doable, but the amount of planning and time it takes is non-trivial.

I so far have no found any other viable solutions either. I keep hearing about logical replication but I haven't seen a great real-world solution at scale so far.

[semiquaver]

What has worked in my experience:

- decouple migration execution from code deployment. Require that all migrations are in totally separate PRs can be run well-in-advance of the code that depends on the db change being run

- maintain a stateful migration execution system using an online schema change system supervised by DBAs

- PRs adding migrations still need to be merged and deployed, but can only do so after the actual migration has run.

[tianzhou]

+1. We have been working on a schema migration tool called Bytebase for 3+ years. And we follow the similar principles.

[CubsFan1060]

You can also migrate it using logical replication.

[winrid]

This largely depends on the disk. I wouldn't expect that to take 30mins on a modern NVME drive, but of course it depends on table size.

[scient]

Large tables take hours, if not days. I attempted a test case on AWS using souped up io2 disks (the fastest most expensive disks they have) and a beast of a DB server (r5.12xl I think) and it became abundantly clear that at certain scale you won't be doing any kind of in-place table updates like that on the system. Especially if your allowed downtime is one hour maintenance window per week...

[aeyes]

I did it on a r6.24xlarge RDS instance and the CPU wasn't doing much during the operation. IO peaked at 40k IOPS on EBS with provisioned IOPS, I'm not sure if a local disk would be any faster but I already know that rewriting the table and creating the indexes are all single threaded so there isn't much you could gain.

Once I got the logical replication setup to work I changed 20 tables on the replica and made the switch with 15 minutes of downtime. That saved me a lot of long nights.

You can get an idea of how long this could take by running pg_repack, it's basically doing the same thing: Copying the data and recreating all the indexes.

[sgarland]

> not sure if a local disk would be any faster

Uh, yes. NVMe drives can hit 1,000,000 IOPS, plus the latency is orders of magnitude lower than EBS.

[aeyes]

Disk wasn't the limit in my case, index creation is single threaded.

[winrid]

Which is still very IO bound... Wonder what kinda IOPS you were observing? Also they make pretty fast CPUs these days :)