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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

=?iso-8859-1?q?Lawrence=20Smith?= <ls_services_eire (AT) yahoo (DOT) ie> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match