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
870
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
300
Five sharding data models and which is right? PGDay Nordic
craigkerstiens
0
190
Postgres at any scale
craigkerstiens
1
840
Five data models for sharding and which is right
craigkerstiens
0
140
Postgres Performance for Humans - All things Open
craigkerstiens
1
310
Postgres Performance for Humans - PyCaribbean
craigkerstiens
1
160
Postgres present and future
craigkerstiens
1
340
Marketing for Developers
craigkerstiens
0
240
Postgres – A Data Platform
craigkerstiens
2
460
Other Decks in Technology
See All in Technology
IPA&AWSダブル全冠が明かす、人生を変えた勉強法のすべて
iwamot
PRO
2
110
「良さそう」と「とても良い」の間には 「良さそうだがホンマか」がたくさんある / 2025.07.01 LLM品質Night
smiyawaki0820
1
550
スタートアップに選択肢を 〜生成AIを活用したセカンダリー事業への挑戦〜
nstock
0
170
20250705 Headlamp: 專注可擴展性的 Kubernetes 用戶界面
pichuang
0
270
Tokyo_reInforce_2025_recap_iam_access_analyzer
hiashisan
0
180
面倒な作業はAIにおまかせ。Flutter開発をスマートに効率化
ruideengineer
0
250
第4回Snowflake 金融ユーザー会 Snowflake summit recap
tamaoki
1
280
Core Audio tapを使ったリアルタイム音声処理のお話
yuta0306
0
190
freeeのアクセシビリティの現在地 / freee's Current Position on Accessibility
ymrl
2
180
OSSのSNSツール「Misskey」をさわってみよう(右下ワイプで私のOSCの20年を振り返ります) / 20250705-osc2025-do
akkiesoft
0
160
American airlines ®️ USA Contact Numbers: Complete 2025 Support Guide
airhelpsupport
0
380
FOSS4G 2025 KANSAI QGISで点群データをいろいろしてみた
kou_kita
0
400
Featured
See All Featured
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
3.9k
Git: the NoSQL Database
bkeepers
PRO
430
65k
Automating Front-end Workflow
addyosmani
1370
200k
The Power of CSS Pseudo Elements
geoffreycrofte
77
5.9k
How GitHub (no longer) Works
holman
314
140k
4 Signs Your Business is Dying
shpigford
184
22k
Bootstrapping a Software Product
garrettdimon
PRO
307
110k
Building Applications with DynamoDB
mza
95
6.5k
Statistics for Hackers
jakevdp
799
220k
The Straight Up "How To Draw Better" Workshop
denniskardys
234
140k
Product Roadmaps are Hard
iamctodd
PRO
54
11k
How to Ace a Technical Interview
jacobian
278
23k
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