如何基于PostgreSQL设计消息数据库，实现用户对话副本独立存储

这问题我之前做类似消息系统的时候也碰到过，你的初始表结构（users、conversations、conversations_users、messages）其实已经搭好了核心框架，现在要解决的就是用户删除消息不影响其他成员的核心需求——本质是要把「消息本身的存储」和「用户对消息的可见性状态」拆分开，不能让所有人共享同一份消息的删除状态。

核心优化思路 #

不再直接删除messages表的记录，而是给每个用户-消息对维护独立的状态（比如是否已删除、是否已读）。用户的删除操作只会修改自己的状态标记，完全不影响其他用户对这条消息的访问。

具体表结构调整 #

1. 新增message_user_states状态表 #

咱们加一张表专门存每个用户对每条消息的个性化状态，这是实现需求的关键：

CREATE TABLE message_user_states (
    id SERIAL PRIMARY KEY,
    message_id INT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE, -- 标记该用户是否删除了这条消息
    is_read BOOLEAN NOT NULL DEFAULT FALSE, -- 顺便可以支持已读/未读功能
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(message_id, user_id) -- 确保一个用户对同一条消息只有一条状态记录
);

这个表的作用就是为每个用户单独记录他们对消息的操作状态，和消息本身的内容解耦。

2. 调整消息写入逻辑 #

当发送一条消息到对话时，除了在messages表插入消息内容，还要给对话里的所有成员创建对应的状态记录：

-- 假设刚插入的消息ID是 :message_id，对话ID是 :conversation_id
INSERT INTO message_user_states (message_id, user_id)
SELECT :message_id, user_id
FROM conversations_users
WHERE conversation_id = :conversation_id;

用INSERT ... SELECT可以批量给所有对话成员生成初始状态（默认未删除、未读），不用循环插入，效率更高。

3. 用户删除消息的正确姿势 #

用户删除消息时，绝对不要删messages表的记录，只需要更新自己在message_user_states里的状态：

UPDATE message_user_states
SET is_deleted = true, updated_at = CURRENT_TIMESTAMP
WHERE message_id = :message_id AND user_id = :current_user_id;

这样操作后，只有该用户查询时看不到这条消息，其他成员完全不受影响。

4. 查询用户可见的消息列表 #

查询对话消息时，要过滤掉该用户标记为已删除的记录：

SELECT m.*
FROM messages m
JOIN message_user_states mus ON m.id = mus.message_id
WHERE mus.user_id = :current_user_id 
  AND m.conversation_id = :conversation_id
  AND mus.is_deleted = false
ORDER BY m.created_at DESC;

额外优化建议 #

• 处理用户退出对话的场景：如果用户退出对话，可以批量把他在这个对话里的所有消息标记为已删除，不用一条条操作：

  UPDATE message_user_states mus
  SET is_deleted = true
  FROM conversations_users cu
  WHERE mus.user_id = :current_user_id
    AND cu.conversation_id = :conversation_id
    AND cu.user_id = mus.user_id;
  

• 定期清理冗余消息：如果一条消息被对话里的所有成员都标记为已删除，那这条消息就没有留存的必要了，可以定期清理来节省存储空间：

  -- 先找出所有被全员删除的消息
  WITH deleted_messages AS (
      SELECT m.id
      FROM messages m
      JOIN conversations_users cu ON m.conversation_id = cu.conversation_id
      JOIN message_user_states mus ON m.id = mus.message_id AND cu.user_id = mus.user_id
      GROUP BY m.id
      HAVING BOOL_AND(mus.is_deleted)
  )
  DELETE FROM messages WHERE id IN (SELECT id FROM deleted_messages);
  

  可以用PostgreSQL的pg_cron扩展把这个做成定时任务，比如每天凌晨跑一次。
• 索引优化提升性能：为了让查询和更新更顺畅，给状态表加几个关键索引：

  -- 加速用户查询自己的可见消息
  CREATE INDEX idx_mus_user_deleted ON message_user_states(user_id, is_deleted);
  -- 加速批量更新同一条消息的状态
  CREATE INDEX idx_mus_message_id ON message_user_states(message_id);
  

为什么不直接给每个用户存独立消息副本？ #

可能你会想，干脆给每个用户复制一份消息内容不就行了？但这样会产生大量冗余数据——比如一条消息有100个成员，就要存100份完全一样的内容，既浪费存储空间，写入时的开销也会大很多。用状态表的方式，只存储用户的状态差异，消息内容只存一份，是更高效的方案。

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