-
-
Notifications
You must be signed in to change notification settings - Fork 631
Description
Right now the orderToAuthz2 table is our biggest in terms of row count. That makes sense: authz2 rows grow as the number of authorizations created, minus any authorization reuse. orderToAuthz2 rows grow as the number of authorizations requested by orders, which does not account for authorization reuse.
This table is also challenging for how we handle creating MariaDB partitions, since it doesn't have any expires field.
We don't really need this to be a separate table. We don't even use it in a JOIN; we select the order, then separately:
"SELECT authzID FROM orderToAuthz2 WHERE orderID = ?",
We should eliminate this large table by adding a blob column authzIDs to the orders table. This column should contain a binary encoded protobuf containing a list of authzIDs. After selecting an order, we would decode this field and use it to query the authz2 table directly. This would have the added benefit of one fewer query during order polling.
Note that this would be our first use of protobufs for stored data, so we would have to be a little careful about backward compatibility.
It would also break the ability to do JOINs of orders and authz in SQL, for instance during manual investigations.