X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 16CA4D1B4AF for ; Sun, 4 Jan 2004 23:35:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47398-07 for ; Sun, 4 Jan 2004 19:34:54 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id D6683D1C94F for ; Sun, 4 Jan 2004 19:34:13 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i04NYDU6038068 for ; Sun, 4 Jan 2004 23:34:13 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i04NT3lZ034110 for pgsql-admin@postgresql.org; Sun, 4 Jan 2004 23:29:03 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: Incremental clustering? Date: Sun, 04 Jan 2004 18:24:53 -0500 Organization: cbbrowne Computing Inc Lines: 45 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:bYvZ5OYr3JMe4ugpKKAHV37p2/4= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/44 X-Sequence-Number: 11907 A long time ago, in a galaxy far, far away, siracusa@mindspring.com (John Siracusa) wrote: > The docs say: > > "Clustering is a one-time operation: when the table is subsequently > updated, the changes are not clustered. That is, no attempt is made > to store new or updated rows according to their index order. If one > wishes, one can periodically recluster by issuing the command > again." > > and > > "During the cluster operation, a temporary copy of the table is > created that contains the table data in the index order. Temporary > copies of each index on the table are created as well. Therefore, > you need free space on disk at least equal to the sum of the table > size and the index sizes." > > I don't relish totally locking and making a temporary copy of my > biggest table, but that's exactly the table that seems to need > clustering the most. Will subsequent cluster command also make a > complete copy? Some form of "incremental clustering" would be > nice... Unfortunately, making this work in a manner that allows concurrency is likely to be really troublesome. The cluster operation potentially has to reorder all the tuples, and the fact that the table is already _partially_ organized only diminishes the potential. If the new data, generally added "at the end," has values that are fairly uniformly distributed across the index, then the operation really will have to reorder all of the tuples... It would be pretty sweet to have a process analagous to 'non-blocking VACUUM' as opposed to 'VACUUM FULL.' But there's no particularly easy way, and, to do so, you'd essentially have to throw away a fair chunk of the benefits of the "clustered" properties. -- "cbbrowne","@","acm.org" http://www.ntlug.org/~cbbrowne/rdbms.html "When you have eliminated the impossible, whatever remains, however improbable, must be the truth." -- Sir Arthur Conan Doyle (1859-1930), English author. Sherlock Holmes, in The Sign of Four, ch. 6 (1889). [...but see the Holmesian Fallacy, due to Bob Frankston... ]