pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Gregory Maxwell <gmaxwell@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Bloated pg_shdepend_depender_index
Date: Thu, 23 Mar 2006 22:31:05 -0400
Message-ID: <20060324023105.GB11242@surnet.cl> (raw)
In-Reply-To: <e692861c0603231716j63d712eemfe7292ff968801a9@mail.gmail.com>
References: <e692861c0603231716j63d712eemfe7292ff968801a9@mail.gmail.com>

Gregory Maxwell wrote:

> I recently noticed that this database has grown to a huge size. ...
> Which I found to be somewhat odd because none of the tables have more
> than around 1000 rows.   I hadn't been vacuuming because I didn't
> think that anything would ever be deleted.... so I performed a vacuum
> full... but no luck, it was still about 6.4GB.
> 
> With some help of the folks on IRC I discovered...
> postgres=#  select relname, pg_relation_size(oid) FROM pg_class ORDER
> BY 2 DESC LIMIT 2;
>            relname           | pg_relation_size
> -----------------------------+------------------
>  pg_shdepend_depender_index  |        159465472
>  pg_shdepend_reference_index |         97271808
> (2 rows)

Huh, that's very strange.

The pg_shdepend table is a shared catalog, unlike most other catalogs.
Still I don't see why would the indexes not shrink.  Do pg_authid,
pg_database, or pg_tablespace show a similar behavior?  Is amarok
creating a lot of temp tables or something?  When you did that vacuum
full, was there a process connected to the same or another database that
could be holding onto the pg_shdepend table?

I'll have a look at the code, but I thought I might throw that out, just
in case it rings a bell.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



view thread (26+ messages)  latest in thread

Message-ID: <20060324023105.GB11242@surnet.cl>
Permalink:  ../20060324023105.GB11242@surnet.cl/
Also on:    postgresql.org/message-id/20060324023105.GB11242@surnet.cl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: alvherre@commandprompt.com, gmaxwell@gmail.com
  Subject: Re: Bloated pg_shdepend_depender_index
  In-Reply-To: <20060324023105.GB11242@surnet.cl>
  References: <e692861c0603231716j63d712eemfe7292ff968801a9@mail.gmail.com> <20060324023105.GB11242@surnet.cl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox