Is renaming schemas more efficient / faster than drop/create in a single transaction

Question

Given the constraint (from customer side) to having only one database - how can I most effectively deploy new versions of data into a production-facing application.

The new data is packaged into a (-Fc) dump file. This data is supposed to replace the existing data, so restoring it must be done by first dropping the existing tables and then restoring the new ones.

The naive approach of just importing the dump file with pg_restore with --single-transaction will lock the whole database which will cause the application dependent on that data to halt/suspend queries.

My intended approach is to utilize schemas. Basically, the application will have set its connection string to use mydb.public. The import imports all new data into mydb.next.

Then, rename mydb.public to mydb.old, rename mydb.next to mydb.public and drop mydb.old.

Would this approach result in a shorter downtime than importing directly? I am not sure how renames of schemas work internally in postgres, but from my surface understanding, it should be a more efficient approach

Answer 1

I don't think that creating a new schema and later renaming it will be faster than dropping the tables and re-creating them, but it will certainly reduce the time during which the data are not available to the user.

Note that you have to cancel all other transactions and prevent users from starting new ones when you drop the old schema, otherwise DROP SCHEMA oldschema CASCADE will block until all concurrent transactions are done.