5 ms·
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.
by winrid 3y ago
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.
- aeyes 3y agoDisk wasn't the limit in my case, index creation is single threaded.
- winrid 3y agoWhich is still very IO bound... Wonder what kinda IOPS you were observing? Also they make pretty fast CPUs these days :)
- scient 3y agoLarge 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 3y agoI 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 3y ago> 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.