Re: [GENERAL] problems with large table

2007-09-14 Thread Mike Charnoky
Thanks, recreating the table solved my problems. Our team is working on implementing some performance tuning based on other recommendations from the list (FSM, etc). Mike Joshua D. Drake wrote: > At this point, you are in a world of hurt :). If you stop a vacuum you > have created a huge mess o

Re: [GENERAL] problems with large table

2007-09-12 Thread Phoenix Kiula
On 13/09/2007, Tom Lane <[EMAIL PROTECTED]> wrote: > "Phoenix Kiula" <[EMAIL PROTECTED]> writes: > > Apart from creating a new table, indexing it, then renaming it to > > original table -- is there an alternative to CLUSTER that doesn't > > impose a painful ACCESS EXCLUSIVE lock on the table? We ar

Re: [GENERAL] problems with large table

2007-09-12 Thread Tom Lane
"Phoenix Kiula" <[EMAIL PROTECTED]> writes: > Apart from creating a new table, indexing it, then renaming it to > original table -- is there an alternative to CLUSTER that doesn't > impose a painful ACCESS EXCLUSIVE lock on the table? We are on > Postgres 8.2.3 and have a heavy duty table that star

Re: [GENERAL] problems with large table

2007-09-12 Thread Alvaro Herrera
Phoenix Kiula escribió: > On 13/09/2007, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > Mike Charnoky wrote: > > > > > Alvaro: The cluster suggestion probably won't help in my case since data > > > in the table should already be naturally ordered by date. > > > > It's not helpful only for reordering

Re: [GENERAL] problems with large table

2007-09-12 Thread Phoenix Kiula
On 13/09/2007, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Mike Charnoky wrote: > > > Alvaro: The cluster suggestion probably won't help in my case since data > > in the table should already be naturally ordered by date. > > It's not helpful only for reordering, but also for getting rid of dead > t

Re: [GENERAL] problems with large table

2007-09-12 Thread Alvaro Herrera
Mike Charnoky wrote: > Alvaro: The cluster suggestion probably won't help in my case since data > in the table should already be naturally ordered by date. It's not helpful only for reordering, but also for getting rid of dead tuples. -- Alvaro Herrera http://www.flickr.

Re: [GENERAL] problems with large table

2007-09-12 Thread Mike Charnoky
I have never heard that stopping a vacuum is problematic... I have had to do this many times in the past without any adverse affects. Is there some sort of documentation which elaborates on this issue? For the record, I did a VACUUM ANALYZE, not FULL. Now that I think about it, I probably should

Re: [GENERAL] problems with large table

2007-09-12 Thread Tom Lane
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > At this point, you are in a world of hurt :). If you stop a vacuum you > have created a huge mess of dead rows in that table. Only if it was a vacuum full, which he didn't mention having tried. I'm kinda wondering whether the vacuum and reindex did

Re: [GENERAL] problems with large table

2007-09-12 Thread Alvaro Herrera
Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > > At this point, I'm at a loss. I've searched the archives for similar > > problems, but none of the suggestions have worked. Is the data in this > > table corrupted? Why are both vacuum and reindex failing to complete

Re: [GENERAL] problems with large table

2007-09-12 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mike Charnoky wrote: > Hi, > At this point, I'm at a loss. I've searched the archives for similar > problems, but none of the suggestions have worked. Is the data in this > table corrupted? Why are both vacuum and reindex failing to complete? > Is

[GENERAL] problems with large table

2007-09-12 Thread Mike Charnoky
Hi, I'm using PostgreSQL 8.1.8 and am having trouble with a table which contains a large amount of data. Data is constantly being inserted into the table, roughly a million inserts per hour at peak. The table currently has about 100 million entries which take up 14G of space (24G with indices).