Ruby loves Postgres

Rulu 2014 talk

Transcript

1. Hello, I’m
   Yannick
   

   View Slide

2. View Slide

3. View Slide

4. View Slide

5. RUBY ❤️ POSTGRESQL
   RUBY ❤️ POSTGRESQL
   

   View Slide

6. PostgreSQL
   is more than a
   datastore
   

   View Slide

7. PostgreSQL
   is a relational
   database
   

   View Slide

8. PostgreSQL
   is webscale
   

   View Slide

9. PostgreSQL
   is webscale
   

   View Slide

10. PostgreSQL
    is open-source
    

    View Slide

11. PostgreSQL
    has a great
    community
    

    View Slide

12. PostgreSQL
    is mature!
    

    View Slide

13. !
    !
    !
    PostgreSQL
    will do your coffee
    

    View Slide

14. ⚡️POWER⚡️
    

    View Slide

15. • MODELS
    • QUERIES
    • SPEED
    What?
    

    View Slide

16. MODELS
    

    View Slide

17. have not integer
    primary keys.
    YOU CAN
    

    View Slide

18. UUID
    

    View Slide

19. run 'CREATE EXTENSION “uuid-ossp"'
    !
    create_table(:users), id: :uuid do
    t.string :name
    end
    !
    User.create
    #=> #”10e43f5f-713f-4efa-
    b225-11800777a322"}>
    

    View Slide

20. add “columns”
    without migrations.
    YOU CAN
    

    View Slide

21. HSTORE
    

    View Slide

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
    

    View Slide

23. cache API results.
    YOU CAN
    

    View Slide

24. JSON
    

    View Slide

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’
    

    View Slide

26. • UUID
    • HSTORE
    • JSON
    Recap
    

    View Slide

27. !
    !
    QUERIES
    

    View Slide

28. have understandable
    hard queries.
    YOU CAN
    

    View Slide

29. AKA WITH clause
    CTE
    

    View Slide

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
    

    View Slide

31. have slow queries
    to fast read only models.
    YOU CAN
    

    View Slide

32. VIEWS
    MATERIALIZED
    

    View Slide

33. PSQL> CREATE MATERIALIZED VIEW bad_users AS
    SELECT * FROM users WHERE bad = false;
    !
    class BadUser < User
    def readonly?
    true
    end
    end
    

    View Slide

34. search your models.
    YOU CAN
    

    View Slide

35. SEARCH
    FULL TEXT
    

    View Slide

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));
    

    View Slide

37. • CTE
    • Materialized Views
    • Full text search
    Recap
    

    View Slide

38. !
    !
    SPEED
    

    View Slide

39. index more than one column.
    YOU CAN
    

    View Slide

40. INDEXES
    MULTICOLUMN
    

    View Slide

41. SELECT * FROM posts
    WHERE !banned AND poster_id = 6;
    !
    SELECT * FROM posts
    WHERE banned;
    !
    CREATE INDEX ON posts(banned, poster_id);
    

    View Slide

42. index only a part of your rows.
    YOU CAN
    

    View Slide

43. INDEXES
    PARTIAL
    

    View Slide

44. SELECT * FROM posts
    WHERE !banned AND poster_id = 6;
    !
    CREATE INDEX ON poster_id WHERE !banned;
    

    View Slide

45. have transformations
    in you indexes.
    YOU CAN
    

    View Slide

46. INDEXES
    FUNCTIONAL
    

    View Slide

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

    View Slide

48. • Partial
    • Multicolumn
    • Functional
    Recap
    

    View Slide

49. !
    TOOLS
    MOAR
    

    View Slide

50. CONTENT
    SHARE
    

    View Slide

51. PUT IMAGE OF DATACLIPS AND WILL
    

    View Slide

52. STATS
    GET QUERIES
    

    View Slide

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
    

    View Slide

54. PUT IMAGE OF EXPENSIVE QUERIES AND TIM
    

    View Slide

55. USAGE
    UNDERSTAND
    

    View Slide

56. View Slide

57. • Dataclips
    • Queries Stats
    • Global usage
    Recap
    

    View Slide

58. PostgreSQL
    is made of unicorns and
    rainbows!
    

    View Slide

59. PostgreSQL
    is made of unicorns and
    rainbows!
    
    

    View Slide

60. Thanks!
    

    View Slide

61. Questions?
    

    View Slide