Implications of leaving a constraint NOT VALID

user_560903 0
Viewed 387

I'm performing schema changes on a large database, correcting ancient design mistakes (expanding primary keys and their corresponding foreign keys from INTEGER to BIGINT). The basic process is:

  • Shutdown our application.
  • Drop DB triggers and constraints.
  • Perform the changes (ALTER TABLE foo ALTER COLUMN bar TYPE BIGINT for each table and primary/foreign key).
  • Recreate the triggers and constraints (NOT VALID).
  • Restart the application.
  • Validate the constraints (ALTER TABLE foo VALIDATE CONSTRAINT bar for each constraint).

Note:

  • Our Postgres DB (version 11.7) and our application are hosted on Heroku.
  • Some of our tables are quite large (millions of rows, the largest being ~1.2B rows).

The problem is in the final validation step. When conditions are just "right", a single ALTER TABLE foo VALIDATE CONSTRAINT bar can create database writes at a pace that exceeds the WAL's write capacity. This leads to varying degrees of unhappiness up to crashing the DB server. (My understanding is that Heroku uses a bespoke WAL plug-in to implement their "continuous backups" and "db follower" features. I've tried contacting Heroku support on this issue -- their response was less than helpful, even though we're on an enterprise-level support contract).

My question: Is there any downside to leaving these constraints in the NOT VALID state?

Related: Does anyone know why validating a constraint generates so much write activity?

postgresql
1 Answers

There are downsides to leaving a constraint as not valid. Firstly, you may have data that doesn't meet the constraint requirements, meaning you have data that shouldn't be in your table. But also the query planner won't be able to use the constraint predicate to rule out rows that meet or don't meet the constraint requirment.

As for all the WAL activity, I could only imagine that's because it has to set the flag for those rows to mark them as valid. This should produce a relatively small amount of WAL to be generated relative to actual row updates, but I guess if you have enough rows being validated, it will generate a lot of WAL. That shouldn't usually cause a crash unless storage becomes full.

user_121961760
Related