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
Postgresql + Ruby = :heart:
Search
Yannick Schutz
April 08, 2014
Programming
4
320
Postgresql + Ruby = :heart:
How you could use postgresql in ways you didn't imagine when using it as a simple datastore.
Yannick Schutz
April 08, 2014
Tweet
Share
More Decks by Yannick Schutz
See All by Yannick Schutz
All those bots are gonna steal your job
ys
1
1.1k
Ruby loves Postgres
ys
1
150
RubyMotion - Apéro Ruby Paris 05-10-2012
ys
1
3k
Other Decks in Programming
See All in Programming
第9回 情シス転職ミートアップ 株式会社IVRy(アイブリー)の紹介
ivry_presentationmaterials
1
240
関数型まつりレポート for JuliaTokai #22
antimon2
0
150
「Cursor/Devin全社導入の理想と現実」のその後
saitoryc
0
150
Benchmark
sysong
0
270
iOSアプリ開発で 関数型プログラミングを実現する The Composable Architectureの紹介
yimajo
2
210
プロダクト志向なエンジニアがもう一歩先の価値を目指すために意識したこと
nealle
0
110
『自分のデータだけ見せたい!』を叶える──Laravel × Casbin で複雑権限をスッキリ解きほぐす 25 分
akitotsukahara
1
550
PHP 8.4の新機能「プロパティフック」から学ぶオブジェクト指向設計とリスコフの置換原則
kentaroutakeda
2
540
データの民主化を支える、透明性のあるデータ利活用への挑戦 2025-06-25 Database Engineering Meetup#7
y_ken
0
320
Code as Context 〜 1にコードで 2にリンタ 34がなくて 5にルール? 〜
yodakeisuke
0
100
Railsアプリケーションと パフォーマンスチューニング ー 秒間5万リクエストの モバイルオーダーシステムを支える事例 ー Rubyセミナー 大阪
falcon8823
4
950
Cline指示通りに動かない? AI小説エージェントで学ぶ指示書の書き方と自動アップデートの仕組み
kamomeashizawa
1
570
Featured
See All Featured
It's Worth the Effort
3n
185
28k
The Straight Up "How To Draw Better" Workshop
denniskardys
234
140k
Building Better People: How to give real-time feedback that sticks.
wjessup
367
19k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
124
52k
Why Our Code Smells
bkeepers
PRO
337
57k
Building a Modern Day E-commerce SEO Strategy
aleyda
42
7.3k
Adopting Sorbet at Scale
ufuk
77
9.4k
How STYLIGHT went responsive
nonsquared
100
5.6k
A better future with KSS
kneath
239
17k
Typedesign – Prime Four
hannesfritz
42
2.7k
The Art of Programming - Codeland 2020
erikaheidi
54
13k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
507
140k
Transcript
Ruby+ Postgresql=♥♥♥♥
Hello, I’m Yannick
None
None
PostgreSQL is more than a datastore
PostgreSQL is a relational database
PostgreSQL is webscale
PostgreSQL is webscale
PostgreSQL is open-source
PostgreSQL is maintained
PostgreSQL has a great community
PostgreSQL has new awesome features coming!
PostgreSQL will do your coffee
Let me show you it’s POWER
PROBLEM
You fetch something from an API call. Like that Oauth
call to twitter.
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?
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?
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?
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?
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?
JSON
class AddJsonPayloadToTwitterAuth < ActiveRecord::Migration def change add_column(:twitter_auths, :json_payload, :json) end
end
PostgreSQL learn you japanese
PROBLEM
You add new user settings every two weeks and needs
new avatar sizes every three.
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?
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?
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?
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?
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?
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
PostgreSQL is not mysql
PROBLEM
You have a really complex query that is blazing fast
but when you come back a week after you’re like ‘WTF is that!’
a) You keep it unreadable b) You change the behavior
c) Something else?
a) You keep it unreadable b) You change the behavior
c) Something else?
a) You keep it unreadable b) You change the behavior
c) Something else?
a) You keep it unreadable b) You change the behavior
c) Something else?
CTE AKA WITH clause
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
VIEWS THANKS PSQL
PostgreSQL believes you can fly
PROBLEM
You really need to add all this complexity that makes
that query run in seconds…
a) You use a complex query b) You denormalize in
MongoDB c) Something else?
a) You use a complex query b) You denormalize in
MongoDB c) Something else?
a) You use a complex query b) You denormalize in
MongoDB c) Something else?
a) You use a complex query b) You denormalize in
MongoDB c) Something else?
VIEWS
CREATE MATERIALIZED VIEW bad_users AS SELECT * FROM users WHERE
bad = false; ! class BadUser < User def readonly? true end end
PostgreSQL might contains kittens and unicorns
PROBLEM
You need to search your users by username, name and
email.
a) You add that elastic search b) You select in
ruby c) Something else?
a) You add that elastic search b) You select in
ruby c) Something else?
a) You add that elastic search b) You select in
ruby c) Something else?
a) You add that elastic search b) You select in
ruby c) Something else?
SEARCH
# 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));
PostgreSQL comes in multiple flavors and colors
Now, you know that the elephant database is more than
a datastore!
Thanks!
Questions?
Questions?