Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Postgresql + Ruby = :heart:

Postgresql + Ruby = :heart:

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

Yannick Schutz

April 08, 2014
Tweet

More Decks by Yannick Schutz

Other Decks in Programming

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