Hi. I have a little time but haven’t contributed before. I’m well experienced in index tuning so feel free to reach out if I can help.

I have a work in progress PR here that adds a couple: https://github.com/WikipediaLibrary/externallinks/pull/417

I've had difficulty finding performance improving indexes for couple of our slowest endpoints, those being project 'top_projects' and 'top_users'. Since the data that these endpoints use only changes daily from the cron jobs we can cache it in memcached with a long expiration to workaround the performance issues with those endpoints, though that's out of scope for this ticket.

I've marked the PR as ready to review. I briefly looked at write performance and haven't identified any unused indexes or any other issues with the writes themselves causing slowdowns. We're still bottlenecked by SELECT operations that precede writes to both aggregates and link events.

In T370533#10629706, @jsn.sherman wrote:

verified that the migration completed and loaded up pageproject and user aggregates; we're still having timeouts on both, but we can assume that we just can't make it over the line of request timeouts. Accepting this as an improvement.

Those were the queries that I had difficulty improving. By their nature they need to scan almost everything in their respective tables (for program 1) so an alternative solution would probably be needed to speed those up.