Postgres What they really use

Some insight into what features and functionality people actually use within their database.

Transcript

1. @craigkerstiens Postgres What they really use
2. Interrupt me Ask questions craig@heroku.com
4. @craigkerstiens Postgres What they really use
5. Shameless plugs http://www.postgresweekly.com http://www.craigkerstiens.com http://www.postgresguide.com http://www.postgresapp.com http://postgres.heroku.com
6. Postgres - TLDR
7. 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
8. What they really use ?
9. Why listen to what I say?
10. Largest fleet of Postgres in the world
11. Over 1 billion write transactions a day
12. What they really use ?
13. Production
14. 43% on 9.1 2% on 9.0 54% on 9.2 Versions
15. Extensions
16. hstore pg_stat_statements postgis uuid-ossp pg_trgm unaccent fuzzystrmatch dblink cube pgcrypto earthdistance tablefunc citext
17. 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%
18. 17% at least 1 of those 22% have 2 8% have 3 2.5% have 4 .7% have 5 .2% have 11
19. 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;
20. 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
21. Indexes
22. 99.9% have an index 28% have gin 13% have gist 92% have unique 8% have conditional
23. Waste?
24. Unused Indexes
25. 23% over 1000 rows 13% over 10000 rows 5% over 100000 rows 1.5% over 1 million rows 2% over 100 million rows
26. Bloat
27. 0.1% over 100 GB 1.5% over 10 GB 8.7% over 1 GB 22.9% over 100 MB
28. 2.3% over 100 MB and 5x bloat factor
29. Pg Extras https://github.com/heroku/heroku-pg-extras/
30. 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%
31. Problems new users face?
32. 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
33. Questions