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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to