使用deleted_at字段标记删除时间，结合部分索引提升查询性能，通过视图或ORM封装统一过滤已删除数据，利用触发器实现级联软删除，并定期归档清理过期数据。
在 PostgreSQL 中实现软删除，核心是通过标记记录为“已删除”而非真正从数据库中移除数据。这种模式能保留历史数据、支持恢复操作，并满足审计需求。要最优实现软删除，需结合字段设计、索引策略、查询封装与应用逻辑协同。
添加删除状态字段
在需要支持软删除的表中增加一个标识字段，用于表示记录是否被删除：
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE; -- 或使用布尔类型 ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
推荐使用 deleted_at 而非布尔值，原因如下：
- 可记录删除时间，便于审计和数据清理
- NULL 值天然表示“未删除”，查询更直观
- 支持按时间恢复或归档策略
创建部分索引提升性能
软删除后，大多数查询只关心“未删除”的记录。为避免全表扫描，应为活跃数据建立部分索引：
CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL; CREATE INDEX idx_orders_active ON orders (user_id) WHERE deleted_at IS NULL;
这样，常规查询自动利用索引过滤掉已删除数据，性能接近硬删除。
统一查询入口避免遗漏
在应用层或数据库层封装数据访问，确保不会意外返回已删除记录：
- 在 ORM 中全局设置软删除条件（如 Laravel 的 SoftDeletes trait）
- 使用视图替代直接查表
- 定义函数或 CTE 封装通用过滤逻辑
示例：创建活跃用户视图
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;
处理关联关系与级联软删除
外键关系下，父记录软删除后，子记录是否自动标记？这取决于业务规则：
- 可手动在应用逻辑中实现级联软删除
- 使用触发器自动同步状态
- 不强制级联，允许子记录独立存在（如订单与用户）
若用触发器：
CREATE OR REPLACE FUNCTION cascade_soft_delete_user() RETURNS TRIGGER AS $$ BEGIN UPDATE orders SET deleted_at = NOW() WHERE user_id = OLD.id AND deleted_at IS NULL; RETURN OLD; END; $$ LANGUAGE plpgsql;CREATE TRIGGER trig_soft_delete_user AFTER UPDATE OF deleted_at ON users FOR EACH ROW WHEN (OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL) EXECUTE FUNCTION cascade_soft_delete_user();
定期归档与清理策略
长期积累的已删除数据会影响备份与维护效率。建议：
- 定期将 deleted_at 非空的数据归档到历史表
- 使用分区表按时间管理生命周期
- 设置任务自动清理超过保留期限的软删除记录
软删除不是简单加个字段就完事。最优实现依赖于字段设计合理、索引优化到位、查询逻辑统一以及后续的数据治理。PostgreSQL 的部分索引和丰富函数能力让这一模式高效可行。基本上就这些。
