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
820
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
250
Five sharding data models and which is right? PGDay Nordic
craigkerstiens
0
160
Postgres at any scale
craigkerstiens
1
690
Five data models for sharding and which is right
craigkerstiens
0
100
Postgres Performance for Humans - All things Open
craigkerstiens
1
250
Postgres Performance for Humans - PyCaribbean
craigkerstiens
1
130
Postgres present and future
craigkerstiens
1
330
Marketing for Developers
craigkerstiens
0
200
Postgres – A Data Platform
craigkerstiens
2
430
Other Decks in Technology
See All in Technology
GraphQLとスキーマファーストで切り開く ライドシェアの未来
ku0522a
0
490
FastlyとfalcoでNode.jsレスな Webサーバー構築 : IPTV版 ABEMAアプリインフラ刷新 / Building web server using Fastly and falco without Node.js for IPTV ABEMA app
nodaguti
0
240
Oracle Base Database Service:サービス概要のご紹介
oracle4engineer
PRO
0
13k
エンジニア向け会社紹介資料
caddi_eng
15
250k
2024年版 運用者たちのLLM
nwiizo
3
290
実践的なバグバウンティ入門
scgajge12
4
2.1k
#Zenoh 完全に理解した 〜組込み純情篇〜
takasehideki
1
450
自社開発した大規模言語モデルをどうプロダクションに乗せて運用していくか〜インフラ編〜
pfn
PRO
4
680
中規模・ミドルTier開発組織におけるDevRelの戦略と実行と成果 - DevRel Guild Conference Mini -
leveragestech
2
260
リクルートのデータマネジメント組織に 求められてきたコト
recruitengineers
PRO
4
340
歴史と背景から改めて振り返るVPC
shotashiratori
2
220
AWS版GitHub?Amazon CodeCatalystの全体像をまとめてみた
oshanqq
1
3.2k
Featured
See All Featured
ReactJS: Keep Simple. Everything can be a component!
pedronauck
662
120k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
226
52k
Building a Scalable Design System with Sketch
lauravandoore
458
32k
Happy Clients
brianwarren
96
6.6k
RailsConf 2023
tenderlove
26
790
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
26
1.9k
Optimizing for Happiness
mojombo
375
69k
WebSockets: Embracing the real-time Web
robhawkes
59
7.3k
Building an army of robots
kneath
302
42k
Scaling GitHub
holman
458
140k
Unsuck your backbone
ammeep
667
57k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
248
20k
Transcript
@craigkerstiens Postgres What they really use
Interrupt me Ask questions
[email protected]
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