[pageald]

For our use case, we found the best approach was to clone all of the data to a temporary table with indices and constraints disabled, perform the updates, re-enable indices and constraints, and then replace the production table with the temporary table. This only works if you are able to update your data in bulk, and if some lag time in your updates is OK. This also has the benefit of never locking your production table.

In situations where real-time updates are important, the key is to minimize your indices as much as possible. Read up on heap only tuples (HOT). If that all isn't enough, maybe consider sharding your database.

Never run VACUUM FULL; it locks too aggressively. Let autovacuum do the job.