Soft Deletes in Database Design
Fast recall
Quick revision
Core answer
Awareness of the operational trade-offs of soft deletes and the ability to design a clean implementation that does not leak deleted data to users.
Forgetting to add deleted_at filters to every query, allowing deleted records to appear in responses. Spring Boot's @SQLRestriction annotation on the entity is the standard mitigation.
Discuss partial indexes on deleted_at IS NULL to keep index size small, and the alternative of archiving old records to a separate table or audit log.
Interview-ready guide
Interview answer
Soft deletes come up whenever an application needs audit history, undo functionality, or regulatory data retention. Instead of running DELETE, you set a deleted_at timestamp and treat the row as invisible to normal queries.
How it works in practice
- Add a
deleted_at TIMESTAMP NULLcolumn — rows with a non-null value are logically deleted, rows with null are active; every query that should exclude deleted records needsWHERE deleted_at IS NULL - In Spring Boot with JPA,
@SQLRestriction("deleted_at IS NULL")on the entity class adds that filter automatically to every repository query — which means forgetting the clause in one custom query doesn't accidentally surface deleted data; but accessing deleted rows then requires a separate native query
The index problem
- A standard index on
user_idincludes all rows, deleted or not — as soft-deleted rows accumulate, the index grows and scans slow down even though those rows are never returned to users - A partial index fixes this:
CREATE INDEX ON orders(user_id) WHERE deleted_at IS NULL— the index contains only active rows, so it stays small, precise, and fast for the queries that actually matter
Trade-offs interviewers expect you to address
- Storage grows without a purge policy — soft-deleted rows accumulate indefinitely; a background job should archive or hard-delete rows past the retention period
- Unique constraints break on re-registration — a unique index on
emailblocks re-use after soft-delete; a partial unique indexWHERE deleted_at IS NULLallows re-registration by excluding deleted rows from the constraint
Soft Deletes in Database Design
Direct Answer
Soft deletes mark records as deleted without physically removing them from the database, typically with a deleted_at timestamp column or an is_deleted boolean. The record remains in the table and can be audited, restored, or referenced by foreign keys. The trade-offs are that every query must filter out deleted records and indexes become less efficient as the table accumulates ghost rows.
Code Example
-- Schema with soft delete column
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total DECIMAL(12,2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT now(),
deleted_at TIMESTAMP -- NULL means active; non-NULL means soft-deleted
);
-- Partial index: only index active records, keeping index lean
CREATE INDEX idx_orders_customer_active
ON orders(customer_id)
WHERE deleted_at IS NULL;
-- Partial unique constraint: allow re-registration after soft delete
CREATE UNIQUE INDEX idx_users_email_active
ON users(email)
WHERE deleted_at IS NULL;
// JPA Entity with automatic filter (Hibernate 6.x)
@Entity
@Table(name = "orders")
@SQLRestriction("deleted_at IS NULL")
public class Order {
@Id @GeneratedValue private Long id;
private Long customerId;
private BigDecimal total;
private LocalDateTime createdAt;
private LocalDateTime deletedAt; // null = active
}
// Soft delete service method
@Service
@Transactional
public class OrderService {
public void softDelete(Long orderId) {
Order order = orderRepository.findById(orderId)
.orElseThrow(() -> new EntityNotFoundException(orderId.toString()));
order.setDeletedAt(LocalDateTime.now());
// Dirty checking commits the update automatically
}
// To access deleted records, bypass @SQLRestriction with a native query
@Query(value = "SELECT * FROM orders WHERE id = :id", nativeQuery = true)
Optional<Order> findByIdIncludeDeleted(@Param("id") Long id);
}
Common Mistakes & Pitfalls
Forgetting to add deleted_at filters to every query, allowing deleted records to appear in responses. Spring Boot's @SQLRestriction annotation on the entity is the standard mitigation.
Check your understanding
Answer these without looking back at the article.
- How do you handle GDPR deletion requests when using soft deletes?
- What is the difference between soft deletes and archiving records to a history table?
- How does @SQLRestriction interact with native queries in Spring Data JPA?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.