restrict database size

aleksss55

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)

avoronin

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.

aleksss55

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

marcothesane

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 ...