Experimental database dumps changelog

Question

Experimental database dumps changelog

pietroalbini opened this issue 4 years ago · 12 comments

This is a low-traffic issue tracking all the changes happening to the experimental database dumps. We recommend subscribing to this issue to get notified whenever we make some changes to the contents of the dumps.

Answer 1 · 2021-05-14T15:27:29.000Z

The next crates.io deploy (happening in the next few days) will include the following changes to the database dumps:

PR #3612: The textsearchable_index_col column will be removed from crates.csv, as that column is an implementation detail of crates.io's search. Users importing the database dumps into a PostgreSQL database will not be affected by this change, as a trigger will populate that column at import time.
PR #3611: The version_downloads.csv file will only include the last 90 days of data instead of full day-to-day historical data. Cumulative download counts are still available in crates.csv and versions.csv.
PR #3549: The version_authors.csv file will be removed, as that data was deleted from the crates.io database too.

We also plan to make the following changes in the future:

Issue #3479: all the data from version_downloads.csv will be moved out of the database dump into separate files, one for each day. This will allow clients interested in this data to download it separately.

Answer 2 · 2022-08-16T21:26:04.000Z

Two relevant changes were just deployed:

#5077 and
#5074

Answer 3 · 2024-02-19T13:00:26.000Z

#8155 will delete the badges table

Answer 4 · 2024-03-06T08:51:19.000Z

#8232 added a new crate_downloads table, which is supposed to replace the crates.downloads column soon. this was done for performance reasons to reduce the amount of bloat in the crates table from the regular downloads column updates. at the moment the data should be in sync, but if everything works out we will stop writing to the crates.downloads column in the near future and eventually remove it.

Answer 5 · 2024-03-13T09:53:13.000Z

as mentioned in the last update, #8295 is going to disable writes to the crates.downloads column. we will keep the column around for now to avoid unnecessary schema churn, but once the system has shown the expected performance benefits we will most likely remove the column completely.

Answer 6 · 2024-04-12T11:38:17.000Z

once #8233 is merged and deployed it will remove the crates.downloads column. please us the crate_downloads table instead.

Answer 7 · 2024-04-25T12:52:22.000Z

#8484 will introduce a new experimental default_versions table with a mapping from crates to their "default" version, that will be shown by the frontend and used in e.g. reverse dependency queries.

Answer 8 · 2024-06-10T14:14:00.000Z

#8748 added an experimental ZIP file artifact at https://static.crates.io/db-dump.zip. this file has the advantage of not having to decompress the entire file if you only need access to a certain database table CSV file. compared to the tarball the ZIP file does not have a top-level datetime path prefix, otherwise the files should contain the exact same data.

Answer 9 · 2024-10-29T08:39:11.000Z

#9756 added a num_no_build column to the versions table
#9786 changed it to be "private" since it is reasonably easy to reconstruct it locally if needed (thanks @dtolnay)

in other words: no changes (except for the few days in between the PRs) and sorry for forgetting to mention the change here 🙈

Answer 10 · 2024-11-19T05:35:11.000Z

#9932 added a new edition column to the versions table

Answer 11 · 2024-11-26T15:59:28.000Z

#9998 adds a couple more columns to the versions table. the data will be backfilled from the saved crate files in the next couple of days. this will likely increase size of the database dump, but with deduplicating compression it hopefully should be manageable.

Answer 12 · 2024-11-29T15:51:21.000Z

#10107 reverts the num_no_build column to be "public" again, to fix the nullability issue during imports
#10078 adds categories and keywords columns to the versions table