thanks Laurenz, at least this confirms the big size is not an issue.
Regarding % of dead tuples vs live tuples, I haven't tried it but
apparently pgstattuple, from contribs should do that, just in case
anybody reading had the same question.
thanks,
H
On 16/05/12 14:41, Albe Laurenz wrote:
> Horaci Macias wrote:
>> after tuning the autovacuum settings I can now see the tables vaccumed
>> and the number of dead tuples dropping whenever an autovacuum happens,
>> which makes sense.
> Great.
>
>> What I don't see though is the size of the tables ever decreasing, but
>> I'm not sure I should see this.
>>
>> Can somebody please confirm whether vacuum (not vacuum full) will ever
>> reduce the size of a table or will the table always have whatever
>> maximum size it ever reached, even if under the hood some inserts
> don't
>> result in size increasing because space is being reused?
>> For example, starting from an empty table, I insert tuples until the
>> table is 1G in size. Then I insert another bunch of tuples and the
> table
>> reaches 2G. If I delete this second bunch of tuples and vacuum (not
>> vacuum full) the table, should I expect the table to be ~1G in size
>> again or is it "normal" that the table stays at 2G (although ~1G
>> contains dead tuples)? If I add again the bunch of tuples I deleted,
>> should I expect the table to remain at ~2G (since the dead tuples
> space
>> was reused) or would the table grow to ~3G?
> Yes, that's expected behaviour.
> AFAIK VACUUM will only reclaim zeroed pages at the end of the table,
> but everything else stays empty.
>
>> Is there any easy way to see how much of the size of a table is
> occupied
>> by dead tuples and how much is occupied by live tuples?
> I don't think there is - you could come up with a formula using
> pg_statistics (stawidth = average width of column) and pg_class
> (reltuples = number of tuples, relpages = number of pages), but
> you'd have to do some accounting for headers and other stuff.
>
> Might be an interesting exercise though.
>
> Yours,
> Laurenz Albe