• Pressing 'f' toggles fullscreen viewing
• Pressing 'w' toggles widescreen
• Pressing 'o' toggles overview mode
• Pressing 'ESC' toggles off these goodies

Slides produced using code from Google io-2012.

• Long-time community member
• PostgreSQL user since v6.5
• That's since the last century!
• Independent consultant based in London
• Big jobs for small clients
• Small jobs for big clients

Full project (small-ish)

• Design
• Development
• Testing
• Deployment
• Monitoring
• Tools and practices

• Not claiming it's all "best practice"
• Guaranteed better than "worst practice"
• No "experimental" extensions/tools
• All suitable for smaller teams/projects
• Works for me

• Cover for sickness, maternity, unfilled vacancy, "trial"
• We're focusing on day-to-day - response is time-critical
• ~25,000 schools in England
• ~45,000 supply teachers
• Allow more for nursery-nurses, support staff etc
• Total spending of £850 million/year

Match on:

• age, subject
• location
• availability

Order by:

• travel time

Then:

• Check id preferred/excluded
• Contact teacher!

• Assume 10% of schools book each morning/evening
• 2,500 requests over about 10 minutes
• Max 5 sec delay for booking response
• Need ~ 10 req/second

• OS: Standard VMs (Debian)
• DB: PostgreSQL (9.2)
• Backend: Perl (modern)
• Frontend: Javascript (:-()

• PostgreSQL 9.2
• apt.postgresql.org
• Extensions:
  • pg_stat_statements
  • crypto
  • intarray
  • pgTAP
• Features:
  • Functional indexes
  • Partial indexes
  • GiST/GIN indexes
  • Custom functions (plpgsql)

• perlbrew
• local::lib
• Dancer
• DBI / DBIx::Class
• Test::More (+ more)
• PgBouncer

• Browser: selenium, phantomjs
• Frontend: qUnit, jasmine etc
• Backend: TAP, Test::More
• DB: pgTAP
• OS: Puppet/Chef/CfEngine/Ansible

• schemas by meaning
  • teacher.teachers, teacher.skills
  • lookup.subjects, util.wkend()
• multiple users
  • db-user: owns database
  • app-user: restricted
• core functionality in DB
  • Core business-rules
  • Auth, search functions
  • Requires a decent ORM/raw SQL
• thin backend wrapper
  • parsing, translation
  • caching

• Schools
• Teachers
• Map
• Bookings

• Schools
  • Name, address
  • Ages, specialisms
  • All courtesy HMG
• Teachers
• Map
• Bookings

• Schools
• Teachers
  • Name, contact details
  • Skills: ages, subjects
  • Availability
• Map
• Bookings

• Schools
• Teachers
• Map
  • Postcodes (courtesy HMG)
  • Lat/lon, easting/northing
• Bookings

• Schools
• Teachers
• Map
• Bookings
  • School
  • Ages, subjects
  • Dates

-- Table: teacher.skills
  id       longcode  NOT NULL
  tid      int       NOT NULL REFERENCES teachers ON DELETE CASCADE
  ages     int4range NOT NULL REFERENCES lookup.age_ranges (ages)
  subject  longcode  NOT NULL REFERENCES lookup.subjects (code)

-- Sample data
1  101  [5,11)   QTS
2  102  [11,16)  MATHS
3  102  [16,18)  CHEM

• Primary: no subjects
• Secondary: subjects
• Age-range is per subject

-- Table: teacher.availability
  wkend  date    NOT NULL
  tid    int     NOT NULL REFERENCES teachers ON DELETE CASCADE
  avail  char(7) NOT NULL

-- Sample data
2013-07-07  101 aaaaaaa
2013-07-14  101 HHHHHHH

• a = available, S=sick, W=working etc
• Can build any length of availability through string_agg()

UK Postcodes

• Fine-grained (but variably-sized)
• Don't line up with any organisational/political boundaries
• Are regularly added to
• Are occasionally changed/deleted
• Can map to the same lat/lon
• Or none at all

• Script db schema in sections
  • Complete rebuild script
• Script-generated test data
• Scripted structural tests
• Scripted functional tests

data_20_map.sql
data_21_map_traveltimes.sql
...
rebuild_db.sh
run_tests.sh
...
schema_00_users.sql
schema_01_database.sql
schema_03_types.sql
...
t/
testdata/

-- schema_40_teacher.sql
BEGIN;

SET search_path=teacher;

-- ...

CREATE TABLE lookup.age_ranges (
    ages        int4range NOT NULL,
    description text NOT NULL,
    PRIMARY KEY (ages)
);
-- ...

-- ROLLBACK;
COMMIT;

BEGIN;

COPY lookup.age_ranges (ages,description) FROM stdin;
[1,5)	Nursery
[5,11)	Primary
[11,16)	Secondary
[16,18)	F.E.
\.

// etc

-- ROLLBACK;
COMMIT;

Richard ❤ Rollback

Objectives:

• Recreate whole DB from scratch
• Populate with test-data
• Keep run-time short

Options:

• RECREATE_USER=y|N
• REBUILD=Y|n
• POPULATE=Y|n
• RUN_TESTS=Y|n
• DBUSER, APPUSER, SUPERUSER, DBNAME, PGPORT...

PSQLCMD=${PSQLCMD:-/usr/lib/postgresql/9.2/bin/psql}
PSQL="$PSQLCMD -X -h $PGHOST -p $PGPORT -U $DBUSER -d $DBNAME 
    -v dbname=$DBNAME -v username=$DBUSER -v appuser=$APPUSER -v superuser=$SUPERUSER 
    -v home=$APPDIR/ -v ON_ERROR_STOP=1 
    -q --pset pager=off"

SU_PSQL=...as above but for superuser

# Halt on any error
set -e
if [ $REBUILD = "Y" ]; then
    echo "Creating db..."
    $SU_PSQL -f schema_01_database.sql
    #
    $PSQL -f schema_03_types.sql
    $PSQL -f schema_20_map_postcodes.sql

Simple wrapper around pg_prove:

• Runs SQL and outputs TAP-compatible test results.
• Can test user capabilities, schema features, queries, exceptions.
• Two parts: PG's pgTap extension + Perl-based pg_prove.
• Very useful indeed (thanks Dave Wheeler)

BEGIN;

SET search_path = pgtap, whatever;

SELECT plan(3);

SELECT ok(1 < 2, 'One less than two - phew!');
SELECT is(2, 1+1, 'Addition also ok');
SELECT throws_like('SELECT 1/0', 
    '%division%', 
    'Dividing by zero still bad');
ROLLBACK;
        
	

Perl script to generate suitable amounts of test data

• DBI to read lookup values
• Output as COPY
  • easier to debug
  • save to file or pipe to psql
• Stateless: mk_tchr(i) = f(i)
• Pre: work_mem, maintenance_work_mem, drop indexes, disable triggers
• Post: restore indexes, enable triggers
• Tunable: small+fast | large+slow
• Start simple and get more realistic as required

Reminder:

• age, subject
• location
• availability

How to index?

• Build an intarray:
  • Availability: 0-27 (4 weeks)
  • Skills: 1000+ for ages, 2000+ for subjects
  • Map: 10000+ east/northing grid (9 ints)
• contains "@>"
• GiST/GIN index

• Not too big a relational crime.
• Read-only - basically a materialized view.
• Will need some triggers to keep it in sync though.

CREATE TABLE search_flags (
    tid       int     NOT NULL REFERENCES teachers ON DELETE CASCADE,
    is_active boolean NOT NULL,
    flags     int[]   NOT NULL, 
    PRIMARY KEY (tid)
);

-- gist|gin__int_ops is from the intarray extension

CREATE INDEX  tch_search_flags_idx 
ON     teacher.search_flags 
USING  gist (flags gist__int_ops) 
WHERE  is_active;

SELECT tid FROM teacher.search_flags 
WHERE is_active AND flags @> ARRAY[1,2,3,1011,2035,10739];

                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on search_flags  (cost=641.79..9653.96 rows=2502 width=4)
   Recheck Cond: ((flags @> '{1,2,3,1011,2035,10739}'::integer[]) AND is_active)
   ->  Bitmap Index Scan on tch_search_flags_idx  (cost=0.00..641.16 rows=2502 width=0)
         Index Cond: (flags @> '{1,2,3,1011,2035,10739}'::integer[])

• Use distance as a proxy
• Mostly works fine
• Do a final check vs online service

• Table map.postcodes - easting/northing
• Distance sort "<->"
• GiST index

• IMMUTABLE function index
• Maps postcode ⇒ point
• But postcodes change...
• ...reindex when postcode table updated

CREATE FUNCTION map.pc_to_pt(tgt_pcode varchar(10)) RETURNS point
LANGUAGE plpgsql AS $$
  DECLARE
    e int;
    n int;
  BEGIN
    SELECT easting,northing INTO e,n FROM map.postcodes WHERE pcode = tgt_pcode;
    IF NOT FOUND THEN
      e := 0;
      n := 0;
    END IF;
    RETURN point(e,n);
  END;
$$ IMMUTABLE;

WITH pcs AS (VALUES ('SE13 6RY'), ('SE9 3JN'), ('W12 9PT'))
SELECT p0.column1 AS from_pc, p1.column1 AS to_pc, 
(map.pc_to_pt(p0.column1) <-> map.pc_to_pt(p1.column1)):

 from_pc  |  to_pc   | dist  
----------+----------+-------
 SE13 6RY | SE13 6RY |     0
 SE13 6RY | SE9 3JN  |  4877
 SE13 6RY | W12 9PT  | 17020
 SE9 3JN  | SE13 6RY |  4877
 SE9 3JN  | SE9 3JN  |     0
 SE9 3JN  | W12 9PT  | 21895
 W12 9PT  | SE13 6RY | 17020
 W12 9PT  | SE9 3JN  | 21895
 W12 9PT  | W12 9PT  |     0
(9 rows)

CREATE FUNCTION teacher.booking_candidates(
    tgt_pcode   varchar, 
    tgt_age     int,
    tgt_subj    varchar,
    tgt_days    date[],
    max_results int = 10)
RETURNS SETOF teacher.teachers
...

...
BEGIN
    tgt_flags := [combine args into flags]

    RETURN QUERY SELECT t.*
    FROM  teacher.teachers     t
    JOIN  teacher.search_flags sf USING (tid)
    WHERE is_active AND sf.flags @> tgt_flags
    ORDER BY map.pc_to_pt(postcode) <-> map.pc_to_pt(tgt_pcode)
    LIMIT max_results;
END;
$$;

SELECT * FROM booking_candidates('SE1 8SW', 9, 'QTS', '{2013-07-08,2013-07-09}');

  tid  | first_name | last_name |    telnum     |           email           | postcode |
-------+------------+-----------+---------------+---------------------------+----------+
 31691 | Ray        | Leopard   | 071 0161 0691 | ray.leopard@google.com    | SE17 3DT |
 31771 | William    | Bee       | 071 0841 0771 | william.bee@google.com    | SW1V 3EU |
 35511 | May        | Viper     | 071 0381 0511 | may.viper@yahoo.com       | SE5 0TS  |
 46431 | Gil        | Viper     | 071 0701 0431 | gil.viper@yahoo.com       | EC1V 3SJ |
 22473 | Yolanda    | Dog       | 071 0883 0473 | yolanda.dog@bt.com        | SE5 0JD  |
 31191 | Ray        | Viper     | 071 0661 0191 | ray.viper@yahoo.com       | N1 9QG   |
 44699 | Zack       | Denmark   | 071 0529 0699 | zack.denmark@google.com   | E1 8HY   |
  2819 | Ulrica     | Denmark   | 071 0049 0819 | ulrica.denmark@google.com | SW8 4JS  |
 20411 | May        | Leopard   | 071 0281 0411 | may.leopard@google.com    | SW3 2QS  |
 48329 | Elena      | Denmark   | 071 0259 0329 | elena.denmark@bt.com      | N1 0WF   |
(10 rows)

Setup...

BEGIN;

SET search_path=pgtap,teacher,public;

SELECT plan(1);

CREATE TEMP TABLE expected_vals (i int, tid int, pc varchar, wkend date);

CREATE TEMP TABLE actual_vals (i int, tid int, pc varchar, wkend date);

Build our expected results...

WITH tgt_teachers AS (
    SELECT * FROM teachers WHERE is_active AND tid < 1000 AND postcode <> 'SE10 8WT'
) ,tgt_wk AS (
    SELECT min(wkend) AS wkend FROM availability
) ,selected_teachers AS (
    SELECT DISTINCT t.tid, t.postcode, tw.wkend
    FROM tgt_teachers t
    JOIN skills       ts USING (tid)
    JOIN availability ta USING (tid)
    JOIN tgt_wk       tw USING (wkend)
    WHERE ts.ages = '[5,11)' AND ts.subject = 'QTS'
          AND ta.avail LIKE 'a%'
    ORDER BY tid LIMIT 5
)
INSERT INTO expected_vals
SELECT row_number() OVER (), tid, postcode, wkend
FROM selected_teachers;

Giving us this.

 i | tid |    pc    |   wkend    
---+-----+----------+------------
 1 | 429 | E1 2JU   | 2013-07-07
 2 | 385 | E1W 3DB  | 2013-07-07
 3 | 289 | BR1 4LL  | 2013-07-07
 4 | 149 | SE10 0PU | 2013-07-07
 5 | 105 | SE10 0DG | 2013-07-07
(5 rows)

Which we test like this.

DO $$
DECLARE
  tst RECORD;
  res RECORD;
BEGIN
    FOR tst IN SELECT * FROM expected_vals LOOP
        res := booking_candidates(tst.pc, 9, 'QTS', ARRAY[tst.wkend-6], 1);
        INSERT INTO actual_vals VALUES (tst.i, res.tid, res.postcode, tst.wkend);
    END LOOP;
END;
$$;

SELECT results_eq(
    $$SELECT i, pc, tid FROM actual_vals ORDER BY i$$,
    $$SELECT i, pc, tid FROM expected_vals ORDER BY i$$,
    'booking_candidates() finds teacher at exact postcode'
);

In a run like this.

$ pg_prove --port=5433 -U pgday t/*
t/20_mode_agg.sql ..... ok   
t/20_pc_to_pt.sql ..... ok   
t/20_to_district.sql .. ok   
t/45_wkend_fns.sql .... ok   
t/50_bookings.sql ..... ok   
t/60_flags_set.sql .... ok   
t/61_avail_flags.sql .. ok   
t/62_pc_flags.sql ..... ok   
t/99_permissions.sql .. ok   
t/avail_rules.sql ..... ok   
All tests successful.
Files=10, Tests=28, 25 wallclock secs ( 0.09 usr  0.03 sys ...)
Result: PASS

• OS: apt
• Configure OS / PostgreSQL
• DB: rebuild.sh
• Perl: cpanm
• Tie them together?

• Python tool - not too many dependencies
• Drives actions over ssh
• Config file lists target hosts
• Hosts can be in one or more groups
• Playbooks list actions
• Actions should be idempotent
• Groups, hosts, playbooks can define/use variables

• packaging: apt, yum
• files: copy, template, lineinfile
• system: users, services
• commands: exec'd or passed to shell
• PG: create db, users, grants

---
- hosts: all
  user:  root
  tasks:
  - name: install pgsql
    apt: pkg=$item
    with_items:
    - postgresql-9.2
    - postgresql-client-9.2
    - postgresql-contrib-9.2
    - postgresql-server-dev-9.2
    - pgxnclient

    

  - name: set archive-mode (enabled)
    lineinfile: dest   = /etc/postgresql/9.2/main/postgresql.conf
                insertbefore = "^#archive_mode"
                regexp = "^archive_mode ="
                line   = "archive_mode = on"
                state  = present

  ...

  - name: start pgsql
    service: name=postgresql enabled=yes state=started

Pros:

• Simple enough to understand
• Small enough to manage 1 vm
• Can talk to anything with a ssh server
• Docs pretty good

Cons:

• First vm takes time - repeated test runs
• Not for 5,000 servers

• Overview on one screen
• Detailed view of one service on one host
• Alerts if service unavailable
• Basic performance stats

• Popular
• Lots of plugins
• Lots of alert options
• Simple to write your own
• Can be fiddly to debug

# Nagios NRPE remote config
command[check_root_disk] = /usr/lib/nagios/plugins/check_disk -w 20% -c 10% -p /

command[pgday_lag] = /usr/lib/nagios/plugins/check_postgres.pl --action=hot_standby_delay
  --host=localhost,{{hostvars[other_host]['ansible_ssh_host']}} 
  --dbuser=pgrepl --dbname=postgres --dbpass=abc123 
  --warning=123 --critical=456

Charts generated by pnp4nagios.

Need a simple script to test random queries.

• Fetch all schools
• Build a list of random bookings
• Fork children to run queries
• Wait for all children to complete
• params
  • number of bookings
  • number of children
  • random seed

Our target:

• 50,000 teachers
• target = 10 req/sec
• 25,000 schools
• 2,500 requests in 10 mins

We got:

• 50,000 teachers
• target = 10 req/sec
• actual = 75 req/sec
• Too easy - 1 core, 1GB RAM

Try bigger:

• 500,000 teachers
• target = 10 req/sec
• actual = 14 req/sec
• Still using - 1 core, 1GB RAM...
• ...but I've already ordered a bigger VM

OK, bigger again:

• 5,000,000 teachers...
• ...it's starting to take a while to generate the test data now
• target = 10 req/sec
• Bigger box - 4 core, 8GB RAM
• actual = 29 req/sec

But that's because we're matching ~10,000 teachers and only keeping 10 of them.

Restrict the area of search so we only throw 5,000 away and we get...

• previous = 29 req/sec
• is now = 60 req/sec
• That's on a vm costing ~ £70/month.

• Use PostgreSQL's features
• Script database construction
• Test database construction & functionality
• Automate deployment
• Set up monitoring

Thanks to:

• Postcodes - ordnance Survey (data.gov.uk)
• Schools - Dept for Education (data.gov.uk)
• VMs - Bytemark and Memset
• OS - Debian Project
• DB - PostgreSQL team
• Contributors to all the tools that made this possible

Code will be on github shortly. Check planet.postgresql.org for a notice.

Any questions?