On 2019-Jan-22, Denisa Cirstescu wrote: > I am trying to add a new column to a really big table and to define an INDEX > and a FOREIGN KEY on that new column using the following instructions: > > ALTER TABLE Employee ADD COLUMN DepartmentId INTEGER; > CREATE INDEX IDX_Employee_DepartmentId ON Employee(DepartmentId); > ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN > KEY(DepartmentId) REFERENCES Department(DepartmentId); > > The table is huge and it takes a lot of time to add the INDEX and the FOREIGN > KEY although all values are NULL. > Considering that the new DepartmentId column is NULL for all rows at this > point, is there a way to make the INDEX and FOREIGN KEY creation run faster?
The typical advice is to run CREATE INDEX CONCURRENTLY instead of plain CREATE INDEX. Also, use "ADD CONSTRAINT ... NOT VALID;" instead, and later do another ALTER TABLE ... VALIDATE CONSTRAINT. That decreases the impact considerably in production scenarios. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
