The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

epoch column encoding

Hello!

I'm using data vault modeling for the data warehouse. So basically there are 3 columns in each table: entity_id, attribute_id and change_time. And because of ordering by entity_id and changes in attribute values cuming retrospectivly I eventually have epoch column as a most heavy column :) Is there any way to reset hidden epoch column encoding? Or make vertica to rebuild underlying containers and alter epoch for old partitions.

Answers

  • bryanwbryanw Employee

    Partitioning the table by change_time may help isolate the updates and potentially improve update and query performance (by partition elimination), but that isn't what you asked. Hierarchical partitions will mitigate excessive ROS count and retain good compression on older partitions, which typically see fewer changes. More on this in a bit.

    AFAIK, the only way to change epoch of a row is to copy the rows - the new (copied) rows will have the current epoch. There are 2 potential strategies - both begin with CREATE TABLE <target_schema.target_table> LIKE <source_schema.source_table> INCLUDING PROJECTIONS
    See: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/CreateTableLike.htm

    Strategy 1: copy the whole table (via INSERT /*+ direct */ ... SELECT), then atomically swap the tables (ALTER TABLE ... RENAME)
    See: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/RenamingTables.htm
    Note: test this on non-production tables to verify primary-foreign column constraints and other attributes follow as expected; add additional ALTER statements to the process as needed.

    Strategy 2: copy partitions (via INSERT /*+ direct */ ... SELECT WHERE change_time ...), then swap partitions between tables
    See: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PartitionManagement/SWAP_PARTITIONS_BETWEEN_TABLES.htm
    Note: swapping partition copies may be a lighter-weight (less data movement) approach to epoch maintenance, and avoids cleanup of table and column attributes
    Note: Strategy 2 may not be viable in some environments where a query may race with the swap partition operation. Swapping partitions is an atomic operation as of the following hotfixes, removing this caveat:
    9.2.1-3, 9.1.1-8 (at least 9.1.1.-11 recommended), 9.0.1-20

    Note: if your current Vertica version is below the hotfix version listed, but Strategy 2 is desirable, please check with support for the latest recommendation regarding hotfixes and your environment (general caveat for anybody reading this message at a later date, as new recommendations may become available)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.