Partition the Serials table

Continuing the theme of the other partitioning issues (#5299, #5298, #5268, and #5267), the Serials table needs attention. As with Registrations, this table is not expected to really age-out data, but ultimately we'll want to split the table across multiple hosts/filesystems, so the table needs attention too, even if the benefit for it is in a yet-further-off future.

As of this writing, the Serials table looks like:

CREATE TABLE `serials` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `registrationID` bigint(20) NOT NULL,
  `serial` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `expires` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial` (`serial`),
  KEY `regId_serials_idx` (`registrationID`),
  CONSTRAINT `regId_serials` FOREIGN KEY (`registrationID`) REFERENCES `registrations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

The Serials table really does need a unique index on the serial number, and in MariaDB our only allowable key constraint in a partitioned table is the Primary Key being unique, so minimally the Serials table will need to be adjusted to remove the id column and instead use serial as the Primary Key. It would seem that then we could partition on serial itself using some number of partitions whose values are spaced through the state space of the serial number field, however varchar is not an allowed type for partitioning. (I can't find a reference for this, but trying it in MariaDB 10.5 fails saying ERROR 1659 (HY000): Field 'serial' is of a not allowed type for this type of partitioning)

Another apparent option would be to use something like the created datetime for the partitioning, but then the primary key becomes practically unusable for lookups, as Boulder would need a-priori knowledge of the timestamp before searching the serial.

While there are options beyond this, where I've settled is with adding another int to the table and the primary key, epoch, which could be the same as the first octet (or two) of the serial field (or could with some risk of low-impact misidentification be simply configurable out-of-band, more later) which identifies a namespace of serial numbers.

CREATE TABLE `serials` (
  `epoch` tinyint unsigned NOT NULL,
  `serial` varchar(255) NOT NULL,
-- ...
  PRIMARY KEY (`epoch`, `serial`)
)
ENGINE=CONNECT TABLE_TYPE=MYSQL,
OPTION_LIST='connect=mysql://%s',
PARTITION BY RANGE (`epoch`) (
  PARTITION `host1/db/serials_0` VALUES LESS THAN (1),
  PARTITION `host2/db/serials_1` VALUES LESS THAN (2),
  PARTITION `host3/db/serials_remainder` VALUES LESS THAN (MAXVALUE)
);

For purposes of de-duplication, the tuple (epoch, serial) becomes the unique value, and we can then partition on epoch. The epoch then can be incremented periodically (by time, or maybe database table size), and when incrementing we can position the new partition as needed, perhaps onto a different data storage device or host.

As to database joins, whatever choices are made regarding epoch, I believe serial should remain as-is as the same data is duplicated in other tables and SQL makes it somewhat a pill to concatenate two numbers safely. This would result in duplicate data per-row, which is a bummer since it'll be compounded by every index on this table (since the entire Primary Key is always the leaf of each index B-tree, and we have at least 4 indexes on serials) but as long as we keep epoch a small number of bytes I think the benefits of partitioning outweigh the expansion in index size.

So to that point, above I define epochas tinyint unsigned (e.g., 0-255). Revisiting my earlier note about misidentification, I'm assuming that we can be confident that Boulder only ever consults a given serials table for issuers of which that table is aware -- e.g., we wouldn't get to querying serials for an certificate issued by a seriously out-of-date intermediate. With my intentions to ultimately partition on this field, it's also relevant to point out that presently MariaDB can only support 8192 partitions, and anything more than a few dozen live connection partitions becomes a logistical nightmare anyway.

Since 255 < 8192, we could use smallint (-32k-32k, or unsigned to 64k) to get the additional partition flexibility MariaDB already offers. Note that since smallint is two bytes, that's effectively the same as using two numbers in place of epoch, like (issuer, epoch, serial) as the primary key, but I think leaving epoch as a single two-byte number has superior flexibility. Still, at the ever-present danger of premature optimization, I would argue that 4 additional bytes per serial (4 indexes * 1 more byte) times the number of serials live (billions) is nontrivial, given that this is data whose ultimate hope is to live in RAM.

Since this change would necessarily change the Primary Key, Boulder is going to need a careful plan for how to migrate forward to a table alteration for this. On the up-side, adding the column will be fast, as long as our first epoch is actually DEFAULT 0. =)

No repository files or tests are named. Start by reviewing the related partitioning issues (#5299, #5298, #5268, and #5267) and the MariaDB CONNECT partitioning reference linked here; done requires an agreed epoch-based schema and a careful migration plan that preserves serial lookups and joins.