Some quickie background: I'm on a project to migrate a fairly large
database from MySQL to PostgreSQL (~2T). As a result of a number of
factors, I have to do it in one shot and I have a limited time window
in which things can be down while I switch it over.
As one of many, many things I'm considering to make this work, I'm
looking at adding constraints after the data move using NOT VALID to
allow them to be applied quickly. This seems pretty straight forward,
but I'm trying to understand if there are any troublesome side-effects
to leaving the constraints unvalidated.
Because of the uptime requirements, there are some very large tables
with may foreign keys that I will never be allowed to take a lock on
long enough to validate all the constraints. It was suggested that
leaving the constraints as NOT VALID might affect the planner, causing
it to use less optimal plans because it doesn't think it can trust
the constraint. Is this true?
It has also been suggested that manually changing the status to valid
in the catalog without going through the validation process could cause
problems, although I haven't found an explanation of what those
problems might be.
I understand that the best way is to go through and do all the steps,
but that may simply be impossible for me because of the lock it
requires and the time involved. Is there any negative effect to
leaving the constraint unvalidated? Is there any actual danger in
manually flipping the value in the catalog (The constraint can be
consider safe because it was previously enforced on the source
database system)
--
Bill Moran <wmoran@potentialtech.com>