Temporary Tables
Temporary Tables still there 5 years later.
Why your junior dev’s temporary table is still there 5 years later
We’ve all seen it: a table named tmp_orders_2020, test_customers_backup, or temp_report_john, created just for a quick test, but still sitting in production years later, full of outdated data, wasting space and confusing everyone.
In my 35 years in banking IT, these temporary tables were a running joke… until they caused real problems.
Let’s talk about why this happens, the hidden costs, and how to stop it.
The story we all know
A junior developer (or even a senior in a hurry) needs to test a query or fix a report.
They create a table:
CREATE TABLE tmp_customer_report AS
SELECT * FROM customers WHERE signup_date > ‘2023-01-01’;
It’s just temporary, I’ll delete it tomorrow.
Tomorrow becomes next week… next month… next year.
Five years later, the table is still there, with millions of rows, no indexes, and duplicated data.
Why Temporary Tables stick around
Forgotten in the rush: deadlines hit, the dev moves on.
It might be useful later: classic hoarding mindset.
No one knows who owns it: naming like tmp_xyz makes ownership unclear.
Fear of breaking something: what if another process uses it?
No cleanup process: production databases rarely have automatic temp table purging.
In banking systems, I’ve seen temp tables from 10+ years ago still taking up gigabytes.
The hidden costs
Wasted storage: especially bad on expensive production servers.
Slower backups: bigger database = longer backup times.
Confusion: new devs waste time investigating mystery tables.
Performance drag: unindexed temp tables in queries slow things down.
Audit/compliance risk: old data hanging around when it shouldn’t.
How to prevent Temporary Tables forever
Naming Convention
Force a strict rule:
– All temp/test tables must start with tmp_ + date + name
– Example: tmp_20251228_henny_orders
-Makes it obvious they’re temporary and who created them.
Documentation
– Require a comment or ticket number:
– COMMENT ON TABLE tmp_20251228_henny_orders IS Quick test for report #12345 – delete by 2026-01-04;
Time-Based cleanup script
– Run monthly:
– Drop tables older than 30 days starting with tmp_
– DROP TABLE IF EXISTS tmp_… ; — Use dynamic SQL or script
Use actual Temporary Tables
– Real temp tables disappear automatically:
– CREATE TEMP TABLE temp_report AS …
– Gone when session ends
Code Review rule
– No permanent table with tmp, test, backup in name allowed in production.
My Banking rule
No table with tmp or test in the name ever made it to production.
We had a monthly temp table hunt, anything older than 30 days got dropped after warning the creator.
Saved gigabytes and countless headaches.
Final thought
Temporary tables are useful, but only if they’re truly temporary.
Set rules early. Enforce them gently but firmly.
Your future self (and the next dev) will thank you.
Have you inherited ancient temp tables? What’s the oldest one you’ve found? Share in the comments!
This brings us to the end of my post on Temporary Tables.
Thank you for taking the time to read my post on Temporary Tables.
I hope you found it enjoyable and insightful.
Stay tuned for more content that is coming soon.
If you like what you read, please consider sharing it with others who might find it helpful.
Contact me
If you have any questions or want to contact me, please drop me an email at info@safecomputer.org
Stay updated with my monthly newsletter
Subscribe to Safe Computer’s monthly newsletter in the right sidebar for tips on job applications, cybersecurity, and more! Get summaries of my latest posts, like this Database Crimes, straight to your inbox. Join now at safecomputer.org!
Disclaimer
All tips and methods mentioned in this blog are tested on Windows 11. Please note that results may vary on other operating systems or versions of Windows. Adapt the instructions accordingly.
Copyright
© 2025 Henny Staas/safecomputer.org. Unauthorized use and/or duplication of this material without express and written permission from this site’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Henny Staas/safecomputer.org with appropriate and specific direction to the original content.