Design a Strategy for Removing Unwanted Data
Good design factors in the overall lifecycle of the database. Databases grow over time, and it is critical to have a strategy to deal with this. For every piece of data you need to determine how long you are going to keep it. Once you determine the lifespawn of the data you will have to put in place a means of removing it. It is a sad fact that this is often neglected until a problem arises that demands it be dealt with.
There are two basic types of data we talk about when we are speaking about retention: transient, or persistent.
Any data which is only necessary for a short period of time is considered transient data. These tables will typically have a large number of inserts that are only valid for a narrow window such as session tables. Query performance will degrade over time if you fail to prune this table regulaly. It is not uncommon for the developer to write logic which will invalidate a session after a specific period of time, but neglect to delete it. This is a mistake seasoned developers have made, so be sure to take it seriously.
Strategies for managing Transient Data
- Delete all sessions that have expired
- Delete all sessions that are older than one hour
These deletes can either be done from a separate utility, or embedded inside of the application.
Any data which you intend to keep for a long period of time is considered persistent data. This can range from access logs to customer records, and retention policies for these can very greatly.
How long data should be kept is based on what type of data it is. Transient data should be purged quickly. For example session tables should be purged shortly after the sessions expire. Failure to prune session tables has resulted in several applications grinding to a halt as they grow to massive proportions and bog down the database server. Design this in from the start and save many headaches.
In addition to tranient data, your database will also contain persistent data. Establishing the appropriate duration for keeping this data requires several steps. The first step is to understand the retention policies that could apply to this data. If the data you are storing is a log and your company requires you to keep logs of this type for 6 months, than you know how long to keep it. Some policies mandate keeping data a certain amount of time, others require destoying data data older than a specified time. Lacking clear external direction for this you will need to make a judgement call. However you arrive at this number it is safe to assume it will change in the future, so allow this to be adjusted in the future.
Even data that seems like it should be permanent should have a strategy for removal. If you have a database that tracks your customers you obviously don't want to expire current customers. You do however want to purge out former customers at some point.
Once you determine how long you are going to keep data you need to determine a strategy for getting rid of the data you don't want. In many cases the data should simply be deleted, but depending on your environment you may want to archive it so it could be retrieved later at need.
Notes about deletes not shrinking data size here.