Building a 10 Million Game Searchable Master Database

As part of my work on the Chessboard Magic Repertoire Builder, I have been working with a variety of databases and external data sources to supplement user analysis and help with building and maintaining chess repertoires.

There is also another reason I keep adding these things to Chessboard Magic: I like learning.

As a solo developer, one of the benefits is that I can quite happily go down technical rabbit holes simply because I find them interesting. Over time I have implemented things such as Maia, Leela Chess Zero, the Correspondence Database, ChessDB and a number of other tools, and each one has introduced me to new APIs, datasets, engines and technologies.

Sometimes the feature itself is the main goal. Sometimes the learning is. Usually it is a bit of both.
One of the resources I already use extensively is the Lichess API, particularly the Player and Masters databases. They are fantastic free resources for opening research because you can take a position and immediately see which moves have been played, how often they have been played and the results associated with them.

The Masters API does have limitations though. It is mainly designed to provide opening statistics rather than act as a completely searchable historical game database.

Chessboard Magic already has searchable databases for Correspondence Chess and TCEC computer chess, and I have been asked a number of times whether I could provide something similar for over-the-board master games.

So I thought I would give it a go.

The goal was not just to store ten million PGNs.

A PGN is basically a text representation of a chess game. It contains things such as the players, event, result and the moves that were played. Storing ten million of those is a large job, but it is not the particularly interesting part.

What I wanted was a database that could take any chess position and tell me which moves had been played from it, how often they had been played, what the results were, and which master games had actually reached that position.

I also wanted normal player searches and direct game lookup, and I wanted all of this to be fast enough to use interactively inside the Repertoire Builder.

This article covers the data I used, the database design, the hardware, the different approaches I tried, what failed along the way, and finally whether the finished database was actually fast enough to be useful.

Data Sources

The first step was finding a sufficiently large source of over-the-board games.

After looking around, I found two realistic options: LumbrasGigaBase and Opening Master. Both provide very large collections of historical chess games and both were suitable for the sort of development and experimentation I wanted to do.

At this stage, I was not trying to decide which one Chessboard Magic would eventually use in production. I first wanted to prove that the idea itself worked. If I could process a dataset of this size, build the indexes and get good enough query performance, then I could deal with the final licensing decision afterwards.

For the experiment, I used the LumbrasGigaBase OTB Complete database.

The source PGN was approximately 8.6 GB and contained 10,355,488 over-the-board games.

That seemed like a good enough test.

LumbrasGigabase Link: https://lumbrasgigabase.com

Database Design

Fortunately, I was not starting from scratch.

Chessboard Magic already has searchable databases for Correspondence Chess and TCEC computer chess, so my first idea was simply to reuse the same design.

Those databases contain fewer than 200,000 games, while this one would contain more than ten million, but the actual requirements were very similar.

The design uses three main tables.

The first table, master_games, contains the games themselves. Each row represents one game and stores the usual information such as the players, ratings, event, date, result and movetext (pgn).

The second table, master_game_fens, links chess positions to games. This is the important part if you want to search by position. Chessboard Magic does not just need to know that a game exists. It needs to answer questions such as, "Which games reached this exact position?" To do that, every position found while processing a game is linked back to that game's ID. If the same position appears in 50,000 different games, this table contains the links needed to find all 50,000 of them.

The third table, master_fen_stats, contains the statistics for each position. Instead of scanning through millions of games every time somebody wants to know what was played from a position, those totals are calculated in advance. The database can therefore already know things such as which moves were played, how often each move was played and how many of those games ended in a win, draw or loss.

Another important part of the design is indexing. A database index works a little like the index at the back of a book. Without one, finding something may mean checking every row until the database finds what it wants. With an index, it has a much faster way to jump to the relevant records. That becomes extremely important once a table starts containing hundreds of millions of rows.

The overall design was therefore already proven on my smaller databases. The real question was whether the same idea would still work when the scale increased by more than fifty times.

Hardware

For the experiment, I used a VPS I already had available.

A VPS, or Virtual Private Server, is essentially a rented computer running in a data centre. It behaves much like a normal desktop or server, except that you access and manage it remotely.

The machine was running Ubuntu and MariaDB, with eight CPU cores, around 15 GB of RAM and approximately 400 GB of SSD storage.

