Developers often use soft delete with boolean flags or timestamps to allow data recovery and meet compliance. However, this approach introduces significant complexity. The author highlights how `archived_at` columns bloat tables with dead data, complicate queries, and slow down migrations and backups. Over time, unmanaged archived records can create performance and operational headaches.

A common pitfall is neglecting retention policies, leaving millions of dead rows from tools like Terraform. Restoring records isn't simple; it may require re-running creation logic with current validations. The author argues that while soft delete seems easy initially, its downstream costs in query complexity and data integrity risks are substantial.

For PostgreSQL, better alternatives exist. One approach uses application-level events to archive data to S3, simplifying the primary database but adding infrastructure. Another uses database triggers to copy deleted rows to a separate archive table, keeping live tables clean and queries efficient. A third method leverages Change Data Capture via Debezium to stream deletions to external systems.