Implementing Soft Deletes in SQL Databases
Cover photo by Taylor Vick on Unsplash
Hard deletes are the default, and honestly, they are usually fine. If you don’t need a record anymore, nuking it from the table is clean and keeps your indexes fast. But sometimes, you need a safety net.
Why not just use a delete flag?
“But Namir, isn’t adding a deleted_at column just cluttering up my schema with junk data?”
You’re right that it adds complexity. You have to filter for nulls on every single query. It is a pain. However, the alternative is losing data that users or legal teams might demand later. If I have to choose between a slightly slower index and a panic-induced restore from a three-day-old backup, I’ll take the slow index every time.
How to do it right
Stop using booleans like is_deleted. They are rarely enough. Use a nullable timestamp instead. It gives you audit context. Who deleted it, and when?
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE;CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;That index is the secret sauce. By using a partial index, you keep the index size small (because it only tracks active records) and your lookups fast. It’s a nice win.
Are you handling the unique constraints?
“My database will throw errors if I try to insert a record with the same email as one that was ‘deleted’.”
This is a classic trap. If your email column is unique, a soft-deleted row blocks you. You’ve got two choices. You can include deleted_at in your unique index, which gets messy. Or, you can just stop using unique constraints on things that might be reused. I’ll admit, this is where my advice feels weak. Sometimes a unique constraint on email is non-negotiable. If that’s your case, you might actually be better off with a hard delete and an ‘archive’ table. I’m still not entirely sure which approach is objectively better for every scenario.
Keeping it clean
Once you go down the soft delete path, your queries get repetitive. If you’re using an ORM, look for a ‘global scope’ or a plugin to handle it automatically. If you’re writing raw SQL, build a view to hide the deleted stuff by default. It’s better than hunting for ‘WHERE deleted_at IS NULL’ in fifty different files.
Do you really need to keep the data forever? If you’re building a system with massive scale, keeping everything is a recipe for disaster. Sometimes, a hard delete with a background job to export that data to a cheaper storage bucket is the smarter move. It’s more work, but it keeps your primary database lean. What are you prioritizing? Speed, or total data recovery?