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
950
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
Micro Frontends for Java Microservices - Devnexus 2024
mraible
PRO
0
430
ログラスを支える設計標準について / loglass-design-standards
urmot
10
2.1k
Build with AI 2024 Seoul - 제로부터 시작하는 Flutter with Gemini 생활 - 박제창
itsmedreamwalker
0
200
Changed Rules: Architectures with Lightweight Stores
manfredsteyer
PRO
0
230
Prepare for Jakarta EE 11 - Performance and Developer Productivity
ivargrimstad
0
470
Doctrine ORMでValue Objectを扱う方法4選 #phpstudy / 4 ways to handle Value Objects with Doctrine ORM
77web
4
110
From Spring Boot 2 to Spring Boot 3 with Java 21 and Jakarta EE
ivargrimstad
0
1.1k
チーム力を高めるスクラム実践法:カンバン公開と課題攻略について - ニフティのスクラムトーク Vol. 2 - NIFTY Tech Talk #18
niftycorp
PRO
1
110
1인 개발자로 행복하게 살기 - GDG 송도 헬로월드 2024
benjaminkim
1
5.6k
コードレビューで学ぶ!Kotlinオブジェクト指向デザインパターン
akkie76
2
180
SpringBoot+MyBatisで例外が出たときどこを見るか
syukai
0
110
ゆるい個人開発のススメ
kuroppe1819
10
950
Featured
See All Featured
WebSockets: Embracing the real-time Web
robhawkes
59
7k
The Invisible Customer
myddelton
114
12k
The Straight Up "How To Draw Better" Workshop
denniskardys
227
130k
The Mythical Team-Month
searls
215
42k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
186
16k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
24
2.3k
How to Ace a Technical Interview
jacobian
272
22k
The MySQL Ecosystem @ GitHub 2015
samlambert
242
12k
Robots, Beer and Maslow
schacon
PRO
155
7.9k
Practical Orchestrator
shlominoach
181
9.7k
The Language of Interfaces
destraynor
151
23k
Fontdeck: Realign not Redesign
paulrobertlloyd
76
4.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?