Skip site navigation (1) Skip section navigation (2)

Re: Investigating the reason for a very big TOAST table size

From: Liron Shiri <lirons(at)checkpoint(dot)com>
To: Daniel Farina <daniel(at)heroku(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Investigating the reason for a very big TOAST table size
Date: 2012-08-28 06:24:15
Message-ID: 6B9568EED16BA541BE4A0F3108351E6301BEA8FE1112@il-ex01.ad.checkpoint.com (view raw or flat)
Thread:
Lists: pgsql-performance
There were no "hot standby" configuration, but the DB has start grow fast after restoring from a base backup as described in http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-BASE-BACKUP 

The DB has been growing for a while, and now it seems to become stable after adjusting the autovacuum cost parameters to be more aggressive.

The DB version is 8.3.7.

Do you think it might be the same issue?
What can we do in order to decrease DB size?

-----Original Message-----
From: Daniel Farina [mailto:daniel(at)heroku(dot)com] 
Sent: Monday, August 27, 2012 7:42 PM
To: Liron Shiri
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Investigating the reason for a very big TOAST table size

On Sun, Aug 26, 2012 at 5:46 AM, Liron Shiri <lirons(at)checkpoint(dot)com> wrote:
> Hi,
>
>
>
> We have a table which its TOAST table size is 66 GB, and we believe 
> should be smaller.
>
> The table size is 472 kb. And the table has 4 columns that only one of 
> them should be toasted.
>
>
>
> The table has only 8 dead tuples, so apparently this is not the problem.
>
>
>
> This table contains a column with bytea type data (kept as TOAST). We 
> tried to check what is the size of the toasted data in each row by 
> using the following query (the data_blob is the bytea column):
>
>
>
> SELECT nid, octet_length(data_blob) FROM my_table ORDER BY
> octet_length(data_blob) DESC;
>
>
>
> This result contain 1782 rows. The sizes I get from each row are 
> between
> 35428 to 42084.
>
>
>
> 1782 * 38000 = 67716000 byte = 64.579 MB .
>
>
>
> What can be the reason for a table size of 66 GB? What else should I check?

Is the size of the database continuing to grow over time, or is it stable?

Have you done a hot-standby promotion on this database, perchance?  I have an open bug report on an unusual situation that began after that:
http://archives.postgresql.org/pgsql-bugs/2012-08/msg00108.php


--
fdr

Scanned by Check Point Total Security Gateway.


In response to

Responses

pgsql-performance by date

Next:From: Pavan DeolaseeDate: 2012-08-28 06:33:13
Subject: Re: Vacuum problems with 9.1
Previous:From: Nimesh SatamDate: 2012-08-28 04:33:00
Subject: Vacuum problems with 9.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group