undefined | Better HN

0 pointswinrid2y ago0 comments

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.

0 comments

scient2y ago

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...

aeyes2y ago

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.

sgarland2y 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.

aeyes2y ago

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

winridOP2y ago

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

j / k navigate · click thread line to collapse

0 comments

scient2y ago

aeyes2y ago

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.

sgarland2y 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.

aeyes2y ago

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

winridOP2y ago

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

j / k navigate · click thread line to collapse