In Matias's great piece earlier this year on our migration to PlanetScale, he mentioned that we still had a non-trivial storage cost due to the sheer size of our database. It's not necessarily big relative to larger companies, but it's big relative to the platonic ideal of the data we actually use.
This is the story of how we fixed that: how we did some magic by deleting the three largest tables in our database. This is a boring story: I like writing boring stories, and I hope you might enjoy reading them.
APIRequest
Every single API request sent to Buttondown—including the ones sent from the app itself—is logged in a table, so that we can show it to users when they're debugging or when we're looking into a support question. Not only were these requests massive in number, each individual one was massive in size, because we were storing a redacted version of the request and response data.
The TTL of these API requests was originally infinite. A couple of years ago we brought it down to around 90 days, feeling comfortable enough that that was the right level of leeway before moving requests to cold storage or otherwise making them inaccessible.
Unfortunately, due to the nature of this table—it's always hot—deletions against it are quite slow. Our efforts to enforce this TTL were akin to bailing out a capsizing ship with a Solo cup.
There are many approaches to fixing this problem: for instance, you could namespace this table by day and then just drop the tables that are out of TTL. We ended up going with a simple approach that sacrificed elegance for ease: dropping some foreign keys and shifting a default to only store request and response data if the backing request used an idempotency key.
Not exactly obvious, but the cohort of users who need this data long term overlap very well with the cohort using idempotency keys to begin with.
RawEmailEvent
We receive millions of webhook-based events from various ESPs every day for things like deliveries, bounces, and rejections. In order to process these events most efficiently, we handle them in batch using a producer/consumer pattern. The webhook handler itself just dumps the raw data into a table, and then something akin to a consumer continuously pulls that table, looking for unprocessed events to—well—process.
For a long time this was a Redis-based system, but as I wrote about earlier this year, we migrated off of Redis and onto a homegrown Postgres solution called asynchronous actions, in order to reduce the number of moving parts we have and increase overall throughput. (Thanks again, PlanetScale.)
One of the things we had to build into asynchronous actions from our Redis days was a very strict opt-in TTL and archiving system, in order to keep reads as fast as possible. And so unshipping this table, having done all the work already, was surprisingly easy. Rather than having raw email events exist as their own model, backed by their own table, we simply have the webhook consumer directly create an asynchronous action that contains the full payload of data—and then the broader system handles archiving it once it's out of harm's way.
EmailEvent
All of that stuff about raw email events? That was actually built after the fact, to support what ended up being our final boss here. Email events are (or were) exactly what they sound like: a user-vendable table of not just deliveries, bounces, et cetera, but also things that don't strictly come from an ESP, like clicks and opens. And remember, we built this ourselves—these events need to go through post-processing in order to remove any differences between the various ESPs and their implementations.
We built raw email events originally to make it easier to access the backing details that constituted a given email event. But then we also created an external event model, which grew to be the load-bearing event bus for webhooks, automations, and analytics. As time passed, it became obvious that we were basically duplicating our work for no discernible reason. We had a bunch of batch processing that would handle ingestion and creation of email events, and then a handful of lightweight transformers that would turn those into external events.
This was just a little silly. But the silliness persisted, because everything that touched email events was some combination of legacy, high-traffic, and easy to break. (This may sound familiar to readers who have worked at software companies.)
Little by little, though, we whittled it away—starting first with readers of events, then writers, until we could safely and confidently say that email events were purely a halfway house to generate external events. From there, we were able to backfill any missing external events going all the way back to 2019, and then unship the model entirely. (Also cataloged here.)
Where we ended up
Ok, enough yapping. What was the net result of all our efforts?
Removing those three tables brought our database size from ~2TB to a mere 750GB: over half of our storage (and storage cost!) goes poof, leading to a cheaper, faster database for everyone.