�ݺ�ߣ

Heroku waza 2013
Lessons Learned
Simon Bagreev
Twitter: @status_200
Github: semmin

I attended
Keynote - Michael Lopp
? Why Python, Ruby and Javascript are Slow - Alex Gaynor
? Heroku Secrets - Noah Zoske
? API VS Game: Fight! - Wesley Beary
? Predictable Failure - Ryan Smith
? Postgres: The bits you haven��t found - Peter Van Hardenberg
? Mobile is Not Different - Matt Thompson
? Everything is a Remix - Kirby Ferguson

liked the most
? Keynote - Michael Lopp
? Why Python, Ruby and Javascript are Slow - Alex Gaynor
? Heroku Secrets - Noah Zoske
? API VS Game: Fight! - Wesley Beary
? Predictable Failure - Ryan Smith
? Postgres: The bits you haven��t found - Peter Van Hardenberg
? Mobile is Not Different - Matt Thompson
? Everything is a Remix - Kirby Ferguson

postgres: 12 bits you
haven��t found
Made me realize

postgres: 12 bits you
haven��t found
? psql -- postgres console -- very powerful
? need to upgrade to 9.2
? tons of tools, extensions, types
? tools for monitoring, BI

WITH operator
Auxiliary statements for use in larger queries

WITH status_with_next AS (
SELECT *, lead(state, 1) OVER (partition by agent_uuid order by time) as next from
agent_statuses
),
oops AS (
SELECT * from status_with_next WHERE state = 'training' and next = 'captured'
)
SELECT name, count(*) as training_captures from oops join agents on agents.uuid =
agent_uuid GROUP BY name ORDER by training_captures DESC;

ARRAys
-- create
CREATE TABLE jobseekers (
email text,
industries integer[],
...
);

