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
   

   View Slide

2. Interrupt me
   Ask questions
   craig@heroku.com
   

   View Slide

3. View Slide

4. @craigkerstiens
   Postgres
   What they really use
   

   View Slide

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

   View Slide

6. Postgres - TLDR
   

   View Slide

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
   

   View Slide

8. What they
   really use ?
   

   View Slide

9. Why listen to
   what I say?
   

   View Slide

10. Largest fleet of
    Postgres
    in the world
    

    View Slide

11. Over 1 billion
    write transactions
    a day
    

    View Slide

12. What they
    really use ?
    

    View Slide

13. Production
    

    View Slide

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

    View Slide

15. Extensions
    

    View Slide

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

    View Slide

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%
    

    View Slide

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

    View Slide

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;
    

    View Slide

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
    

    View Slide

21. Indexes
    

    View Slide

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

    View Slide

23. Waste?
    

    View Slide

24. Unused
    Indexes
    

    View Slide

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

    View Slide

26. Bloat
    

    View Slide

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

    View Slide

28. 2.3% over 100 MB
    and 5x bloat factor
    

    View Slide

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

    View Slide

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%
    

    View Slide

31. Problems
    new users
    face?
    

    View Slide

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
    

    View Slide

33. Questions
    

    View Slide