Postgres – Past, Present Future

Slide 1

Slide 1 text

Postgres Past | Present | Future

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

Good ! Postgres Postgres-QL

Slide 5

Slide 5 text

Good ! Postgres Postgres-QL Bad ! Postgres-SQL Postgray-SQL

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

TLDR in quote “Postgres - it’s the emacs of databases”

Slide 8

Slide 8 text

Postgres The Past Well over 5 years ago

Slide 9

Slide 9 text

Past | Present | Future In Time 1986 Development Began 1989 First major release 1995 SQL Support

Slide 10

Slide 10 text

MVCC Past | Present | Future Each query sees transactions committed before it

Slide 11

Slide 11 text

MySQL Won Past | Present | Future

Slide 12

Slide 12 text

MySQL Won Past | Present | Future Was easier to install ! Simpler replication setup

Slide 13

Slide 13 text

Postgres early focus Past | Present | Future We care about safety and integrity of data

Slide 14

Slide 14 text

Postgres The Present 5 years until now

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Past | Present | Future Postgres 8.3

Slide 21

Slide 21 text

Past | Present | Future UUID hstore Full text search Postgres 8.3

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

Past | Present | Future Postgres 8.3 - hstore

Slide 25

Slide 25 text

Past | Present | Future Postgres 8.3 - hstore hstore - horrible name

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

hstore in action

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Past | Present | Future Postgres 8.3 - full text search Stemming Ranking / Boost Support Multiple languages Fuzzy search for mispelling Accent support

Slide 36

Slide 36 text

Past | Present | Future Postgres 8.4 - AKA SQLs your friend

Slide 37

Slide 37 text

Past | Present | Future Postgres 8.4 - AKA SQLs your friend Window function ! Common Table Expressions

Slide 38

Slide 38 text

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.”

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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;

Slide 41

Slide 41 text

Past | Present | Future Postgres 8.4 - CTEs

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Slide 49

Slide 49 text

Past | Present | Future Postgres 9.0 - Finally replication

Slide 50

Slide 50 text

Past | Present | Future Postgres 9.0 - Finally replication Replication

Slide 51

Slide 51 text

Past | Present | Future Postgres 9.0 - Finally replication Replication Listen/Notify

Slide 52

Slide 52 text

Past | Present | Future Postgres 9.1 - a JVM like foundation

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

Past | Present | Future Postgres 9.1 - Foreign Tables

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

Past | Present | Future Postgres 9.1 - Extensions

Slide 57

Slide 57 text

Past | Present | Future Postgres 9.1 - Extensions Easier method for installing contrib Extensions grow More to come

Slide 58

Slide 58 text

Past | Present | Future Postgres 9.1 - Sync rep

Slide 59

Slide 59 text

Past | Present | Future Postgres 9.1 - Sync rep 9.0 was a foundation Sync rep = granular control Per transaction basis

Slide 60

Slide 60 text

Past | Present | Future Postgres 9.1 - unlogged tables

Slide 61

Slide 61 text

Past | Present | Future Postgres 9.1 - KNN

Slide 62

Slide 62 text

Past | Present | Future Indexes

Slide 63

Slide 63 text

Past | Present | Future Indexes B-Tree ! GIN ! GiST ! SP-GiST ! KNN

Slide 64

Slide 64 text

Past | Present | Future Indexes

Slide 65

Slide 65 text

Past | Present | Future Indexes - B-Tree Default Usually what you want

Slide 66

Slide 66 text

Past | Present | Future Indexes - GIN Multiple values in single column Array/hstore

Slide 67

Slide 67 text

Past | Present | Future Indexes - GiST Values that can overlap Full text search Shapes GIS

Slide 68

Slide 68 text

Past | Present | Future Indexes - Others B-Tree ! GIN ! GiST ! SP-GiST ! KNN ! VODKA

Slide 69

Slide 69 text

Past | Present | Future Postgres 9.2 - User friendly finally

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

Past | Present | Future Postgres 9.2 - User friendly finally Index Only Scans Pg_stat_statements JSON Datatype

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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;

Slide 75

Slide 75 text

Pg Stat Statements total | avg | query --------+--------+------------------------- 295.76 | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) !

Slide 76

Slide 76 text

Performance Guidelines

Slide 77

Slide 77 text

Past | Present | Future Performance

Slide 78

Slide 78 text

Past | Present | Future Performance Aim for high cache hit rate

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

Pg Stat Statements total | avg | query --------+--------+------------------------- 295.76 | 10.13 | SELECT id FROM users... 219.13 | 80.24 | SELECT * FROM ... (2 rows) !

Slide 86

Slide 86 text

Past | Present | Future Postgres 9.2 - User friendly finally Index Only Scans ! Pg_stat_statements ! JSON Datatype

Slide 87

Slide 87 text

JSON

Slide 88

Slide 88 text

JSON CREATE TABLE bar (foo json); ! SELECT '{"id":1,"email": "[email protected]",}'::json;

Slide 89

Slide 89 text

Past | Present | Future Postgres 9.3 - App Dev Friendly

Slide 90

Slide 90 text

Past | Present | Future Postgres 9.3 - App Dev Friendly Materialized Views

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

Past | Present | Future Postgres 9.3 - App Dev Friendly Materialized Views Writeable FDWs Better JSON

Slide 93

Slide 93 text

Postgres The Future some fact some speculation

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

Past | Present | Future Postgres 9.4 - Prewarm

Slide 96

Slide 96 text

Past | Present | Future Postgres 9.4 - Prewarm Replicas are often cold Run a utility, warm em up

Slide 97

Slide 97 text

Past | Present | Future Postgres 9.4 - Refresh materialized view

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

Past | Present | Future Postgres 9.4 - Ordered set aggregates

Slide 102

Slide 102 text

Past | Present | Future Postgres 9.4 - Ordered set aggregates I kinda understand these Median Percentile Hypothetical values

Slide 103

Slide 103 text

Past | Present | Future Postgres 9.4 - Logical Decoding

Slide 104

Slide 104 text

Past | Present | Future Postgres 9.4 - Logical Decoding WAL is binary

Slide 105

Slide 105 text

Past | Present | Future Postgres 9.4 - Logical Decoding WAL is binary Decoding = SQL

Slide 106

Slide 106 text

Past | Present | Future Postgres 9.4 - Logical Decoding WAL is binary Decoding = SQL

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

Past | Present | Future Postgres 9.4 - JSONB

Slide 109

Slide 109 text

Past | Present | Future Postgres 9.4 - JSONB Binary JSON Easier to get performance (GIN)

Slide 110

Slide 110 text

Postgres The Future some fact some speculation

Slide 111

Slide 111 text

Past | Present | Future Speculation

Slide 112

Slide 112 text

Past | Present | Future Speculation Upsert

Slide 113

Slide 113 text

Past | Present | Future Speculation Upsert Multimaster

Slide 114

Slide 114 text

Past | Present | Future Speculation Upsert Multimaster Extensions - apt-get

Slide 115

Slide 115 text

Past | Present | Future Speculation Upsert Multimaster Extensions - apt-get Foreign data wrappers

Slide 116

Slide 116 text

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

Slide 117

Slide 117 text

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

Slide 118

Slide 118 text

Recap PG was and still is safe for your data

Slide 119

Slide 119 text

Recap PG became user friendly for power users

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

Recap PG i s b e c o m i n g a platform for your data, more than relational database