For anyone less familiar with the terminology, the CPU cores are the parts of the processor doing the computational work, RAM is the computer's fast working memory, and the SSD is the permanent storage where the operating system, files and database live.

This is not particularly exotic hardware.

In broad terms, it is comparable to a reasonably powerful modern desktop computer with a large SSD.

That was actually part of the appeal of the experiment. I wanted to see what was possible using fairly ordinary hardware rather than immediately reaching for a huge cloud setup or specialist database infrastructure.

Attempt 1: Reuse the Existing Process

My first attempt was the obvious one: use exactly the same approach that already worked for my Correspondence and TCEC databases.

For those smaller databases, the process is deliberately simple. Python reads the PGNs, replays each game and keeps track of the positions and statistics it finds. Those statistics are stored in RAM while the process runs. For example, if the program sees the same chess position several times, it can keep a running total of which moves were played and what the results were. Every time the position appears again, those totals are updated.

Once all the games have been processed, the final statistics are written out and loaded into MariaDB and with fewer than 200,000 games, this works very well. The entire end-to-end process takes around 30 minutes.

So naturally I tried the same thing with more than ten million games.

At first, everything looked fine.

The problem was that the amount of information being held in RAM kept growing. RAM is the computer's fast working memory. It is much faster than reading and writing from disk, which makes it great for processing data, but there is only a limited amount of it available.

Every new position the program discovered potentially needed another entry in memory, and with ten million games there were a lot of unique positions.
Eventually the server ran out of physical RAM and started relying heavily on swap. Swap is where the operating system uses part of the disk as temporary overflow memory. It can stop a program from crashing immediately, but disk storage is far slower than RAM.

Once the server began relying heavily on swap, performance dropped dramatically, and at that point it was obvious that the process was not going to finish sensibly.

The basic database design was not necessarily the problem. The problem was trying to keep the statistics for the entire dataset in memory at the same time.

What worked perfectly well for 200,000 games simply did not scale to ten million.

Attempt 2: Reduce the Number of Positions

My next thought was to reduce the amount of information being collected.

The main use case was repertoire building, so the opening and middlegame positions were more important than positions reached very deep into an endgame.

I therefore tried limiting the statistics to the first 80 ply of each game. A ply is one move by one player, so 80 ply is equivalent to 40 complete moves by White and Black.

The idea was simple. If I stopped collecting positions once games reached move 40, the program would see fewer unique positions and therefore use less memory.

It helped. It just did not help enough. By the time the process had completed only around 5% of the database, the server had already consumed approximately 15 GB of RAM and another 15 GB of swap.

That made it fairly obvious that the underlying problem had not gone away.

More importantly, I started questioning whether I liked the compromise in the first place. If I was going to go through the effort of building a full Masters database, I did not really want an arbitrary limit where positions after move 40 simply disappeared from the statistics. The source games were complete, so ideally the database should be complete as well. I removed the 80-ply limit.

The problem needed to be solved properly.

Attempt 3: Process the Data in Chunks

The third attempt was where things started to work.

Instead of trying to calculate the statistics for the entire database in memory, I split the work into batches of 25,000 games. This is generally called chunking with the idea is simply to break one enormous task into smaller pieces.

Python would process 25,000 games and build the statistics in memory exactly as before. Once those 25,000 games were complete, the statistics were written to disk. The memory could then be cleared before starting the next batch.

This made a huge difference.

Previously, memory usage kept growing throughout the entire run because the program was trying to remember the statistics for every game processed so far. Now it only needed to remember one batch. That meant the memory requirement stayed roughly the same whether I was processing the first 25,000 games or the final 25,000 games.

The game records themselves and the links between positions and games could simply be written directly to disk as the PGNs were processed, so they did not need to build up in memory either.

By the end of the run, I had approximately 415 separate statistics files. More importantly, I had finally processed the complete PGN.

Of course, I had also created another problem.

The same chess position might appear in hundreds of different files. Imagine one position appears in batch 1, batch 50 and batch 300. Each of those files now contains only part of the final statistics. Eventually those partial results need to be combined.

My first thought was to merge the files back together in Python. That very quickly started recreating the exact same problem I had just solved. To merge everything globally, Python once again needed to keep an enormous number of positions in memory.

