Re: [GENERAL] Catalog bloat (again)

2016-02-10 Thread Ivan Voras
As a follow-up, here's a portion of the nightly vacuum's logs, just want to confirm if my conclusions are right: INFO: vacuuming "pg_catalog.pg_attribute" INFO: scanned index "pg_attribute_relid_attnam_index" to remove 3014172 row versions DETAIL: CPU 0.20s/1.08u sec elapsed 3.72 sec. INFO:

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Scott Mead
-- Scott Mead via mobile IPhone : +1-607-765-1395 Skype : scottm.openscg Gtalk: sco...@openscg.com > On Jan 27, 2016, at 22:11, Joshua D. Drake wrote: > >> On 01/27/2016 03:37 PM, Ivan Voras wrote: >> >> >> On 28 January 2016 at 00:13, Bill Moran > > wro

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Bill Moran
On Thu, 28 Jan 2016 00:37:54 +0100 Ivan Voras wrote: > On 28 January 2016 at 00:13, Bill Moran wrote: > > > On Wed, 27 Jan 2016 23:54:37 +0100 > > Ivan Voras wrote: > > > > > So, question #1: WTF? How could this happen, on a regularly vacuumed > > > system? Shouldn't the space be reused, at le

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Joshua D. Drake
On 01/27/2016 03:37 PM, Ivan Voras wrote: On 28 January 2016 at 00:13, Bill Moran mailto:wmo...@potentialtech.com>> wrote: On Wed, 27 Jan 2016 23:54:37 +0100 Ivan Voras mailto:ivo...@gmail.com>> wrote: > So, question #1: WTF? How could this happen, on a regularly vacuumed > sy

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Joshua D. Drake
On 01/27/2016 03:37 PM, Ivan Voras wrote: On 28 January 2016 at 00:13, Bill Moran mailto:wmo...@potentialtech.com>> wrote: On Wed, 27 Jan 2016 23:54:37 +0100 Ivan Voras mailto:ivo...@gmail.com>> wrote: > So, question #1: WTF? How could this happen, on a regularly vacuumed > sy

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Jerry Sievers
Ivan Voras writes: > On 28 January 2016 at 00:13, Bill Moran wrote: > > On Wed, 27 Jan 2016 23:54:37 +0100 > Ivan Voras wrote: > > > So, question #1: WTF? How could this happen, on a regularly vacuumed > > system? Shouldn't the space be reused, at least after a VACUUM? The

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Ivan Voras
On 28 January 2016 at 00:13, Bill Moran wrote: > On Wed, 27 Jan 2016 23:54:37 +0100 > Ivan Voras wrote: > > > So, question #1: WTF? How could this happen, on a regularly vacuumed > > system? Shouldn't the space be reused, at least after a VACUUM? The issue > > here is not the absolute existence

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Bill Moran
On Wed, 27 Jan 2016 23:54:37 +0100 Ivan Voras wrote: > > I've done my Googling, and it looks like this is a fairly common problem. > In my case, there's a collection of hundreds of databases (10 GB+) with > apps which are pretty much designed (a long time ago) with heavy use of > temp tables - so

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread John R Pierce
On 1/27/2016 2:54 PM, Ivan Voras wrote: For blocking reasons, we'd like to avoid vacuum fulls on these tables (as it seems like touching them will lock up everything else). vacuum full isn't nearly as nasty in 9.x than it was in much older releases, so a lot of the caveats no longer apply. w

Re: [GENERAL] Catalog bloat (again)

2016-01-27 Thread Andrew Sullivan
On Wed, Jan 27, 2016 at 11:54:37PM +0100, Ivan Voras wrote: > So, question #1: WTF? How could this happen, on a regularly vacuumed > system? Shouldn't the space be reused, at least after a VACUUM? The issue > here is not the absolute existence of the bloat space, it's that it's > constantly growing

[GENERAL] Catalog bloat (again)

2016-01-27 Thread Ivan Voras
Hi, I've done my Googling, and it looks like this is a fairly common problem. In my case, there's a collection of hundreds of databases (10 GB+) with apps which are pretty much designed (a long time ago) with heavy use of temp tables - so a non-trivial system. The databases are vacuumed (not-full

Re: [GENERAL] Catalog Bloat

2015-02-02 Thread Jim Nasby
On 1/30/15 11:22 AM, Bill Moran wrote: >Any suggestions on eliminating? Not sure if tools like pg_reorg are >appropriate (or effective) or even vacuum full (yikes). >I'd prefer not to take a complete outage - but I would if this bloat is >really an issue. I don't know the parameters of the syst

Re: [GENERAL] Catalog Bloat

2015-01-30 Thread Bill Moran
On Fri, 30 Jan 2015 09:30:31 -0600 Jeff Amiel wrote: > Probably temp table related ...but catalog bloat on one of my databases > appears to be pretty bad. > > Is the below bloat (table and index) something to worry about? > pg_stat_all_tables show the relations ARE getting successfully vacuumed.

[GENERAL] Catalog Bloat

2015-01-30 Thread Jeff Amiel
Probably temp table related ...but catalog bloat on one of my databases appears to be pretty bad. Is the below bloat (table and index) something to worry about? pg_stat_all_tables show the relations ARE getting successfully vacuumed... Any suggestions on eliminating? Not sure if tools like pg_re

Re: [GENERAL] Catalog Bloat in Development - Frequently dropping/adding schemas and objects

2012-06-29 Thread Ben Madin
David, the VACUUM FULL VERBOSE command might overcome this - I believe it works by effectively doing what you are proposing with a drop database and recreate. It does however lock the tables during the process (not a problem in a dev environ one assumes) but may not be ideal on a live database.

[GENERAL] Catalog Bloat in Development - Frequently dropping/adding schemas and objects

2012-06-29 Thread David Johnston
In my current development environment I often drop some or all of the schemas in the database and then re-create them schemas and the objects they contain. When I go to bring up the database in my GUI it takes a considerable amount of time to initialize. I suspect this is because the catalog tabl