Ruby loves Postgres - Speaker Deck

Slide 1

Slide 1 text

Hello, I’m Yannick

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

RUBY ❤️ POSTGRESQL RUBY ❤️ POSTGRESQL

Slide 6

Slide 6 text

PostgreSQL is more than a datastore

Slide 7

Slide 7 text

PostgreSQL is a relational database

Slide 8

Slide 8 text

PostgreSQL is webscale

Slide 9

Slide 9 text

PostgreSQL is webscale

Slide 10

Slide 10 text

PostgreSQL is open-source

Slide 11

Slide 11 text

PostgreSQL has a great community

Slide 12

Slide 12 text

PostgreSQL is mature!

Slide 13

Slide 13 text

! ! ! PostgreSQL will do your coﬀee

Slide 14

Slide 14 text

⚡️POWER⚡️

Slide 15

Slide 15 text

• MODELS • QUERIES • SPEED What?

Slide 16

Slide 16 text

MODELS

Slide 17

Slide 17 text

have not integer primary keys. YOU CAN

Slide 18

Slide 18 text

UUID

Slide 19

Slide 19 text

run 'CREATE EXTENSION “uuid-ossp"' ! create_table(:users), id: :uuid do t.string :name end ! User.create #=> #”10e43f5f-713f-4efa- b225-11800777a322"}>

Slide 20

Slide 20 text

add “columns” without migrations. YOU CAN

Slide 21

Slide 21 text

HSTORE

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

cache API results. YOU CAN

Slide 24

Slide 24 text

JSON

Slide 25

Slide 25 text

! 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’

Slide 26

Slide 26 text

• UUID • HSTORE • JSON Recap

Slide 27

Slide 27 text

! ! QUERIES

Slide 28

Slide 28 text

have understandable hard queries. YOU CAN

Slide 29

Slide 29 text

AKA WITH clause CTE

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

have slow queries to fast read only models. YOU CAN

Slide 32

Slide 32 text

VIEWS MATERIALIZED

Slide 33

Slide 33 text

PSQL> CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users WHERE bad = false; ! class BadUser < User def readonly? true end end

Slide 34

Slide 34 text

search your models. YOU CAN

Slide 35

Slide 35 text

SEARCH FULL TEXT

Slide 36

Slide 36 text

# 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));

Slide 37

Slide 37 text

• CTE • Materialized Views • Full text search Recap

Slide 38

Slide 38 text

! ! SPEED

Slide 39

Slide 39 text

index more than one column. YOU CAN

Slide 40

Slide 40 text

INDEXES MULTICOLUMN

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

index only a part of your rows. YOU CAN

Slide 43

Slide 43 text

INDEXES PARTIAL

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

have transformations in you indexes. YOU CAN

Slide 46

Slide 46 text

INDEXES FUNCTIONAL

Slide 47

Slide 47 text

SELECT * FROM users WHERE lower(email) = ‘[email protected]’; ! CREATE INDEX ON users(lower(email));

Slide 48

Slide 48 text

• Partial • Multicolumn • Functional Recap

Slide 49

Slide 49 text

! TOOLS MOAR

Slide 50

Slide 50 text

CONTENT SHARE

Slide 51

Slide 51 text

PUT IMAGE OF DATACLIPS AND WILL

Slide 52

Slide 52 text

STATS GET QUERIES

Slide 53

Slide 53 text

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