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 full-size slide

  2. Interrupt me
    Ask questions
    [email protected]

    View full-size slide

  3. @craigkerstiens
    Postgres
    What they really use

    View full-size slide

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

    View full-size slide

  5. Postgres - TLDR

    View full-size slide

  6. 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 full-size slide

  7. What they
    really use ?

    View full-size slide

  8. Why listen to
    what I say?

    View full-size slide

  9. Largest fleet of
    Postgres
    in the world

    View full-size slide

  10. Over 1 billion
    write transactions
    a day

    View full-size slide

  11. What they
    really use ?

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  14. 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 full-size slide

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

    View full-size slide

  16. 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 full-size slide

  17. 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 full-size slide

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

    View full-size slide

  19. Unused
    Indexes

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  24. 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 full-size slide

  25. Problems
    new users
    face?

    View full-size slide

  26. 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 full-size slide