A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Tue, Mar 6, 2012 at 7:05 PM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

 We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x

 The origin database "data/base" directory is 197 GB in size.

 The slave database "data/base" directory is 562 GB in size and is
 over 75% filesystem utilization which has set off the "disk free" siren.

 My biggest table* measures 154 GB on the origin, and 533 GB on
 the slave.  (*As reported by

 SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid))
  As "Size" from pg_catalog.pg_statio_user_tables
  ORDER BY pg_total_relation_size(relid) DESC;
 )

I ran VACUUM FULL on this table, but it is still over 500 GB in size.
And growing...
I'm up to 77% utilization on the filesystem.

"check_postgres --action=bloat" now returns OK. So it's not bloat.
What else could it be?

Best,
Aleksey

On 03/07/2012 06:27 PM, Aleksey Tsalolikhin wrote:

SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid))
As "Size" from pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
)

I ran VACUUM FULL on this table, but it is still over 500 GB in size.
And growing...
I'm up to 77% utilization on the filesystem.

"check_postgres --action=bloat" now returns OK. So it's not bloat.
What else could it be?

Try disabling replication on that table and clustering the table and
then re-enabling replication. I would have to double check but I think
check_postgres --action=bloat only checks for dead space, not usable
space, so you could actually still have bloat, just bloat that is usable.

Alternatively you could disable replication on that table, truncate the
table, and then re-enable replication for that table. A concern would be
is that it is a large table regardless, which means you are going to
hold open a transaction to refill it.

JD

Best,
Aleksey

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

Dear Joshua,

You wrote:

Try disabling replication on that table and clustering the table and then re-enabling
replication.

...

Alternatively you could disable replication on that table, truncate the table, and then
re-enable replication for that table. A concern would be is that it is a large table
regardless, which means you are going to hold open a transaction to refill it.

I don't see any way to disable replication on a table in Slony. I do
see I can remove
a table from the replication set, and then add it back in. Is that
what you meant, or
am I missing something?

I ask because I know when a table is added to a replication set, it is
copied over in
full from origin to slave, and since this table is huge, I'll need to
schedule a maintenance
window to minimize impact on production.

Yours truly,
Aleksey

On Wed, Mar 7, 2012 at 8:12 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

"check_postgres --action=bloat" returns OK [after VACUUM FULL].  So it's not bloat.
What else could it be?

I would have to double check but I think
check_postgres --action=bloat only checks for dead space, not usable space,
so you could actually still have bloat, just bloat that is usable.

This is how check_postgres checks. How would I check for usable
bloat, to confirm
that that's what I am running into? What is usable bloat, anyway?
(Is there some write-up
on it?)

SELECT
current_database() AS db, schemaname, tablename,
reltuples::bigint AS tups, relpages::bigint AS pages, otta,
ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR
sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS
tbloat,
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint -
otta END AS wastedpages,
CASE WHEN relpages < otta THEN 0 ELSE
bs*(sml.relpages-otta)::bigint END AS wastedbytes,
CASE WHEN relpages < otta THEN '0 bytes'::text ELSE
(bs*(relpages-otta))::bigint || ' bytes' END AS wastedsize,
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0
ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta
END AS wastedipages,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END
AS wastedibytes,
CASE WHEN ipages < iotta THEN '0 bytes' ELSE
(bs*(ipages-iotta))::bigint || ' bytes' END AS wastedisize,
CASE WHEN relpages < otta THEN
CASE WHEN ipages < iotta THEN 0 ELSE ipages-iotta::bigint END
ELSE CASE WHEN ipages < iotta THEN relpages-otta::bigint
ELSE relpages-otta::bigint + ipages-iotta::bigint END
END AS totalwastedbytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma
END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname,
COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS
ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0)
AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE
hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma
ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM
'#"[0-9]+.[0-9]+#"%' for '#')
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE
4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND
nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
WHERE sml.relpages - otta > 0 OR ipages - iotta > 10 ORDER BY
totalwastedbytes DESC LIMIT 10

Yours,
Aleksey

