Re: Database takes up MUCH more disk space than it should

On 2012-Jan-21, at 6:39 PM, Scott Marlowe wrote:

> On Sat, Jan 21, 2012 at 1:37 AM, Dan Charrois <dan001@syz.com> wrote:
>> Hi everyone.  I'm currently in the situation of administering a rather large PostgreSQL database which for some
reasonseems to be even much larger than it should be. 
>>
>> I'm currently running version 8.4.5 - not the latest and greatest, I know - but this is a live database that would
problematicto take down to upgrade unless all else fails - especially considering its size if it does need to be
rebuiltsomehow. 
>>
>> Anyway, I'm no stranger to SQL, but new to PostgreSQL - all my SQL administration in the past has been with MySQL.
SoI'm somewhat bumbling my way through administrative commands trying to solve this - please bear with me. 
>>
>> The size of the tables reported by \dt+ add up to around 120 GB.  The size of the indexes reported with \di+ adds up
toaround 15 GB.  This is pretty consistent with what I would expect the data to require. 
>>
>> The problem is, the disk usage of the pgsql directory where the data is kept (as reported by 'du') comes to 647 GB -
significantlymore than it should.  select pg_database_size('mydatabase') confirms this, returning 690830939920. 
>>
>> Vacuuming the tables (full and otherwise) hasn't helped, but then considering how the database is used, I didn't
reallyexpect it to.  It's strictly a read-only database, with the exception of once a month when it is refreshed by
loadingnew data into newly created tables, and once that is done, vacuum analyzing the new tables, dropping the old
tables,then renaming the new ones to have the name of the old ones.  Vacuums never claim to recover any space, and the
diskusage stays the same. 
>>
>> So how do I find out what's eating up all this extra space?
>
> Real quick, if you run pg_database_size(name) for each db, including
> template1 and postgres, what do you get back?


Thanks for your reply, Scott.

My database: 697490323216
postgres: 5537796
template0: 5537796
template1: 5537796

pg_database_size(mydatabase) did return a value consistent with disk usage.  But that value didn't jive with what I
expectedfrom summing up the sizes I got from \dt+ 

It looks like a large TOAST table, not reported by \dt+ was the biggest culprit, but I thought it was orphaned.  Due to
somehelp by Adrian Klaver, it looks like I was mistaken - it was in fact used by one of my tables.  So it looks like
therewasn't really a problem at all - other than my data still taking up a lot more physical storage than I thought it
did.I don't think there's much I can do about it, but that's much better than assuming it was related to a problem that
neededfixing. 

Thanks again!

Dan
--
Syzygy Research & Technology
Box 83, Legal, AB  T0G 1L0 Canada
Phone: 780-961-2213