DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=hxrsVKf5IiLXadHneRX7FBCdkDpgUFz/VYOXvgUnOsAgmj6pfnaZ+RGYAFbyOrEvlX
	7lb/wstLUFwQZrlO+OUhbzxot4BGdSC37jGGrsuM9kWV3JTZpIg/3jPzQb6Wd3aUB/lB
	+rDdFT/mqRtndBSwjCzvNfwxrUKJwt/x57wm8=
Message-ID: <4D4BC491.7080908@gmail.com>
Date: Fri, 04 Feb 2011 11:19:13 +0200
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: Scott Carey <scott@richrelevance.com>, Stephen Frost <sfrost@snowman.net>,
	Scott Marlowe <scott.marlowe@gmail.com>,
	Robert Schnabel <schnabelr@missouri.edu>, "david@lang.hm" <david@lang.hm>,
	pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: How to best use 32 15k.7 300GB drives?
References: <20110128172810.GE30352@tamriel.snowman.net>
	<C9684183.1E40B%scott@richrelevance.com>
	<AANLkTikWy+U5qK88EhefVo1c-jRTe4gs-01X528XtFKn@mail.gmail.com>
	<AANLkTimhu_2QgX=UX8QtQn_W0c-JqxNmF4+kPZOPgP0v@mail.gmail.com>
In-Reply-To: <AANLkTimhu_2QgX=UX8QtQn_W0c-JqxNmF4+kPZOPgP0v@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

03.02.11 20:42, Robert Haas написав(ла):
> 2011/1/30 Віталій Тимчишин<tivv00@gmail.com>:
>> 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.
Yes, but take into account operations on a (by different reasons) 
clustered tables, like removing archived data (yes I know, this is best 
done with partitioning, but one must still go to a point when he will 
decide to use partitioning :) ).
> 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.
Why do you expect such a invasive code changes? I know little about 
postgresql code layering, but what I propose (with changing delete to 
truncate) is:
1) Leave tuple addressing as it is now
2) Allow truncated files, treating non-existing part as if it contained 
not used tuples
3) Make vacuum truncate file if it has not used tuples at the end.

The only (relatively) tricky thing I can see is synchronizing truncation 
with parallel ongoing scan.

Best regards, Vitalii Tymchyshyn