Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it? - Mailing list pgsql-general

If I didn't change the data type to charcater varying vaccuming the database doesn't reduce the database size



.::.
Sent from my BlackBerry®
powered by The ESQ Way 165

-----Original Message-----
From: Alban Hertroys <haramrae@gmail.com>
Date: Mon, 12 Mar 2012 16:43:49 
To: <hidayat365@gmail.com>
Cc: John R Pierce<pierce@hogranch.com>; <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave.
 How to compact it?

On 12 March 2012 09:20, Nur Hidayat <hidayat365@gmail.com> wrote:
> FYI, after I changed text field into character varying, I vaccuum the whole database, resulting in much smaller
databasesize
 

What I think that happened in your case is that because of the
data-type change every row in the table got rewritten to a new version
where said column was of the new type. The subsequent vacuum then
removed the old (bloated) rows with the old type from the database
file.

And thus you ended up with a clean table.

> -----Original Message-----
> From: "Nur Hidayat" <hidayat365@gmail.com>
> Date: Mon, 12 Mar 2012 08:18:09
> To: John R Pierce<pierce@hogranch.com>; <pgsql-general@postgresql.org>
> Reply-To: hidayat365@gmail.com
> Subject: Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
>
> Yes, I am aware of that, but that's the fact I'm facing
> Right now I'am happy enough my system runs well without eating up my drive :)
> I'll investigate more later when time available :)
>
> Cheers,
> Nur Hidayat
>
>
>
> .::.
> Sent from my BlackBerry®
> powered by The ESQ Way 165
>
> -----Original Message-----
> From: John R Pierce <pierce@hogranch.com>
> Sender: pgsql-general-owner@postgresql.orgDate: Mon, 12 Mar 2012 00:39:28
> To: <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave.
>  How to compact it?
>
> On 03/12/12 12:06 AM, Nur Hidayat wrote:
>>
>> I once have the same problem. In my case it's because most of my table
>> using text datatype.
>> When I change the field type to character varying (1000) database size
>> reduced significantly
>>
>> Unfortunately, I haven't investigate more, but it looks like how
>> postgres stores data
>
> that doesn't make any sense.   text and character varying storage is
> exactly hte same, the only difference is the varchar has an optional
> length constraint
>
>
>
> --
> john r pierce                            N 37, W 122
> santa cruz ca                         mid-left coast
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.