Thread: Q: Reclaiming deleted space in data files
If I insert a large amount of data into a Postgres table, so that the data file (or files) grow by a large amount (something in the gigabyte range), and I subsequently delete the table containing this data (or delete the data from the table), can I reclaim the space without major hassle? I'm guessing I can with the VACUUM command. Are there any other mechanisms for this? How "long" would it take, i.e. if I had deleted a gigabyte of data, is this vacuum process going to be something which happens quickly, i.e. a matter of minutes, or is it the kind of thing which will thrash for days and block the DB? (I appreciate the precise timing will depend on the size and structure of the DB, hardware etc.). Please understand I'm not intending to do this kind of thing often, but I have gotten into this problem with my current DB setup and it's given me the impetus to look for possible alternatives. Thx Lawrence Smith ===== http://www.explanation-guide.info ________________________________________________________________________ BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk
=?iso-8859-1?q?Lawrence=20Smith?= <ls_services_eire@yahoo.ie> writes:
> If I insert a large amount of data into a Postgres
> table, so that the data file (or files) grow by a
> large amount (something in the gigabyte range), and I
> subsequently delete the table containing this data (or
> delete the data from the table), can I reclaim the
> space without major hassle?
Well, DROP TABLE reclaims the space immediately, so that case isn't very
interesting. If you're talking about deleting *all* the rows in a table
but keeping the table, TRUNCATE is your best option. If you've updated
or deleted lots of rows but don't want to lose what remains, your
options are VACUUM FULL or CLUSTER. VACUUM FULL will be faster if only
a relatively small part of the data needs to be moved to perform the
compaction. CLUSTER is probably faster if a large amount of
rearrangement will be needed (and as a bonus you can speed up retrievals
on whichever index you pick to cluster by). Either one is likely to
take a while if there are gigabytes worth of data still in the table.
Note that a plain VACUUM will release wholly-empty pages at the end of
the table, but under most scenarios that doesn't translate into giving a
lot of space back to the system. VACUUM FULL actively moves rows down
into free space in earlier pages so as to create empty pages at the end,
which it can then release.
regards, tom lane
Tom, many thanks for the quick and informative answer, you guys are amazing. I've done a test Postgres install and I see the data dirs are divided up into individual files (rather than one monolithic file like my current system). I'll take a closer look at Posgtres, the projects still at a stage where I can risk a change. Once again, thanks. Lawrence Smith ===== http://www.explanation-guide.info ________________________________________________________________________ BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk