Query all partitions when loading loose FK records

What does this MR do and why?

This change prepares the loose foreign key scheduling query to handle multiple database partitions and fair queueing.

Query

Prep: create 2 more partitions with incrementing partition index.

CREATE TABLE gitlab_partitions_static.loose_foreign_keys_deleted_records_2
PARTITION OF loose_foreign_keys_deleted_records                                                                                                                FOR VALUES IN (2);

CREATE TABLE gitlab_partitions_static.loose_foreign_keys_deleted_records_3
PARTITION OF loose_foreign_keys_deleted_records
FOR VALUES IN (3);

Prep: add some data

id = 1
1000.times do
  id +=1
  LooseForeignKeys::DeletedRecord.create({
    fully_qualified_table_name: 'public.issues',
    primary_key_value: id,
    partition: 2
  })
end

1000.times do
  id +=1
  LooseForeignKeys::DeletedRecord.create({
    fully_qualified_table_name: 'public.issues',
    primary_key_value: id,
    partition: 3
  })
end

Loading a batch:

• https://explain.depesz.com/s/jC93 (partition scanning stops once LIMIT is reached)
• https://explain.depesz.com/s/e56d (uses Append when more partitions are involved)

Update query

Unfortunately, the UPDATE query which marks the records processed is not so great. Due to the "range", it scans all partitions:

 Update on loose_foreign_keys_deleted_records  (cost=0.28..87.88 rows=3 width=67) (actual time=0.127..0.127 rows=0 loops=1)
   Update on loose_foreign_keys_deleted_records_1
   Update on loose_foreign_keys_deleted_records_2
   Update on loose_foreign_keys_deleted_records_3
   ->  Index Scan using loose_foreign_keys_deleted_records_1_pkey on loose_foreign_keys_deleted_records_1  (cost=0.28..17.29 rows=1 width=76) (actual time=0.027..0.027 rows=0 loops=1)
         Index Cond: ((id >= 1) AND (id <= 4))
         Filter: ((fully_qualified_table_name = 'public.projects'::text) AND (status = 1))
   ->  Index Scan using loose_foreign_keys_deleted_records_2_pkey on loose_foreign_keys_deleted_records_2  (cost=0.28..35.29 rows=1 width=63) (actual time=0.049..0.049 rows=0 loops=1)
         Index Cond: ((id >= 1) AND (id <= 4))
         Filter: ((fully_qualified_table_name = 'public.projects'::text) AND (status = 1))
   ->  Index Scan using loose_foreign_keys_deleted_records_3_pkey on loose_foreign_keys_deleted_records_3  (cost=0.28..35.29 rows=1 width=63) (actual time=0.050..0.050 rows=0 loops=1)
         Index Cond: ((id >= 1) AND (id <= 4))
         Filter: ((fully_qualified_table_name = 'public.projects'::text) AND (status = 1))
 Planning Time: 0.658 ms
 Execution Time: 0.217 ms
(15 rows)

We can fix this if we update the records by id and partition: https://explain.depesz.com/s/DcZY

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.