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 INSERT
s.
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?
- Inserts are going to partition 1.
- When it's "time" to add a new partition the partition manager will create it.
- Create a new partition with
partition=2
. - Update the default value for the partition column, so the writes are redirected to partition 2.
- 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.