Skip site navigation (1) Skip section navigation (2)

Re: Vacuum-full very slow

From: Listmail <lists(at)peufeu(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Cc: "Steve Crawford" <scrawford(at)pinpointresearch(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Vacuum-full very slow
Date: 2007-04-25 22:13:13
Message-ID: op.trc1cbe2zcizji@apollo13 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
> I don't see a way to remove the old index entries before inserting new
> ones without creating a window where the index and table will be
> inconsistent if vacuum fails.

	VACUUM FULL is slow because it plays with the indexes...
	CLUSTER is slow because it has to order the rows...

	Maybe, drop all the indexes, VACUUM FULL only the table, then recreate  
all the indexes ?
	If vacuum fails, the index drop would be rolled back.

	By the way, about indexes :

	When you have a small table (say, for a website, maybe a few tens of  
megabytes max...) reindexing it takes just a few seconds, maybe 10-20  
seconds.
	It could be interesting, performance-wise, to tell postgres not to bother  
about crash-survivability of indexes on this table. Like temporary tables.  
Write nothing to WAL. If it crashes, on recovery, postgres would reindex  
the table.
	btree indexing is so fast on postgres that I'd definitely use this  
feature.
	I'd rather trade a minute of recovery versus less disk IO for index  
update.

	You could even do that for whole tables (like, web sessions table) which  
hold "perishable" data...

> CLUSTER avoids all this thrashing by recopying the whole table, but
> of course that has peak space requirements approximately twice the
> table size (and is probably not a win anyway unless most of the table
> rows need to be moved).  You pays your money, you takes your choice.
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq



In response to

Responses

pgsql-hackers by date

Next:From: Jonathan VanascoDate: 2007-04-25 22:45:21
Subject: Re: Schema as versioning strategy
Previous:From: Tom LaneDate: 2007-04-25 22:05:56
Subject: Re: strange buildfarm failures

pgsql-general by date

Next:From: Jonathan VanascoDate: 2007-04-25 22:45:21
Subject: Re: Schema as versioning strategy
Previous:From: Tom LaneDate: 2007-04-25 21:08:49
Subject: Re: Vacuum-full very slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group