From: | Daniel Farina <daniel(at)heroku(dot)com> |
---|---|
To: | Liron Shiri <lirons(at)checkpoint(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-27 16:42:19 |
Message-ID: | CAAZKuFYXvT4MZvHpfSSuwxFJKw0O+cjQ+Av8vrB4--Su-d2Mzg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nimesh Satam | 2012-08-28 04:33:00 | Vacuum problems with 9.1 |
Previous Message | Albe Laurenz | 2012-08-27 12:47:12 | Re: Execution from java - slow |