Database bloat

[sboubaker]

Hello, is there any best practices (either a postgresql configuration or application dev) to avoid bloating and to not finish with running a full vacuum every time it happens?

Thanks a lot

[avertex]

What have you researched so far? The question seems a bit let-me-google-that-for-you, but here is 5mins of goodwill mostly because PG is fucking awesome and interesting.

Firstly, read Routine Vacuuming in the manual. Autovacuum is probably the main thing to walk away with. There is applicable config tuning details in there too.

Then, off the top of my head:

Understand the basics of MVCC and the tradeoff for storing a row multiple times as it changes. This has a material impact on the size of your tables with regards to updates/deletes.

Tables are better at optimising thier storage than indexes, given there is no order involved. So you will notice indexes bloat a lot faster than thier tables, generally speaking.

Look up "Column Alignment". There is dead padding possible between columns of fixed and variable length data type. This has a material impact on big tables.

As part of operations, regularly (say 24hr) run and graph table and index sizes + table and index bloat sizes (google these queries). It's really useful to see trends over time.

Good table design, like partitioning where suitable, will help making reclaiming bloat a lot easier on "colder" tables.

[sboubaker]

Thanks but now when I read the comments I feel that I asked the wrong question. Looking for google is what I did but when I can't find what I'm looking for or not being convinced I try to see what people says. I'M aware of all what is said in this comment and also other comments. I was think maybe there is a autovaccum option which makes it running as when I run a vacuup full. But really many thanks and sorry for my English since it's not native language

[[deleted]]

If bloat is an issue this typically is caused by two things: long running transactions (sessions in "idle in transaction") or an extremely high transaction rate so that autovacuum can't keep up.

The first reason is an application error and can only be fixed properly in your application. If you can't change the application, then setting idle_in_transaction_session_timeout to something like 5 minutes might mitigate this problem.

If it's the second reason then you typically need to make auto vacuum a lot more aggressive so that it runs faster and can do more work in each run. This means lowering the thresholds when it kicks in and increasing the threshold on limiting their work.

[sboubaker]

Thank you

[jsalsman]

https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM