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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
