Deletes slow down queries

[jenellis]

Hi! Thanks for the forums site!

We are having an issue with deletes. We need to delete a lot of data from tables once a week. It seems like the more we delete, queries against these tables get slower and slower. Is it better to truncate a table and reload only the data we want?

Thank for any help on this.

-J

[NorbertKrupa]

If you don't know already, when a row is deleted in Vertica, it isn't actually removed from physical storage (it's marked for deletion until the epoch advances). These operations take place in WOS and overflow to ROS if they do not fit in memory. If possible, the best practice is to use partitions and drop the entire partition. In your case, you may be able to partition on week and drop the week's partition.

Here are some resources:

• Optimizing DELETEs and UPDATEs for Performance
• Bulk Deleting and Purging Data
• Best Practices for DELETE and UPDATE

[jenellis]

Thanks for the valuable links!