Storing Messages: Schema, IDs and Pagination

So far Relay messages exist only in flight. Refresh the page and they are gone. Chat storage looks like a simple append-only log, and it mostly is, but a few early decisions are very hard to change later: how messages are identified, how duplicates are prevented and how history is paged. This part gets those right in Postgres.

The schema

Three tables cover the core: conversations, the members of each conversation and the messages. A direct message is simply a conversation with two members, which keeps one code path for DMs and groups.

CREATE TABLE conversations (
  id          uuid PRIMARY KEY,
  kind        text NOT NULL CHECK (kind IN ('direct', 'group')),
  title       text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE conversation_members (
  conversation_id      uuid NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  user_id              uuid NOT NULL,
  joined_at            timestamptz NOT NULL DEFAULT now(),
  last_read_message_id uuid,
  PRIMARY KEY (conversation_id, user_id)
);

-- "Which conversations is this user in?"
CREATE INDEX conversation_members_user_idx ON conversation_members (user_id);

CREATE TABLE messages (
  id               uuid PRIMARY KEY,          -- UUIDv7, time-sortable
  client_id        uuid NOT NULL,             -- generated by the sender
  conversation_id  uuid NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_id        uuid NOT NULL,
  body             text NOT NULL CHECK (length(body) BETWEEN 1 AND 4000),
  created_at       timestamptz NOT NULL DEFAULT now(),
  UNIQUE (sender_id, client_id)
);

-- History, newest first, per conversation.
CREATE INDEX messages_conversation_id_idx ON messages (conversation_id, id DESC);

The composite index (conversation_id, id DESC) is the heart of the design. Loading the latest messages, paging backwards, catching up after a reconnect and counting unread messages are all range scans on it. The last_read_message_id column on members is used for read receipts in part 5.

Choosing message IDs

Message IDs have a harder job than most primary keys. They are used for ordering, as pagination cursors, in URLs and in client caches, and they should keep working if you ever split messages across several databases. The options:

• —`bigserial`: compact and ordered, but generated by one database. Once you shard, two shards hand out the same numbers, and the values leak your message volume to anyone who can see them.
• —Timestamps alone: two messages in the same millisecond collide or order arbitrarily, and clocks on different servers disagree.
• —UUIDv4: globally unique but random, so you cannot sort by it and inserts scatter across the index, which hurts write performance on large tables.
• —Time-sortable IDs (UUIDv7, ULID, Snowflake): a millisecond timestamp in the high bits and randomness or a sequence in the low bits. Unique without coordination, roughly ordered by creation time, and index-friendly because new IDs land at the end of the B-tree.

Relay uses UUIDv7. It fits the native uuid column type, sorts correctly because Postgres compares UUIDs byte by byte, and Postgres 18 can generate it natively with uuidv7(). Relay generates it in the application with the uuid package so the ID is known before the insert and works on any Postgres version.

Idempotent writes with client IDs

Part 2 showed that an ack can time out after the server has already saved the message. When the client retries, the server must recognise the retry and return the original message instead of inserting a copy. The UNIQUE (sender_id, client_id) constraint makes the database enforce that, even when two retries race each other on different servers.

import { Pool } from 'pg';
import { v7 as uuidv7 } from 'uuid';
import type { Message, SendPayload } from '../../shared/events';

export const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

const COLUMNS = `id, client_id AS "clientId", conversation_id AS "conversationId",
  sender_id AS "senderId", body, created_at AS "createdAt"`;

export async function saveMessage(senderId: string, p: SendPayload): Promise<{ message: Message; created: boolean }> {
  const inserted = await pool.query<Message>(
    `INSERT INTO messages (id, client_id, conversation_id, sender_id, body)
     VALUES ($1, $2, $3, $4, $5)
     ON CONFLICT (sender_id, client_id) DO NOTHING
     RETURNING ${COLUMNS}`,
    [uuidv7(), p.clientId, p.conversationId, senderId, p.body],
  );
  if (inserted.rows[0]) return { message: inserted.rows[0], created: true };

  // Conflict: this is a retry. Return the message we already stored.
  const existing = await pool.query<Message>(
    `SELECT ${COLUMNS} FROM messages WHERE sender_id = $1 AND client_id = $2`,
    [senderId, p.clientId],
  );
  return { message: existing.rows[0], created: false };
}

export async function isMember(conversationId: string, userId: string) {
  const { rowCount } = await pool.query(
    'SELECT 1 FROM conversation_members WHERE conversation_id = $1 AND user_id = $2',
    [conversationId, userId],
  );
  return rowCount === 1;
}

ON CONFLICT ... DO NOTHING RETURNING returns zero rows when the row already exists, which is why the second query is needed. The created flag lets the caller skip the broadcast for retries, since the other members already received the message the first time.

Write, then broadcast

The order of operations in the send handler matters. If you broadcast first and the insert fails, other users see a message that does not exist, and it disappears the next time they load history. Always persist first, then broadcast the stored row, then ack.

socket.on('message:send', async (payload, ack) => {
  const parsed = sendSchema.safeParse(payload);
  if (!parsed.success) return ack({ ok: false, error: 'invalid_payload' });

  const room = `conv:${parsed.data.conversationId}`;
  if (!socket.rooms.has(room)) return ack({ ok: false, error: 'not_joined' });

  try {
    const { message, created } = await saveMessage(socket.data.userId, parsed.data);
    if (created) socket.to(room).emit('message:new', message);
    ack({ ok: true, data: message });
  } catch (err) {
    console.error('saveMessage failed', err);
    ack({ ok: false, error: 'server_error' });
  }
});

This keeps a simple invariant: anything a client has seen over the socket is in the database. That invariant is what makes catching up after a reconnect in part 5 possible, because the database becomes the source of truth and the socket is just a fast notification channel on top.

Paging history with keyset cursors

Opening a conversation loads the latest 50 messages. Scrolling up loads the 50 before those. The tempting implementation is LIMIT 50 OFFSET 500, and it has two problems. First, Postgres still reads and throws away the 500 skipped rows, so deep pages get slower and slower. Second, new messages arriving while the user scrolls shift every offset, producing duplicates and gaps.

Keyset pagination fixes both. Instead of a page number, the client sends the ID of the oldest message it has, and the query asks for messages strictly older than it. Because IDs are time-sortable and indexed with the conversation, each page is a single index range scan, no matter how deep.

export async function history(conversationId: string, before?: string, limit = 50) {
  const { rows } = await pool.query<Message>(
    `SELECT ${COLUMNS} FROM messages
     WHERE conversation_id = $1
       AND ($2::uuid IS NULL OR id < $2::uuid)
     ORDER BY id DESC
     LIMIT $3`,
    [conversationId, before ?? null, Math.min(limit, 100) + 1],
  );
  const hasMore = rows.length > limit;
  const page = rows.slice(0, limit).reverse(); // oldest first for rendering
  return { messages: page, nextCursor: hasMore ? page[0].id : null };
}

Fetching limit + 1 rows tells you whether there is another page without a separate COUNT, which on a large conversation would be far more expensive than the page itself. Run EXPLAIN ANALYZE on this query and you should see an Index Scan using messages_conversation_id_idx with a handful of buffers read.

History over REST or over the socket?

Both work. Relay loads history with a plain HTTP endpoint, GET /conversations/:id/messages?before=<id>, and reserves the socket for live events. HTTP responses can be cached, compressed and retried by standard tooling, they show up cleanly in logs and traces, and a large history page does not queue up behind live messages on the same connection. The socket stays small and latency sensitive, which is what it is good at.

Mind the connection pool

Every message is at least one database write, and chat traffic is bursty. The pool size per instance multiplied by the number of instances must stay under what Postgres can handle, and in part 7 Relay will run many instances. Part 4 of the Scaling series walks through pool sizing, PgBouncer and read replicas; the same rules apply here. A useful default is a small pool (5 to 10 connections) per instance with a pooler in front of Postgres, and history reads sent to a replica once traffic grows.

Key takeaways

• —Three tables cover the core model; a DM is a conversation with two members.
• —Index (conversation_id, id DESC): it serves history, catch-up and unread counts.
• —Use time-sortable IDs such as UUIDv7: unique without coordination and index-friendly.
• —A unique (sender_id, client_id) constraint turns retries into no-ops.
• —Write first, broadcast second, so everything a client sees is in the database.
• —Page history with keyset cursors, never OFFSET.

Next up, part 5: Delivery Guarantees: Acks, Retries and Read Receipts, where Relay stops losing messages across flaky networks and learns who has read what.