Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Postgres What they really use

Postgres What they really use

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

Craig Kerstiens

November 01, 2013
Tweet

More Decks by Craig Kerstiens

Other Decks in Technology

Transcript

  1. @craigkerstiens
    Postgres
    What they really use

    View Slide

  2. Interrupt me
    Ask questions
    [email protected]

    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