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
810
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
240
Five sharding data models and which is right? PGDay Nordic
craigkerstiens
0
160
Postgres at any scale
craigkerstiens
1
660
Five data models for sharding and which is right
craigkerstiens
0
100
Postgres Performance for Humans - All things Open
craigkerstiens
1
240
Postgres Performance for Humans - PyCaribbean
craigkerstiens
1
120
Postgres present and future
craigkerstiens
1
330
Marketing for Developers
craigkerstiens
0
190
Postgres – A Data Platform
craigkerstiens
2
430
Other Decks in Technology
See All in Technology
Git 研修 Advanced【MIXI 24新卒技術研修】
mixi_engineers
PRO
0
200
20240724_cm_odyssey_hibiyatech
hiashisan
0
110
AOAI Dev Day LLMシステム開発 Tips集
hirosatogamo
15
3.8k
AI研修【MIXI 24新卒技術研修】
mixi_engineers
PRO
0
130
GoとアクターモデルでES+CQRSを実践! / proto_actor_es_cqrs
ytake
1
160
大規模ドラレコデータ収集・機械学習基盤を支える AWS CDK 〜導入・運用事例紹介〜
pemugi
0
110
Github Actions 로 Android 팀의 효율성 극대화
hadonghyun
0
160
サービス開発を前に進めるために 新米リードエンジニアが 取り組んだこと / Steps Taken by a Novice Lead Engineer to Advance Service Development
nologyance
0
180
Datadog Cloud SIEMを使ってAWS環境の脅威を可視化した話/lifeistech-datadog-cloud-siem
gidajun
0
480
AIアシスタントの活用で品質の向上と開発ワークフローのスピードアップ
nagix
1
210
Azure Pipelinesを使用したCICDベースラインアーキテクチャ実践
yuriemori
0
190
成長期に歩みを止めないための創業期の開発文化形成
mayah
6
420
Featured
See All Featured
Optimising Largest Contentful Paint
csswizardry
18
2.6k
For a Future-Friendly Web
brad_frost
173
9.2k
Statistics for Hackers
jakevdp
792
220k
Atom: Resistance is Futile
akmur
261
25k
YesSQL, Process and Tooling at Scale
rocio
166
14k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
662
120k
How GitHub Uses GitHub to Build GitHub
holman
471
290k
Put a Button on it: Removing Barriers to Going Fast.
kastner
58
3.3k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
12
3.8k
Building Effective Engineering Teams - LeadDev
addyosmani
47
2.2k
ParisWeb 2013: Learning to Love: Crash Course in Emotional UX Design
dotmariusz
105
6.8k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
228
16k
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