From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Horaci Macias *EXTERN*" <hmacias(at)avaya(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: vacuum, vacuum full and problems releasing disk space |
Date: | 2012-05-16 12:41:20 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C207E6A58F@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Horaci Macias | 2012-05-16 12:46:17 | Re: vacuum, vacuum full and problems releasing disk space |
Previous Message | Christian J. Dietrich | 2012-05-16 11:27:18 | missing pg_clog files after pg_upgrade |