Is Postgres
the future of Open SQL?
Harold Giménez
Heroku Department of Data
@hgmnz
Slide 2
Slide 2 text
Brief history of the project
Durability
Data types
Query constructs
Advanced Indexing
Extensions
Concurrency
Is Postgres the future of Open SQL?
Slide 3
Slide 3 text
In the beginning...
1977 - 1985: "Ingres"
1986 - 1994: "Postgres"
1995: Postgres95
1996: PostgreSQL 6.0, an open source project.
Slide 4
Slide 4 text
A true open source project
Liberal, BSD-like license.
Community ownership
No corporate "gatekeeper"
Slide 5
Slide 5 text
Durable
Durable
Slide 6
Slide 6 text
Balancing durability and performance
Slide 7
Slide 7 text
Data types
Slide 8
Slide 8 text
Data types timestamptz
interval
ranges
enums
geom
inet, cidr, macaddr
xml
json
array
money
tsvector
text
char
date, timestamp
uuid
boolean
numeric types
Slide 9
Slide 9 text
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
Slide 10
Slide 10 text
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
Slide 11
Slide 11 text
SELECT *
FROM users
WHERE properties -> 'city' = 'Austin'
http://www.postgresql.org/docs/current/static/hstore.html
Slide 12
Slide 12 text
Query constructs
Slide 13
Slide 13 text
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
Slide 14
Slide 14 text
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;
Slide 15
Slide 15 text
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
Slide 16
Slide 16 text
Advanced Indexing
Slide 17
Slide 17 text
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
Slide 18
Slide 18 text
Extensions
Slide 19
Slide 19 text
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;
Slide 20
Slide 20 text
Concurrency
Slide 21
Slide 21 text
Try it now
$ heroku addons:add heroku-postgresql
$ heroku pg:psql
Slide 22
Slide 22 text
No content
Slide 23
Slide 23 text
Side projects to high performance apps
Fully managed
Followers
Forks
Dataclips
Continuous Protection
Run vanilla Postgres, always.
Slide 24
Slide 24 text
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