Re: Yet another "drop table vs delete" question

On Apr 22, 2009, at 8:04 AM, Thomas Finneid wrote:

> Alvaro Herrera wrote:
>> Try TRUNCATE.  That leaves the less garbage behind and takes the less
>> time.
>
> A follow up question, as far as I understand it, delete removes
> entries in the fsm, so vacuum has to clean it all up when performing
> a delete, is this approximately correct? what happens with truncate?
> does it remove everything so that vacuum has almost no work to do or
> is it approximately as much work either way?

No, DELETE doesn't do anything with the FSM.  It simply marks the
tuple in the page as deleted (not visible) to transactions with
transaction ids larger than the DELETE transaction's commit id
(assuming it commits).  So, once all transactions with transaction ids
lower than that DELETE transaction's commit id it can be considered
dead since nothing can see it anymore.  VACUUM looks for those dead
tuples and adds them to the FSM.  INSERTs and UPDATEs (and COPYs) then
look to the free space map for a dead tuple first when space is needed
for a new tuple before allocating space in hopes of avoiding that
space allocation by reusing the dead tuple's space.

You're pretty much on with regards to TRUNCATE as it deletes the pages
that are allocated to the table (I'm not sure if the TRUNCATE handles
clearing out the FSM entries for that table or if VACUUM does when the
table is next vacuum'd).

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k