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
600
Five data models for sharding and which is right
craigkerstiens
0
96
Postgres Performance for Humans - All things Open
craigkerstiens
1
230
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
Kernel MemoryでAzure OpenAI Serviceとお手軽データソース連携
mitsuzono
1
280
【基本】データベース設計
oracle4engineer
PRO
2
150
Python と Snowflake はズッ友だょ!~ Snowflake の Python 関連機能をふりかえる ~
__allllllllez__
2
140
AOAI をきっかけに 社内の Azure 管理を見直した話
recruitengineers
PRO
1
450
MixIT 2024 - Pulumi : Gérer son infra avec son langage de programmation préféré
ju_hnny5
1
120
EMとして2023年度に頑張ったこと / What we did well in FY2023 as a EM
pauli
1
210
開発パフォーマンスを最大化するための開発体制
ham0215
7
1.1k
Gradle Build Scanを使ってビルドのことを知ろう potatotips #87
tomorrowkey
2
150
アクセス制御にまつわる改善 / Improving access control
itkq
0
590
MapLibreとAmazon Location Service
dayjournal
1
180
R3のコードから見る実践LINQ実装最適化・コンカレントプログラミング実例
neuecc
3
2.2k
Microsoft Intune 勉強会 第 2 回目
tamaiyutaro
2
380
Featured
See All Featured
Why You Should Never Use an ORM
jnunemaker
PRO
51
8.6k
Building a Scalable Design System with Sketch
lauravandoore
457
32k
A Modern Web Designer's Workflow
chriscoyier
689
190k
Atom: Resistance is Futile
akmur
260
25k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
79
43k
Reflections from 52 weeks, 52 projects
jeffersonlam
345
19k
GitHub's CSS Performance
jonrohan
1025
450k
Scaling GitHub
holman
457
140k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
41
4.4k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
26
2.3k
Clear Off the Table
cherdarchuk
85
310k
GraphQLとの向き合い方2022年版
quramy
33
12k
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