> 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