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
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
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
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:
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,
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
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
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
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
We re-tested these settings a few times after our initial test and realized that the execution time I posted was shewed, because the execution plan was cached after the initial run. Subsequent executions ran in a little over a second.
There ended up being no significant saving by setting these para
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 10
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
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
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
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
Folks,
I'm seeing what may be a major performance bug in BIND in 9.2.4.
We have a client who has an application which uses
Tomcat+Hibernate+JDBC. They are in the process of upgrading this
application from 8.4.17 to 9.2.4. As part of this, they have been doing
performance testing, and 9.2 is com
On 05/29/2013 05:14 PM, Josh Berkus wrote:
> Here's timings overall for 29 test cycles (cycle 1 has been omitted).
> As you can see, parse+execute times are pretty much constant, as are
> application think times, but bind times vary quite a lot. In 8.4, the
> 29 cycles are constantly 4.5min to 5.7
17 matches
Mail list logo
