Postgresql + Ruby = :heart:

How you could use postgresql in ways you didn't imagine when using it as a simple datastore.

Transcript

1. Ruby+
   Postgresql=♥♥♥♥
   

   View Slide

2. Hello, I’m
   Yannick
   

   View Slide

3. View Slide

4. View Slide

5. PostgreSQL
   is more than a
   datastore
   

   View Slide

6. PostgreSQL
   is a relational
   database
   

   View Slide

7. PostgreSQL
   is webscale
   

   View Slide

8. PostgreSQL
   is webscale
   

   View Slide

9. PostgreSQL
   is open-source
   

   View Slide

10. PostgreSQL
    is maintained
    

    View Slide

11. PostgreSQL
    has a great
    community
    

    View Slide

12. PostgreSQL
    has new awesome
    features coming!
    

    View Slide

13. PostgreSQL
    will do your coffee
    

    View Slide

14. Let me show you it’s
    POWER
    

    View Slide

15. PROBLEM
    

    View Slide

16. You fetch something
    from an API call.
    Like that Oauth call to
    twitter.
    

    View Slide

17. a) You add a new cache layer like Redis
    b) You add a table with all the fields
    c) You add fields to an existing model
    d) Something else?
    

    View Slide

18. a) You add a new cache layer like Redis
    b) You add a table with all the fields
    c) You add fields to an existing model
    d) Something else?
    

    View Slide

19. a) You add a new cache layer like Redis
    b) You add a table with all the fields
    c) You add fields to an existing model
    d) Something else?
    

    View Slide

20. a) You add a new cache layer like Redis
    b) You add a table with all the fields
    c) You add fields to an existing model
    d) Something else?
    

    View Slide

21. a) You add a new cache layer like Redis
    b) You add a table with all the fields
    c) You add fields to an existing model
    d) Something else?
    

    View Slide

22. JSON
    

    View Slide

23. class AddJsonPayloadToTwitterAuth < ActiveRecord::Migration
    def change
    add_column(:twitter_auths, :json_payload, :json)
    end
    end
    

    View Slide

24. PostgreSQL
    learn you japanese
    

    View Slide

25. PROBLEM
    

    View Slide

26. You add new user settings
    every two weeks and
    needs new avatar sizes
    every three.
    

    View Slide

27. a) You store that in Redis
    b) You add a table with all the fields
    c) You add fields to the existing model
    d) Something else?
    

    View Slide

28. a) You store that in Redis
    b) You add a table with all the fields
    c) You add fields to the existing model
    d) Something else?
    

    View Slide

29. a) You store that in Redis
    b) You add a table with all the fields
    c) You add fields to the existing model
    d) Something else?
    

    View Slide

30. a) You store that in Redis
    b) You add a table with all the fields
    c) You add fields to the existing model
    d) Something else?
    

    View Slide

31. a) You store that in Redis
    b) You add a table with all the fields
    c) You add fields to the existing model
    d) Something else?
    

    View Slide

32. HSTORE
    

    View Slide

33. add_column :users, :settings, :hstore
    !
    class User < ActiveRecord::Base
    # This exposes accessors. user.wants_push = false
    store_accessor :settings, :wants_push,
    :wants_mails,
    :auto_save
    store_accessor :avatars, :large, :medium, :small
    end
    

    View Slide

34. PostgreSQL
    is not mysql
    

    View Slide

35. PROBLEM
    

    View Slide

36. You have a really complex
    query that is blazing fast
    but when you come back a
    week after you’re like
    ‘WTF is that!’
    

    View Slide

37. a) You keep it unreadable
    b) You change the behavior
    c) Something else?
    

    View Slide

38. a) You keep it unreadable
    b) You change the behavior
    c) Something else?
    

    View Slide

39. a) You keep it unreadable
    b) You change the behavior
    c) Something else?
    

    View Slide

40. a) You keep it unreadable
    b) You change the behavior
    c) Something else?
    

    View Slide

41. CTE
    AKA WITH clause
    

    View Slide

42. def bad_users_with_karma
    User.find_by_sql(query)
    end
    !
    def query
    <WITH bad_users AS
    (SELECT * FROM users WHERE bad = true)
    SELECT * FROM bad_users
    WHERE karma > #{karma_limit}
    SQL
    end
    

    View Slide

43. VIEWS
    THANKS
    PSQL
    

    View Slide

44. PostgreSQL
    believes you
    can fly
    

    View Slide

45. PROBLEM
    

    View Slide

46. You really need to add all
    this complexity that makes
    that query run in seconds…
    

    View Slide

47. a) You use a complex query
    b) You denormalize in MongoDB
    c) Something else?
    

    View Slide

48. a) You use a complex query
    b) You denormalize in MongoDB
    c) Something else?
    

    View Slide

49. a) You use a complex query
    b) You denormalize in MongoDB
    c) Something else?
    

    View Slide

50. a) You use a complex query
    b) You denormalize in MongoDB
    c) Something else?
    

    View Slide

51. VIEWS
    

    View Slide

52. CREATE MATERIALIZED VIEW bad_users AS
    SELECT * FROM users WHERE bad = false;
    !
    class BadUser < User
    def readonly?
    true
    end
    end
    

    View Slide

53. PostgreSQL
    might contains
    kittens and unicorns
    

    View Slide

54. PROBLEM
    

    View Slide

55. You need to search your users
    by username, name and email.
    

    View Slide

56. a) You add that elastic search
    b) You select in ruby
    c) Something else?
    

    View Slide

57. a) You add that elastic search
    b) You select in ruby
    c) Something else?
    

    View Slide

58. a) You add that elastic search
    b) You select in ruby
    c) Something else?
    

    View Slide

59. a) You add that elastic search
    b) You select in ruby
    c) Something else?
    

    View Slide

60. SEARCH
    

    View Slide

61. # Using textacular
    !
    User.basic_search(“yannick”)
    User.basic_search(name: “yan:*”)
    User.fuzzy_search(“yannick”) # uses trigrams
    !
    create index on users using
    gin(to_tsvector('english', name));
    

    View Slide

62. PostgreSQL
    comes in multiple
    flavors and colors
    

    View Slide

63. Now, you know that the
    elephant
    database is more than a
    datastore!
    

    View Slide

64. Thanks!
    

    View Slide

65. Questions?
    

    View Slide

66. Questions?
    

    View Slide