4 ms·
> Instead of updating tables build their replacements under a different name then rename them. This works really well, although a few minor warnings. If you r
by harikb 5y ago
> Instead of updating tables build their replacements under a different name then rename them.
This works really well, although a few minor warnings.
If you run backups, they will block DDL changes like creation and rename of a table. Now this might block all reads on your table until backup completes. Make sure you do this on a schema that isn’t being backed up or do other workarounds.
Other than that, this trick is really nice for instant replace and can also reduce disk space churn / fragmentation.
- stadeschuldt 5y agoIn the above mentioned scenario there is no need for backups as the database (or more precisely the schema) is recreated at each run.
- atwebb 5y agoThis works well until it doesn't and you're incrementally adding time to the loads. Some DBs handle it better than others. What most are looking for is performant, less constrained materialized views. If you're running ELT then MVs are a big deal. Additionally some DBs (SQL Server) don't have an option for pure "no logging" so, even with simple, there's overhead to the large writes. I like schema swaps too for the tables rather than renames but the pattern is the same.