Setup sliding partitioning strategy for LFK

What does this MR do and why?

This MR sets up the sliding partitioning strategy for the LooseForeignKeys::DeletedRecord model. The partition creation and dropping behaviour are disabled by default. We have the following feature flags in place:

• lfk_automatic_partition_creation
• lfk_automatic_partition_dropping

What is sliding partitioning strategy

Related issue: #343084 (closed)

To address bloat issues in the loose_foreign_keys_deleted_records table, we decided to use list partitioning (based on the partition column). As of today, the table has one partition. This partition receives all INSERTs.

  Partitioned table "public.loose_foreign_keys_deleted_records"
           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 | 1                                                              | 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 |              |
 consume_after              | timestamp with time zone |           |          | now()                                                          | plain    |              |
Partition key: LIST (partition)
Indexes:
    "loose_foreign_keys_deleted_records_pkey" PRIMARY KEY, btree (partition, id)
    "index_loose_foreign_keys_deleted_records_for_partitioned_query" btree (partition, fully_qualified_table_name, consume_after, id) WHERE status = 1
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')

What causes the bloat?

• When processing a record, we update the status column which increases the dead tuples.
• Records are not deleted.

The partitioning strategy will periodically add a new partition and delete the old one(s). How does the "sliding" part work?

1. Inserts are going to partition 1.
2. When it's "time" to add a new partition the partition manager will create it.
3. Create a new partition with partition=2.
4. Update the default value for the partition column, so the writes are redirected to partition 2.
5. Sometime later, if partition 1 has no "pending" records (everything processed), the partition can be dropped.

The current configuration will try to create one partition per day. Callbacks:

• next_partition_if: create a new partition if the first record in the current partition is older than a day.
• detach_partition_if: drop the old, non-active partition if we don't have pending rows. At this point, the partition will not get any inserts.

Queries

• next_partition_if: https://explain.depesz.com/s/i3Vv
• detach_partition_if: https://explain.depesz.com/s/EqFP

Migration

Up:

== 20211202094944 MoveLooseFkDeletedRecordsToDynamicSchema: migrating =========
-- execute("ALTER TABLE gitlab_partitions_static.loose_foreign_keys_deleted_records_1 SET SCHEMA gitlab_partitions_dynamic")
   -> 0.0011s
== 20211202094944 MoveLooseFkDeletedRecordsToDynamicSchema: migrated (0.0012s)

Down:

== 20211202094944 MoveLooseFkDeletedRecordsToDynamicSchema: reverting =========
-- execute("ALTER TABLE gitlab_partitions_dynamic.loose_foreign_keys_deleted_records_1 SET SCHEMA gitlab_partitions_static")
   -> 0.0009s
== 20211202094944 MoveLooseFkDeletedRecordsToDynamicSchema: reverted (0.0009s)

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.