Hi.
A lot of time during pg_restore of a large database is spent on
validating all the foreign keys. In contrast to importing data and
creating indexes this operation does not parallelize well. So large
percentage of parallel restore time ends up using single worker to
validate foreign keys for the largest table.
If we'd have a option to restore the table without validating foreign
keys and leaving them in NOT VALID state, the downtime needed for us to
restore would decrease significantly.
If we'd also have an option to avoid blocking updates on the table
during (potentially long) validating, for example: ALTER TABLE distributors VALIDATE CONSTRAINT CONCURRENTLY distfk;
Then we could postpone it and do it during normal operation of the
database, out of precious disaster recovery time.
Alternatively maybe it should be allowed to do for example: ALTER TABLE distributor ADD CONSTRAINT distfk FOREIGN
KEY(address) REFERENCES addresses (address) VALID;
It would mean that the database should assume that this constraint is
valid. Should be possible to turn it on using some pg_restore option (or
pg_dump option when dumping to text format), though maybe only when
restoring whole database, not single table. Though there's a possibility
that a partially failed restore could leave database in inconsistent
state. So I'd rather prefer the above option (NOT VALID + VALIDATE
CONCURRENTLY).
Any comments on this? Does it look like a good idea? It shouldn't be
hard to implement.
--
Tomasz "Tometzky" Ostrowski