Postgres What they really use

Slide 1

Slide 1 text

@craigkerstiens Postgres What they really use

Slide 2

Slide 2 text

Interrupt me Ask questions [email protected]

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

@craigkerstiens Postgres What they really use

Slide 5

Slide 5 text

Shameless plugs http://www.postgresweekly.com http://www.craigkerstiens.com http://www.postgresguide.com http://www.postgresapp.com http://postgres.heroku.com

Slide 6

Slide 6 text

Postgres - TLDR

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

What they really use ?

Slide 9

Slide 9 text

Why listen to what I say?

Slide 10

Slide 10 text

Largest ﬂeet of Postgres in the world

Slide 11

Slide 11 text

Over 1 billion write transactions a day

Slide 12

Slide 12 text

What they really use ?

Slide 13

Slide 13 text

Production

Slide 14

Slide 14 text

43% on 9.1 2% on 9.0 54% on 9.2 Versions

Slide 15

Slide 15 text

Extensions

Slide 16

Slide 16 text

hstore pg_stat_statements postgis uuid-ossp pg_trgm unaccent fuzzystrmatch dblink cube pgcrypto earthdistance tablefunc citext

Slide 17

Slide 17 text

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%

Slide 18

Slide 18 text

17% at least 1 of those 22% have 2 8% have 3 2.5% have 4 .7% have 5 .2% have 11

Slide 19

Slide 19 text

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;

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

Indexes

Slide 22

Slide 22 text

99.9% have an index 28% have gin 13% have gist 92% have unique 8% have conditional

Slide 23

Slide 23 text

Waste?

Slide 24

Slide 24 text

Unused Indexes

Slide 25

Slide 25 text

23% over 1000 rows 13% over 10000 rows 5% over 100000 rows 1.5% over 1 million rows 2% over 100 million rows

Slide 26

Slide 26 text

Bloat

Slide 27

Slide 27 text

0.1% over 100 GB 1.5% over 10 GB 8.7% over 1 GB 22.9% over 100 MB

Slide 28

Slide 28 text

2.3% over 100 MB and 5x bloat factor

Slide 29

Slide 29 text

Pg Extras https://github.com/heroku/heroku-pg-extras/

Slide 30

Slide 30 text

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%

Slide 31

Slide 31 text

Problems new users face?

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

Questions