Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Postgresql + Ruby = :heart:
Yannick Schutz
April 08, 2014
Programming
4
290
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
810
Ruby loves Postgres
ys
1
140
RubyMotion - Apéro Ruby Paris 05-10-2012
ys
1
3k
Other Decks in Programming
See All in Programming
확장 가능한 테라폼 코드 관리 (Scalable Terraform Code Management)
posquit0
1
320
Modern Web Apps with Spring Boot, Angular & TypeScript
toedter
12
14k
未経験QAの私が、よきQA(Question Asker) になっていく物語
atamaplus
0
310
TechFeed Conference 2022 - Kotlin Experimental
jmatsu
0
820
【Qiita Night】新卒エンジニアによるSwift6与太予想
eiji127
0
180
CIでAndroidUIテストの様子を録画してみた
mkeeda
0
180
Update from the Elixir team - 2022
whatyouhide
0
200
Jetpack Compose 頑張らないPreviewParameterProvider
horie23
0
110
Learning DDD輪読会#4 / Learning DDD Book Club #4
suzushin54
1
150
Cloud-Conference-Day-Spring Cloud + Spring Webflux: como desenvolver seu primeiro microsserviço reativo em Java?
kamilahsantos
1
140
インフラエンジニアの多様性と評価、またはキャリアへのつなげ方 / Careers as infrastructure engineers
katsuhisa91
0
530
코드 품질 1% 올리기
pluu
1
1k
Featured
See All Featured
Optimizing for Happiness
mojombo
365
63k
The Cult of Friendly URLs
andyhume
68
4.7k
Rebuilding a faster, lazier Slack
samanthasiow
62
7.2k
BBQ
matthewcrist
74
7.9k
Web development in the modern age
philhawksworth
197
9.3k
Building a Scalable Design System with Sketch
lauravandoore
447
30k
Design by the Numbers
sachag
271
17k
The World Runs on Bad Software
bkeepers
PRO
56
5.2k
Producing Creativity
orderedlist
PRO
333
37k
How to Ace a Technical Interview
jacobian
265
21k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
315
19k
It's Worth the Effort
3n
172
25k
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?