Is Postgres the Future of Open SQL

What are some of the unique features in postgres that make it so sweet?

Transcript

1. Is Postgres the future of Open SQL? Harold Giménez Heroku Department of Data @hgmnz
2. Brief history of the project Durability Data types Query constructs Advanced Indexing Extensions Concurrency Is Postgres the future of Open SQL?
3. In the beginning... 1977 - 1985: "Ingres" 1986 - 1994: "Postgres" 1995: Postgres95 1996: PostgreSQL 6.0, an open source project.
4. A true open source project Liberal, BSD-like license. Community ownership No corporate "gatekeeper"
5. Durable Durable
6. Balancing durability and performance
7. Data types
8. Data types timestamptz interval ranges enums geom inet, cidr, macaddr xml json array money tsvector text char date, timestamp uuid boolean numeric types
9. CREATE TABLE users( id uuid primary key DEFAULT uuid_generate_v4(), name text not null, tags text[], properties hstore, last_seen_from inet, created_at timestamptz ); http://www.postgresql.org/docs/current/static/datatype.html
10. SELECT * FROM users WHERE tags @> ['beer', 'cheeseburgers']; SELECT name, unnest(tags) as tag FROM users WHERE name = 'hgmnz'; http://www.postgresql.org/docs/current/static/arrays.html
11. SELECT * FROM users WHERE properties -> 'city' = 'Austin' http://www.postgresql.org/docs/current/static/hstore.html
12. Query constructs
13. WITH active_users AS ( SELECT * FROM users WHERE active ), recent_events AS ( SELECT * FROM events WHERE created_at > now() - interval '1 day' ) SELECT * FROM active_users JOIN recent_events WHERE recent_events.name = 'eat_cheeseburger'; http://www.postgresql.org/docs/current/static/queries-with.html
14. WITH moved_rows AS ( DELETE FROM events WHERE date >= '2013-02-01' AND date < '2013-03-01' RETURNING * ) INSERT INTO event_logs SELECT * FROM moved_rows;
15. SELECT meal, lead(meal, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS next_meal FROM meals; http://www.postgresql.org/docs/current/static/tutorial-window.html http://www.postgresql.org/docs/current/static/functions-window.html
16. Advanced Indexing
17. CREATE INDEX idx_active_user_names ON users(name) WHERE active; CREATE INDEX idx_user_age ON users(age(now(), dob)); CREATE INDEX idx_user_properties ON users(properties) USING gist; CREATE INDEX CONCURRENTLY ...; https://devcenter.heroku.com/articles/postgresql-indexes
18. Extensions
19. Useful Extensions PostGIS Procedural Languages (eg: plv8) Data Types Foreign Data Wrappers CREATE EXTENSION hstore; CREATE LANGUAGE plv8; CREATE EXTENSION postgis; CREATE EXTENSION redis_fdw;
20. Concurrency
21. Try it now $ heroku addons:add heroku-postgresql $ heroku pg:psql
23. Side projects to high performance apps Fully managed Followers Forks Dataclips Continuous Protection Run vanilla Postgres, always.
24. Durable: http://www.flickr.com/photos/donshall/4923279827/ Data types/workbench: http://www.flickr.com/photos/26012426@N08/3990405563/in/photostream/ queries/library: http://www.flickr.com/photos/52814185@N02/8541947962/ Indexes: http://www.flickr.com/photos/gserafini/3182508221/ Extensions: http://www.flickr.com/photos/larimdame/69841886/ Concurrency: http://www.flickr.com/photos/vidyo/5273740859/ Photo Credits Thank You! Harold Giménez Heroku Department of Data @hgmnz