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
780
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
210
Five sharding data models and which is right? PGDay Nordic
craigkerstiens
0
150
Postgres at any scale
craigkerstiens
1
590
Five data models for sharding and which is right
craigkerstiens
0
96
Postgres Performance for Humans - All things Open
craigkerstiens
1
220
Postgres Performance for Humans - PyCaribbean
craigkerstiens
1
110
Postgres present and future
craigkerstiens
1
320
Marketing for Developers
craigkerstiens
0
190
Postgres – A Data Platform
craigkerstiens
2
420
Other Decks in Technology
See All in Technology
OpenTelemetry を使ったトレースエグザンプラーの活用 / otel-trace-exemplar
k6s4i53rx
2
630
ここが嬉しいABAC ここが辛いよABAC #再解説+補足編
masahirokawahara
0
160
開発生産性向上サービスを作るFindyが自分たちで開発生産性を爆上げした組織づくりの歩み / Findy's path to boosting its own development productivity 2024-04-17
ma3tk
0
170
シン・Kafka / shin-kafka
oracle4engineer
PRO
6
2.7k
SIEMを用いて、セキュリティログ分析の可視化と分析を実現し、PDCAサイクルを回してみた
coconala_engineer
0
180
反実仮想機械学習とは何か
usaito
PRO
6
1.1k
Oracle Exadata Database Service on Cloud@Customer (ExaDB-C@C) - UI スクリーン・キャプチャ集
oracle4engineer
PRO
1
1.1k
PHP"オレ"カンファレンスの告知
ysknsid25
0
300
Microsoft Cloudで開発ライフサイクルを保護する
kkamegawa
0
140
Four keys改善の取り組み事例紹介
sansantech
PRO
2
220
「ふりかえりのふりかえり」をふりかえり、実のあるふりかえりにする
naitosatoshi
0
210
**強い**エンジニアのなり方 - フィードバックサイクルを勝ち取る / grow one day each day
soudai
59
17k
Featured
See All Featured
In The Pink: A Labor of Love
frogandcode
137
21k
GitHub's CSS Performance
jonrohan
1023
450k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
219
21k
How to Ace a Technical Interview
jacobian
272
22k
WebSockets: Embracing the real-time Web
robhawkes
59
7k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
18
1.7k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
153
14k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
34
8.8k
Building an army of robots
kneath
300
41k
Docker and Python
trallard
33
2.7k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
124
32k
Why You Should Never Use an ORM
jnunemaker
PRO
50
8.6k
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