Temporary data and Vertica catalog
Normally I would open a ticket but I thought this could be an interesting open discussion.
I'm performing a lot of ETL within Vertica. This requires using large number of CREATE TABLE/TRUNCATE TABLE/MOVE_PARTITIONS_TO_TABLE operations.
I realize that these DDLs will have two issues:
- require global locks (short block of query initiation).
- Increase catalog size.
I could go to local temporary table solution. This hopefully doesn't require any global lock on the catalog or have any footprint on catalog size. The issue with that is it's not resilience to session disconnects.
There for I'm considering few other options.
Can anyone tell me how heavy does each of these options is on catalog size and global locks?
(For the matter, let's assume latest patched Vertica 8.1 running on AWS.)
- Global temportary table
- Local temporary table
- Flex table
- External table (as copy)
- And finally, regular table