Ruby loves Postgres

Transcript

1. Hello, I’m Yannick

2. None
3. None
4. None

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

56. None

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?