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

Stop materializing of edit and vote counts

XMLWordPrintable

      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.

            chirlu Ulrich Klauer
            chirlu Ulrich Klauer
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved:

                Version Package
                Schema change, 2016-05-23