restrict database size

aleksss55aleksss55 Community Edition User

Hello,
Could you please help me with the following issue?
I have installed vertica cluster. I can't understand how I can restrict database size in time or in size. For example, data in a database must be deleted older than 30 days or when a database size of 100 GB is reached (what comes first)

Tagged:

Answers

  • avoroninavoronin
    edited May 2020

    You can estimate the size of tables by running the following query

    SELECT anchor_table_schema,
    anchor_table_name,
    SUM(used_bytes) / (1024/1024/1024/1024) AS TABLE_SIZE_GB,
    (SUM(SUM(used_bytes)) OVER (ORDER BY sum(used_bytes) DESC)) / (1024/1024/1024/1024) AS TABLE_SIZE_GB
    FROM v_monitor.projection_storage
    --WHERE anchor_table_schema = 'A_TMP'
    GROUP BY anchor_table_schema, anchor_table_name
    order by sum(used_bytes) desc;

    Otherwise I recommend you to partition you table by date (or Year-month-day) and truncate partitions old enough.

  • aleksss55aleksss55 Community Edition User

    OK, I understood about date, but may be there are opportunity delete data by size?

  • marcothesanemarcothesane - Select Field - Administrator

    What do you actually mean by size, @aleksss55 ?
    The Vertica table's footprint on disk, or its license size?
    Footprint on disk: see @avoronin 's answer.
    You can get the license size of a table with:

    SELECT AUDIT('tpc_ds.store_sales');
    -- out Time: First fetch (0 rows): 0.278 ms. All rows formatted: 0.282 ms
    -- out    audit   
    -- out -----------
    -- out  317923727
    

    This corresponds to the bytes that the file would occupy if you exported all rows of the table - the schema, the whole database to file in format CSV.
    Now, how would you automate deletions by size?

    • Dropping tables?
    • Deleting the oldest rows until you're back to the allowed size - per table? per database?, per schema?
    • Deleting the rows with the longest numeric values / family names, etc?

    All you can do here, as far as I'm concerned, is to put together an automatic warning system: run @avoronin 's query, or the AUDIT() function on all concerned tables, and save the output; then compare the obtained sizes to the allowed sizes - per table, per schema, as you need it. In case of rule infringement, you could for example automate the sending of a mail to the mail address corresponding to the user id that owns the table , something like that ...

    Good luck ...

Leave a Comment

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