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

Postgres – Past, Present Future

Craig Kerstiens
April 02, 2014
220

Postgres – Past, Present Future

A look at where Postgres came from, recent highlights from releases in past 5 years, and whats coming.

Craig Kerstiens

April 02, 2014
Tweet

Transcript

  1. Hi! I’m Craig I work at Heroku. I’m a product

    manager, this means I do whatever engineers don’t want to. I blog some and curate postgres weekly @craigkerstiens
  2. It might help to explain that the pronunciation is "post-gres"

    or "post-gres-cue-ell", not "post-gray-something". ! I heard people making this same mistake in presentations at this past weekend's Postgres Anniversary Conference :-( Arguably, the 1996 decision to call it PostgreSQL instead of reverting to plain Postgres was the single worst mistake this project ever made. It seems far too late to change now, though. ! regards, tom lane
  3. Why 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
  4. Past | Present | Future In Time 1986 Development Began

    1989 First major release 1995 SQL Support
  5. MySQL Won Past | Present | Future Was easier to

    install ! Simpler replication setup
  6. Postgres early focus Past | Present | Future We care

    about safety and integrity of data
  7. Past | Present | Future Datatypes Numeric Integer Precision Floating

    Point Serial Monetary Character Character Var Char String Binary Date/Time Time DateTime Intervals Timezones Boolean Enums Geometric Points Lines Boxes Polygons Circles Network Addresses Inet Cidr Mac Address UUID XML Arrays
  8. Past | Present | Future Datatypes Numeric Integer Precision Floating

    Point Serial Monetary Character Character Var Char String Binary Date/Time Time DateTime Intervals Timezones Boolean Enums Geometric Points Lines Boxes Polygons Circles Network Addresses Inet Cidr Mac Address UUID XML Arrays
  9. Past | Present | Future Datatypes Numeric Integer Precision Floating

    Point Serial Monetary Character Character Var Char String Binary Date/Time Time DateTime Intervals Timezones Boolean Enums Geometric Points Lines Boxes Polygons Circles Network Addresses Inet Cidr Mac Address UUID XML Arrays
  10. Past | Present | Future Datatypes Numeric Integer Precision Floating

    Point Serial Monetary Character Character Var Char String Binary Date/Time Time DateTime Intervals Timezones Boolean Enums Geometric Points Lines Boxes Polygons Circles Network Addresses Inet Cidr Mac Address UUID XML Arrays
  11. Past | Present | Future Datatypes Numeric Integer Precision Floating

    Point Serial Monetary Character Character Var Char String Binary Date/Time Time DateTime Intervals Timezones Boolean Enums Geometric Points Lines Boxes Polygons Circles Network Addresses Inet Cidr Mac Address UUID XML Arrays
  12. Past | Present | Future Postgres 8.3 - UUID Much

    better than serial keys Just works
  13. Past | Present | Future Postgres 8.3 - UUID Much

    better than serial keys Just works create extension uuid; create table foo (id uuid);
  14. Past | Present | Future Postgres 8.3 - hstore hstore

    - horrible name key-value store in a column
  15. Past | Present | Future Postgres 8.3 - hstore hstore

    - horrible name key-value store in a column
  16. Past | Present | Future Postgres 8.3 - hstore hstore

    - horrible name key-value store in a column Even better
  17. Past | Present | Future Postgres 8.3 - hstore hstore

    - horrible name key-value store in a column Even better Can index on keys/values
  18. Past | Present | Future Postgres 8.3 - hstore hstore

    - horrible name key-value store in a column Even better Can index on keys/values Can filter on keys values
  19. Past | Present | Future Postgres 8.3 - hstore hstore

    - horrible name key-value store in a column Even better Can index on keys/values Can filter on keys values
  20. create extension hstore; ! create table foo (example hstore); !

    INSERT INTO foo VALUES ( ‘key => "examplevalue", state => “California”', ); ! SELECT * FROM foo WHERE example->key = ‘examplevalue’; ! hstore in action
  21. create extension hstore; ! create table foo (example hstore); !

    INSERT INTO foo VALUES ( ‘key => "examplevalue", state => “California”', ); ! SELECT * FROM foo WHERE example->key = ‘examplevalue’; ! hstore in action
  22. Past | Present | Future Postgres 8.3 - full text

    search Stemming Ranking / Boost Support Multiple languages Fuzzy search for mispelling Accent support
  23. Past | Present | Future Postgres 8.4 - AKA SQLs

    your friend Window function ! Common Table Expressions
  24. Past | Present | Future Postgres 8.4 - Window functions

    Aggregate over set, compute some value at a row ! “A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function.”
  25. SELECT email, users.data->'state', sum(total(items)), rank() OVER (PARTITION BY users.data->'state' ORDER

    BY sum(total(items)) desc) FROM users, purchases WHERE purchases.user_id = users.id GROUP BY 1, 2; window functions in action
  26. window functions in action SELECT email, users.data->'state', sum(total(items)), rank() OVER

    (PARTITION BY users.data->'state' ORDER BY sum(total(items)) desc) FROM users, purchases WHERE purchases.user_id = users.id GROUP BY 1, 2;
  27. Past | Present | Future Postgres 8.4 - CTEs If

    you like writing SQL you’re weird
  28. Past | Present | Future Postgres 8.4 - CTEs If

    you like writing SQL you’re weird If you like reading SQL see a counselor
  29. Past | Present | Future Postgres 8.4 - CTEs If

    you like writing SQL you’re weird If you like reading SQL see a counselor Window functions make SQL bearable
  30. Past | Present | Future Postgres 8.4 - CTEs If

    you like writing SQL you’re weird If you like reading SQL see a counselor Window functions make SQL bearable
  31. Past | Present | Future Postgres 8.4 - CTEs If

    you like writing SQL you’re weird If you like reading SQL see a counselor Window functions make SQL bearable It’s like a view within your query that you can reference
  32. WITH top_5_products AS ( SELECT products.*, count(*) FROM products, line_items

    WHERE products.id = line_items.product_id GROUP BY products.id ORDER BY count(*) DESC LIMIT 5 ) ! SELECT users.email, count(*) FROM users, line_items, top_5_products WHERE line_items.user_id = users.id AND line_items.product_id = top_5_products.id GROUP BY 1 ORDER BY 1; CTEs in action
  33. WITH top_5_products AS ( SELECT products.*, count(*) FROM products, line_items

    WHERE products.id = line_items.product_id GROUP BY products.id ORDER BY count(*) DESC LIMIT 5 ) ! SELECT users.email, count(*) FROM users, line_items, top_5_products WHERE line_items.user_id = users.id AND line_items.product_id = top_5_products.id GROUP BY 1 ORDER BY 1; CTEs in action
  34. Past | Present | Future Postgres 9.1 - a JVM

    like foundation Foreign Tables Extensions Sync Rep Unlogged tables KNN
  35. Past | Present | Future Postgres 9.1 - Foreign Tables

    Query from in Postgres to something else Between Postgres databases Other sources Other DBs Redis LDAP S3 Twitter
  36. Past | Present | Future Postgres 9.1 - Extensions Easier

    method for installing contrib Extensions grow More to come
  37. Past | Present | Future Postgres 9.1 - Sync rep

    9.0 was a foundation Sync rep = granular control Per transaction basis
  38. Past | Present | Future Indexes - GiST Values that

    can overlap Full text search Shapes GIS
  39. Past | Present | Future Indexes - Others B-Tree !

    GIN ! GiST ! SP-GiST ! KNN ! VODKA
  40. Past | Present | Future Postgres 9.2 - User friendly

    finally Index Only Scans Pg_stat_statements
  41. Past | Present | Future Postgres 9.2 - User friendly

    finally Index Only Scans Pg_stat_statements JSON Datatype
  42. Pg Stat Statements $ select * from pg_stat_statements where query

    ~ 'from users where email'; ! ! userid │ 16384 dbid │ 16388 query │ select * from users where email = ?; calls │ 2 total_time │ 0.000268 rows │ 2 shared_blks_hit │ 16 shared_blks_read │ 0 shared_blks_dirtied │ 0 shared_blks_written │ 0 local_blks_hit │ 0 local_blks_read │ 0 local_blks_dirtied │ 0 local_blks_written │ 0 temp_blks_read │ 0 temp_blks_written │ 0 time_read │ 0 time_write │ 0
  43. Pg Stat Statements SELECT (total_time / 1000 / 60) as

    total, (total_time/calls) as avg, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;
  44. Pg Stat Statements total | avg | query --------+--------+------------------------- 295.76

    | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) !
  45. Past | Present | Future Performance Aim for high cache

    hit rate Frequent queries execute in under 10 ms
  46. Past | Present | Future Performance Aim for high cache

    hit rate Frequent queries execute in under 10 ms Rarer queries in under 100 ms
  47. Past | Present | Future Performance Aim for high cache

    hit rate Frequent queries execute in under 10 ms Rarer queries in under 100 ms Reports, whatever’s right for your app
  48. Explain # EXPLAIN SELECT last_name FROM employees WHERE salary >=

    50000; QUERY PLAN -------------------------------------------------- Seq Scan on employees width=6) Filter: (salary >= 50000) (3 rows) (cost=0.00..35811.00 rows=1
  49. Explain # EXPLAIN SELECT last_name FROM employees WHERE salary >=

    50000; QUERY PLAN -------------------------------------------------- Seq Scan on employees width=6) Filter: (salary >= 50000) (3 rows) startup time max time rows return (cost=0.00..35811.00 rows=1
  50. Explain Analyze # EXPLAIN ANALYZE SELECT last_name FROM employees WHERE

    salary >= 50000; QUERY PLAN -------------------------------------------------- Seq Scan on employees (cost=0.00..35811.00 rows=1 width=6) (actual time=2.401..295.247 rows=1428 loops=1) Filter: (salary >= 50000) Total runtime: 295.379 (3 rows) ! Filter: (salary >= 50000) (3 rows) startup time max time rows return actual time 2.401..295.247 rows=1428 295.379
  51. Pg Stat Statements total | avg | query --------+--------+------------------------- 295.76

    | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) !
  52. Past | Present | Future Postgres 9.2 - User friendly

    finally Index Only Scans ! Pg_stat_statements ! JSON Datatype
  53. Past | Present | Future Postgres 9.3 - App Dev

    Friendly Materialized Views Writeable FDWs
  54. Past | Present | Future Postgres 9.3 - App Dev

    Friendly Materialized Views Writeable FDWs Better JSON
  55. Past | Present | Future Postgres 9.4 - Prewarm !

    Refresh materialized view ! Ordered set aggregates ! JSONB ! Logical decoding ! !
  56. Past | Present | Future Postgres 9.4 - Prewarm Replicas

    are often cold Run a utility, warm em up
  57. Past | Present | Future Postgres 9.4 - Refresh materialized

    view Materialized views can’t be read while updating
  58. Past | Present | Future Postgres 9.4 - Refresh materialized

    view Materialized views can’t be read while updating Now they can
  59. Past | Present | Future Postgres 9.4 - Refresh materialized

    view Materialized views can’t be read while updating Now they can Yes, they were of minimal use before
  60. Past | Present | Future Postgres 9.4 - Ordered set

    aggregates I kinda understand these Median Percentile Hypothetical values
  61. Past | Present | Future Postgres 9.4 - Logical Decoding

    WAL is binary Decoding = SQL Means cross version upgrades, multimaster, etc
  62. Past | Present | Future Postgres 9.4 - JSONB Binary

    JSON Easier to get performance (GIN)
  63. Past | Present | Future Speculation Upsert Multimaster Extensions -

    apt-get Foreign data wrappers Document storage
  64. Past | Present | Future Speculation Upsert Multimaster Extensions -

    apt-get Foreign data wrappers Document storage Pluggable storage engines
  65. Recap PG b e c a m e u s

    e r f r i e n d l y f o r a p p l i c a t i o n developers
  66. Recap PG i s b e c o m i

    n g a platform for your data, more than relational database
  67. Why 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