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
Modern Angular: Renovation for Your Applications
manfredsteyer
PRO
0
200
AWS IaCの注目アップデート 2024年10月版
konokenj
3
3.1k
Importmapを使ったJavaScriptの 読み込みとブラウザアドオンの影響
swamp09
4
1.2k
CSC305 Lecture 13
javiergs
PRO
0
130
【Kaigi on Rails 2024】YOUTRUST スポンサーLT
krpk1900
1
240
Kubernetes for Data Engineers: Building Scalable, Reliable Data Pipelines
sucitw
1
190
開発効率向上のためのリファクタリングの一歩目の選択肢 ~コード分割~ / JJUG CCC 2024 Fall
ryounasso
0
360
Kaigi on Rails 2024 - Rails APIモードのためのシンプルで効果的なCSRF対策 / kaigionrails-2024-csrf
corocn
5
3.3k
/←このスケジュール表に立ち向かう フロントエンド開発戦略 / A front-end development strategy to tackle a single-slash schedule.
nrslib
1
590
デプロイを任されたので、教わった通りにデプロイしたら障害になった件 ~俺のやらかしを越えてゆけ~
techouse
52
32k
gopls を改造したら開発生産性が高まった
satorunooshie
8
240
LLM生成文章の精度評価自動化とプロンプトチューニングの効率化について
layerx
PRO
2
130
Featured
See All Featured
The Illustrated Children's Guide to Kubernetes
chrisshort
48
48k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
107
49k
What's new in Ruby 2.0
geeforr
342
31k
Six Lessons from altMBA
skipperchong
26
3.5k
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
4
290
Done Done
chrislema
181
16k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
28
9.1k
Building a Modern Day E-commerce SEO Strategy
aleyda
38
6.9k
4 Signs Your Business is Dying
shpigford
180
21k
Code Reviewing Like a Champion
maltzj
519
39k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
47
5k
A designer walks into a library…
pauljervisheath
202
24k
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?