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

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