Cache the TODO counter

Every page displays the top navigation bar which contains a little icon pointing to the todos page. Should there be any todos this icon will also show a small number indicating the number of todos. This feature results in the following query being executed on every page:

SELECT COUNT(*)
FROM "todos"
WHERE "todos"."user_id" = X
AND ("todos"."state" IN ('pending'));

Here X would be the ID of the user viewing the page.

The query plan for GitLab.com is as follows:

                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1751.03..1751.04 rows=1 width=0) (actual time=1.505..1.505 rows=1 loops=1)
   ->  Bitmap Heap Scan on todos  (cost=22.48..1750.38 rows=260 width=0) (actual time=1.502..1.502 rows=0 loops=1)
         Recheck Cond: (user_id = 209240)
         Filter: ((state)::text = 'pending'::text)
         Rows Removed by Filter: 982
         ->  Bitmap Index Scan on index_todos_on_user_id  (cost=0.00..22.42 rows=818 width=0) (actual time=0.195..0.195 rows=995 loops=1)
               Index Cond: (user_id = 209240)
 Total runtime: 1.546 ms

While not that bad keep in mind that this runs for every single logged in user on every single page.

To reduce some of the load we should move the code for this counter into a dedicated method (if not done so already) and cache the output for a short period of time (e.g. 30 to 60 seconds) or cache it for a longer period of time and only update it when the list of todos for a user changes. Proof of concept code:

class User < ActiveRecord::Base
  def todo_count
    Rails.cache.fetch(['users', id, 'todo_count'], expires_in: 60) do
      todos.unread.count
    end
  end
end