Database Vacuum - How much bloat is too much bloat?

[Jackbayer13]

Hello!

We have recently moved to a new DB vacuum process at the recommendation of Adobe Support. We now raise a ticket with support to request a vacuum of a maximum of five tables within the instance. We have a portal where we can see the table and index bloat on the tables. We requested a vacuum in November when the max table bloat was 34.2 and max index bloat was 22.8. The down-time for this vacuum was only an hour, so it has made me think we can let bloat accumulate a little longer before requesting a vacuum.

My question is: at what levels does table and index bloat start to impact instance performance? How high should we let bloat get before requesting a vacuum?

Thanks!

[LakshmiPravallika · Accepted solution]

Hi @Jackbayer13 ,

To perform bloat it is mandatory to have 20% of dead rows of the total table size. You can check it by 

If Full Vaccuming of a table is required, it requires the down time of the database.

Please refer to the below documentation links:

https://postgresmonitor.com/docs/bloat-recommendation/

https://dba.stackexchange.com/questions/246045/how-often-do-you-run-vacuum-full

An example to calculate Bloat and when to do Vacuuming is as shared below

https://www.highgo.ca/2021/03/20/how-to-check-and-resolve-bloat-in-postgresql/

Regards,

Pravallika.

[Jackbayer13]

Hi Pravallika,

Thanks for your reply, that is very helpful. In your answer you start to say how to check the percentage of dead rows in the table, but it appears the rest of your sentence is missing. Could you please elaborate on how best to check the percentage of dead rows in a table?

Thanks,

Jack Bayer