On Mon, Nov 12, 2012 at 10:38 AM, Lists <lists@benjamindsmith.com> wrote:
> On 11/10/2012 02:21 PM, Jeff Janes wrote:
>>
>> On Fri, Nov 9, 2012 at 4:28 PM, Lists <lists@benjamindsmith.com> wrote:
>>
>>>
>>> 2) It was sheer chance that I discovered the need to reindex prior to
>>> vacuum
>>> in order to get the disk space back.
>>
>> As of 9.0, a "vacuum full" inherently does a reindex, so doing an
>> explicit one is neither necessary nor beneficial.
>>
>> I don't know if your discovery is based on a non-full vacuum, or on an
>> older server.
>
>
> I can only state that merely doing a "vacuum full" or "vacuum full $tables"
> sequentially did not free the space, whereas the sequential reindex $table,
> each followed immediately by a vacuum full $table) did.
With what version?
> If you'd like I can
> easily recreate the scenario by simply not "cleaning up" one of the DB
> servers until it bloats up and make available (limit distribution) a binary
> copy of the database (EG: rsync the /var/lib/pgsql/ filesystem late at
> night) in order to help identify why it didn't work as expected.
Do you think can make an easily script-able way to re-create the
resistant bloat? That would be better than trying to disseminate
binary files, I think.
What I did was just create and drop temp tables in a tight loop, with
autovacuum off, and then once pg_attribute got good and bloated, did a
vacuum full as the database owner or superuser.
>>
>> If all of your long-lived objects were created before pg_attribute got
>> bloated and so the bloat was due only to short-lived objects, then
>> non-full vacuum (if run often enough) should eventually be able to
>> return that space as the short-lived objects near the end start to go
>> away. However, if even a single long-live object finds itself at the
>> end of the table, then only a vacuum full will ever be able to reclaim
>> that space.
>>
>
> Since the time period involved (weeks/months) would have included both a
> large number of created/destroyed temp tables and occasionally altered
> persistent objects it would appear that the full option a very good idea, at
> least periodically.
If you can prevent the extreme bloat from occurring in the first
place, then the "end" of the table would not be so far away from its
desired size that it needs to get reset by a vacuum full.
If you find your self in need of a vacuum full, then you should do
one. But you should ask yourself what went wrong that you got into
that situation in the first place.
Cheers,
Jeff