A shape databases love
Chat storage has a shape databases love. Your writes are append-only, your reads are always the most recent slice of one conversation, and old data goes cold fast.
Reach for a wide-column store, which fits naturally. Key the rows by conversation and sort them by a time-ordered message identifier, so every message in a conversation lives together on disk in order.
Get two cheap operations out of that. Fetching the latest 50 messages is one sequential read, and inserting is one cheap write. One large chat product ran years of growth on exactly this model.
Choose your message identifiers with care. Use time-sortable unique ones rather than raw timestamps, because two messages in the same millisecond would collide and client clocks lie.
Notice how much one identifier buys you: ordering inside the conversation, pagination cursors, and a deduplication key, all in one value.
Offline delivery comes free
Get offline delivery almost free from this design. When the publish finds no live socket for Ben, nothing special happens at all, because the message is already saved.
Have Ben's client keep a cursor per conversation, the identifier of the last message it saw. On reconnect it asks each active conversation for everything after that cursor, your server reads the tail, and the client merges and deduplicates by identifier.
As the same code path as scrolling up through history. It is exactly why cursor sync beats keeping a separate offline queue per person.
Keep push notifications as separate plumbing. When your recipient has no connection, your chat server hands an event to the notification service, which goes out through the platform. That wakes the app, and the app then syncs through the cursor.
Do not deliver message content on the push channel itself. It is lossy and the platforms rate limit it.
Worked example
Wei's team stores messages for a gaming chat app in Postgres, a relational database, in one table indexed by conversation and timestamp. At 40 million messages a day the table passes 2 billion rows, vacuum falls behind, and history reads at p99, the slowest one in a hundred, hit 900 ms. They migrate to Cassandra with (channel_id, message_id) as the primary key, and the read pattern that hurt Postgres becomes the layout on disk: latest 50 messages is one partition scan, 4 ms at p99. Offline sync also gets simpler. The old system kept a per-user undelivered table that needed cleanup jobs; now a reconnecting client sends its last message ID per channel, like 7194332412, and the server returns the tail. One production incident later they add a cap: a client offline for a month gets the latest 500 messages plus a gap marker, not two weeks of backfill.