Databases Intermediate

What is Soft Delete?

A pattern where records are marked as deleted with a flag or timestamp rather than being physically removed from the database.

Soft delete adds a deleted_at timestamp or is_deleted boolean to tables. Queries filter out deleted records: WHERE deleted_at IS NULL. This preserves data for auditing, undo functionality, and referential integrity.

Trade-offs include increased storage, more complex queries (must always filter), unique constraint challenges, and GDPR compliance concerns (soft-deleted data still exists). Alternatives include audit tables (copy before delete) and event sourcing.

Related Terms

A data structure that improves the speed of data retrieval operations on database tables at the cost of additional storage.

A column that creates a link between two tables by referencing the primary key of another table.

A database object that automatically executes a specified function when certain events (INSERT, UPDATE, DELETE) occur on a table.

Write-Ahead Log (WAL)

A technique where changes are first written to a log before being applied to the database, ensuring crash recovery and data integrity.

Structured Query Language — the standard language for managing and querying data in relational databases.

A PostgreSQL maintenance operation that reclaims storage from dead tuples and updates statistics for the query planner.

View All Databases Terms →