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
