compression difference

Navin_CNavin_C Vertica Customer
Hello All,

This is a part of some test performed.

We tried replicating the data in same cluster with different schema name.

We have more then 10 schema for doing this

So for schema A having 30 tables 
we replicated schema B having same 30 tables in same cluster

How we did this:
1. export the definition of 30 tables along with all projection definition
2 Changed the definition(name) and deployed the ddl into same cluster
3. inserted all records from schema A to schema B table wise using insert..select statement

According to these steps all the things should be same in both schemas

Differeces observed

1. One of the schema (B) is doubled in terms of compressed size from Schema A
2. Others schemas (B) are reduced in terms of compressed size in range of 4-5 GB from Schemas A

We have checked the rowcount for each tables and they are perfectly matching their corresponding tables in other schema

Got the compressed size from projection storage table 

Why is there such a difference in compression size.


  • Options
    Abhishek_RanaAbhishek_Rana Vertica Employee Employee

    If you assign an ENCODING value in the CREATE PROJECTION statement, that ENCODING value gets exported properly. If an ENCODING value is not specified, then AUTO is assumed, which can lead to the issue you have specified. The database designer typically lists an ENCODING value for fields in a projection. 

    Please check "projection_columns" and "column_storage" tables for verifying if encoding is not changed in new schema.



  • Options
    Navin_CNavin_C Vertica Customer
    Hi Abhishek,

    As I said about the steps.

    I have exported the actual projection definition and then deployed it.
    So whatever the actual encoding is for Schema A will be same for Schema B.


Leave a Comment

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