How to safely drop a deprecated database table

#database #deprecation

Stop! Do not immediately drop a table because it appears unused or deprecated!

I have a safer process for dropping deprecated tables that is less dangerous and mitigates issues! Dropping a table can be very dangerous as you could lose all the historical data of that table if you don't have a backup. Also, you could be shooting yourself in the foot; if the table is still being used somewhere in your application, this undoubtedly introduces bugs. The later is more likely to happen if your application is not well tested. And most codebases that I've encountered are not well tested, so this process is likely applicable to you.

Developers, including myself occasionally, have a terrible tendency to trust their memory and expertise of the application's internal workings. Just because you have a firm grasp of the application internals, you shouldn't just pull the trigger and drop the table. We are all human, we can forget and make mistakes. Do not rely on your memory instead rely on a process.

Here is the process that I follow and highly recommend when dropping a deprecated table.

The Steps

Step 1 - Determine if this table is indeed 'deprecated' and no other team is using it.

You must be sure that the table in question is not being used anymore by anyone! Products like [Looker] have given non-technical people access to your database tables. The external team now has the power to analyze data and act on them. They might rely on it to perform their operations. You should reach out to other teams that you believe could be using it and get their consent on the deprecation process. If someone is relying on it, then stop and rethink the deprecation. Otherwise, let's move onto the next step.

Step 2 - Change the database table's name.

Rename the table's name to deprecated_{date}_tablename to see if you missed any issues referencing to that table. Bug reports (I recommend having a bug monitoring system in place first) will inform you of these issues. No worries. We can rollback to change the name back and handle the damage control. The damage incurred would be significantly less severe than actually dropping the table. Rollback and then start again from step (1).

Step 3 - Let the renamed deprecated table 'marinate' in production.

You'll need to let the renamed deprecated table stay on production for enough time to indicate that it is safe to move on. Sometimes you'll have code that references to the table that doesn't execute very often. In this step, you'll need to decide how long is long enough. You can think about your applications usage profile to better help you decide. For instance, if you are a point of sales system that generates weekly reports to your clients then you might want to wait a week. This time can vary between different use cases, so I'd suggest using your best judgment here.

Step 4 - Drop the table! Boom!

Now that we've gone through the precautionary steps 1, 2, and 3. You can drop that renamed table! Let's help that database shed some weight~

Summary

There you have it! These are the steps that I take when I am deprecating a database table (BTW also works for columns too). This process has saved me from headaches on numerous occasions. I had halted deprecation of a table after I discovered it was being used heavily by another team. In another case, I found that I didn't remove all references to the table and rolled back smoothly so that I could address them.

The best thing about having steps and a process is that you can rely on and trust in the process. It is too much for a single developer to accurately memorize every detail of a niche part of your much broader application.

Even if you choose not to use these steps, I still urge you to take extra precautions when dropping a database table for the sake of yourself and the other teams that rely on the database.