briefly description of v_vertica247_node0003_catalog and v_vertica247_node0003_data

Naveen_Kumar

Hi,
i am continuously copying data to flex table. and deleting data after some certain interval from table.

as you have mentioned  in documentation,
"Every time you add data to the database, or perform a DML operation, the new data is held in memory (WOS) and moved to storage locations (/path/<databasename>/ )on disk (ROS) at regular intervals".

So, i wanted to know, does it keep all data permanently or the data present in data table(since, in data table we are keeping only few days data).

we are getting space issues on our machine. 
i wanted to know does vertica run janitor or any process on   v_vertica247_node0003_data to clean up old data.

Thanks  for any help.

Sajan_Sangraula

Hi Naveen,
Can you please clarify a bit? Do you mean to ask whether Vertica keeps data in WOS even after moving to ROS, or whether Vertica retains data in persistent storage after data is moved to ROS?

If you want to clean up deleted data to free up disk storage, you can run the following SQL commands to purge all deleted data&colon;
select make_ahm_now();
select purge();

There arn't any janitor jobs to clean up old data unless you add your own scripts, I dont think you would like data to be deleted by itself anyways . Some of your questions on WOS and ROS may be answered here: https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/ConceptsGuide/Components/HybridStorageMod...

Ben_Vandiver

If your pattern is especially regular (e.g., you keep 6 months of data and regularly toss away the oldest month), table partitioning might be the solution for you.  Add a PARTITION BY clause on your table and use drop_partition() -- this reclaims space immediately.