0

As seen in this answer by setting postgresql's hot_standby_feedback on it may cause having bloated table on long running queries. Therefore, in case that size goes out of control (my database consumes way too much space because of dead rows) to be able to handle it.

Therefore, I thought to enforce a VACCUMM action in this situation to delete stale and unused rows in case that storage goes out of controll as immediate action.

But how I can VACUUM all the tables of my database manually? Also, how I can make a dedicated user that is able only to VACUUM in order to create custom scripts that I can run in these situations?

1

If you have hot_standby_feedback = on, a manual VACUUM won't delete the dead row versions if there is a long running query on the standby.

The only way to do that would be to set old_snapshot_threshold to a value different from the default value. Then VACUUM (and autovacuum) will remove dead tuples even if an old transaction might still need them. Note that that would defy the purpose of hot_standby_feedback, as then the long running query might receive a snapshot too old error.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.