MySQL / PostgreSQL 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.
PostgreSQL does not allow one to specify multiple tables in a single query. However, it will make sure to update and keep all the foreign key relationships when you rename a single table. This is actually a lot easier and prevents user error.
ALTER TABLE `products_buffer` RENAME to `products`; ALTER TABLE `substitutions_buffer` RENAME to `substitutions`;
I wanted to be certain of this so I created and branch for PostgreSQL in my Github codebase which is easy enough for you to run through Docker.
First published: 15th April 2021