Cleaning ActiveInsights table periodically

[AliOsm]

Thanks for this great gem, I'm using it currently in production where I'm handling ~100-200 TPM.

I just found that the table size increased by a huge margin in a small period of time and reached ~4GBs, and thought that the gem could provide an ActiveJob job that cleans the table and keeps the last N days of data only, what do you think?

[npezza93]

love it. we can probably do something similar to what solid cache/cable do.

[thedumbtechguy]

love it. we can probably do something similar to what solid cache/cable do.

Be careful with autovacuum on postgres when you do this.

[thedumbtechguy]

Alternatively, you could run a periodic aggregation over a given resolution period e.g 5mins for older data while giving more resolution for recent data.

[AliOsm]

love it. we can probably do something similar to what solid cache/cable do.

Be careful with autovacuum on postgres when you do this.

What is the concern exactly? I'm not a postgres expert :3

[thedumbtechguy]

love it. we can probably do something similar to what solid cache/cable do.

Be careful with autovacuum on postgres when you do this.

What is the concern exactly? I'm not a postgres expert :3

When deleting large amounts of data, you can trigger auto vacuuming, which can impact performance.

By default, autovacuum starts when 20% of the rows plus 50 rows are inserted, updated, or deleted. For example, a table with 1 million rows must have more than 200,050 dead rows before an autovacuum starts ((1000,000 x 0.2) + 50).

[AliOsm]

love it. we can probably do something similar to what solid cache/cable do.

Be careful with autovacuum on postgres when you do this.

What is the concern exactly? I'm not a postgres expert :3

When deleting large amounts of data, you can trigger auto vacuuming, which can impact performance.

By default, autovacuum starts when 20% of the rows plus 50 rows are inserted, updated, or deleted. For example, a table with 1 million rows must have more than 200,050 dead rows before an autovacuum starts ((1000,000 x 0.2) + 50).

Got it, yeah this could run autovacuum alot of times. The table has a lot of rows after 3-4 days of usage only.