We are currently looking at partitioning a multi TB table leaving all existing data in place and simply attaching it as a partition to a new table. To prevent locking, we are trying to add an INVALID check constraint first and then validate it.
I can trivially prove the invalid constraint is valid with a simple SELECT which will use an existing index and return instantaneously. But AFAIK Theres no way to mark a constraint as valid without scanning all the rows.
This operation is really problematic on a production database with heavy IO load.
Is there a solid ready why validating check constraints cannot use existing indexes? If I can prove the constraint is valid so trivially with a SELECT, then why can Postgres not do the same (or similar)?
