Keyboard shortcuts

  • 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.

Richard Huxton is...

  • 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

What's this all about?

Full project (small-ish)

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

What's this all about?

  • 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

The Project

UK Supply Teachers

  • 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

Bookings

Match on:

  • age, subject
  • location
  • availability

Order by:

  • travel time

Then:

  • Check id preferred/excluded
  • Contact teacher!

Target capacity

  • 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

Overview - Technology

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

PostgreSQL - Detail

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

Backend - Perl

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

Testing

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

Design

Database Design

  • 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

Design - Elements

  • Schools
  • Teachers
  • Map
  • Bookings

Design - Elements

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

Design - Elements

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

Design - Elements

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

Design - Elements

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

Design - Teacher Skills

-- 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

Design - Teacher Availability

-- 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()

Design - Maps

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

Development Setup

Development Approach

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

The scripts directory

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 scripts

-- 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;

Data scripts

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;

Rollback

Richard ❤ Rollback

Rebuild script

Objectives:

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

Rebuild script

Options:

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

Rebuild script

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

Rebuild script

# 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

Test script

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)

pg_prove script

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;
        
	

Test data

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

Development - I

Bookings Search

Reminder:

  • age, subject
  • location
  • availability

How to index?

You didn't see me do this...

Denormalise

  • 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.

Teacher Search-flags

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)
);

Teacher Search-flags

-- 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;

Teacher Search-flags

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[])

Development - II

Travel Time

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

Travel Time

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

You didn't see me do this either...

To Immutability... and Beyond!

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

Travel Time

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;

Travel Time - Usage

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)

Travel Time - Testing

BEGIN;

SET search_path = pgtap, teacher;

SELECT plan(3);

-- Bad postcode should give (0,0)
SELECT ok(
	map.pc_to_pt('ZZ1 1ZZ') ~= point(0,0),
	'Bad postcode is 0,0'
);

-- NW postcode should be west of SE postcode
SELECT ok(
	(map.pc_to_pt('NW1 8NH'))[0] < (map.pc_to_pt('SE13 7RY'))[0],
	'NW is west of SE'
);

-- NW postcode should be north of SE postcode
SELECT ok(
	(map.pc_to_pt('NW1 8NH'))[1] > (map.pc_to_pt('SE13 7RY'))[1],
	'NW is north of SE'
);

ROLLBACK;

Development - III

All Together

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
...

All Together

...
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;
$$;

All Together

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)

All Together

All Together - Test

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);

All Together - Test

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;

All Together - Test

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)

All Together - Test

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'
);

All Together - Test

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

Deployment

Installation/Setup Tools

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

Ansible

  • 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

Ansible actions

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

Ansible Playbook

---
- 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

    

Ansible Playbook

  - 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

Ansible - Pros and Cons

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

Monitoring

Monitoring - Objectives

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

Monitoring - Nagios

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

Nagios config

# 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

Nagios screens

Nagios screens

Charts generated by pnp4nagios.

And so...

Performance

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

Performance

Our target:

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

Performance

We got:

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

Performance - I

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

Performance - II

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

Performance - II

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.

In Summary

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

Thanks

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

Questions?

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

Any questions?