6

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I am skeptical about the WAL activity generated by constraint validation. In theory the database should not write to the target table because it only needs to read data to validate the constraint and update the system catalog when done. Do you have any documentation about a flag for valid rows related to constraints in PostgreSQL ?
We recently performed a constraint validation on a 550M+ row table and it did cause WAL creation to increase dramatically. So much so that pg_wal did fill and crashed our server. We had to double the storage size of pg_wal three times after starting the validation.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.