postgresql软删除如何最优实现_postgresql软删除模式_技术教程

使用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();