Ruby loves Postgres

Rulu 2014 talk

1. Hello, I’m Yannick
5. RUBY ❤️ POSTGRESQL RUBY ❤️ POSTGRESQL
6. PostgreSQL is more than a datastore
7. PostgreSQL is a relational database
8. PostgreSQL is webscale
9. PostgreSQL is webscale
10. PostgreSQL is open-source
11. PostgreSQL has a great community
12. PostgreSQL is mature!
13. ! ! ! PostgreSQL will do your coffee
14. ⚡️POWER⚡️
15. • MODELS • QUERIES • SPEED What?
16. MODELS
17. have not integer primary keys. YOU CAN
18. UUID
19. run 'CREATE EXTENSION “uuid-ossp"' ! create_table(:users), id: :uuid do t.string :name end ! User.create #=> #<User @values={:id=>”10e43f5f-713f-4efa- b225-11800777a322"}>
20. add “columns” without migrations. YOU CAN
21. HSTORE
22. 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
23. cache API results. YOU CAN
24. JSON
25. ! add_column(:twitter_auth, :json_payload, :json) ! user.twitter_auth = Omniauth.fetch_auth(:twitter) ! user.twitter_auth[:nickname] = ‘yann_ck’ user.twitter_auth[:nickname] #=> ‘yann_ck’
26. • UUID • HSTORE • JSON Recap
27. ! ! QUERIES
28. have understandable hard queries. YOU CAN
29. AKA WITH clause CTE
30. def bad_users_with_karma User.find_by_sql(query, @karma_limit) end ! def query <<-SQL WITH bad_users AS (SELECT * FROM users WHERE bad = true) SELECT * FROM bad_users WHERE karma > ? SQL end
31. have slow queries to fast read only models. YOU CAN
32. VIEWS MATERIALIZED
33. PSQL> CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users WHERE bad = false; ! class BadUser < User def readonly? true end end
34. search your models. YOU CAN
35. SEARCH FULL TEXT
36. # 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));
37. • CTE • Materialized Views • Full text search Recap
38. ! ! SPEED
39. index more than one column. YOU CAN
40. INDEXES MULTICOLUMN
41. SELECT * FROM posts WHERE !banned AND poster_id = 6; ! SELECT * FROM posts WHERE banned; ! CREATE INDEX ON posts(banned, poster_id);
42. index only a part of your rows. YOU CAN
43. INDEXES PARTIAL
44. SELECT * FROM posts WHERE !banned AND poster_id = 6; ! CREATE INDEX ON poster_id WHERE !banned;
45. have transformations in you indexes. YOU CAN
46. INDEXES FUNCTIONAL
47. SELECT * FROM users WHERE lower(email) = ‘yannick@heroku.com’; ! CREATE INDEX ON users(lower(email));
48. • Partial • Multicolumn • Functional Recap
49. ! TOOLS MOAR
50. CONTENT SHARE
51. PUT IMAGE OF DATACLIPS AND WILL
52. STATS GET QUERIES
53. SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100; Stolen from craigkerstiens.com
54. PUT IMAGE OF EXPENSIVE QUERIES AND TIM
55. USAGE UNDERSTAND
57. • Dataclips • Queries Stats • Global usage Recap
58. PostgreSQL is made of unicorns and rainbows!
59. PostgreSQL is made of unicorns and rainbows!
60. Thanks!
61. Questions?