Comments

I guess not having Vertica be able to do inplace updates is what makes it fast.

You need to be aware that Vertica is a write once(on datafile/tablespace -- how ever you wanna call it) and this triggers this bad update performance but in the same time it does make a difference(much faster) when loading and reading from Vertica.

Is important to know that Vertica was build for a purpouse and is addressing that porpuse. Load data fast and query the data fast.

I sugesst you find a way to implement merge statement, partition switching, implement partitions.

As Adrian has mentioned, Vertica is optimized for load and read operations. Depending on the frequency and amount of data being moved, an alternative is to perform a correlated query and load into a staging table. You can then replace the staged table with your source table.

If you are looking at possibly using a MERGE operation, consider using a correlated join instead.

In my experience, the fastest way of updating over 20% of all rows in a big table is as follows:
Imagine you increase the salary in the target table by 2 percent for everyone (won't we all like that ...?)

CREATE TABLE help_target LIKE target INCLUDING PROJECTIONS;
INSERT INTO help_target
SELECT
id
, name
, dob
, salary*1.02
, hire_dt
FROM target;
ALTER TABLE target RENAME TO target_old; -- or drop it if you don't need the previous status any more
ALTER TABLE help_target RENAME TO target;
GRANT SELECT ON target TO PUBLIC; -- or whatever grants existed on the old target table