A schema for conversations that is still fast at ten million messages

Messaging has two access patterns and they pull in opposite directions: list the conversations, newest first, and read one conversation's messages, oldest first. Design for both explicitly or the first one gets slower every day.

Do not compute the inbox

The naive inbox query is a group-by over every message to find each conversation's most recent. It is correct, it is elegant, and it gets slower forever.

Keep a conversations row and denormalise onto it: last_message_at, a preview of the last message, the unread and unanswered state. Update it in the same transaction as the message insert. The inbox becomes an index scan over one small table.

CREATE INDEX conversations_inbox
  ON conversations (tenant_id, last_message_at DESC);

Partition messages by time

Almost every read is recent. Time partitions mean the working set is the current partition, old data can be moved to cheaper storage, and deleting a year is dropping a partition rather than a delete that runs for six hours and bloats the table.

The columns worth having from the start

• Direction, as a column rather than inferred from whether a sender is null.
• The provider's own id, indexed. Every status callback arrives keyed by it.
• Which identity it went out from. The number or Page, because that is where a reply comes back to.
• A status, and a timestamp per status change. Not just the current one - statuses arrive late and out of order.
• Actor. Who sent it, copied onto the row rather than joined - people get renamed and leave, and none of that may rewrite what an old conversation says.

What not to store on the message

Media bytes. Store a reference and keep the file in object storage. A messages table with blobs in it cannot be partitioned usefully, cannot be backed up quickly, and turns a schema migration into an outage.