时间戳

首页 > 解决方案 > 构建可扩展的高效消息表

database - 构建可扩展的高效消息表

问题描述

我在 PostgreSql 中构建了一个消息系统，其中 2 个人可以互相发送消息。我还希望能够获得带有最后一条消息的对话列表作为预览（如whatsapp）

想法1.我从这个简单的消息表开始

| id | to | from | text | createdAt

“to”和“from”指的是用户ID。

在学习查询以获取与最后一条消息的对话列表后，我放弃了这一点，因为每次用户进入消息选项卡时都无法运行。这里是

select *
from messages
join (select max( id ) as maxId from messages where "from" = ? or "to" = ? GROUP BY GREATEST( "to", "from" ), LEAST( "to", "from" )) latest
on messages.id=latest.maxId
order by id desc

这也没有加入用户表。

想法 2. 我决定用一个对话表对消息进行分组

| id | user1 | user2 | lastMessage

lastMessage 将是消息 id，消息表将更改为此

| id | convo | user | text | createdAt

要获得包含他们最后一条消息的 convos 列表，这将是一个简单的连接。但缺点是每次发送消息时，我都必须执行 2 个额外的查询来更新 lastMessage 并检查用户是否是该 convo 的一部分。

我认为 2 个额外的查询超过了与第一个想法有关的子查询

我正在寻找客观上最好的方法来用最少的查询/子查询来完成这两个要求，同时仍然具有可扩展性和可管理性

标签： databasepostgresql

解决方案

也许您可以详细说明您的缓慢SELECT陈述并这样做：

WITH last_msg_per_user_and_chat AS
    (SELECT DISTINCT ON(m."from", m."to")
        m."id", m."from", m."to", m."text", m."createdAt"
    FROM messages m
    WHERE (m."from" = ? OR m."to" = ?)
    ORDER BY m."createdAt" DESC)
SELECT lm.*
FROM last_msg_per_user_and_chat lm
WHERE NOT EXISTS(
    SELECT 1
    FROM last_msg_per_user_and_chat lm2
    WHERE lm2."from" = lm."to"
        AND lm2."to" = lm."from"
        AND lm2."createdAt" > lm."createdAt"
)

如果您现在在("from", "to", "createdAt" DESC)and上建立索引("to", "from", "createdAt" DESC)，您的查询应该不会那么糟糕。

其他一些提示：

  • 不要依赖id当你想要按时间排序的时候。通常，这应该是相同的，但您不能确定。你有一个专门的领域，所以你应该使用它。
  • 不要在 中使用带引号的标识符PostgreSQL，因为您需要在"任何地方使用。不这样做通常会更容易。
  • 您可以将对话组织为表格conversation(id)，并在您的用户和表格中的对话之间进行 am:n 映射，例如participant(id, user_id, conversation_id). 这也将允许小组对话。

推荐阅读