I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk insert (using copy) time was cut from 22 minutes to 2.3 minutes. The deletion time was also improved. I plan to make this bulk update every two hours or every hour.

Although the performance now (after SSD) is compatible with a more frequent update I have read some horror stories about SSD death due to limited NAND endurance combined with write amplification. As SSDs are expensive I would like to push its death as far into the future as possible. Hence my question: What really happens to the disk file in a delete and subsequent vacuum? I guess there are two disk writes, one to mark the row as deleted and the other when vacuuming to mark it as available to overwrite. If instead of deleting and vacuuming I partition the table creating and dropping tables at each bulk insert/delete would I be minimizing the SSD wearing?

  • 1
    the short answer is "yes" Commented Feb 28, 2012 at 16:34
  • 2
    You had reason for concern 3-4 years ago. Most modern SSD drives, even consumer grade models, have write cycle lifetimes that exceed their useful expected life even at fairly high daily write rates. See Debunking SSD lifespan and random write performance concerns
    – dbenhur
    Commented Mar 12, 2012 at 15:09
  • I suggest use partition table, it should be easier when you deleting lots of data.
    – francs
    Commented Mar 22, 2012 at 8:10

1 Answer 1


You can save on the delete and vacuum if you will use smart partitioning! Make sure your design is compatible with dropping partitions instead of actual DELETE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.