boulder icon indicating copy to clipboard operation
boulder copied to clipboard

Change crlShards schema to not have auto_increment primary key

Open aarongable opened this issue 1 year ago • 0 comments

The crlShards table schema is currently https://github.com/letsencrypt/boulder/blob/a2141cb695de88970bd843aaee91bd8a3c605be5/sa/db/boulder_sa/20230519000000_CrlShards.sql#L4-L13

I now believe that this schema was a mistake. This table does not grow arbitrarily and does not need to be partitioned, so the existence of an auto_increment primary key is a waste.

Instead, this should be a simple key-value table, where the keys are of the form fmt.Sprintf(%d:%d", issuerID, idx). The overall schema should be something like:

CREATE TABLE `crlShards` (
  `id` tinytext NOT NULL,
  `thisUpdate` datetime,
  `nextUpdate` datetime,
  `leasedUntil` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When this work is done, the lease functions which select-and-uodate this table should be changed to use SELECT...FOR UPDATE as suggested by the InnoDB doc.

aarongable avatar Feb 28 '25 18:02 aarongable