Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 24 '12 at 21:54

This question came from our site for professional and enthusiast programmers.

1  
the short answer is "yes" –  Jack Douglas Feb 28 '12 at 16:34
1  
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 Mar 12 '12 at 15:09
    
I suggest use partition table, it should be easier when you deleting lots of data. –  francs Mar 22 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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