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

what distinguishes table partitioning from projection segmentation?

Hi there,

 

Is there anyone could express the keys of the difference. If we assume that a Vertica table denotes a logical definition, how someone may apprehend the points correctly, in this way it is like we are argueing a reality on a logical/virtual basement. Any documentation also would be appreciated.

 

Thanks.

Comments

  • Segmentation

    - splits projection into chunks of data and stores each segment/chuck of data one each node.

    - egmentation distributes contiguous pieces of projections, called segments, for large tables across database nodes. This maximizes database performance by distributing the load.

    The split is based on hash or a choice of columns with high cardinality, the hash and high cardinality cols are used so data skew ( see here in more detail Improve Projection Performance).

    Partition

    - is splits the table into smaller pieces, is good for data purging(partition drop does not create delete vectors) and query performance(partition pruning).

    - is table property and will apply to all projection for that table.

     

     

  • SruthiASruthiA Employee

    Hi,

     

    HP vertica supports table partitioning and projection segementation. These two features are independent of each other. Some of the key differences are

    1)As the name itself specifies, table partitioning is a table level property and divides large table into smaller ones. it applies to all projections in a given table. it also specifies how data is organized within individual nodes.Vertica selects a partition for each row based on the result of a partitioning expression provided in the CREATE TABLE statement.

    2)To give a brief Projections are how vertica stores data.vertica automatically creates projections if none are present for first time data is loaded. They are always updated at load time. it is similar to concept of materialized views.As mentioned by you, table is logical. Logical means no data is stored. There is just one base table called Anchor table, it is the table on which the projections are based on.Projection segementation defines how data is spread among different cluster nodes. It distributes the data evenly across multiple nodes so that all nodes can participate in query execution. Buddy projection is a copy of projection segement which is stored on different node. it provides high availability when one of the nodes is down.


    There is a example in the following documentation which shows the distinguision between table partitioning and projection segmentation

    https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/PartitioningAndSegmentingData.htm%3FTocPath%3DAdministrator%27s%20Guide|Working%20with%20Table%20Partitions|_____3

     

     

    -Regards,

     Sruthi

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.