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
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
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
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
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
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
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
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
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.”
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
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;
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
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
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
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
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
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
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
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
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
craigkerstiens
akmur
mongodb
nonsquared
kneath
kevinliebholz
sachag
jensimmons
frogandcode
bkeepers
tanoku
thoeni
lara