The nightmare when a customer accidentally clicks “Delete”
A few years ago, I stayed up all night just because a VIP customer accidentally deleted an order worth 200 million VND. At that time, the system used physical DELETE commands. The data vanished from the hard drive instantly. It took me over 4 hours to extract the backup file from the previous night to restore that single record. It was an exhausting and risky experience for my career.
After that incident, I established a rule: Never truly delete data unless absolutely necessary. Instead, use Soft Delete. This technique only marks a record as “deleted” to hide it from the UI, but the data remains safe in the database for recovery when needed.
However, Soft Delete isn’t as simple as adding an is_deleted column. If implemented without careful planning, you will soon face performance issues and Unique Constraint errors as the system grows.
Why You Should Use DATETIME Instead of Boolean?
Many developers often choose is_deleted with a TINYINT(1) type. But my practical experience suggests using a deleted_at column with a DATETIME or TIMESTAMP type.
ALTER TABLE users ADD COLUMN deleted_at DATETIME DEFAULT NULL;The reason is simple. First, you know exactly when the data was deleted for auditing purposes. Second, its NULL value is extremely useful for fast data filtering. Finally, it is the key to solving the Unique Index problem that I will analyze below.
When performing a deletion, we simply need to update the current timestamp:
UPDATE users SET deleted_at = NOW() WHERE id = 123;Every subsequent query must then include WHERE deleted_at IS NULL. It sounds easy, but this is where the real technical issues arise.
Thoroughly Handling Unique Constraint Errors
The Problem: Deleted emails cannot be re-registered
Suppose the email column in the users table is UNIQUE. User A deletes their account; the record remains there with the old email. When User A wants to return and register with that same email, MySQL will throw a Duplicate Entry error because the email already exists (even though it’s soft-deleted).
Solution 1: Include deleted_at in a Composite Unique Index
You could create a Unique Index on the (email, deleted_at) pair. However, MySQL has a specific characteristic: if a column in a Unique Index contains a NULL value, it allows multiple identical rows because NULL != NULL. This unintentionally breaks the uniqueness logic for active accounts.
Solution 2: Use Virtual Columns (Recommended for MySQL 8.0+)
This is the method I usually use to keep data as clean as possible. We create a Virtual Column that only takes a value when the record has not been deleted:
ALTER TABLE users
ADD COLUMN active_email VARCHAR(255)
GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL;
CREATE UNIQUE INDEX idx_unique_active_email ON users(active_email);With this approach, if deleted_at has a value (deleted), active_email will be NULL. Since MySQL allows multiple NULL values in a Unique Index, you can delete an email multiple times, but only one email is allowed to exist in the “active” state.
Optimizing Indexes for Large Databases
The systems I manage currently have tables exceeding 50GB. If every SELECT statement filters by deleted_at without an index, MySQL will have to scan millions of old rows, significantly slowing down response times.
Never index deleted_at individually. Instead, use a Composite Index. For example, if you frequently search for users by status:
CREATE INDEX idx_status_active ON users (status, deleted_at);Placing deleted_at at the end of the index helps the MySQL optimizer quickly discard deleted records before filtering by other conditions.
Operational and Data Cleanup Strategies
Soft-deleted data accumulates over the years. According to my statistics, this “junk” data can account for up to 30% of table capacity after 2 years of operation. To keep the system running smoothly, you need two strategies:
1. Use Database Views for Security
To prevent developers from forgetting to add the WHERE deleted_at IS NULL condition, which could lead to data leakage on the UI, create a View:
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;The development team only needs to query from this View. Any risks regarding soft delete logic are completely eliminated at the database level.
2. Automate Hard Deletion
Data deleted for too long (e.g., over 1 year) usually has no recovery value. I typically set up a Cronjob to run at 2 AM for cleanup:
DELETE FROM users
WHERE deleted_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 5000; -- Process in batches to avoid locking the table for too longThis cleanup helps shrink index size, increase RAM cache speed, and significantly save on storage costs.
In summary, Soft Delete is an excellent safety net for your data. However, implement it professionally by combining Virtual Columns and Composite Indexes so you don’t sacrifice performance for safety. I hope these real-world insights help your system operate more stably.
