Re: index bloat

"David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> As promised, here are two runs of VACUUM VERBOSE on the problem table ...
> There was a lot of activity on the campaign_email table on Friday
> (Saturday's VACUUM) as compared with Monday (Tuesday's VACUUM)

Well, what these numbers show is that you have 5% to 10% daily turnover
of data in this table (maybe more --- are these two days representative,
do you think)? But anyway, taking that number as gospel, you'd expect
that the table and indexes would settle at about 10% free space
immediately after each VACUUM. That would represent a steady state:
just enough free space to get eaten up till the next VACUUM. The table
itself seems to have stabilized, and the "email_campaign_idx" index as
well -- note the latter didn't grow at all, and its internal free space
is in the 10% ballpark:

> INFO: index "email_campaign_idx" now contains 5881215 row versions in 31435
> pages
> DETAIL: 501822 index row versions were removed.
> 2016 index pages have been deleted, 896 are currently reusable.

> INFO: index "email_campaign_idx" now contains 5583831 row versions in 31435
> pages
> DETAIL: 280860 index row versions were removed.
> 3266 index pages have been deleted, 2531 are currently reusable.

I'm not sure why the other indexes don't seem to have reached their
steady-state 10% free yet. What can you tell us about the patterns
of data being inserted into these various indexes?

regards, tom lane