Programster's Blog

Tutorials focusing on Linux, programming, and open-source

MySQL Table Double Buffering

I've worked on a couple of projects now where there is a need to compute a large result-set and hand them from one system to another in some manner because shared databases always lead to maintenance/development issues in the future. The trouble with this is usually one has a "live" system, but you need to quickly swap out one result-set with the fresh one, and you don't want a period of downtime whilst you perform a lot of operations doing this.

The solution I've found is that it's easiest to ingest the data into a temporary "buffer" table which can take as long as it needs. Then when the importation has finished, rename this table to take over your live/primary table that is actively being served up. The key thing is that this operation takes around a second or less, unlike the data ingestion which can take many minutes or hours (depends on the size/format of your import file).

The trouble comes when you have multiple tables with foreign key relationships between each other. How are we supposed to maintain these relationships when moving/renaming the tables? Luckily MySQL is clever enough to maintain the foreign-key relationships between the tables if you rename all of them in one single statement. E.g.

RENAME TABLE 
  `products_buffer` to `products`, 
  `substitutions_buffer` to `substitutions`;

I wanted to be certain of this so I created and published a codebase on Github which is easy enough for you to run through Docker.

I have only tested this in MariaDB 10, but plan to test with PostgreSQL next.

Last updated: 15th April 2021
First published: 15th April 2021