Postgresql + Ruby = :heart:

Postgresql + Ruby = :heart:

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

F17e757902ab6a18cba1bacaa976eb0f?s=128

Yannick Schutz

April 08, 2014
Tweet

Transcript

  1. Ruby+ Postgresql=♥♥♥♥

  2. Hello, I’m Yannick

  3. None
  4. None
  5. PostgreSQL is more than a datastore

  6. PostgreSQL is a relational database

  7. PostgreSQL is webscale

  8. PostgreSQL is webscale

  9. PostgreSQL is open-source

  10. PostgreSQL is maintained

  11. PostgreSQL has a great community

  12. PostgreSQL has new awesome features coming!

  13. PostgreSQL will do your coffee

  14. Let me show you it’s POWER

  15. PROBLEM

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

    call to twitter.
  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?
  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?
  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?
  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?
  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?
  22. JSON

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

    end
  24. PostgreSQL learn you japanese

  25. PROBLEM

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

    new avatar sizes every three.
  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?
  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?
  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?
  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?
  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?
  32. HSTORE

  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
  34. PostgreSQL is not mysql

  35. PROBLEM

  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!’
  37. a) You keep it unreadable b) You change the behavior

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

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

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

    c) Something else?
  41. CTE AKA WITH clause

  42. def bad_users_with_karma User.find_by_sql(query) end ! def query <<-SQL WITH bad_users

    AS (SELECT * FROM users WHERE bad = true) SELECT * FROM bad_users WHERE karma > #{karma_limit} SQL end
  43. VIEWS THANKS PSQL

  44. PostgreSQL believes you can fly

  45. PROBLEM

  46. You really need to add all this complexity that makes

    that query run in seconds…
  47. a) You use a complex query b) You denormalize in

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

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

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

    MongoDB c) Something else?
  51. VIEWS

  52. CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users WHERE

    bad = false; ! class BadUser < User def readonly? true end end
  53. PostgreSQL might contains kittens and unicorns

  54. PROBLEM

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

    email.
  56. a) You add that elastic search b) You select in

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

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

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

    ruby c) Something else?
  60. SEARCH

  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));
  62. PostgreSQL comes in multiple flavors and colors

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

    a datastore!
  64. Thanks!

  65. Questions?

  66. Questions?