Ruby loves Postgres

Transcript

1. 1.

   Hello, I’m Yannick

2. 2.

   None
3. 3.

   None
4. 4.

   None
5. 5.

   RUBY ❤️ POSTGRESQL RUBY ❤️ POSTGRESQL

6. 6.

   PostgreSQL is more than a datastore

7. 7.

   PostgreSQL is a relational database

8. 8.

   PostgreSQL is webscale

9. 9.

   PostgreSQL is webscale

10. 10.

    PostgreSQL is open-source

11. 11.

    PostgreSQL has a great community

12. 12.

    PostgreSQL is mature!

13. 13.

    ! ! ! PostgreSQL will do your coffee

14. 14.

    ⚡️POWER⚡️

15. 15.

    • MODELS • QUERIES • SPEED What?

16. 16.

    MODELS

17. 17.

    have not integer primary keys. YOU CAN

18. 18.

    UUID

19. 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. 20.

    add “columns” without migrations. YOU CAN

21. 21.

    HSTORE

22. 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. 23.

    cache API results. YOU CAN

24. 24.

    JSON

25. 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. 26.

    • UUID • HSTORE • JSON Recap

27. 27.

    ! ! QUERIES

28. 28.

    have understandable hard queries. YOU CAN

29. 29.

    AKA WITH clause CTE

30. 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. 31.

    have slow queries to fast read only models. YOU CAN

32. 32.

    VIEWS MATERIALIZED

33. 33.

    PSQL> CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users

    WHERE bad = false; ! class BadUser < User def readonly? true end end
34. 34.

    search your models. YOU CAN

35. 35.

    SEARCH FULL TEXT

36. 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. 37.

    • CTE • Materialized Views • Full text search Recap

38. 38.

    ! ! SPEED

39. 39.

    index more than one column. YOU CAN

40. 40.

    INDEXES MULTICOLUMN

41. 41.

    SELECT * FROM posts WHERE !banned AND poster_id = 6;

    ! SELECT * FROM posts WHERE banned; ! CREATE INDEX ON posts(banned, poster_id);
42. 42.

    index only a part of your rows. YOU CAN

43. 43.

    INDEXES PARTIAL

44. 44.

    SELECT * FROM posts WHERE !banned AND poster_id = 6;

    ! CREATE INDEX ON poster_id WHERE !banned;
45. 45.

    have transformations in you indexes. YOU CAN

46. 46.

    INDEXES FUNCTIONAL

47. 47.

    SELECT * FROM users WHERE lower(email) = ‘yannick@heroku.com’; ! CREATE

    INDEX ON users(lower(email));
48. 48.

    • Partial • Multicolumn • Functional Recap

49. 49.

    ! TOOLS MOAR

50. 50.

    CONTENT SHARE

51. 51.

    PUT IMAGE OF DATACLIPS AND WILL

52. 52.

    STATS GET QUERIES

53. 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. 54.

    PUT IMAGE OF EXPENSIVE QUERIES AND TIM

55. 55.

    USAGE UNDERSTAND

56. 56.

    None
57. 57.

    • Dataclips • Queries Stats • Global usage Recap

58. 58.

    PostgreSQL is made of unicorns and rainbows!

59. 59.

    PostgreSQL is made of unicorns and rainbows!

60. 60.

    Thanks!

61. 61.

    Questions?