Re: V8.4 TOAST table problem

David,

(As a preface, I have already gone forward with completely rebuilding the
database which seems to have finally fixed the problem. Rebuilding the
table itself had no effect, and I couldn't wait much longer to move
forward.)

Yes, this seems similar, however, the key difference being that VACUUM FULL
did not alleviate the problem. The extra "bloated" disk space was still
considered "in use" by the data server, and so it was never returned to the
system. I have a suspicion that the server was storing the table data in
pages in an inefficient manner (by unknown means) because we had roughly
~5x the number of pages used on that TOAST table to store the same number
of tuples compared to other similar databases.

Depending on how often you have to use VACUUM FULL, you might want to
consider tweaking the autovacuum to be more aggressive on that hot table to
keep it in check more often. (Recycling the disk space more efficiently
rather than sending it back to the server only to be reallocated to the
database again.)

On Fri, Jul 12, 2013 at 4:09 AM, David Welton <davidw(at)dedasys(dot)com> wrote:

> Hi,
>
> I have a very similar problem... details below.
>
> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul(dot)tilles(at)noaa(dot)gov> wrote:
> > Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to
> > take advantage of autovacuum features. This server exists in a very
> closed
> > environment (isolated network, limited root privileges; this explains the
> > older software in use) and runs on RHEL5.5 (i686). After the upgrade, the
> > database has constantly been growing to the tune of 5-6 GB a day.
> Normally,
> > the database, as a whole, is ~20GB; currently, it is ~89GB. We have a
> couple
> > other servers which run equivalent databases and actually synchronize the
> > records to each other via a 3rd party application (one I do not have
> access
> > to the inner workings). The other databases are ~20GB as they should be.
>
> Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit
> system:
>
> PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc
> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>
> > Running the following SQL, it's fairly obvious there's an issue with a
> > particular table, and, more specifically, its TOAST table.
>
> Same thing here: we have a table with around 2-3 megs of data that is
> blowing up to *10 gigs*.
>
> > This TOAST table is for a table called "timeseries" which saves large
> > records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the
> records
> > in timeseries yields ~16GB for that column. There should be [b]no
> reason[/b]
> > this table's TOAST table should be as large as it is.
>
> Similar situation: it's a bytea column that gets "a lot" of updates;
> in the order of 10's of thousands a day.
>
> > I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum
> runs
> > to completion with no errors.
>
> VACUUM FULL fixes the problem for us by recouping all the wasted disk
> space. I don't have the knowledge to investigate much further on my
> own, but I'd be happy to try out a few things. The database is,
> unfortunately, sensitive data that I can't share, but I could probably
> script a similar situation...
>
> --
> David N. Welton
>
> http://www.dedasys.com/
>

--
Bradley D. J. McCune