boulder icon indicating copy to clipboard operation
boulder copied to clipboard

Remove orderToAuthz2 table

Open jsha opened this issue 2 months ago • 0 comments

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.

jsha avatar Oct 22 '25 17:10 jsha