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

Ruby loves Postgres

Ruby loves Postgres

Rulu 2014 talk

Avatar for Yannick Schutz

Yannick Schutz

June 24, 2014
Tweet

More Decks by Yannick Schutz

Other Decks in Programming

Transcript

  1. 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"}>
  2. 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
  3. 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
  4. PSQL> CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users

    WHERE bad = false; ! class BadUser < User def readonly? true end end
  5. SELECT * FROM posts WHERE !banned AND poster_id = 6;

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

    ! CREATE INDEX ON poster_id WHERE !banned;
  7. 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