$30 off During Our Annual Pro Sale. View Details »

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

  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?