Re:My Notes on Gitlab's Postgres Schema Design (2022)
by winrid on Saturday February 17, 2024 @07:25PM
(#39414848)
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.
[ Parent
| on HN ]
Re:My Notes on Gitlab's Postgres Schema Design (2022)
by aeyes on Saturday February 17, 2024 @07:58PM
(#39415063)
Disk wasn't the limit in my case, index creation is single threaded.
Re:My Notes on Gitlab's Postgres Schema Design (2022)
by winrid on Saturday February 17, 2024 @09:04PM
(#39415431)
Which is still very IO bound... Wonder what kinda IOPS you were observing? Also they make pretty fast CPUs these days :)
Re:My Notes on Gitlab's Postgres Schema Design (2022)
by scient on Sunday February 18, 2024 @09:58AM
(#39419554)
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...
Re:My Notes on Gitlab's Postgres Schema Design (2022)
by aeyes on Sunday February 18, 2024 @10:20AM
(#39419738)
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.