Postgres – Past, Present Future

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

Transcript

1. Postgres Past | Present | Future
2. 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
3. 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
4. Good ! Postgres Postgres-QL
5. Good ! Postgres Postgres-QL Bad ! Postgres-SQL Postgray-SQL
6. 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
7. TLDR in quote “Postgres - it’s the emacs of databases”
8. Postgres The Past Well over 5 years ago
9. Past | Present | Future In Time 1986 Development Began 1989 First major release 1995 SQL Support
10. MVCC Past | Present | Future Each query sees transactions committed before it
11. MySQL Won Past | Present | Future
12. MySQL Won Past | Present | Future Was easier to install ! Simpler replication setup
13. Postgres early focus Past | Present | Future We care about safety and integrity of data
14. Postgres The Present 5 years until now
15. 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
16. 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
17. 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
18. 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
19. 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
20. Past | Present | Future Postgres 8.3
21. Past | Present | Future UUID hstore Full text search Postgres 8.3
22. Past | Present | Future Postgres 8.3 - UUID Much better than serial keys Just works
23. Past | Present | Future Postgres 8.3 - UUID Much better than serial keys Just works create extension uuid; create table foo (id uuid);
24. Past | Present | Future Postgres 8.3 - hstore
25. Past | Present | Future Postgres 8.3 - hstore hstore - horrible name
26. Past | Present | Future Postgres 8.3 - hstore hstore - horrible name key-value store in a column
27. Past | Present | Future Postgres 8.3 - hstore hstore - horrible name key-value store in a column
28. Past | Present | Future Postgres 8.3 - hstore hstore - horrible name key-value store in a column Even better
29. Past | Present | Future Postgres 8.3 - hstore hstore - horrible name key-value store in a column Even better Can index on keys/values
30. 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
31. 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
32. hstore in action
33. 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
34. 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
35. Past | Present | Future Postgres 8.3 - full text search Stemming Ranking / Boost Support Multiple languages Fuzzy search for mispelling Accent support
36. Past | Present | Future Postgres 8.4 - AKA SQLs your friend
37. Past | Present | Future Postgres 8.4 - AKA SQLs your friend Window function ! Common Table Expressions
38. 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.”
39. 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
40. 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;
41. Past | Present | Future Postgres 8.4 - CTEs
42. Past | Present | Future Postgres 8.4 - CTEs If you like writing SQL you’re weird
43. Past | Present | Future Postgres 8.4 - CTEs If you like writing SQL you’re weird If you like reading SQL see a counselor
44. 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
45. 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
46. 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
47. 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
48. 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
49. Past | Present | Future Postgres 9.0 - Finally replication
50. Past | Present | Future Postgres 9.0 - Finally replication Replication
51. Past | Present | Future Postgres 9.0 - Finally replication Replication Listen/Notify
52. Past | Present | Future Postgres 9.1 - a JVM like foundation
53. Past | Present | Future Postgres 9.1 - a JVM like foundation Foreign Tables Extensions Sync Rep Unlogged tables KNN
54. Past | Present | Future Postgres 9.1 - Foreign Tables
55. 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
56. Past | Present | Future Postgres 9.1 - Extensions
57. Past | Present | Future Postgres 9.1 - Extensions Easier method for installing contrib Extensions grow More to come
58. Past | Present | Future Postgres 9.1 - Sync rep
59. Past | Present | Future Postgres 9.1 - Sync rep 9.0 was a foundation Sync rep = granular control Per transaction basis
60. Past | Present | Future Postgres 9.1 - unlogged tables
61. Past | Present | Future Postgres 9.1 - KNN
62. Past | Present | Future Indexes
63. Past | Present | Future Indexes B-Tree ! GIN ! GiST ! SP-GiST ! KNN
64. Past | Present | Future Indexes
65. Past | Present | Future Indexes - B-Tree Default Usually what you want
66. Past | Present | Future Indexes - GIN Multiple values in single column Array/hstore
67. Past | Present | Future Indexes - GiST Values that can overlap Full text search Shapes GIS
68. Past | Present | Future Indexes - Others B-Tree ! GIN ! GiST ! SP-GiST ! KNN ! VODKA
69. Past | Present | Future Postgres 9.2 - User friendly finally
70. Past | Present | Future Postgres 9.2 - User friendly finally Index Only Scans
71. Past | Present | Future Postgres 9.2 - User friendly finally Index Only Scans Pg_stat_statements
72. Past | Present | Future Postgres 9.2 - User friendly finally Index Only Scans Pg_stat_statements JSON Datatype
73. 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
74. 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;
75. Pg Stat Statements total | avg | query --------+--------+------------------------- 295.76 | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) !
76. Performance Guidelines
77. Past | Present | Future Performance
78. Past | Present | Future Performance Aim for high cache hit rate
79. Past | Present | Future Performance Aim for high cache hit rate Frequent queries execute in under 10 ms
80. Past | Present | Future Performance Aim for high cache hit rate Frequent queries execute in under 10 ms Rarer queries in under 100 ms
81. 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
82. 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
83. 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
84. 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
85. Pg Stat Statements total | avg | query --------+--------+------------------------- 295.76 | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) !
86. Past | Present | Future Postgres 9.2 - User friendly finally Index Only Scans ! Pg_stat_statements ! JSON Datatype
87. JSON
88. JSON CREATE TABLE bar (foo json); ! SELECT '{"id":1,"email": "craig.kerstiens@gmail.com",}'::json;
89. Past | Present | Future Postgres 9.3 - App Dev Friendly
90. Past | Present | Future Postgres 9.3 - App Dev Friendly Materialized Views
91. Past | Present | Future Postgres 9.3 - App Dev Friendly Materialized Views Writeable FDWs
92. Past | Present | Future Postgres 9.3 - App Dev Friendly Materialized Views Writeable FDWs Better JSON
93. Postgres The Future some fact some speculation
94. Past | Present | Future Postgres 9.4 - Prewarm ! Refresh materialized view ! Ordered set aggregates ! JSONB ! Logical decoding ! !
95. Past | Present | Future Postgres 9.4 - Prewarm
96. Past | Present | Future Postgres 9.4 - Prewarm Replicas are often cold Run a utility, warm em up
97. Past | Present | Future Postgres 9.4 - Refresh materialized view
98. Past | Present | Future Postgres 9.4 - Refresh materialized view Materialized views can’t be read while updating
99. Past | Present | Future Postgres 9.4 - Refresh materialized view Materialized views can’t be read while updating Now they can
100. 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
101. Past | Present | Future Postgres 9.4 - Ordered set aggregates
102. Past | Present | Future Postgres 9.4 - Ordered set aggregates I kinda understand these Median Percentile Hypothetical values
103. Past | Present | Future Postgres 9.4 - Logical Decoding
104. Past | Present | Future Postgres 9.4 - Logical Decoding WAL is binary
105. Past | Present | Future Postgres 9.4 - Logical Decoding WAL is binary Decoding = SQL
106. Past | Present | Future Postgres 9.4 - Logical Decoding WAL is binary Decoding = SQL
107. Past | Present | Future Postgres 9.4 - Logical Decoding WAL is binary Decoding = SQL Means cross version upgrades, multimaster, etc
108. Past | Present | Future Postgres 9.4 - JSONB
109. Past | Present | Future Postgres 9.4 - JSONB Binary JSON Easier to get performance (GIN)
110. Postgres The Future some fact some speculation
111. Past | Present | Future Speculation
112. Past | Present | Future Speculation Upsert
113. Past | Present | Future Speculation Upsert Multimaster
114. Past | Present | Future Speculation Upsert Multimaster Extensions - apt-get
115. Past | Present | Future Speculation Upsert Multimaster Extensions - apt-get Foreign data wrappers
116. Past | Present | Future Speculation Upsert Multimaster Extensions - apt-get Foreign data wrappers Document storage
117. Past | Present | Future Speculation Upsert Multimaster Extensions - apt-get Foreign data wrappers Document storage Pluggable storage engines
118. Recap PG was and still is safe for your data
119. Recap PG became user friendly for power users
120. 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
121. Recap PG i s b e c o m i n g a platform for your data, more than relational database
122. 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
123. Fin. @craigkerstiens http://speakerdeck/u/craigkerstiens