Postgresql + Ruby = :heart:

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?