Speeding up adding fky on a very large table
AWS Aurora based on PG 13.
Large partitioned table of 5+ billion rows and 7TB in size.
ALTER TABLE abc ADD CONSTRAINT fk_123 FOREIGN KEY (a,b,c) REFERENCES xyz(1,2,3);
It seems this is not parallelized. Is there a way. Or directly going into each partition is the only way ( not even sure it is possible).
Import Notes
Reference msg id not found: 1365946824.316128.1666186309386.ref@mail.yahoo.com
On 10/19/22 08:31, Ravi Krishna wrote:
AWS Aurora based on PG 13.
Large partitioned table of 5+ billion rows and 7TB in size.
ALTER TABLE abc ADD CONSTRAINT fk_123 FOREIGN KEY (a,b,c) REFERENCES
xyz(1,2,3);It seems this is not parallelized. Is there a way. Or directly going into
each partition is the only way ( not even sure it is possible).
In vanilla Postgresql (including RDS Postgresql) you can add the FK to each
partition and then ALTER TABLE ONLY the parent table.
Don't know about Aurora, though.
--
Angular momentum makes the world go 'round.
Our issue is that it takes 20hrs to index the full table. Hopefully we can add FK in multiple child partitions concurrently, otherwise doing it per partition offers no advantage from performance pov.
Need to test. Hopefully PG should not lock the referred table during the first build, stopping concurrent execution of other partitions.
In vanilla Postgresql (including RDS Postgresql) you can add the FK to each partition and then ALTER TABLE ONLY the parent table.
Don't know about Aurora, though