What We'll Explore:
Arrays

What We'll Explore 

Rank and CTEs

Tagging

• We want to apply tags to notes entered into our system
  
• We don't care about generating a tag cloud
• We want to be able to find all notes with a given tag

Many to Many relation

• Usually, we'll generate a many-to-many relation between notes and tags
• We'll have a tag record for each tag applied to a note
• We'll have to generate a new tag when a user wants to use one we have not previously encountered

Using Arrays for tags

• PostgreSQL has native support for array columns
• We can store the tags as an array column on our notes
• We no longer have to worry about creating tag records when we encounter a new one

Adding our tags array to the model

class AddTagsToNotes < ActiveRecord::Migration
  def change
    add_column :notes, :tags, :string, array: true
  end
end

Finding Tagged Notes

• Now that we are tagging our notes, we need a way of finding them
• PostgreSQL has multiple ways of checking if an array contains a value
• ANY(...)
• @> - contains operator

ANY

• We can use the ANY() function to see if a value is stored in any element of the array

SELECT notes.*
FROM notes
WHERE 'useful' = ANY(notes.tags)

• This won't use any indexes 

@> - Contains

• We can use the @> operator to see if an array on the left contains the array on the right of the operator (order matters)

SELECT notes.*
FROM notes
WHERE notes.tags @> '{useful}'

GIN indexing our Array

class AddTagsToNotes < ActiveRecord::Migration
  def change
    add_index :notes, :tags, using: :gin
  end
end

Some Benchmarking

Finding Via Has_Many

First Run - 370 ms

Nested Loop
   ->  Bitmap Heap Scan on post_tags
         Recheck Cond: (tag_id = 1)
         ->  Bitmap Index Scan on index_post_tags_on_tag_id
               Index Cond: (tag_id = 1)
   ->  Index Scan using posts_pkey on posts
         Index Cond: (id = post_tags.post_id) 
 Total runtime: 370.139 ms

Subsequent calls - 13 ms

Nested Loop
  ->  Bitmap Heap Scan on post_tags
        Recheck Cond: (tag_id = 2)
        ->  Bitmap Index Scan on index_post_tags_on_tag_id
              Index Cond: (tag_id = 2)
  ->  Index Scan using posts_pkey on posts
        Index Cond: (id = post_tags.post_id)
Total runtime: 13.511 ms

Array: Finding via ANY

First run: 503 ms

Aggregate  
  ->  Seq Scan on posts
        Filter: ('Tag #1'::text = ANY ((array_tags)::text[]))
        Rows Removed by Filter: 98989
Total runtime: 503.517 ms

Subsequent runs: 500 ms

Aggregate
  ->  Seq Scan on posts
        Filter: ('Tag #1'::text = ANY ((array_tags)::text[]))
        Rows Removed by Filter: 98989
Total runtime: 499.618 ms

Array: Finding via @>

First run - Pre index: 536 ms

Aggregate
  ->  Seq Scan on posts
        Filter: (array_tags @> '{"Tag #1"}'::character varying[])
        Rows Removed by Filter: 98989
Total runtime: 536.183 ms

 Aggregate
  ->  Bitmap Heap Scan on posts
       Recheck Cond: (gin_tags @> '{"Tag #1"}'::character varying[])
       ->  Bitmap Index Scan on index_posts_on_gin_tags
             Index Cond: (gin_tags @> '{"Tag #1"}'::character varying[])
 Total runtime: 8.056 ms

Results

Drawbacks

• You lose the ability to efficiently create a list of tags
• Getting a distinct list of tags from a series of arrays is slow
• It's also fairly complex
• GIN can be slow to update
• Multiple inserts into index (one for each key extracted)
• Can be mitigate somewhat using FASTUPDATE

Notes

• Database-side Cache matters
• You will see a performance increase on subsequent runs, as the results will be in memory
• Results across the board were quicker after the first table scan, as the table was in memory after that

Leaderboards

• Need a leaderboard for each game
• Need the ability to return a user's best score for a given game
• Want to page through our scores, but maintain ranking

Rank()

SELECT rank() OVER(ORDER BY points DESC), username, points
FROM scores
WHERE game = 'Barking Bostons'

┌──────┬──────────┬────────┐
│ rank │ username │ points │
├──────┼──────────┼────────┤
│    1 │ Brian    │    300 │
│    2 │ Dan      │    200 │
│    3 │ Dan      │    100 │
│    4 │ Doug     │     30 │
└──────┴──────────┴────────┘

First attempt at getting a user's rank

SELECT rank() OVER (ORDER BY scores.points DESC), username, points
FROM scores
WHERE game = 'Barking Bostons' AND username = 'Dan'

• We get back the user's scores for the game
• BUT the ranks are only for the user's scores, it does not take into account other users

┌──────┬──────────┬────────┐
│ rank │ username │ points │
├──────┼──────────┼────────┤
│    1 │ Dan      │    200 │
│    2 │ Dan      │    100 │
└──────┴──────────┴────────┘

Using Common Table Expressions to get the ranks of a User's Scores

• Common Table Expressions (CTEs) allow you to alias a subquery

WITH top_scores_for_game AS (
  SELECT rank() OVER (ORDER BY points DESC), username, points
  FROM scores
  WHERE scores.game = 'Barking Bostons'
)
SELECT top_scores_for_game.*
FROM top_scores_for_game

• The above query will return all the results from the WITH statement

Attempt #2

• We'll use a CTE to get all the scores for a game, then find our user within those results

WITH top_scores_for_game AS (
  SELECT rank() OVER (ORDER BY points DESC), username, points
  FROM scores
  WHERE scores.game = 'Barking Bostons'
)
SELECT top_scores_for_game.*
FROM top_scores_for_game
WHERE top_scores_for_game.username = 'Dan'

┌──────┬──────────┬────────┐
│ rank │ username │ points │
├──────┼──────────┼────────┤
│    2 │ Dan      │    200 │
│    3 │ Dan      │    100 │
└──────┴──────────┴────────┘

Getting ranked results in ActiveRecord

query = <<-SQL
WITH top_scores_for_game AS (
  SELECT rank() OVER (ORDER BY points DESC), username, points
  FROM scores
  WHERE scores.game = 'Barking Bostons'
)
SELECT top_scores_for_game.*
FROM top_scores_for_game
WHERE top_scores_for_game.username = 'Dan'
SQL

Score.find_by_sql query
=> [#<Score ... >, #<Score ... >]

Using postgres_ext for rank and CTE

Score.ranked(points: :desc).where(game: 'Barking Bostons')

• Now lets get the user's ranked scores for the game using a CTE

Score.from_cte('top_scores_for_game',
  Score.ranked(points: :desc).where(game: 'Barking Bostons')).where(username: 'Dan')