How to safely drop / delete a table?

Question

I need to drop a table but I want to be 100% sure the table is unused first. How can I do so with complete certainty?

I've already:

• Made sure there are no references to the table in the codebase
• Dropped the table in the staging environment over a week ago
• Renamed the table in production (I appended _to_delete at the end) over a week ago
• Asked other engineers if table is needed

I suppose I can revoke permissions to the table from the application database user as a next step. What I would love is to be able to record table access to know for sure that table is not being referenced, but I wasn't able to find a way to do that over a specific timeframe.

And yes, I'm realize I'm being a bit paranoid (I could always restore the table from backup if it turns out it's needed) but I'm not a DBA so I'd prefer to be extra cautious.

Answer 1

Paranoia is a virtue for a database administrator.

Revoking permissions seems like a good way to proceed.

To check if the table is used, observe the seq_scan and idx_scan columns of the pg_stat_user_tables entry for the table. If these values don't change, the table is not accessed. These values are not 100% accurate, since statistics are deliberately sent via a UDP socket, but if the numbers don't change at all, you can be pretty certain that the table is unused.

Answer 2

Create a backup of the table and then drop the table, if application breaks then you always have the option to re-create it with the backup table.