Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Ruby loves Postgres
Yannick Schutz
June 24, 2014
Programming
1
140
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
810
Postgresql + Ruby = :heart:
ys
4
290
RubyMotion - Apéro Ruby Paris 05-10-2012
ys
1
3k
Other Decks in Programming
See All in Programming
Micro Frontends with Module Federation: Beyond the Basics @codecrafts2022
manfredsteyer
PRO
0
110
アプリのログをチーム外で活用してもらうためにやったこと
shotakashihara
0
190
microCMS × Shopifyで、ECサイトがリニューアル後急成長した話
microcms
0
470
よりUXに近いSLI・SLOの運用による可用性の再設計
kazumanagano
3
590
WindowsコンテナDojo:第2回 Windowsコンテナアプリのビルド、公開、デプロイ
oniak3ibm
PRO
0
140
機能横断型チームにおける技術改善
takeshiakutsu
3
470
Oculus Interaction SDK 概説 / xrdnk-caunity-LT4
xrdnk
0
190
byte列のbit表現を得るencodingライブラリ作った
convto
1
140
Git Rebase
bkuhlmann
7
1k
Swift Concurrencyによる安全で快適な非同期処理
tattn
2
320
Reactでアプリケーションを構築する多様化
sakito
4
3.4k
Composing an API with Kotlin (Kotlin Dev Day 2022)
zsmb
0
260
Featured
See All Featured
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
3
440
Code Reviewing Like a Champion
maltzj
506
37k
Fireside Chat
paigeccino
11
1.3k
Six Lessons from altMBA
skipperchong
14
1.3k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
268
11k
Reflections from 52 weeks, 52 projects
jeffersonlam
337
17k
Building a Scalable Design System with Sketch
lauravandoore
447
30k
Large-scale JavaScript Application Architecture
addyosmani
499
110k
Music & Morning Musume
bryan
35
4.1k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
226
15k
Faster Mobile Websites
deanohume
294
28k
Building Your Own Lightsaber
phodgson
94
4.6k
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) = ‘yannick@heroku.com’; ! 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?