history_node table is taking too much storage [database: postgres]

[talha-naeem1]

My cadence history_node table is taking too much storage. Even I have updated the domain retention to 7days which was previously set to 30 days, but storage is still not coming down. Any suggestion, what's the problem here?

[demirkayaender]

Could you quantify your problem a little bit? How big is your table? Roughly how many workflows and activities have you run?

Note that, changing retention will only impact the new workflows, for already started workflows it will take however many days it was set to be retained. If this is an outage type of situation, you might need to manually delete as a one-time operation.

[talha-naeem1]

@demirkayaender My history_node table has grown to 250GB. I have around 100 workflows running on a 1-minute cron schedule, with an additional 50-100 workflows triggered by different scenarios. I'm also noticing a significant number of runs with a Continued_as_new state, which I believe are not subject to retention policies.

How can I better manage the history of my cron workflows? Additionally, are there any other potential issues I should be aware of?

Manually deleting these flows is a huge effort—could you suggest any methods for automating the deletion process?

[demirkayaender]

You see ContinueAsNew because these are cron workflows and a new instance needs to be run. That status is one of the "closed" statuses we have and they are subject to retention policies.

Total disk size depends on roughly this:
"number_of_workflows x size_of_workflow_inputs" + "number_of_workflows x number_of_activities x average_activity_input_output_size"

So if your initial retention was 30 days, you will have 30 x 24 x 60 x 100 = 4,320,000 workflows retained.
if your input is, let's say 1KB to both workflows and activities and let's say you have 10 activities per workflow then you get
(num_workflows) * (wf_input + (num_activities * (activity_input + activity_output))) =
(4.3M) * (1KB + (10 * 1KB)) =
4.3M * 11KB = ~50GB

There's also some extra overhead stored per activity and workflow such as "decisions" which would require about this much storage. Then you are getting 100GB.

Given this, numbers may not be super off.

Lowering the retention is definitely way to go but as I said, it only takes effect for the new workflows. You would need to wait the old ones to expire then you will see the impact.

[talha-naeem1]

Thanks, @demirkayaender, for all the clarification. I will most likely wait for the 30-day period to complete to assess the impact on storage. I really appreciate your response!