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
1.1k
Postgresql + Ruby = :heart:
ys
4
320
RubyMotion - Apéro Ruby Paris 05-10-2012
ys
1
3k
Other Decks in Programming
See All in Programming
AIと”コードの評価関数”を共有する / Share the "code evaluation function" with AI
euglena1215
1
180
AI時代の『改訂新版 良いコード/悪いコードで学ぶ設計入門』 / ai-good-code-bad-code
minodriven
23
9.2k
Porting a visionOS App to Android XR
akkeylab
0
660
CDK引数設計道場100本ノック
badmintoncryer
2
430
初学者でも今すぐできる、Claude Codeの生産性を10倍上げるTips
s4yuba
16
12k
猫と暮らす Google Nest Cam生活🐈 / WebRTC with Google Nest Cam
yutailang0119
0
170
AI時代のソフトウェア開発を考える(2025/07版) / Agentic Software Engineering Findy 2025-07 Edition
twada
PRO
97
35k
新メンバーも今日から大活躍!SREが支えるスケールし続ける組織のオンボーディング
honmarkhunt
5
8.4k
AIプログラマーDevinは PHPerの夢を見るか?
shinyasaita
1
240
チームのテスト力を総合的に鍛えて品質、スピード、レジリエンスを共立させる/Testing approach that improves quality, speed, and resilience
goyoki
5
1.1k
リバースエンジニアリング新時代へ! GhidraとClaude DesktopをMCPで繋ぐ/findy202507
tkmru
2
390
「テストは愚直&&網羅的に書くほどよい」という誤解 / Test Smarter, Not Harder
munetoshi
0
190
Featured
See All Featured
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
21
1.3k
How to Think Like a Performance Engineer
csswizardry
25
1.7k
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
10
970
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
3.9k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
29
9.6k
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
Code Review Best Practice
trishagee
69
19k
4 Signs Your Business is Dying
shpigford
184
22k
Rebuilding a faster, lazier Slack
samanthasiow
83
9.1k
The Invisible Side of Design
smashingmag
301
51k
The Art of Programming - Codeland 2020
erikaheidi
54
13k
The Pragmatic Product Professional
lauravandoore
35
6.7k
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?