Why a two-user Convex chat app read tens of MB a day

I was puzzled by my Convex dashboard, which showed a surprisingly high database bandwidth usage for a chat app I was working on. The app only had a few hundred kilobytes of actual messages, so I was confused about where the tens of megabytes of bandwidth were coming from. After some investigation, I discovered that the key to understanding this phenomenon was the concept of "reactive queries" in Convex.

Convex runs queries on the server and sends the result to the client, then keeps watching the data for changes. Whenever any document that the query touched changes, Convex re-runs the entire query and pushes the fresh result to every client subscribed to it. This means that the database bandwidth is not just the size of your data, but the size of the query result multiplied by how many times it re-runs.

For example, if a query returns 200 KB of data and re-runs 150 times due to frequent changes, it would result in 30 MB of bandwidth usage, even though only 200 KB of data was actually stored. I realized that the reason my bandwidth usage was so high was due to several factors:

1. Plain queries re-read the whole list: Every time a new message arrived, the query re-ran and re-shipped the entire list. This meant that even a small new message would cause the entire list of messages to be re-read and re-sent, leading to a significant increase in bandwidth usage.

2. Streaming an LLM reply was secretly quadratic: When I was saving the reply from an LLM to a document on every flush, I was writing the accumulated text back to a document token by token. This resulted in a quadratic increase in the bytes written as the reply length grew, making the process more expensive as the reply got longer.

3. .collect() reads the entire table, every call: In some of my server functions, I was using .collect() to pull every matching row into an array. This meant that if a function like a daily-usage check or a rate-limiter ran on every single message, it would re-read the entire conversation every time someone sent a message, leading to a linear scaling of reads on the hot path.

4. An unstable argument silently re-subscribes: If I passed an array of ids into a query as an argument and built that array fresh on every render, Convex would treat it as a new query argument even if the contents were identical. This caused the query to re-subscribe and re-run from scratch on every render, leading to unnecessary re-subscriptions.

By reframing the bandwidth usage as the product of the query result size and the number of times it re-runs, I was able to identify and address the root causes of the increased bandwidth usage. Implementing solutions such as pagination, avoiding quadratic operations, optimizing .collect() usage, and using stable arguments for queries helped significantly reduce the bandwidth consumption.