Archive old entries in `version_downloads` table

[jtgeibel]

We should find a place to archive daily download counts, and drop old entries from version_downloads. We only ever query for the last 90 days of recent downloads. We could upload a CSV of the previous day's downloads to S3 as part of a daily background job.

Currently, the version_downloads table consumes 4241 MB and its primary key index consumes 1825 MB. Reducing the size of this table should greatly reduce cache pressure on our database sever (with 4GB of RAM) and will make the size of our experimental database dumps much more practical.

[Turbo87]

Summary from the team meeting today:

• we already only export the last 90 days in the database dump
• we need a way to convert older entries in the database to CSV files (or similar)
• we need a public place to store these exported files
• afterwards we can remove the old data from the database

[nikhilpatel0]

Any update on this, Specifically how to access older data?

[dtolnay]

I can provide a complete version_downloads table dating back to 2014-11-11 to whoever sends me a preferred way to share a large file. Currently the csv is 120,554,803 rows, 2.3GB, gzipped is 361MB.

[nikhilpatel0]

Thanks for your help. If you can upload it to any file sharing service, that would be really helpful. Gdrive/Dropbox/Onedrive or any service you prefer.

[dtolnay]

https://send.vis.ee/download/6030078658da7a07/#QzIAS1VImWg0p5WfEAi9Dw

$ zcat version_downloads.csv.gz | (head; tail)
date,version_id,downloads
2014-11-11,6,7
2014-11-11,9,1
2014-11-11,10,1
2014-11-11,12,1
2014-11-11,13,1
2014-11-11,15,1
2014-11-11,16,1
2014-11-11,17,1
2014-11-11,20,1
2022-08-10,599691,6
2022-08-10,599692,6
2022-08-10,599693,6
2022-08-10,599694,4
2022-08-10,599695,5
2022-08-10,599696,5
2022-08-10,599697,5
2022-08-10,599698,4
2022-08-10,599699,4
2022-08-10,599700,4

Data from the last day is obviously partial because the day is not over yet.

[nikhilpatel0]

@dtolnay Thank you so much!!