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.2k
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
GraphQL×Railsアプリのデータベース負荷分散 - 月間3,000万人利用サービスを無停止で
koxya
1
1.2k
Le côté obscur des IA génératives
pascallemerrer
0
130
高度なUI/UXこそHotwireで作ろう Kaigi on Rails 2025
naofumi
4
3.7k
育てるアーキテクチャ:戦い抜くPythonマイクロサービスの設計と進化戦略
fujidomoe
1
160
エンジニアとして高みを目指す、 利益を生み出す設計の考え方 / design-for-profit
minodriven
23
12k
ポスターセッション: 「まっすぐ行って、右!」って言ってラズパイカーを動かしたい 〜生成AI × Raspberry Pi Pico × Gradioの試作メモ〜
komofr
0
1.2k
dynamic!
moro
9
6.9k
CI_CD「健康診断」のススメ。現場でのボトルネック特定から、健康診断を通じた組織的な改善手法
teamlab
PRO
0
190
CSC509 Lecture 04
javiergs
PRO
0
300
Railsだからできる 例外業務に禍根を残さない 設定設計パターン
ei_ei_eiichi
0
380
Introducing ReActionView: A new ActionView-Compatible ERB Engine @ Kaigi on Rails 2025, Tokyo, Japan
marcoroth
3
960
10年もののAPIサーバーにおけるCI/CDの改善の奮闘
mbook
0
790
Featured
See All Featured
Bootstrapping a Software Product
garrettdimon
PRO
307
110k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
9
850
The Invisible Side of Design
smashingmag
301
51k
GitHub's CSS Performance
jonrohan
1032
460k
Code Reviewing Like a Champion
maltzj
525
40k
KATA
mclloyd
32
15k
Producing Creativity
orderedlist
PRO
347
40k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
32
1.6k
Fashionably flexible responsive web design (full day workshop)
malarkey
407
66k
The Pragmatic Product Professional
lauravandoore
36
6.9k
Statistics for Hackers
jakevdp
799
220k
For a Future-Friendly Web
brad_frost
180
9.9k
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?