Why do you assume 4 tuples per TOAST page? about pgsql-bloat-estimation HOT 4 CLOSED

Per documentation, TAOSTed values are sliced into rows with maximum size of TOAST_MAX_CHUNK_SIZE, which is about 2kB. See http://www.postgresql.org/docs/current/static/storage-toast.html :

Out-of-line values are divided (after compression if used) into chunks of at most TOAST_MAX_CHUNK_SIZE bytes (by default this value is chosen so that four chunk rows will fit on a page, making it about 2000 bytes). Each chunk is stored as a separate row in the TOAST table for the owning table.

from pgsql-bloat-estimation.

Seems like that would make for an overly generous estimate; it assumes that all rows use the max size, no?

Not that I have better figures for you ...

from pgsql-bloat-estimation.

Seems like that would make for an overly generous estimate; it assumes that all rows use the max size, no?

Indeed. But I prefer to over-estimating the size of the bloat.

Not that I have better figures for you ...

Having a finer estimation of the toast bloat is complex. I didn't dig very far, but I see at least 4 big issues with bloat estimation for toast:

• what fields will be moved out-of-line ?
• when fields will be compressed ?
• what fields will be compressed ?
• compressed data size is hard to estimate...

I have no time presently to work on this issues. We have pushed some optimizations in check_pga (OPMDG/check_pgactivity@be9bacd) related to table bloat estimation I hadn't time to backport here...

Any help, research or discussion from outside is welcome.

In the meantime, it would be interesting to document the different situations where these bloat estimation queries are totally wrong because of this.

from pgsql-bloat-estimation.

I couldn't come up withanything better, so I just used your math.

from pgsql-bloat-estimation.