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
FindyにおけるTakumi活用と脆弱性管理のこれから
rvirus0817
0
540
ファインディ株式会社におけるMCP活用とサービス開発
starfish719
0
2.1k
私の後悔をAWS DMSで解決した話
hiramax
4
210
Introducing ReActionView: A new ActionView-compatible ERB Engine @ Rails World 2025, Amsterdam
marcoroth
0
710
Things You Thought You Didn’t Need To Care About That Have a Big Impact On Your Job
hollycummins
0
110
基礎から学ぶ大画面対応(Learning Large-Screen Support from the Ground Up)
tomoya0x00
0
4.3k
アセットのコンパイルについて
ojun9
0
130
請來的 AI Agent 同事們在寫程式時,怎麼用 pytest 去除各種幻想與盲點
keitheis
0
130
Compose Multiplatform × AI で作る、次世代アプリ開発支援ツールの設計と実装
thagikura
0
170
Performance for Conversion! 分散トレーシングでボトルネックを 特定せよ
inetand
0
3.4k
CJK and Unicode From a PHP Committer
youkidearitai
PRO
0
110
Navigation 2 を 3 に移行する(予定)ためにやったこと
yokomii
0
350
Featured
See All Featured
Designing Experiences People Love
moore
142
24k
Thoughts on Productivity
jonyablonski
70
4.8k
Product Roadmaps are Hard
iamctodd
PRO
54
11k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
52
5.6k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
285
14k
Making Projects Easy
brettharned
117
6.4k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
32
1.6k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
31
2.5k
StorybookのUI Testing Handbookを読んだ
zakiyama
31
6.1k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
29
2.9k
Fashionably flexible responsive web design (full day workshop)
malarkey
407
66k
The Cost Of JavaScript in 2023
addyosmani
53
8.9k
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?