When the scripts that generate the data for letsencrypt.org/stats broke yet again, we decided to retire it rather than repair it. Let’s Encrypt issues six to ten million certificates each day, producing a large volume of logs that keeps growing. It became increasingly time-consuming and difficult to answer questions about our own issuance like “how many certificates use the ‘shortlived’ profile.” Using raw logs, this requires finding, parsing and extracting relevant portions of loglines. Querying the database behind our issuance API is not a practical option, as it’s built for transactions rather than analysis. We’d also used a log search SaaS product, but our bills were growing much faster than we’d like, and while it was fine for searching, it wasn’t able to do the analytic workloads we needed. We knew we could dream bigger and better.Daily issuance of Let’s Encrypt certificates over the last 180 daysThis led us to seek a self-hosted solution with efficient storage for structured data in addition to logs. We chose ClickHouse because of its potential as a data warehouse; the combination of cost-efficient storage and fast aggregation over large datasets appealed to us. A bonus of ClickHouse is that it is open source, a key principle valued by Let’s Encrypt.The first step in building our new infrastructure was to purchase new hardware. To back our ClickHouse warehouse, we bought three PowerEdge R7715 servers. Each is equipped with a 32-core AMD EPYC 9355P 3.55GHz processor, 384 GB of RAM and 32 × 3.2TB NVMe drives, working out to roughly 100TB raw storage. With structured data and 100 days’ worth of logs already in our database, we are only at ~14% of total capacity, leaving lots of room for future growth.Logs are the bulk of our storage use and the foundation of our structured data, as every other table we build is derived from them. When it comes to log search, ClickHouse covers our basic needs with quick ingest and interactive SQL. However, there are query ergonomics that we want to improve, like using OpenTechnology’s tracing features and ClickHouse’s tokenization settings.Our primary target for structured data are our issuance records. A materialized view extracts those records from logs into their own table, and further views pre-aggregate from there. One such view counts issuance by day per profile. Now, questions like “what is our issuance by profile over the last 180 days” can be answered within milliseconds.Daily issuance count of certificates by profile, excluding “classic”, over the last 180 daysWe used this approach to completely rebuild the pipeline for our public stats page. The scripts we abandoned used to take hours each day to read and process dozens of compressed data files. The Rube-Goldberg-Machine-like collection of steps failed several times a year, requiring us to intervene and fix it. ClickHouse now computes those same stats in less than 10 seconds. Aside from pre-aggregated issuance tables, we can accomplish this because ClickHouse’s native functions are capable of quick and complex aggregations. Below is a simplified snippet of our materialized view for daily stats, counting the unique set of active domains over months across millions of rows. Two things to point out about this query: array handling means we can query nested fields without reshaping the underlying data, and uniq uses approximations to stay fast at scale.SELECTuniq(arrayJoin(arrayMap(x -> x.value, arrayFilter(x -> x.type = 'dns', identifiers)))) AS fqdns_active,uniq(arrayJoin(etld_plus_one)) AS reg_domains_activeFROM boulder.cert_issuancesWHERE not_before >= yesterday() - 90 AND not_after >= yesterday() AND not_before