On 01/22/12 12:32 AM, Dan Charrois wrote:
It looks like a large TOAST table, not reported by \dt+ was the biggest culprit, but I thought it was orphaned. Due to some help by Adrian Klaver, it looks like I was mistaken - it was in fact used by one of my tables. So it looks like there wasn'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 needed fixing.
well, there's a good possibiltiy that clustering that table will cause a big chunk of that space to be freed up. first, make sure you have no old '<IDLE> in transasction' processes, then run CLUSTER table USING indexname; (probably the primary key, unless there's another index thats used more frequently). this is a high overhead operation that will copy all the data in that table to new disk space, then delete the old files, ordering it by the specified key, it will then rebuild all indexes. naturally, there's a global lock on this table for the duration, so this should be done during a maintenance window with your application(s) shut down.
-- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
