Choosing a Load Method
For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect.
In databases created in versions of Vertica prior to 9.3, by default, Vertica loads table data into WOS. After WOS reaches full capacity, Vertica continues to load data to ROS containers. This approach is suitable for small bulk-load operations. If desired, you can specify alternative load methods. You can do so at two levels:
- Table metadata, through
- DML statements:
COPY FROM VERTICA,
If a table specifies a load option, Vertica uses it for all DML operations unless the DML statement specifies otherwise.
AUTO(default): Initially loads data into WOS, suitable for smaller bulk loads.
DIRECT: Loads data directly into ROS containers, suitable for large (>100 MB) bulk loads. Vertica testing has shown that direct loads offer optimal performance in the widest range of scenarios.
TRICKLE: Loads data only into WOS, suitable for frequent incremental loads.
Vertica also supports three load hints:
/*+TRICKLE*/. These hints let you control how individual
UPDATE operations load table data, overriding the target table's load setting, if any.
AUTO: Loading Into WOS
For databases created prior to Vertica 9.3, if no load option is specified for an operation Vertica uses the AUTO method to load data into WOS. After WOS reaches full capacity, Vertica continues to load data to ROS containers.
DIRECT: Loading Directly to ROS
For databases created in Vertica 9.3 and later, Vertica uses the
DIRECT method to load data directly into ROS containers, bypassing the WOS.
DIRECT is best suited for large data loads (100 MB or more).
A large initial bulk load can temporarily affect query performance while Vertica organizes the data.
=> COPY a FROM stdin DIRECT; => COPY b FROM LOCAL STDIN DIRECT;
DIRECT to load many smaller data sets. This approach results in many ROS containers that must be combined later.
For databases created prior to Vertica 9.3, use the
TRICKLE load option to load data incrementally after the initial bulk load is complete. Trickle loading loads data into the WOS. If the WOS becomes full, an error occurs and the entire data load is rolled back. Use the
TRICKLE option only when the following conditions are true:
- You have a finely tuned load and moveout process at your site.
- You are confident that the WOS has room to hold the data you are loading.
This option is more efficient than
AUTO when you want to load data into partitioned tables.
For more details about trickle loading, see Trickle Loading Data.