Increase the performance and usefulness of the annotation table

[jon-betts]

Tasks

• Move annotation CRUD actions into a service #7932

The problem

Our current annotation table originated from an ElasticSearch index, and does not make full use of relational database semantics.

Ideally we'd like a set of tables which:

• Enable fast queries
• Makes use of simple and direct foreign keys for relationships with other objects like groups and users
• Has an auto-increment primary key

Difficulties this causes us

This has some bad outcomes for us:

• Joining from the annotation to the user table is difficult and inefficient
• The annotation table contains a lot of information, and is very large
• This makes it extremely slow to query
• There is no index on the created column

There is complicated logic required to perform reporting and certain types of query over the annotation table for the email digest functionality. Reporting in particular is a sore point, as we have to replicate the structure into a whole new table, which prevents us from easily changing anything to do with annotations. Ad-hoc reporting is basically off the table.

The plan so far

• Consolidate annotation related interactions inside services
• Consider creating a parallel data structure in a new and nicer format
• Swap the existing table over to be a view over the new nicer tables
• Swap services etc to use the new tables under the hood
• Remove the backward compatibility view
• Consider where we can optimise reporting or email digest related functionality and other benefits