基于PostgreSQL实现全库软删除的方案咨询

1. 视图+触发器：透明化软删与查询过滤 #

这是最贴合你需求的通用方案，能直接解决「每次查询要加isDeleted判断」「删除操作需改逻辑」的核心痛点，完全在服务端实现逻辑封装。

实现步骤： #

1. 基础表改造：给需要软删的表新增状态字段：

   ALTER TABLE your_table ADD COLUMN is_deleted boolean DEFAULT false;
   ALTER TABLE your_table ADD COLUMN deleted_at timestamptz; -- 可选，记录删除时间
   

2. 创建业务视图：默认过滤未删除记录，应用端直接查询该视图替代原表：

   CREATE VIEW your_table_view AS
   SELECT * FROM your_table WHERE is_deleted = false;
   

3. 触发器拦截删除操作：把原生DELETE自动转为软删更新，无需修改应用端的DELETE语句：
   先定义触发器函数：

   CREATE OR REPLACE FUNCTION soft_delete_trigger()
   RETURNS TRIGGER AS $$
   BEGIN
     UPDATE your_table 
     SET is_deleted = true, deleted_at = NOW()
     WHERE id = OLD.id;
     RETURN NULL; -- 阻止原始硬删除执行
   END;
   $$ LANGUAGE plpgsql;
   

   绑定到目标表的DELETE事件：

   CREATE TRIGGER trigger_your_table_soft_delete
   BEFORE DELETE ON your_table
   FOR EACH ROW EXECUTE FUNCTION soft_delete_trigger();
   

优势： #

• 应用端查询无需额外加过滤条件，删除操作仍用原生DELETE，完全透明。
• 需查看已删除记录时，直接查询原表即可。

2. 分区表：大数据量场景的归档式软删 #

如果你的表数据量庞大，需要将已删除记录隔离归档，用分区表方案既能保证查询性能，又能方便后续清理。

实现步骤： #

1. 创建分区主表：以is_deleted作为分区键：

   CREATE TABLE your_table (
     id INT PRIMARY KEY,
     -- 其他业务字段
     is_deleted boolean DEFAULT false
   ) PARTITION BY LIST (is_deleted);
   

2. 创建分区分表：分别存储未删除和已删除数据：

   -- 活跃数据分区（默认写入）
   CREATE TABLE your_table_active PARTITION OF your_table FOR VALUES IN (false);
   -- 已删除数据分区
   CREATE TABLE your_table_deleted PARTITION OF your_table FOR VALUES IN (true);
   

3. 软删逻辑：执行UPDATE切换分区（PostgreSQL会自动将数据移动到对应分区）：

   UPDATE your_table SET is_deleted = true WHERE id = 1;
   

   也可以结合上面的触发器，把DELETE自动转为该UPDATE操作。

优势： #

• 查询活跃数据时仅扫描对应分区，性能优于全表过滤。
• 已删除数据可单独归档、备份或清理，不影响主业务表性能。

3. 自动级联软删：解决关联表处理问题 #

针对关联表的软删级联需求，用触发器可以实现自动同步，无需应用端手动处理：

比如orders表关联order_items表，当订单软删时自动标记对应订单项：

CREATE OR REPLACE FUNCTION cascade_soft_delete()
RETURNS TRIGGER AS $$
BEGIN
  -- 仅在从「未删除」转为「已删除」时触发级联
  IF NEW.is_deleted = true AND OLD.is_deleted = false THEN
    -- 动态执行关联表更新
    EXECUTE format(
      'UPDATE %I SET is_deleted = true, deleted_at = NOW() WHERE %I = $1',
      TG_ARGV[0], TG_ARGV[1]
    ) USING OLD.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

绑定到主表的UPDATE事件：

CREATE TRIGGER trigger_orders_cascade_delete
AFTER UPDATE OF is_deleted ON orders
FOR EACH ROW EXECUTE FUNCTION cascade_soft_delete('order_items', 'order_id');

4. 规则（Rules）：轻量替代触发器方案 #

如果不需要复杂的级联逻辑，也可以用PostgreSQL规则直接将DELETE转为软删更新，语法更简洁：

CREATE RULE rule_your_table_soft_delete AS
ON DELETE TO your_table DO INSTEAD
UPDATE your_table SET is_deleted = true, deleted_at = NOW() WHERE id = OLD.id;

注意：规则灵活性不如触发器，适合简单单表软删场景。

适配TypeScript原生SQL的建议 #

• 业务查询直接使用视图名代替原表名，无需修改现有SQL结构。
• 删除操作仍执行原生DELETE FROM your_table WHERE id = ?，触发器/规则会自动完成软删转换，兼容原有代码。
• 如需查询已删除记录，直接查询原表或创建包含全量数据的视图（如your_table_with_deleted）。

内容的提问来源于stack exchange，提问作者Phynae