is it "bad" to bulk update a boolean column semi-regularly on potentially a large set of records in Postgres?

Question

pertaining to a previous question I asked: how do you prevent dead rows from hanging around in postgresql?

If I have a table with 300k rows, and I were to update all rows in the table, setting a boolean column from false to true, is this an OK thing to do or should bulk updates over a certain size always be avoided? I'm still a bit fuzzy on the whole Vacuum thing here, my understanding is that the MVCC in postgres causes every row to be duplicated temporarily and then auto-vacuum should come in an reclaim that space. I am wondering, hypothetically, if these 300k rows were to have that boolean value flipped once a week, would I eventually run out of disk space, or would this not be a problem as long as there was enough time between the updates for auto-vacuum to come and keep things in check?

Answer 1

So, 300k rows total doesn't seem like a huge amount, I wouldn't be overly worried unless you have a particular cause for concern (e.g. your UPDATE taking way too long, holding row locks for too long, etc).

But two suggestions which may be helpful for your particular use-case:

First, make sure that your UPDATE statement does not touch rows it does not need to. If you want to set all values of some_bool_column to false, do it like this:

UPDATE mytable SET some_bool_column = false WHERE some_bool_column IS DISTINCT FROM false;

although semantically the UPDATE above is exactly the same as if it did not have a WHERE clause (ignoring triggers), you will avoid a ton of I/O for tuples which already have some_bool_column = false.

Second, if you can, try to take advantage of Heap-Only Tuples aka the HOT optimization for your UPDATEs. So if you can avoid having an index on some_bool_column, these bulk UPDATEs will be faster and may be able to avoid contributing to index bloat. Often, you don't want to have an index on a boolean column anyway, since the selectivity will be low (or if one of the boolean values is rare, just use a partial index on that value.)

Answer 2

For good MVCC explanations, I would look at these links:

Jim Nasby's VACUUM talk: https://www.youtube.com/watch?v=b1fcvkl0ffQ MVCC Concurrency Explanation: https://devcenter.heroku.com/articles/postgresql-concurrency

If you're only doing it once a week, unless there's a lot of other activity that stops autovacuum from running, it should do a reasonable job of cleanup.

If you're worried about it, and you schedule the update job during a quiet time, you should use a VACUUM ANALYZE VERBOSE after the bulk update, to help clean up the older rows and update the statistics on the table too.

You can also monitor bloat with the suggestions from your previous post, and disk space.

Finally, you could tune autovaccum for this particular table to be much more aggressive on the vacuuming:

Aggressive Autovacuum on PostgreSQL

Hope that helps with your situation. =)