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
Postgres What they really use
Search
Craig Kerstiens
November 01, 2013
Technology
6
850
Postgres What they really use
Some insight into what features and functionality people actually use within their database.
Craig Kerstiens
November 01, 2013
Tweet
Share
More Decks by Craig Kerstiens
See All by Craig Kerstiens
Product planning w/ gridding - Effort vs. Impact rule of thirds
craigkerstiens
0
280
Five sharding data models and which is right? PGDay Nordic
craigkerstiens
0
180
Postgres at any scale
craigkerstiens
1
780
Five data models for sharding and which is right
craigkerstiens
0
120
Postgres Performance for Humans - All things Open
craigkerstiens
1
280
Postgres Performance for Humans - PyCaribbean
craigkerstiens
1
140
Postgres present and future
craigkerstiens
1
330
Marketing for Developers
craigkerstiens
0
220
Postgres – A Data Platform
craigkerstiens
2
440
Other Decks in Technology
See All in Technology
ビジネスモデリング道場 目的と背景
masuda220
PRO
9
680
Tech Blogを書きやすい環境づくり
lycorptech_jp
PRO
1
270
プロダクトエンジニア 360°フィードバックを実施した話
hacomono
PRO
0
130
大規模アジャイルフレームワークから学ぶエンジニアマネジメントの本質
staka121
PRO
2
100
AIエージェント元年
shukob
0
140
依存パッケージの更新はコツコツが勝つコツ! / phpcon_nagoya2025
blue_goheimochi
3
190
IAMポリシーのAllow/Denyについて、改めて理解する
smt7174
2
170
The Future of SEO: The Impact of AI on Search
badams
0
250
転生CISOサバイバル・ガイド / CISO Career Transition Survival Guide
kanny
3
1.1k
コンテナサプライチェーンセキュリティ
kyohmizu
1
120
ソフトウェアエンジニアと仕事するときに知っておいたほうが良いこと / Key points for working with software engineers
pinkumohikan
1
130
白金鉱業Meetup Vol.17_あるデータサイエンティストのデータマネジメントとの向き合い方
brainpadpr
7
950
Featured
See All Featured
YesSQL, Process and Tooling at Scale
rocio
172
14k
GitHub's CSS Performance
jonrohan
1030
460k
Bash Introduction
62gerente
611
210k
Rebuilding a faster, lazier Slack
samanthasiow
80
8.8k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
How to Ace a Technical Interview
jacobian
276
23k
Scaling GitHub
holman
459
140k
Making Projects Easy
brettharned
116
6k
Agile that works and the tools we love
rasmusluckow
328
21k
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
7
640
Facilitating Awesome Meetings
lara
52
6.2k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
160
15k
Transcript
@craigkerstiens Postgres What they really use
Interrupt me Ask questions craig@heroku.com
None
@craigkerstiens Postgres What they really use
Shameless plugs http://www.postgresweekly.com http://www.craigkerstiens.com http://www.postgresguide.com http://www.postgresapp.com http://postgres.heroku.com
Postgres - TLDR
Postgres - TLDR Datatypes Conditional Indexes Transactional DDL Foreign Data
Wrappers Concurrent Index Creation Extensions Common Table Expressions Fast Column Addition Listen/Notify Table Inheritance Per Transaction sync replication Window functions NoSQL inside SQL Momentum
What they really use ?
Why listen to what I say?
Largest fleet of Postgres in the world
Over 1 billion write transactions a day
What they really use ?
Production
43% on 9.1 2% on 9.0 54% on 9.2 Versions
Extensions
hstore pg_stat_statements postgis uuid-ossp pg_trgm unaccent fuzzystrmatch dblink cube pgcrypto
earthdistance tablefunc citext
extension adoption hstore 11.5% pg_stat_statements 3.5% postgis 3% uuid-ossp 3%
pg_trgm 3% unaccent 1.5% fuzzystrmatch 1.5% dblink 1.5% cube 1% pg_crypto 1% earthdistance 1% tablefunc 0.75% citext 0.5%
17% at least 1 of those 22% have 2 8%
have 3 2.5% have 4 .7% have 5 .2% have 11
PLV8 CREATE FUNCTION js_filter(js_function text, json_arguments text, data json) RETURNS
numeric as $$ var func = eval(js_function); var args = eval(json_arguments); var final_args = [data].concat(args); var result = func.apply(null, final_args); return 0 < result ? 1 : 0; $$ LANGUAGE plv8 IMMUTABLE STRICT;
PLV8 SELECT json_obj FROM some_table_with_json_obj_column WHERE js_filter( 'function (json, age)
{return json.age < age; }', '21', data.json_obj ) = 1; https://github.com/webnuts/full-throttle-postgres
Indexes
99.9% have an index 28% have gin 13% have gist
92% have unique 8% have conditional
Waste?
Unused Indexes
23% over 1000 rows 13% over 10000 rows 5% over
100000 rows 1.5% over 1 million rows 2% over 100 million rows
Bloat
0.1% over 100 GB 1.5% over 10 GB 8.7% over
1 GB 22.9% over 100 MB
2.3% over 100 MB and 5x bloat factor
Pg Extras https://github.com/heroku/heroku-pg-extras/
command usage index_usage 25.5% locks 19.0% cache_hit 18.0% blocking 7.5%
index_size 7.5% outliers 5.5% vacuum_stats 4.0% bloat 4.0% total_index_size 3.0% unused_indexes 2.0%
Problems new users face?
1. What do I need to pay attention to? 2.
How do I setup replication? 3. What editors are available? 4. How do I understand performance? 5. How can I use the cool stuff in my app? Top 5
Questions