Re: [PERFORM] Best practice when reindexing in production

2013-05-31 Thread Jeff Janes
On Wednesday, May 29, 2013, Niels Kristian Schjødt wrote: > Hi, > > I have a database with quite some data (millions of rows), that is heavily > updated all the time. Once a day I would like to reindex my database (and > maybe re cluster it - don't know if that's worth it yet?). I need the > datab

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Daniele Varrazzo
On Wed, May 29, 2013 at 6:47 PM, Alan Hodgson wrote: > On Wednesday, May 29, 2013 06:25:21 PM Daniele Varrazzo wrote: >> My solution has been to become pg_repack maintainer. YMMV. Just don't >> expect vacuum to reduce the indexes size: it doesn't. > > It's not supposed to. It is supposed to keep t

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Alan Hodgson
On Wednesday, May 29, 2013 06:25:21 PM Daniele Varrazzo wrote: > My solution has been to become pg_repack maintainer. YMMV. Just don't > expect vacuum to reduce the indexes size: it doesn't. It's not supposed to. It is supposed to keep them from indefinitely growing, though, which it does reasona

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Daniele Varrazzo
On Wed, 2013-05-29 at 19:12 +0200, Jesper Krogh wrote: > Second, if the new index is significantly smaller than the old on, I > suggest that you try to crank up the autovacuum daemon instead of > blindly dropping and creating indexes, this will help to mitigate the > bloat you're seeing accumul

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Jesper Krogh
On 29/05/13 14:24, Niels Kristian Schjødt wrote:On 29/05/13 14:24, Niels Kristian Schjødt wrote: Hi, I have a database with quite some data (millions of rows), that is heavily updated all the time. Once a day I would like to reindex my database (and maybe re cluster it - don't know if that's w

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Igor Neyman
From: Matheus de Oliveira [mailto:matioli.math...@gmail.com] Sent: Wednesday, May 29, 2013 10:19 AM To: Igor Neyman Cc: Niels Kristian Schjødt; Magnus Hagander; pgsql-performance@postgresql.org list Subject: Re: [PERFORM] Best practice when reindexing in production On Wed, May 29, 2013 at

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Matheus de Oliveira
On Wed, May 29, 2013 at 10:55 AM, Igor Neyman wrote: > > > You could do something like this (which considers you use simple names for > your indexes, where simple ~ [a-z_][a-z0-9_]*): > > SELECT > regexp_replace(i.indexdef, '^CREATE( UNIQUE)? INDEX (.*) ON (.*)', > 'CREATE\1 INDEX CONCURRENTLY tmp

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Igor Neyman
You could do something like this (which considers you use simple names for your indexes, where simple ~ [a-z_][a-z0-9_]*): SELECT  regexp_replace(i.indexdef, '^CREATE( UNIQUE)? INDEX (.*) ON (.*)', 'CREATE\1 INDEX CONCURRENTLY tmp_\2 ON \3;') || E'\n' || E'BEGIN;\n' || 'DROP INDEX ' || i.index

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Matheus de Oliveira
On Wed, May 29, 2013 at 9:41 AM, Niels Kristian Schjødt < nielskrist...@autouncle.com> wrote: > Thanks > > Can you think of a way to select all the indexes programmatically from a > table and run CREATE INDEX CONCURRENTLY for each of them, so that I don't > have to hardcode every index name + crea

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Magnus Hagander
On Wed, May 29, 2013 at 8:41 AM, Niels Kristian Schjødt wrote: > Thanks > > Can you think of a way to select all the indexes programmatically from a > table and run CREATE INDEX CONCURRENTLY for each of them, so that I don't > have to hardcode every index name + create statement ? You can use s

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Niels Kristian Schjødt
Thanks Can you think of a way to select all the indexes programmatically from a table and run CREATE INDEX CONCURRENTLY for each of them, so that I don't have to hardcode every index name + create statement ? Den 29/05/2013 kl. 14.26 skrev Magnus Hagander : > On Wed, May 29, 2013 at 8:24 AM,

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Niels Kristian Schjødt
I looked at pg_repack - however - is it "safe" for production? It seems very intrusive and black-box-like to me... Den 29/05/2013 kl. 14.30 skrev Armand du Plessis : > > On Wed, May 29, 2013 at 2:26 PM, Magnus Hagander wrote: > On Wed, May 29, 2013 at 8:24 AM, Niels Kristian Schjødt > wrote:

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Armand du Plessis
On Wed, May 29, 2013 at 2:26 PM, Magnus Hagander wrote: > On Wed, May 29, 2013 at 8:24 AM, Niels Kristian Schjødt > wrote: > > Hi, > > > > I have a database with quite some data (millions of rows), that is > heavily updated all the time. Once a day I would like to reindex my > database (and maybe

Re: [PERFORM] Best practice when reindexing in production

2013-05-29 Thread Magnus Hagander
On Wed, May 29, 2013 at 8:24 AM, Niels Kristian Schjødt wrote: > Hi, > > I have a database with quite some data (millions of rows), that is heavily > updated all the time. Once a day I would like to reindex my database (and > maybe re cluster it - don't know if that's worth it yet?). I need the

[PERFORM] Best practice when reindexing in production

2013-05-29 Thread Niels Kristian Schjødt
Hi, I have a database with quite some data (millions of rows), that is heavily updated all the time. Once a day I would like to reindex my database (and maybe re cluster it - don't know if that's worth it yet?). I need the database to be usable while doing this (both read and write). I see that