Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Ruby loves Postgres
Search
Yannick Schutz
June 24, 2014
Programming
1
150
Ruby loves Postgres
Rulu 2014 talk
Yannick Schutz
June 24, 2014
Tweet
Share
More Decks by Yannick Schutz
See All by Yannick Schutz
All those bots are gonna steal your job
ys
1
1k
Postgresql + Ruby = :heart:
ys
4
300
RubyMotion - Apéro Ruby Paris 05-10-2012
ys
1
3k
Other Decks in Programming
See All in Programming
日付と正規化
megmogmog1965
0
140
君たちはどうコードをレビューする (される) か / 大吉祥寺.pm
utgwkk
15
8.5k
最古の関数型言語「Lisp」ことはじめ / lisp_in_kamiyama
uhooi
1
190
Polarsの成長: v0.14からv1.0までの変遷と今後の展望
zerebom
1
350
CSC307 Lecture 10
javiergs
PRO
0
310
Temporalを取り巻く仕様を整理する
sajikix
0
110
ドメイン駆動設計の実践
masuda220
PRO
17
5.1k
12年前の『型システム入門』翻訳の思い出話
mame
11
1.2k
AHC035解説
terryu16
0
710
Async Await: Mastering Python's Time-Bending Tricks - EuroPython2024
yanbo
1
290
Namespace on read
tagomoris
2
370
入社1ヶ月でここまでやった!Findy Toolsインフラ支援の最適化
rvirus0817
6
1.4k
Featured
See All Featured
Clear Off the Table
cherdarchuk
89
320k
Web Components: a chance to create the future
zenorocha
307
41k
The Straight Up "How To Draw Better" Workshop
denniskardys
229
130k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
360
22k
What's in a price? How to price your products and services
michaelherold
239
11k
In The Pink: A Labor of Love
frogandcode
139
22k
Keith and Marios Guide to Fast Websites
keithpitt
408
22k
Leading Effective Engineering Teams 2024
addyosmani
3
300
The Brand Is Dead. Long Live the Brand.
mthomps
52
36k
Optimising Largest Contentful Paint
csswizardry
18
2.6k
Intergalactic Javascript Robots from Outer Space
tanoku
266
26k
GitHub's CSS Performance
jonrohan
1026
450k
Transcript
Hello, I’m Yannick
None
None
None
RUBY ❤️ POSTGRESQL RUBY ❤️ POSTGRESQL
PostgreSQL is more than a datastore
PostgreSQL is a relational database
PostgreSQL is webscale
PostgreSQL is webscale
PostgreSQL is open-source
PostgreSQL has a great community
PostgreSQL is mature!
! ! ! PostgreSQL will do your coffee
⚡️POWER⚡️
• MODELS • QUERIES • SPEED What?
MODELS
have not integer primary keys. YOU CAN
UUID
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"}>
add “columns” without migrations. YOU CAN
HSTORE
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
cache API results. YOU CAN
JSON
! 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’
• UUID • HSTORE • JSON Recap
! ! QUERIES
have understandable hard queries. YOU CAN
AKA WITH clause CTE
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
have slow queries to fast read only models. YOU CAN
VIEWS MATERIALIZED
PSQL> CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users
WHERE bad = false; ! class BadUser < User def readonly? true end end
search your models. YOU CAN
SEARCH FULL 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));
• CTE • Materialized Views • Full text search Recap
! ! SPEED
index more than one column. YOU CAN
INDEXES MULTICOLUMN
SELECT * FROM posts WHERE !banned AND poster_id = 6;
! SELECT * FROM posts WHERE banned; ! CREATE INDEX ON posts(banned, poster_id);
index only a part of your rows. YOU CAN
INDEXES PARTIAL
SELECT * FROM posts WHERE !banned AND poster_id = 6;
! CREATE INDEX ON poster_id WHERE !banned;
have transformations in you indexes. YOU CAN
INDEXES FUNCTIONAL
SELECT * FROM users WHERE lower(email) = ‘
[email protected]
’; ! CREATE
INDEX ON users(lower(email));
• Partial • Multicolumn • Functional Recap
! TOOLS MOAR
CONTENT SHARE
PUT IMAGE OF DATACLIPS AND WILL
STATS GET QUERIES
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
PUT IMAGE OF EXPENSIVE QUERIES AND TIM
USAGE UNDERSTAND
None
• Dataclips • Queries Stats • Global usage Recap
PostgreSQL is made of unicorns and rainbows!
PostgreSQL is made of unicorns and rainbows!
Thanks!
Questions?