This issue relates to the following code lib/xmldb/xmldb_index.php::validateDefinition():
https://github.com/moodle/moodle/blob/master/lib/xmldb/xmldb_index.php#L364-L370

The following constant is set to indicate the maximum bytes size of any created index:

    const INDEX_MAX_BYTES = 765;

And it is used accordingly:

                case XMLDB_TYPE_CHAR:

                    if ($field->getLength() > self::INDEX_MAX_BYTES / 3) {

                        return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_CHAR field "'.$field->getName().'" can not be indexed because it is too long.'

                                .' Limit is '.(self::INDEX_MAX_BYTES/3).' chars.';

                    }

                    $total += ($field->getLength() * 3); // the most complex utf-8 chars have 3 bytes

                    break;

There are a couple of things going on here:

1. The INDEX_MAX_BYTES is set to the lower of our Database engines:
   1. MySQL: historically the maximum size of an index in MySQL was 767 bytes (https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html). That's because we used the COMPACT/REDUNDNAT row formats. In MDL-48228 we moved from Antelope file format to Barracuda as a requirement, and moved tables to use the DYNAMIC/COMPRESSED row formats instead. This means that the index key prefix limit in MySQL is now 3,072 bytes.
   2. MS SQL Server: The maximum bytes per index key is 900 bytes for a clustered index, or 1,700 for a non-clustered index.
      We use nonclustered indexes because we create indexes with:

      CREATE INDEX ...

   3. Oracle The maximum key length is 6,398 bytes
   4. Postgres uses a B-tree index, and the maximum length of ra key is 1/3 the buffer page size (8192) making the maximum index size 2,730 bytes
2. UTF8 requires 3 bytes per character, but we now require utf8mb4 which requires 4 bytes per character
   So the math is wrong

Our new lowest index length is with sqlsrv at 1700 bytes.
Our new encoding is utf8mb4 at up-to 4 bytes per character

Therefore we need to update the index validation to these values, giving a maximum key length of 425 characters, a slight increase from 255.