Partition builds queuing tables using list-based partitioning
ci_pending_builds
is a queuing-like table. Records are being consumed quickly and continuously. Records older than 24 hours can be expired (deleted).
As such, it is small (< 5MB) but has a significant rate of churn (updates/deletes).
Due to the high churn rate, autovacuum activity is very frequent (sometimes multiple times per second): https://log.gprd.gitlab.net/goto/5546efb4b33b9cda77fd684df76ea7f1
Overall, we'd like to tune autovacuum activity for this table (separate issue - https://gitlab.com/gitlab-org/gitlab/-/issues/343623).
Proposal
This issue suggests to prepare ci_pending_builds
for list-style partitioning to implement a partitioning-based queuing pattern in postgres (todo - we should describe this somewhere as a pattern! => #347027 (closed)).
This is in preparation for #343084 (closed), which adds ability to manage those partitions.
To prepare for this, we can do the following:
- Create new table, partitioned by
LIST(partition_number)
- Add one partition,
ci_pending_builds_1
- Migrate over to using this table
Once we have migrated to this table, it provides an extension point: We can add a new partition and expire partitions. This allows us to quickly recover from "autovacuum tuning mishaps", bloat accumulated (https://gitlab.com/gitlab-org/gitlab/-/issues/343623#note_714765556) and with #343084 (closed), we may be able to lower autovacuum frequency further. Until we have #343084 (closed), adding partitions can also be a manual operation (a migration).