Tweet
Tweet

Slide 1

Slide 1 text

Ruby+ Postgresql=♥♥♥♥

Slide 2

Slide 2 text

Hello, I’m Yannick

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

PostgreSQL is more than a datastore

Slide 6

Slide 6 text

PostgreSQL is a relational database

Slide 7

Slide 7 text

PostgreSQL is webscale

Slide 8

Slide 8 text

PostgreSQL is webscale

Slide 9

Slide 9 text

PostgreSQL is open-source

Slide 10

Slide 10 text

PostgreSQL is maintained

Slide 11

Slide 11 text

PostgreSQL has a great community

Slide 12

Slide 12 text

PostgreSQL has new awesome features coming!

Slide 13

Slide 13 text

PostgreSQL will do your coffee

Slide 14

Slide 14 text

Let me show you it’s POWER

Slide 15

Slide 15 text

PROBLEM

Slide 16

Slide 16 text

You fetch something from an API call. Like that Oauth call to twitter.

Slide 17

Slide 17 text

a) You add a new cache layer like Redis b) You add a table with all the fields c) You add fields to an existing model d) Something else?

Slide 18

Slide 18 text

a) You add a new cache layer like Redis b) You add a table with all the fields c) You add fields to an existing model d) Something else?

Slide 19

Slide 19 text

a) You add a new cache layer like Redis b) You add a table with all the fields c) You add fields to an existing model d) Something else?

Slide 20

Slide 20 text

a) You add a new cache layer like Redis b) You add a table with all the fields c) You add fields to an existing model d) Something else?

Slide 21

Slide 21 text

a) You add a new cache layer like Redis b) You add a table with all the fields c) You add fields to an existing model d) Something else?

Slide 22

Slide 22 text

JSON

Slide 23

Slide 23 text

class AddJsonPayloadToTwitterAuth < ActiveRecord::Migration def change add_column(:twitter_auths, :json_payload, :json) end end

Slide 24

Slide 24 text

PostgreSQL learn you japanese

Slide 25

Slide 25 text

PROBLEM

Slide 26

Slide 26 text

You add new user settings every two weeks and needs new avatar sizes every three.

Slide 27

Slide 27 text

a) You store that in Redis b) You add a table with all the fields c) You add fields to the existing model d) Something else?

Slide 28

Slide 28 text

a) You store that in Redis b) You add a table with all the fields c) You add fields to the existing model d) Something else?

Slide 29

Slide 29 text

a) You store that in Redis b) You add a table with all the fields c) You add fields to the existing model d) Something else?

Slide 30

Slide 30 text

a) You store that in Redis b) You add a table with all the fields c) You add fields to the existing model d) Something else?

Slide 31

Slide 31 text

a) You store that in Redis b) You add a table with all the fields c) You add fields to the existing model d) Something else?

Slide 32

Slide 32 text

HSTORE

Slide 33

Slide 33 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 34

Slide 34 text

PostgreSQL is not mysql

Slide 35

Slide 35 text

PROBLEM

Slide 36

Slide 36 text

You have a really complex query that is blazing fast but when you come back a week after you’re like ‘WTF is that!’

Slide 37

Slide 37 text

a) You keep it unreadable b) You change the behavior c) Something else?

Slide 38

Slide 38 text

a) You keep it unreadable b) You change the behavior c) Something else?

Slide 39

Slide 39 text

a) You keep it unreadable b) You change the behavior c) Something else?

Slide 40

Slide 40 text

a) You keep it unreadable b) You change the behavior c) Something else?

Slide 41

Slide 41 text

CTE AKA WITH clause

Slide 42

Slide 42 text

def bad_users_with_karma User.find_by_sql(query) end ! def query <<-SQL WITH bad_users AS (SELECT * FROM users WHERE bad = true) SELECT * FROM bad_users WHERE karma > #{karma_limit} SQL end

Slide 43

Slide 43 text

VIEWS THANKS PSQL

Slide 44

Slide 44 text

PostgreSQL believes you can fly

Slide 45

Slide 45 text

PROBLEM

Slide 46

Slide 46 text

You really need to add all this complexity that makes that query run in seconds…

Slide 47

Slide 47 text

a) You use a complex query b) You denormalize in MongoDB c) Something else?

Slide 48

Slide 48 text

a) You use a complex query b) You denormalize in MongoDB c) Something else?

Slide 49

Slide 49 text

a) You use a complex query b) You denormalize in MongoDB c) Something else?

Slide 50

Slide 50 text

a) You use a complex query b) You denormalize in MongoDB c) Something else?

Slide 51

Slide 51 text

VIEWS

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

PostgreSQL might contains kittens and unicorns

Slide 54

Slide 54 text

PROBLEM

Slide 55

Slide 55 text

You need to search your users by username, name and email.

Slide 56

Slide 56 text

a) You add that elastic search b) You select in ruby c) Something else?

Slide 57

Slide 57 text

a) You add that elastic search b) You select in ruby c) Something else?

Slide 58

Slide 58 text

a) You add that elastic search b) You select in ruby c) Something else?

Slide 59

Slide 59 text

a) You add that elastic search b) You select in ruby c) Something else?

Slide 60

Slide 60 text

SEARCH

Slide 61

Slide 61 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 62

Slide 62 text

PostgreSQL comes in multiple flavors and colors

Slide 63

Slide 63 text

Now, you know that the elephant database is more than a datastore!

Slide 64

Slide 64 text

Thanks!

Slide 65

Slide 65 text

Questions?

Slide 66

Slide 66 text

Questions?