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?

MySQL

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

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.

References

• EDUCBA - Postgres Rename Table