Skip to content

Setup sliding partitioning strategy for LFK

Adam Hegyi requested to merge loose-fk-sliding-partitioning into master

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

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.

Edited by Adam Hegyi

Merge request reports