On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

 We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x

 The origin database "data/base" directory is 197 GB in size.

 The slave database "data/base" directory is 562 GB in size and is
 over 75% filesystem utilization which has set off the "disk free" siren.

 My biggest table* measures 154 GB on the origin, and 533 GB on
 the slave.  (*As reported by

 SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid))
  As "Size" from pg_catalog.pg_statio_user_tables
  ORDER BY pg_total_relation_size(relid) DESC;
 )

 I took a peek at this table on the slave using pgadmin3. The table
 has auto-vacuum enabled, and TOAST autovacuum enabled.

 There are 8.6 million live tuples, and 1.5 million dead tuples.

 Last autovacuum was over a month ago.

 Last autoanalyze was 3 hours ago.

 Table size is 4 Gigs, and TOAST table size is 527 Gigs.
 Indexes size is 3 Gigs.

 Autovacuum threshold is 20%, and the table is just under that threshold.

 I ran vacuum analyze verbose.  But the filesystem is still at 76%
utilization.
 In fact, now, the "data/base" directory has grown to 565 GB.

 Why is my slave bigger than my master?  How can I compact it, please?

Do you have a long running transaction on the slave? vacuum will not
reuse space that was freed after the longest running transaction.

You need to use the CLUSTER command to compact it, or VACUUM FULL
followed by a REINDEX if you don't have enough disk space to run
CLUSTER. And neither of these will do anything if the space is still
live because some old transaction might still need to access the old
tuples.

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

 We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x

 My biggest table measures 154 GB on the origin, and 533 GB on
 the slave.

 Why is my slave bigger than my master?  How can I compact it, please?

On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop
<stuart@stuartbishop.net> wrote back:

Do you have a long running transaction on the slave? vacuum will not
reuse space that was freed after the longest running transaction.

You need to use the CLUSTER command to compact it, or VACUUM FULL
followed by a REINDEX if you don't have enough disk space to run
CLUSTER. And neither of these will do anything if the space is still
live because some old transaction might still need to access the old
tuples.

Dear Stuart,

We do not run any transactions on the slave besides we pg_dump the
entire database every 3 hours. I don't have enough disk space to CLUSTER
the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX
TABLE.

I'd love to get some insight into how much logical data I have versus how
much physical space it is taking up. Is there some admin tool or command
or query that will report that? For each table (and index), I'd like
to know how
much data is in that object (logical data size) and how much space it is taking
up on disk (physical data size).

Yours,
Aleksey

On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

 We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x

 My biggest table measures 154 GB on the origin, and 533 GB on
 the slave.

 Why is my slave bigger than my master?  How can I compact it, please?

On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop
<stuart@stuartbishop.net> wrote back:

Do you have a long running transaction on the slave? vacuum will not
reuse space that was freed after the longest running transaction.

You need to use the CLUSTER command to compact it, or VACUUM FULL
followed by a REINDEX if you don't have enough disk space to run
CLUSTER. And neither of these will do anything if the space is still
live because some old transaction might still need to access the old
tuples.

Dear Stuart,

 We do not run any transactions on the slave besides we pg_dump the
entire database every 3 hours.  I don't have enough disk space to CLUSTER
the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX
TABLE.

 I'd love to get some insight into how much logical data I have versus how
much physical space it is taking up.  Is there some admin tool or command
or query that will report that?  For each table (and index), I'd like
to know how
much data is in that object (logical data size) and how much space it is taking
up on disk (physical data size).

Do you do things like truncate on the master? Cause truncates don't
get replicated in slony.

On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

 We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x

 My biggest table measures 154 GB on the origin, and 533 GB on
 the slave.

 Why is my slave bigger than my master?  How can I compact it, please?

On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop
<stuart@stuartbishop.net> wrote back:

Do you have a long running transaction on the slave? vacuum will not
reuse space that was freed after the longest running transaction.

You need to use the CLUSTER command to compact it, or VACUUM FULL
followed by a REINDEX if you don't have enough disk space to run
CLUSTER. And neither of these will do anything if the space is still
live because some old transaction might still need to access the old
tuples.

Dear Stuart,

 We do not run any transactions on the slave besides we pg_dump the
entire database every 3 hours.  I don't have enough disk space to CLUSTER
the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX
TABLE.

 I'd love to get some insight into how much logical data I have versus how
much physical space it is taking up.  Is there some admin tool or command
or query that will report that?  For each table (and index), I'd like
to know how
much data is in that object (logical data size) and how much space it is taking
up on disk (physical data size).

On Thu, Mar 8, 2012 at 11:58 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Do you do things like truncate on the master?  Cause truncates don't
get replicated in slony.

Dear Scott,

No, we do not truncate this table on the master. We only add to it.

The REINDEX FULL completed and the table is still swollen.

Yours,
Aleksey

