Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Ruby loves Postgres

Ruby loves Postgres

Rulu 2014 talk

Yannick Schutz

June 24, 2014
Tweet

More Decks by Yannick Schutz

Other Decks in Programming

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) = ‘[email protected]’;
    !
    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