Implement partitioning strategy for loose foreign keys

The loose_foreign_keys_deleted_records table is currently a list partitioned table where we have one partition. The partition key is a bigint column called partition.

           Column           |           Type           | Collation | Nullable |                            Default                             | Storage  | Stats target | Description
----------------------------+--------------------------+-----------+----------+----------------------------------------------------------------+----------+--------------+-------------
 id                         | bigint                   |           | not null | nextval('loose_foreign_keys_deleted_records_id_seq'::regclass) | plain    |              |
 partition                  | bigint                   |           | not null |                                                                | plain    |              |
 primary_key_value          | bigint                   |           | not null |                                                                | plain    |              |
 status                     | smallint                 |           | not null | 1                                                              | plain    |              |
 created_at                 | timestamp with time zone |           | not null | now()                                                          | plain    |              |
 fully_qualified_table_name | text                     |           | not null |                                                                | extended |              |
Partition key: LIST (partition)
Indexes:
    "loose_foreign_keys_deleted_records_pkey" PRIMARY KEY, btree (partition, id)
Check constraints:
    "check_1a541f3235" CHECK (char_length(fully_qualified_table_name) <= 150)
Partitions: gitlab_partitions_static.loose_foreign_keys_deleted_records_1 FOR VALUES IN ('1')

The idea is to periodically add a new partition to the table with an incremented partition value. As we process the records from the table, the old partitions can be dropped.

Which partition will get the inserts?

The system should automatically insert to the partition with the highest partition value. This can be determined in several ways:

• By parsing the attached partition names and find the highest postfix number.
• Keep track of the current partition in a separate table (configurable).
• Use and update the DEFAULT value for the partition column.

Requirements for the strategy:

• Ability to define a condition for deleting the partition: drop the partition when all records in the partition are processed (status=2).
• Periodically create a new partition and "redirect" the inserts.