Search Postgresql Archives

Re: vacuum, vacuum full and problems releasing disk space

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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.


> 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
> 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
> 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
> 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
> 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.

Laurenz Albe

Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:

[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux