Re: Looking for settings/configuration for FASTEST reindex on idle system.

On Thu, Jan 9, 2014 at 2:03 PM, Jeff Amiel <becauseimjeff(at)yahoo(dot)com> wrote:
> I have a maintenance window coming up and using pg_upgrade to upgrade from 9.2.X to 9.3.X.
> As part of the window, I’d like to ‘cluster’ each table by its primary key. After doing so, I see amazing performance improvements (probably mostly because of index bloat - but possibly due to table fragmentation)

[...]

> fsync (set to off)
> setting wal_level to minimal (to avoid wal logging of cluster activity)
> bumping up maintenance work men (but I’ve also seen/read that uber high values cause disk based sorts which ultimately slow things down)
> Tweaking checkpoint settings (although with wal_level set to minimal - I don’t think it comes into play)
>
> any good suggestions for lighting a fire under this process?

Another idea is to drop all indexes except ones you are going to
cluster tables by, cluster the tables and then restore the dropped
indexes. Use xargs with -P to perform things in parallel, for example

cat indexdefs.sql | xargs -d '\n' -I {} -P 5 psql -e dbname -c '{}'

will start creating indexes from indexdefs.sql (assuming one index
definition in one line) by 5 in parallel. You can also cluster all
your tables a similar way by several tables in parallel.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com