2011/1/30 ÐÑÑÐÐÑÐ ÐÐÐÑÐÑÐÐ <tivv00@xxxxxxxxx>: > I was thinking if a table file could be deleted if it has no single live > row. And if this could be done by vacuum. In this case vacuum on table that > was fully updated recently could be almost as good as cluster - any scan > would skip such non-existing files really fast. Also almost no disk space > would be wasted. VACUUM actually already does something along these lines. If there are 1 or any larger number of entirely-free pages at the end of a table, VACUUM will truncate them away. In the degenerate case where ALL pages are entirely-free, this results in zeroing out the file. The problem with this is that it rarely does much. Consider a table with 1,000,000 pages, 50% of which contain live rows. On average, how many pages will this algorithm truncate away? Answer: if the pages containing live rows are randomly distributed, approximately one. (Proof: There is a 50% chance that the last page will contain live rows. If so, we can't truncate anything. If not, we can truncate one page, and maybe more. Now the chances of the next page being free are 499,999 in 999,999, or roughly one-half. So we have an almost-25% chance of being able to truncate at least two pages. And so on. So you get roughly 1/2 + 1/4 + 1/8 + 1/16 + 1/32 + ... = 1 page.) Your idea of having a set of heaps rather than a single heap is an interesting one, but it's pretty much catering to the very specific case of a full-table update. I think the code changes needed would be far too invasive to seriously contemplate doing it just for that one case - although it is an important case that I would like to see us improve. Tom Lane previously objected to the idea of on-line table compaction on the grounds that people's apps might break if CTIDs changed under them, but I think a brawl between all the people who want on-line table compaction and all the people who want to avoid unexpected CTID changes would be pretty short. A bigger problem - or at least another problem - is that moving tuples this way is cumbersome and expensive. You basically have to move some tuples (inserting new index entries for them), vacuum away the old index entries (requiring a full scan of every index), and then repeat as many times as necessary to shrink the table. This is not exactly a smooth maintenance procedure, or one that can be done without significant disruption, but AFAIK nobody's come up with a better idea yet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
