So with 2,904,488 events, the indices size increased by 187MB. This is before using the new event purging features, so I think the actual impact will be much less than that after the purging is done. It seems worth it for us, to have the event purging finish in seconds rather than hours, and the amount of space used should be fine.
The other option would be to add and remove the indices only when needed, as part of the purging process since that seems like the only time they are normally used. If a site had a problem with the extra space used, they could go that route.
I was curious how much extra space this would take up, so here are the stats from our system.
Before:
select count(*) size('action_ trigger. event') ) "Table Size" relation_ size('action_ trigger. event') ) "Total Table Size" size('action_ trigger. event') ) "Index Size" trigger. event --+---- ------- -+----- ------- ------+ ------- -----
,pg_size_pretty (pg_relation_
,pg_size_pretty (pg_total_
,pg_size_pretty (pg_indexes_
from action_
;
count | Table Size | Total Table Size | Index Size
-------
2904488 | 311 MB | 548 MB | 237 MB
After:
count | Table Size | Total Table Size | Index Size --+---- ------- -+----- ------- ------+ ------- -----
-------
2904488 | 311 MB | 735 MB | 424 MB
So with 2,904,488 events, the indices size increased by 187MB. This is before using the new event purging features, so I think the actual impact will be much less than that after the purging is done. It seems worth it for us, to have the event purging finish in seconds rather than hours, and the amount of space used should be fine.
The other option would be to add and remove the indices only when needed, as part of the purging process since that seems like the only time they are normally used. If a site had a problem with the extra space used, they could go that route.
I'll work on a signoff.
Josh