-- insert
INSERT INTO jobseekers(email, zip, industries, source, created_at, updated_at) VALUES ('
sbagreev@gmail.com', '23453', '{1,2,3}', 'EG', now(), now());
INSERT INTO jobseekers(email, zip, industries, source, created_at, updated_at) VALUES
('simon.bagreev@gmail.com', '23453', '{4,5,6}', 'EG', now(), now());

-- query by inclusion @>
SELECT email FROM jobseekers WHERE industries @> ARRAY[3,1];
-- > sbagreev@gmail.com

-- query by intersection &&
SELECT email FROM jobseekers WHERE industries && ARRAY[4];
-- > simon.bagreev@gmail.com

dblink
Connect to other Postgres Databases
-- add extension
CREATE EXTENSION dblink;

-- connect to the external database
SELECT dblink_connect('quasar_connection','dbname=quasar_development');
dblink_connect
----------------
OK
(1 row)

-- query remote database
SELECT * FROM dblink('quasar_connection', 'select id, email from users') AS
quasar_users(id int, email varchar(255));

id | email
----+----------------------------------------
24 | user1@gmail.com
23 | sbagreev@gmail.com
29 | simon.bagreev@gmail.com
25 | user5@employmentguide.com

listen / notify
Postgres�� own queueing, used to send notifications to clients
-- create listener
LISTEN USER_UPDATES;

/* send notification to listeners
can be done from trigger */
NOTIFY USER_UPDATES, 'User with id=23 just changed email';

-- on the listener
Asynchronous notification "user_updates" with payload "User with id=23 just changed
email" received from server process with PID 81016.

regexes
-- pattern matching
SELECT 'foo bar baz' ~ 's+baz' as matches_pattern;

-- search and replace
UPDATE jobseekers SET zip = regexp_replace(zip, '23', '45');

SELECT zip FROM jobseekers;
zip
-------
45451
45451

-- add extension
UUIDs
CREATE EXTENSION "uuid-ossp";

-- create table with uuid as PK
CREATE TABLE t (uuid uuid PRIMARY KEY DEFAULT uuid_generate_v4(), name text);

d t
Table "public.t"
Column | Type | Modifiers
--------+------+-------------------------------------
uuid | uuid | not null default uuid_generate_v4()
name | text |

INSERT INTO t(name) VALUES ('foo');

SELECT * FROM t;
uuid | name
--------------------------------------+------
7f7941f3-6705-4c28-8028-d1ee5e8d3b8b | foo

-- add extension
hstore !!!
CREATE EXTENSION hstore;
-- create applications table with custom attributes
CREATE TABLE job_applications(uuid uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
attrs hstore,
company_id integer);

-- company 1 wants only name and email in their applications
INSERT INTO job_applications (company_id, attrs) VALUES (1, 'name => "Simon Bagreev", email => "
sbagreev@gmail.com"');

-- company 2 also wants address
INSERT INTO job_applications (company_id, attrs) VALUES (2, 'name => "Simon Bagreev", email =>
"sbagreev@gmail.com", address => "123 Broadwalk St"');

-- company 3 wants phone number too
INSERT INTO job_applications (company_id, attrs) VALUES (3, 'name => "Simon Bagreev", email =>
"sbagreev@gmail.com", address => "123 Broadwalk St", phone => "7570000000"');

SELECT * from job_applications;
attrs | company_id
-------------------------------------------------------------------------------------------------------------+------------
"name"=>"Simon Bagreev", "email"=>"sbagreev@gmail.com" | 1
"name"=>"Simon Bagreev", "email"=>"sbagreev@gmail.com", "address"=>"123 Broadwalk St" | 2
"name"=>"Simon Bagreev", "email"=>"sbagreev@gmail.com", "phone"=>"7570000000", "address"=>"123 Broadwalk St"| 3

hstore
Can add / delete keys

-- company 1 decides to add a phone number to the app
UPDATE job_applications SET attrs = attrs || 'phone=>7573518500'::hstore WHERE company_id=1;

SELECT attrs, company_id FROM job_applications;
attrs | company_id
-------------------------------------------------------------------------------------------------------------+------------
"name"=>"Simon Bagreev", "email"=>"sbagreev@gmail.com", "phone"=>"7570000000", "address"=>"123 Broadwalk St"| 3
"name"=>"Simon Bagreev", "email"=>"sbagreev@gmail.com", "phone"=>"7570000000" | 1

hstore
Querying

-- what companies are using address on their application?
SELECT attrs, company_id FROM job_applications WHERE attrs ? 'address';
attrs | company_id
-------------------------------------------------------------------------------------------------------------+------------
"name"=>"Simon Bagreev", "email"=>"sbagreev@gmail.com", "phone"=>"7573518500", "address"=>"123 Broadwalk St" | 3

-- find all applicants with address 123 Broadwalk
SELECT attrs, company_id FROM job_applications WHERE attrs @> 'address=>"123 Broadwalk St"'::hstore;
attrs | company_id
-------------------------------------------------------------------------------------------------------------+------------
"name"=>"Simon Bagreev", "email"=>"sbagreev@gmail.com", "phone"=>"7573518500", "address"=>"123 Broadwalk St" | 3

HSTore
? can be indexed
? searchable

postgres has more to offer
? full-text search
? TOAST - store large values
? pgcrypto - encryption for your DB
? pg_stat_activity, pg_stat_statements - stats
? postGIS - geographical awesomeness
? JSON and PL/V8 -- schemaless SQL

heroku secrets presentation
? use --version=9.2 flag when adding Postgres addon on Heroku
? encrypt your app��s config, store it on AWS
? use multi-processed and multi-threaded servers
? dataclips! - read-only, easy-to-share quick database queries, exportable to
many formats, can export data to Google docs

Before / After
Questions I had going there:
? Q: Want to hear more explanations about recent ��dumb routing issue��, and suggestions from
Heroku to make it ��smarter��, especially on Cedar stack.
? A: Doesn��t affect Cedar multi-threaded environment
? Q: Want to learn more about multi-threading and multi-processing on Heroku (does it make
sense to do it in Rails/Heroku? If so, how?).
? A: Unicorn for multi-process, JRuby + JVM for multi-threading
? Q: When is Rails 4.0 / Ruby 2.0 will be officially supported by Heroku?
? A: Already is.
? Q: Will Heroku support multiple availability zones (US-West, Asia)?
? A: In Beta (US West, EU)

Before / After
Questions I had going there:
? Q: Will the SLA (99.99% uptime) be improved any time soon?
? A: Constantly working on it
? Q: Want learn more tricks for increasing performance on Heroku, and running large enterprise-
scale applications.
? A: Many-many options

�ݺ�ߣ

Heroku Waza 2013 Lessons Learned

More Related Content

Heroku Waza 2013 Lessons Learned