Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@xxxxxxxx> 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
