See https://gitlab.com/gitlab-org/gitlab/-/issues/396802+ for the discussion around this strategy. #### Proposal The `reply_key` on `sent_notifications` is currently a random, unique, hexadecimal token that is non-sequential. This should be modified to be sequential, starting with a non-hex byte (i.e. `g`) and followed by the date in ISO format (`2023-07-07`). This will allow range partitioning, plus a 'legacy' table containing all values prior to partitioning. * Partition the `sent_notifications` table by range * Create a partition for `reply_key` values `from (minvalue) to ('g')` * Implement partitions per year-month combination * Update the `reply_key` generation to prepend `g_year-month` See https://gitlab.com/gitlab-org/gitlab/-/issues/396802#note_1460818651 and the thread around it for more information on creation of partitions why this key format was selected. ##### Notes * The initial partition will be larger than 100 GiB (currently 128GiB of data is present at the time of writing). That is considered to be acceptable. This partition will receive less and less use over time. Approval is in https://gitlab.com/gitlab-org/gitlab/-/issues/396802#note_1460818651. * The possibility of deleting rows with no associated issue or MR was explored. While worth doing in and of itself, it would only produce a saving of 5% on the table size. See https://gitlab.com/gitlab-org/gitlab/-/issues/396802#note_1460805973. * This should be confirmed during implementation, but from https://gitlab.com/gitlab-org/gitlab/-/issues/396802#note_1335549604 configuring a 1 year retention policy on the partitions should be enough from a product perspective.