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
Deletes slow down queries
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Deletes slow down queries
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:
Here are some resources:
Checkout vertica.tips for more Vertica resources.
Re: Deletes slow down queries
Thanks for the valuable links!