From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | hidayat365(at)gmail(dot)com |
Cc: | John R Pierce <pierce(at)hogranch(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it? |
Date: | 2012-03-12 15:43:49 |
Message-ID: | CAF-3MvO2BxT_CRvUqxNmtzjWfKyCCYmW0v57qhMZ+bXBpGbDoA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12 March 2012 09:20, Nur Hidayat <hidayat365(at)gmail(dot)com> wrote:
> FYI, after I changed text field into character varying, I vaccuum the whole database, resulting in much smaller database size
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(at)gmail(dot)com>
> Date: Mon, 12 Mar 2012 08:18:09
> To: John R Pierce<pierce(at)hogranch(dot)com>; <pgsql-general(at)postgresql(dot)org>
> Reply-To: hidayat365(at)gmail(dot)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(at)hogranch(dot)com>
> Sender: pgsql-general-owner(at)postgresql(dot)orgDate: Mon, 12 Mar 2012 00:39:28
> To: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2012-03-12 16:16:40 | fsync default setting and version |
Previous Message | PgSQL | 2012-03-12 14:17:33 | Re: Error installing postgresq91-python package |