Postgresql - How to safely rename table

Question

In PostgreSQL 13, I am loading data to a Staging table which has the same schema structure as the old table i.e. Target table and once all rows are loaded I am Dropping the old table and renaming Staging to the old table name.

The above works, But my concern is even if the table rename command should not take a lot of time still the worst-case scenario, What if someone tries to query the old table at the same time while it was executing the DROP old table query? my understanding is their application will fail basically some sort of table lock issue.

So how can we avoid that or how can we safely rename/switch the table without such issues?

Thanks!

UPDATE

In MS SQL Server we can do table switching instead of renaming tables which is explained very well in the below link. So do we have similar option in Postgresql as well?

https://littlekendra.com/2017/01/19/why-you-should-switch-in-staging-tables-instead-of-renaming/

Answer 1

You can simply do it like this in a single transaction:

START TRANSACTION;

/* this will block until the last concurrent transaction
   that uses the table is done */
DROP TABLE tab;

ALTER TABLE tab_new RENAME TO tab;

COMMIT;

A concurrent transaction that tries to access the table will have to wait until the transaction is done, whereupon it will use the new table. There should be no errors, and the only disruption can happen if a long running concurrent transaction blocks the DROP TABLE for a long time.

If you want to avoid blocking all concurrent statements for a longer time, you can add the following after the start of the transaction:

SET LOCAL lock_timeout = '500ms';

That will cancel the DROP TABLE and abort the containing transaction if the lock is not granted withing half a second. Then you retry the transaction after a randomized delay and keep repeating until it succeeds.