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