So instead I moved the merging into MariaDB (the database), and loaded all of the partial statistics into a temporary table and asked the database to combine them. This process is called aggregation which simply means taking lots of partial records and combining them into final totals. If one file says a position appeared 100 times and another says it appeared 200 times, the final result should contain 300.

This worked much better than trying to do it in Python, but the sheer size of the data created another problem.

Asking MariaDB to combine hundreds of millions of rows in one enormous job turned out to be a pretty brutal workload. The operation could run for hours with very little obvious indication of progress.

So I had solved the Python memory problem, but I had effectively moved the bottleneck into the database.

Attempt 4: Split the Aggregation

At this point, the solution was to apply the same lesson again.

If processing ten million games at once was too much, I split the games into chunks. If combining hundreds of millions of statistics rows at once was too much, I could split that job into chunks as well.

The only important requirement was that every copy of the same chess position had to be processed together, otherwise, I could end up with one total for a position in one batch and another total in a different batch, which would just create another merging problem afterwards.

So I divided the statistics into 32 consistent groups, making sure that every occurrence of the same position always ended up in the same group.
Each group could then be processed independently.

Once a group was complete, its totals were final and could be written directly into the main statistics table.

So instead of one huge aggregation across hundreds of millions of rows, MariaDB now had 32 much smaller jobs to complete.This also made the process much easier to follow. Rather than watching one enormous operation run for hours with very little feedback, I could see each group complete one after another.

That was the final piece.

The PGN could now be processed without exhausting the server's memory, and the resulting statistics could be combined without asking the database to deal with everything in one gigantic operation.

The database was finally built.

Now came the more important question.

Was it actually fast enough to use?

Results

The original PGN contained 10,355,488 games, and processing it into the files required for the database took approximately 30 hours.

Once loaded, the master_games table contained all games and occupied approximately 9.57 GB.

Loading those games into MariaDB took around two minutes.

The master_game_fens table was much larger.

Because every game contains many positions, one game can contribute dozens or even hundreds of records to this table.

It eventually contained 815,877,602 rows and occupied approximately 37.88 GB (including its indexes).

Loading it took around 51 minutes.

The largest table was master_fen_stats.

This contained 586,321,841 rows of position and move statistics.

Including its indexes, the table occupied approximately 108.71 GB, and completing the final aggregation took around 10 hours.

Across the three main tables, the finished database contains a little over 1.4 billion rows and occupies approximately 156 GB. Those numbers are interesting, but they were not really the result I cared about. The important question was speed.

Chessboard Magic needs to query this database while somebody is actively using the Repertoire Builder.

A database query is simply a request for information.

For example: "Give me the statistics for this position," or "Show me the first 20 master games that reached this position."

If moving a chess piece meant waiting several seconds for the database every time, the whole thing would feel slow no matter how impressive the database looked on paper.

Fortunately, the first benchmarks were much better than I expected.

Looking up move statistics for a position takes around 11–12 milliseconds. A millisecond is one thousandth of a second, so 12 milliseconds is roughly 0.012 seconds.

That includes common opening positions, even though the statistics table contains more than 586 million rows.
Searching for games from a position was similarly fast.

Returning the top 20 games from a position with roughly one million matching games takes around 17–18 milliseconds. Player searches are broader, so they are naturally slower, but returning 20 games for a player such as Magnus Carlsen still takes around 1.3 seconds.

Those were the results I really wanted to see.

The experiment had started with an 8.6 GB PGN containing just over ten million games. It ended with a searchable database containing more than 1.4 billion rows and taking around 156 GB of storage. Yet the queries that actually matter inside the Repertoire Builder were still returning in milliseconds.

What I found most interesting was that I did not actually need to redesign the whole database. The same basic structure I had already used for my much smaller Correspondence and TCEC databases still worked. What had to change was the way I processed the data. That was probably the most useful lesson from the whole experiment.

At each stage, I did not need to throw everything away and start again. I just needed to find the part that no longer scaled and make that particular problem smaller.

And eventually, the database worked.

I am currently tidying up the code used to build the database, and I will be putting it on GitHub soon for anyone who wants to look through the process, experiment with it themselves, or use it as a starting point for building something similar.