Skip to content

Remove "creations" in gitlab_subscription_histories

Alper Akgun requested to merge 118432-remove-creations-in-gitlab_subscription_histories into master

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

Availability and Testing

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

Merge request reports