DELETE and UPDATE Performance Considerations
To improve the performance of your DELETE
and UPDATE
queries, consider the following issues:
-
Query performance after large deletes—A large number of (unpurged) deleted rows can negatively affect query performance.
To eliminate rows that have been deleted from the result, a query must do extra processing. If 10% or more of the total rows in a table have been deleted, the performance of a query on the table degrades. However, your experience may vary depending on the size of the table, the table definition, and the query. If a table has a large number of deleted rows, consider purging those rows to improve performance. For more information on purging, see Purging Deleted Data.
- Recovery performance—Recovery is the action required for a cluster to restore K-safety after a crash. Large numbers of deleted records can degrade the performance of a recovery. To improve recovery performance, purge the deleted rows. For more information on purging, see Purging Deleted Data.
- Concurrency—
DELETE
andUPDATE
take exclusive locks on the table. Only oneDELETE
orUPDATE
transaction on a table can be in progress at a time and only when no loads (orINSERT
s) are in progress.DELETE
s andUPDATE
s on different tables can be run concurrently.
For detailed tips about improving DELETE
and UPDATE
performance, see DELETE and UPDATE Optimization.
Caution: Vertica does not remove deleted data immediately but keeps it as history for the purposes of historical query. A large amount of history can result in slower query performance. For information about how to configure the appropriate amount of history to retain, see Purging Deleted Data.