Remove "creations" in gitlab_subscription_histories
What does this MR do?
Remove "creations" (entries with change_type=0) in gitlab_subscription_histories on gitlab.com
The reason is that we don't track them anymore !21642 (merged) This is a followup MR to remove existing redundant data/
Issue #118432 (closed)
Data
We have 6267 records with change_type = 0
at at 2019-01-13 18:19 and the table grows 10K rows/month
Here's all the data to be deleted:
select * from gitlab_subscription_histories where change_type=0 order by id;
gitlab_subscription_histories_change_type_0.sql.txt
gitlabhq_production=> select change_type,max(created_at), count(*) from gitlab_subscription_histories group by change_type;
change_type | max | count
-------------+-------------------------------+-------
1 | 2020-01-13 15:16:17.325309+00 | 8271
2 | 2020-01-13 15:06:49.090239+00 | 4664
0 | 2019-12-19 09:53:52.539535+00 | 6267 # data to be deleted was last added in 2019 dec 19
Query plan
explain DELETE FROM gitlab_subscription_histories WHERE change_type=0 returning *
- This uses a seq-scan but it requires 3.9 msec as the data amount is low https://explain.depesz.com/s/CNltl
- If possibly I'd like to avoid adding an index as this table is a history table without the need for an index.
Time: 85.654 ms
- planning: 0.379 ms
- execution: 85.275 ms
- I/O read: 46.257 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 12650 (~98.80 MiB) from the buffer pool
- reads: 178 (~1.40 MiB) from the OS file cache, including disk I/O
- dirtied: 120 (~960.00 KiB)
- writes: 0
ModifyTable on public.gitlab_subscription_histories (cost=0.00..908.17 rows=6267 width=6) (actual time=2.353..84.860 rows=6267 loops=1)
Buffers: shared hit=12650 read=178 dirtied=120
I/O Timings: read=46.257
-> Seq Scan on public.gitlab_subscription_histories (cost=0.00..908.17 rows=6267 width=6) (actual time=2.197..76.877 rows=6267 loops=1)
Filter: (gitlab_subscription_histories.change_type = 0)
Rows Removed by Filter: 9779
Buffers: shared read=177 dirtied=2
I/O Timings: read=46.229
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry - [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. - [-] Tested in all supported browsers
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec
-
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Edited by Mayra Cabrera