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

Ruby loves Postgres

Ruby loves Postgres

Rulu 2014 talk

F17e757902ab6a18cba1bacaa976eb0f?s=128

Yannick Schutz

June 24, 2014
Tweet

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?