Temporary data and Vertica catalog

Eyal_Yurman

Hi there!

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

Best regards,
Eyal Yurman.

Jim_Knicely

Hi,

You can check the LOCK_USAGE system table to find out which statements take a GCL:

For example, creating a GLOBAL TEMP table takes a Global Catalog Lock:

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
 object_name | mode | count
-------------+------+-------
(0 rows)

dbadmin=> create global temp table test (c1 int);
CREATE TABLE

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
   object_name    | mode | count
------------------+------+-------
 Cluster Topology | S    |     1
 Local Catalog    | X    |     1
 Global Catalog   | X    |     1
(3 rows)

Whereas a LOCAL TEMP table does not take a Global Catalog Lock:

dbadmin=> \q

[dbadmin@s18384357 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
 object_name | mode | count
-------------+------+-------
(0 rows)

dbadmin=> create local temp table test_loc (c1 int);
CREATE TABLE

dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
 object_name | mode | count
-------------+------+-------
(0 rows)

KWillets

We had the regular table version on my last job, and the short answer is that we spent a lot of time chasing GCL timeouts. We would have used either global or local temp, but our ETL tool did not guarantee connection continuity between statements.