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
880
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
310
Five sharding data models and which is right? PGDay Nordic
craigkerstiens
0
200
Postgres at any scale
craigkerstiens
1
860
Five data models for sharding and which is right
craigkerstiens
0
140
Postgres Performance for Humans - All things Open
craigkerstiens
1
320
Postgres Performance for Humans - PyCaribbean
craigkerstiens
1
160
Postgres present and future
craigkerstiens
1
340
Marketing for Developers
craigkerstiens
0
250
Postgres – A Data Platform
craigkerstiens
2
470
Other Decks in Technology
See All in Technology
allow_retry と Arel.sql / allow_retry and Arel.sql
euglena1215
1
160
AI開発ツールCreateがAnythingになったよ
tendasato
0
120
Flutterでキャッチしないエラーはどこに行く
taiju59
0
220
サラリーマンの小遣いで作るtoCサービス - Cloudflare Workersでスケールする開発戦略
shinaps
1
370
なぜスクラムはこうなったのか?歴史が教えてくれたこと/Shall we explore the roots of Scrum
sanogemaru
5
1.5k
バイブスに「型」を!Kent Beckに学ぶ、AI時代のテスト駆動開発
amixedcolor
2
490
ハードウェアとソフトウェアをつなぐ全てを内製している企業の E2E テストの作り方 / How to create E2E tests for a company that builds everything connecting hardware and software in-house
bitkey
PRO
1
110
実践!カスタムインストラクション&スラッシュコマンド
puku0x
0
310
Obsidian応用活用術
onikun94
1
440
ガチな登山用デバイスからこんにちは
halka
1
230
Autonomous Database - Dedicated 技術詳細 / adb-d_technical_detail_jp
oracle4engineer
PRO
4
10k
「どこから読む?」コードとカルチャーに最速で馴染むための実践ガイド
zozotech
PRO
0
240
Featured
See All Featured
How to Create Impact in a Changing Tech Landscape [PerfNow 2023]
tammyeverts
53
2.9k
Connecting the Dots Between Site Speed, User Experience & Your Business [WebExpo 2025]
tammyeverts
8
520
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
252
21k
Building Flexible Design Systems
yeseniaperezcruz
328
39k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
333
22k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
51
5.6k
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
12
1.1k
Visualization
eitanlees
148
16k
Building Adaptive Systems
keathley
43
2.7k
A Tale of Four Properties
chriscoyier
160
23k
It's Worth the Effort
3n
187
28k
Context Engineering - Making Every Token Count
addyosmani
1
21
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