Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

restrict database size

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.

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

  • 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

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