Uploaded image for project: 'MusicBrainz Server'
  1. MusicBrainz Server
  2. MBS-8719

Stop materializing of edit and vote counts

    XMLWordPrintable

    Details

      Description

      The edit and editor tables have yes_votes, no_votes and edits_accepted, edits_rejected, auto_edits_accepted, edits_failed columns, respectively, that are materialized (in Perl) versions of data already existing elsewhere. Their purpose is, of course, to speed up access to these numbers, e.g. when displaying a user profile.

      This materialization also has some disadvantages, however. Most importantly, each vote submitted/each edit entered or decided requires an update of the tables, which can mean that an updated copy of the row is inserted and the old row deleted; this causes table bloat and can move related entries apart (the latter mostly relevant for edit), hurting performance. Furthermore, the numbers can get out of sync with reality (cf. MBS-1873).

      This materialization isn't really needed: User profiles already include the number of votes, of cancelled edits, etc. for which no materialization exists. Moreover, Postgres 9.2+ has index-only scans that can heavily improve the performance of counting queries (if an appropriate index is available, which we would need to verify/add; cf. MBS-8718).

      We should therefore simply remove those columns.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                chirlu Ulrich Klauer
                Reporter:
                chirlu Ulrich Klauer
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved:

                  Packages

                  Version Package
                  Schema change, 2016-05-23