Draft: Sliding list partitioning for loose foreign keys

Simon Tomlinsonrequested to merge
loose-foreign-keys-partitioning into master

What does this MR do and why?

Describe in detail what your merge request does and why.

Implements the sliding list partitioning strategy from !73406 (merged) for the loose_foreign_keys_deleted_records table.

This is the second half of #343084 (closed)

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

  1. Feature.enable(:partition_pruning)

  2. Change the PARTITION_DURATION for DeletedRecord to something very short like 1.second

  3. Create a LooseForeignKeys::DeletedRecord on the console, note that it goes into partition 1 (possibly 2 if you had existing records)

  4. Restart your console or run Gitlab::Database::Partitioning::PartitionManager.new(LooseForeignKeys::DeletedRecord).sync_partitions

  5. \d+ loose_foreign_keys_deleted_records in gdk psql and see that the new partition was added

  6. Mark all the deleted records for your oldest partition as processed (LooseForeignKeys::DeletedRecord.for_partition(<id>).update_all(status: :processed))

  7. Sync partitions again with Gitlab::Database::Partitioning::PartitionManager.new(LooseForeignKeys::DeletedRecord).sync_partitions or a console restart

  8. \d+ loose_foreign_keys_deleted_records and see that we removed the old partition Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports