From: PFC <lists@peufeu.com>
To: Adrian Moisey <adrian@careerjunction.co.za>
Cc: pgsql-performance@postgresql.org
Subject: Re: db size
Date: Mon, 14 Apr 2008 11:33:54 +0200
Message-ID: <op.t9lgusticigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <48032237.4040604@careerjunction.co.za>
References: <4802F9E4.3000704@careerjunction.co.za>
<op.t9lf4tvrcigqcu@apollo13.peufeu.com>
<48032237.4040604@careerjunction.co.za>
>
>> If you suspect your tables or indexes are bloated, restore your
>> dump to a test box.
>> Use fsync=off during restore, you don't care about integrity on the
>> test box.
>> This will avoid slowing down your production database.
>> Then look at the size of the restored database.
>> If it is much smaller than your production database, then you have
>> bloat.
>
> I have done that, and I get the following:
>
> the live one is 113G
> the restored one is 78G
Ah.
Good news for you is that you know that you can do something ;)
Now, is the bloat in the tables (which tables ?) or in the indexes (which
indexes ?), or in the toast tables perhaps, or in the system catalogs or
all of the above ? Or perhaps there is a long-forgotten process that got
zombified while holding a huge temp table ? (not very likely, but who
knows).
Use pg_relation_size() and its friends to get an idea of the size of
stuff.
Perhaps you have 1 extremely bloated table or index, or perhaps
everything is bloated.
The solution to your problem depends on which case you have.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, adrian@careerjunction.co.za
Subject: Re: db size
In-Reply-To: <op.t9lgusticigqcu@apollo13.peufeu.com>
* 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