DIRECT Is Now the Default Load Type

Write Optimized Store (WOS) is a memory-resident data structure for short-term data storage while Read Optimized Store (ROS) is a highly optimized, read-oriented, disk storage structure, organized by projection.

Prior to Vertica 9.3, by default, Vertica initially loads data into WOS.

For databases created in version 9.3 and later, Vertica now uses a default load type of DIRECT. This setting loads data directly to ROS, bypassing WOS. Databases created in earlier versions are not affected.

Example:

dbadmin=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v8.1.1-27
(1 row)

dbadmin=> CREATE TABLE test1 (c1 INT, c2 VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT /*+ TRICKLE */ INTO test1 SELECT 1, 'A';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT wos_row_count, wos_used_bytes,
dbadmin->        ros_row_count, ros_used_bytes, ros_count
dbadmin->   FROM projection_storage
dbadmin->  WHERE anchor_table_name = 'test1'
dbadmin->  ORDER BY GREATEST(wos_row_count, ros_row_count) DESC
dbadmin->  LIMIT 1;
 wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes | ros_count
---------------+----------------+---------------+----------------+-----------
             1 |          16384 |             0 |              0 |         0
(1 row)

Note that in the above example, using Vertica 8.1.1-27, the WOS columns have values > 0.

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.3.0-0
(1 row)

dbadmin=> CREATE TABLE test1 (c1 INT, c2 VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT /*+ TRICKLE */ INTO test1 SELECT 1, 'A';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT wos_row_count, wos_used_bytes,
dbadmin->        ros_row_count, ros_used_bytes, ros_countt
dbadmin->   FROM projection_storage
dbadmin->  WHERE anchor_table_name = 'test1'
dbadmin->  ORDER BY GREATEST(wos_row_count, ros_row_count) DESC
dbadmin->  LIMIT 1;
 wos_row_count | wos_used_bytes | ros_row_count | ros_used_bytes | ros_count
---------------+----------------+---------------+----------------+-----------
             0 |              0 |             1 |             79 |         1
(1 row)

Note that in the above example, using Vertica 9.3.0, the ROS columns have values > 0.

Have fun!

Helpful Links:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Glossary/WOSWriteOptimizedStore.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Glossary/ROSReadOptimizedStore.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/ChoosingALoadMethod.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/PROJECTION_STORAGE.htm