On Thu, Mar 8, 2012 at 1:10 PM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:

 We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x

 My biggest table measures 154 GB on the origin, and 533 GB on
 the slave.

 Why is my slave bigger than my master?  How can I compact it, please?

On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop
<stuart@stuartbishop.net> wrote back:

Do you have a long running transaction on the slave? vacuum will not
reuse space that was freed after the longest running transaction.

You need to use the CLUSTER command to compact it, or VACUUM FULL
followed by a REINDEX if you don't have enough disk space to run
CLUSTER. And neither of these will do anything if the space is still
live because some old transaction might still need to access the old
tuples.

Dear Stuart,

 We do not run any transactions on the slave besides we pg_dump the
entire database every 3 hours.  I don't have enough disk space to CLUSTER
the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX
TABLE.

 I'd love to get some insight into how much logical data I have versus how
much physical space it is taking up.  Is there some admin tool or command
or query that will report that?  For each table (and index), I'd like
to know how
much data is in that object (logical data size) and how much space it is taking
up on disk (physical data size).

On Thu, Mar 8, 2012 at 11:58 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Do you do things like truncate on the master?  Cause truncates don't
get replicated in slony.

Dear Scott,

 No, we do not truncate this table on the master.  We only add to it.

 The REINDEX FULL completed and the table is still swollen.

If you pg_dump -t tablename from each machine, are the backups about
the same size?

Dear Scott,

When I pg_dump -t bigtablename on the Slony slave, the dump file is
212G in size.

I am unable to perform the same test on the master until I get a
maintenance window, which may not be for a few weeks, as it does
impact our production system when we dump from the master (the web app
gets noticeably slower).

I compare this 212 GB size (logical size) with the 550 GB reported
size (physical size on disk), which corresponds to / aligns with "du
-sh" output, 550 GB.

I remember now I had a similar issue about a year and a half or so,
when we had a jump in database size, also with replication involved,
and one site became bigger than the other. I talked to Bruce M. about
it at some conference and he suggested looking at his site, where he
has explanation of how Postgres stores data, low-level, like structure
of a page and so on. Unfortunately I was unable to carve out the time
to drill into it then, just continued running with the larger database
size... so now this issue is coming back to haunt me, even bigger
now.

The size had doubled earlier, and now it has nearly tripled.

I'm afraid the easiest (quickest) solution will be for me to destroy
the RAID 1E array and rebuild it as a RAID 5 array, which would give
me a bigger filesystem, buying me time to study up on what Bruce
suggested, or else to hire a professional Postgres consultant (if
$WORK ever coughs up the money).

Our resident Oracle DBA expert (since we don't have a Postgres one)
suggested I try truncating the table on the slave (with replication
down) and then restoring it from this pg_dump, just to see if the size
of the new table will be 200 GB or 500 GB. If 200, we're home free;
if 500, we need to continue to investigate.

In the meantime, I owe you the size of the bigtable from
production... if anybody has any other suggestions, I am all ears.

Yours very truly,
Aleksey

Hai Aleksey,

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

Regards,
Nur Hidayat

On Mon, Mar 12, 2012 at 1:32 PM, Aleksey Tsalolikhin <
atsaloli.tech@gmail.com> wrote:

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

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

FYI, after I changed text field into character varying, I vaccuum the whole database, resulting in much smaller database size

Cheers,
NH

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

-----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

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 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@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.

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 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@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.

On 03/12/12 2:28 PM, Nur Hidayat wrote:

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

changing the data type required every tuple to get rewritten. a
vacuum full, or a cluster likely would have done the same or better
reduction in size..

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Mon, Mar 12, 2012 at 3:06 AM, Nur Hidayat <hidayat365@gmail.com> 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

I'll bet what happened was postgres re-wrote your table for you,
effectively doing a compaction. You can get similar effect by doing
an alter table and "changing" an INTEGER field to be INTEGER.
Postgres does not optimize that do a no-op, so you get the re-writing
effect.

On Tue, Mar 13, 2012 at 5:14 AM, Vick Khera <vivek@khera.org> wrote:

I'll bet what happened was postgres re-wrote your table for you,
effectively doing a compaction.  You can get similar effect by doing
an alter table and "changing" an INTEGER field to be INTEGER.
Postgres does not optimize that do a no-op, so you get the re-writing
effect.

How does table rewriting work? Does it happen a row at a time or all at once?

In other words, how much free disk space is needed on an 800 TB filesystem
to rewrite a 550 TB table? (Have I got enough space?)

Aleksey