search

crtsh / certwatch_db

Database schema
https://crt.sh/
GNU General Public License v3.0
199 stars 36 forks source link

Make `certificate_lifecycle` a materialized view #63

Closed RufusJWB closed 5 years ago

RufusJWB commented 5 years ago

Do you think it would be possible to change the certificate_lifecycle view into a materialized view? This would highly improve the speed of querying this view - but of course will take a lot of memory. Additionally it would be necessary to define an update cycle, I think every 30 minutes or even every hour would be enough. SO describes some possible options to do so. I think for our scenario, the CRON-job would be very well suited, as we don't have the requirement of always being up-to-date to the latest change.

robstradling commented 5 years ago

Hi @RufusJWB. I'm afraid it's not practical to do this. The master database server already has its work cut out keeping up with the growth rate of the monitored logs. Continually updating this proposed materialized view would hurt performance too much; and I'm pretty sure that each update would take several days to complete. And disk space is not free or unlimited. :-(

RufusJWB commented 5 years ago

hi @robstradling hm, that's so sad. The reason for creating this issue is, that it often takes a long time to execute this view, especially for CAs with a lot of issued certificates. Maybe creating some more indices could help improving the speed. Do you as administrator have some more performance measuring tools available to find out, which indices are missing / would helpful?