Ruby loves Postgres

Ruby loves Postgres

Rulu 2014 talk

F17e757902ab6a18cba1bacaa976eb0f?s=128

Yannick Schutz

June 24, 2014
Tweet

Transcript

  1. 2.
  2. 3.
  3. 4.
  4. 16.
  5. 18.
  6. 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"}>
  7. 21.
  8. 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
  9. 24.
  10. 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
  11. 33.

    PSQL> CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users

    WHERE bad = false; ! class BadUser < User def readonly? true end end
  12. 38.
  13. 41.

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

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

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

    ! CREATE INDEX ON poster_id WHERE !banned;
  15. 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
  16. 56.
  17. 60.