The great fight cleanup

Every fight played on Leek Wars is recorded. The turn-by-turn actions, the full replay, everything is stored so you can rewatch any battle, even a very old one. Over twelve years, that adds up to 47.7 million fights and a big chunk of the database.

The historical format was JSON stored as text, barely compressed by Postgres. It worked, but it was heavy. So heavy that, about a year ago, the years 2014 to 2022 had to be moved out of the database for lack of space. They were sleeping as inert dumps on the disk: 83 GB that served no purpose and that nobody could browse. Only six years remained accessible.

It was time to deal with it. With two goals: reclaim space, and above all bring those archives back online.

The idea

I tried quite a few approaches, including a custom binary format. Against all odds, a fairly classic approach won: zstd level 19 with a trained dictionary.

The key part is the dictionary. Fights look a lot like each other: same action codes, same structures showing up from one fight to the next. A 256 KB dictionary trained on a real sample captures that shared redundancy, which value-by-value compression simply cannot do. I trained one for the actions, another for the fight report.

It also changes the nature of what gets stored. Until now, the actions and the report lived in text columns: readable JSON, which Postgres compressed on its own with its built-in algorithm (pglz, honest but dated, and above all with no dictionary support). Now they are binary columns holding the zstd output directly, and Postgres is told not to compress anything on top: the bytes are stored as-is, the job is already done, and done better. Decompression happens on the fly when a fight is requested. Nothing changes on the player side: the fight displays exactly the same, it just weighs a lot less.

One implementation subtlety: two different engines handle this data. The fight generator, written in Java, now writes every new fight directly compressed, the moment it ends. And the website API, in PHP, decompresses when you open a fight. Both use zstd with exactly the same dictionaries, each through its own library, and must produce perfectly interchangeable results. Decompression takes under a millisecond, invisible in the loading time. And the old text format stays readable: throughout the whole migration, both formats coexisted in the same tables without anyone noticing.

The ratios measured on real fights hover around 5 to 7x on actions and 6 to 8x on the report. On disk, that's roughly a 2x gain over what Postgres was already compressing by itself, which is the fair comparison. The most telling example is the year 2025: down from 46 GB to 26 GB.

For the curious: how compression works

A compressor like zstd does, at its core, one very simple thing: it spots repetitions. When it reads the same sequence twice, it keeps the first one and replaces the second with a reference, something like "copy the 30 bytes seen a bit earlier". The more repetitions it finds, the smaller the result. The compression level (1 to 22 for zstd) sets how hard it searches: at level 19, it explores a huge number of candidates to find the best references. Compressing is slow, but it changes nothing for decompression, which stays nearly instant: a fight decompresses in under a millisecond. And the benchmark showed me an interesting cliff: between level 19 and level 12, you gain 18x in speed for only 8% more size. Here storage wins, so I kept 19.

The problem is that a compressor always starts cold. It knows nothing about the file it opens, and learns the patterns as it reads. On a big file, no big deal: the first few kilobytes serve as a warm-up and everything after benefits. But a fight is a small document of a few kilobytes. By the time zstd figures out what a fight looks like, the fight is already over. And since every fight is compressed separately, that learning cost is paid 47.7 million times.

That's where the dictionary comes in. You feed thousands of real fights to a trainer, which extracts the most frequent patterns: the action codes that show up constantly, the JSON structures, the typical sequences of a game turn. All of it gets packed into a 256 KB file. Then every fight is compressed "with the dictionary in mind": from the very first byte, zstd can reference those known patterns, as if it had already read thousands of fights before this one. The dictionary is stored only once, and every fight benefits from it.

That's also why my custom binary format lost the match. No matter how tightly I encoded each value, I was only capturing the redundancy inside a single fight. zstd with a dictionary captures the redundancy between fights, and that's where most of the gain hides.

The thirteen years in detail

"Before" is the size the year occupied in the database as text (for the reimported archives, that's their size once restored, right before compression). "After" is the same year once compressed and compacted.

| Year | Fights | Before (text) | After (zstd) | Gain | |---|---|---|---|---| | 2014 | 4.11 M | 13 GB | 7.4 GB | −43% | | 2015 | 6.87 M | 29 GB | 16 GB | −45% | | 2016 | 4.30 M | 22 GB | 12 GB | −45% | | 2017 | 4.00 M | 25 GB | 14 GB | −44% | | 2018 | 3.81 M | 23 GB | 13 GB | −43% | | 2019 | 2.63 M | 17 GB | 10 GB | −41% | | 2020 | 2.67 M | 19 GB | 11 GB | −42% | | 2021 | 2.80 M | 30 GB | 16 GB | −47% | | 2022 | 3.00 M | 27 GB | 15 GB | −44% | | 2023 | 3.00 M | 28 GB | 16 GB | −43% | | 2024 | 4.00 M | 42 GB | 24 GB | −43% | | 2025 | 4.48 M | 46 GB | 26 GB | −43% | | 2026 | 2.01 M | 18 GB | 19 GB* | — | | Total | 47.7 M | ≈ 339 GB | 199 GB | ≈ −41% |

\* 2026 is the current year: the table was compressed in place, without rebuilding, and keeps growing every day with new fights. The freed space gets reused as it goes, so the gain is real but invisible in the table size.

The marathon

The tricky part was running all of this on production without ever interrupting the service. Every year followed the same ritual: reimport the archive, compress fight by fight while checking that decompression gives back the original byte for byte, copy everything into a compact table, meticulously compare the old and the new, then swap. All of it from a separate container, isolated from the game, so the migration could never get in the way of ongoing fights.

The big years mostly required patience. 2015 alone is almost 7 million fights and about ten hours of compression.

Other disk optimizations

The fights were the big piece, but while I had my hands in the engine, I did a full tour of the disk.

Notifications. 70 million rows, 14 GB. Compression is useless here: each notification only weighs a few dozen bytes, and compressing values that small would actually make them bigger. The indexes, though, had room to improve. The date index weighed 1.78 GB; replaced with a BRIN index, which indexes ranges of blocks instead of every single row (perfect for a table where dates arrive naturally in order), it now weighs... 216 KB. Yes, kilobytes. The primary key, bloated by years of insertions, went from 2.5 to 1.5 GB after a rebuild. Bottom line: 14 → 11 GB, with no lock and no downtime.

The bloat hunt. Postgres indexes fragment over time: with enough insertions and deletions, their pages end up half empty but keep their spot on the disk. I scanned the whole database looking for the least dense indexes and rebuilt the worst ones, online, without blocking anyone. About 3.5 GB reclaimed.

The rest. JavaScript source maps piling up build after build (7 GB reclaimed by tightening the retention), old dumps made redundant after each reimport, and around twenty GB of dead Docker containers and images.

And to make it last, two automatic tasks now run on the server: one rebuilds the indexes that swell back up every quarter, the other cleans up whatever accumulates every week. The cleanup takes care of itself now.

The result

In the end, all thirteen years are compressed: 47.7 million fights in 199 GB. The reclaimed space made it possible to reimport everything, so if you want to dig up one of your fights from 2014, it's browsable again.

All of that without losing a single fight along the way, and without interrupting the game. You won't see it on screen, but it makes things better for everyone: we take good care of the beloved server, and it pays us back :)