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=♥♥♥♥
2. Hello, I’m Yannick
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?