Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Simple and Awesome Database Tricks

3a1f18e5445bd821f290ed68c1f2d925?s=47 Barrett Clark
February 10, 2017

Simple and Awesome Database Tricks

This talk discusses a collection of things that have been helpful through the years. Some are supported by ActiveRecord, and some fall outside the scope of what ActiveRecord provides. You'll learn things like awesome datatypes, proactive and reactive performance testing, time-series data, backup and restore strategies, PostGIS, scripting with Postgres, and more!

3a1f18e5445bd821f290ed68c1f2d925?s=128

Barrett Clark

February 10, 2017
Tweet

Transcript

  1. DATABASE TRICKS Simple and Awesome @barrettclark RubyConf AU

  2. @BARRETTCLARK @barrettclark RubyConf AU

  3. @barrettclark RubyConf AU

  4. @barrettclark RubyConf AU

  5. 12 @barrettclark RubyConf AU

  6. DATABASE TOOLS @barrettclark RubyConf AU

  7. EXPLAIN ANALYZE Proactive Performance Testing 1 @barrettclark RubyConf AU

  8. POSTGRES TENK TEST TABLE SELECT * FROM tenk WHERE unique1

    > 9000 AND hundred = 10; @barrettclark RubyConf AU
  9. POSTGRES TENK TEST TABLE -[ RECORD 1 ]------------------------------------- id |

    f5855584-f5b9-4067-896e-f622cbfe331c unique1 | 9410 unique2 | 193 two | 0 four | 2 ten | 0 twenty | 10 hundred | 10 thousand | 410 twothousand | 1410 fivethous | 4410 tenthous | 9410 odd | 20 even | 21 stringu1 | YXAAAA stringu2 | LHAAAA string4 | HHHHxx created_at | 2016-12-11 23:32:51.542842 updated_at | 2016-12-11 23:32:51.542842 > 9000 = 10 @barrettclark RubyConf AU
  10. POSTGRES TENK TEST TABLE EXPLAIN ANALYZE SELECT * FROM tenk

    WHERE unique1 > 9000 AND hundred = 10; @barrettclark RubyConf AU
  11. EXPLAIN ANALYZE SELECT * FROM reporting.tenks WHERE unique1 > 9000

    AND hundred = 10; QUERY PLAN --------------------------------------------------------------------------------------------------- Seq Scan on tenks (cost=0.00..323.00 rows=10 width=105) (actual time=0.116..8.047 rows=10 loops=1) Filter: ((unique1 > 9000) AND (hundred = 10)) Rows Removed by Filter: 9990 Planning time: 0.596 ms Execution time: 8.104 ms @barrettclark RubyConf AU
  12. EXPLAIN ANALYZE SELECT * FROM reporting.tenks WHERE unique1 > 9000

    AND hundred = 10; QUERY PLAN --------------------------------------------------------------------------------------------------- Seq Scan on tenks (cost=0.00..323.00 rows=10 width=105) (actual time=0.116..8.047 rows=10 loops=1) Filter: ((unique1 > 9000) AND (hundred = 10)) Rows Removed by Filter: 9990 Planning time: 0.596 ms Execution time: 8.104 ms @barrettclark RubyConf AU
  13. @barrettclark RubyConf AU https://flic.kr/p/9wRhVt

  14. EXPLAIN ANALYZE SELECT * FROM reporting.tenks WHERE unique1 > 9000

    AND hundred = 10; QUERY PLAN ------------------------------------------------------------------------------------------------- ----------------------------------------------- Bitmap Heap Scan on tenks (cost=19.73..207.61 rows=10 width=105) (actual time=0.563..4.548 rows=10 loops=1) Recheck Cond: (unique1 > 9000) Filter: (hundred = 10) Rows Removed by Filter: 989 Heap Blocks: exact=173 -> Bitmap Index Scan on "index_reporting.tenks_on_unique1" (cost=0.00..19.72 rows=992 width=0) (actual time=0.439..0.439 rows=999 loops=1) Index Cond: (unique1 > 9000) Planning time: 1.951 ms Execution time: 4.787 ms @barrettclark RubyConf AU
  15. EXPLAIN ANALYZE SELECT * FROM reporting.tenks WHERE unique1 > 9000

    AND hundred = 10; QUERY PLAN ------------------------------------------------------------------------------------------------- ----------------------------------------------- Bitmap Heap Scan on tenks (cost=19.73..207.61 rows=10 width=105) (actual time=0.563..4.548 rows=10 loops=1) Recheck Cond: (unique1 > 9000) Filter: (hundred = 10) Rows Removed by Filter: 989 Heap Blocks: exact=173 -> Bitmap Index Scan on "index_reporting.tenks_on_unique1" (cost=0.00..19.72 rows=992 width=0) (actual time=0.439..0.439 rows=999 loops=1) Index Cond: (unique1 > 9000) Planning time: 1.951 ms Execution time: 4.787 ms @barrettclark RubyConf AU
  16. 8.104 ms —> 4.787 ms @barrettclark RubyConf AU

  17. Sort (cost=330064.26..330070.21 rows=2381 width=223) (actual time=50145.487..50240.744 rows=62355 loops=1) Sort Key:

    base_with_timestamps.arr_timestamp Sort Method: external merge Disk: 7776kB CTE base_query -> WindowAgg (cost=260870.25..284682.15 rows=476238 width=102) (actual time=31259.222..38590.799 rows=447791 loops=1) -> Sort (cost=260870.25..262060.85 rows=476238 width=74) (actual time=31259.131..33986.029 rows=447791 loops=1) Sort Key: departures.tail_num, departures.day_of_month, departures.dep_time Sort Method: external merge Disk: 37992kB -> Seq Scan on departures (cost=0.00..194796.86 rows=476238 width=74) (actual time=5932.339..15963.390 rows=447791 loops=1) Filter: ((NOT cancelled) AND (year = 1999) AND (month = 6)) Rows Removed by Filter: 5080093 CTE base_with_timestamps -> CTE Scan on base_query (cost=0.00..34527.25 rows=476238 width=191) (actual time=31259.283..47015.957 rows=447791 loops=1) -> CTE Scan on base_with_timestamps (cost=0.00..10721.31 rows=2381 width=223) (actual time=31357.699..49643.945 rows=62355 loops=1) Filter: ((unique_carrier)::text = 'UA'::text) Rows Removed by Filter: 385436 Planning time: 0.968 ms Execution time: 50442.575 ms @barrettclark RubyConf AU
  18. @barrettclark RubyConf AU https://explain.depesz.com

  19. @barrettclark RubyConf AU http://tatiyants.com/pev

  20. * DIFFERENT ENVIRONMENTS ARE DIFFERENT @barrettclark RubyConf AU

  21. PG STAT STATEMENTS Reactive Performance Testing 2 @barrettclark RubyConf AU

  22. CREATE EXTENSION pg_stat_statements; @barrettclark RubyConf AU

  23. EXTENSION?! @barrettclark RubyConf AU

  24. ActiveRecord migration? @barrettclark RubyConf AU

  25. edit postgres config postgresql.conf @barrettclark RubyConf AU

  26. show all; config_file @barrettclark RubyConf AU

  27. @barrettclark RubyConf AU

  28. shared_preload_libraries = 'pg_stat_statements' @barrettclark RubyConf AU

  29. SELECT (total_time / 1000 / 60) AS total_minutes, (total_time /

    calls) AS average_time_ms, calls, query FROM pg_stat_statements ORDER BY 2 DESC LIMIT 100; @barrettclark RubyConf AU
  30. @barrettclark RubyConf AU

  31. DO NOT LEAVE THIS RUNNING IN PRODUCTION!!! @barrettclark RubyConf AU

  32. PG GEM Postgres + Ruby = ! 3 @barrettclark RubyConf

    AU
  33. @barrettclark RubyConf AU https://flic.kr/p/4zUrP9

  34. module PGConnection def conn config = YAML.load_file(File.open('config/database.yml'))['development'] @conn ||= PG.connect(

    :dbname => config['database'], :user => config['username'], :password => config['password'], :host => config['host'] || 'localhost' ) end end class PgStatStatements extend PGConnection def self.stats sql = <<-SQL SELECT (total_time / 1000 / 60) AS total_minutes, (total_time/calls) AS average_time_ms, calls, query FROM pg_stat_statements ORDER BY 2 DESC LIMIT 100; SQL conn.exec(sql) end end if __FILE__==$0 require 'pp' require 'pg' require 'yaml' PgStatStatements.stats.take(5).each do |row| %w(total_minutes average_time_ms).each do |column| row[column] = Float(row[column]) end pp row end end @barrettclark RubyConf AU
  35. module PGConnection def conn config = YAML.load_file(File.open('config/database.yml'))['development'] @conn ||= PG.connect(

    :dbname => config['database'], :user => config['username'], :password => config['password'], :host => config['host'] || 'localhost' ) end end @barrettclark RubyConf AU
  36. class PgStatStatements extend PGConnection def self.stats sql = <<-SQL SELECT

    (total_time / 1000 / 60) AS total_minutes, (total_time/calls) AS average_time_ms, calls, query FROM pg_stat_statements ORDER BY 2 DESC LIMIT 100; SQL conn.exec(sql) end end @barrettclark RubyConf AU
  37. if __FILE__==$0 require 'pp' require 'pg' require 'yaml' PgStatStatements.stats.take(5).each do

    |row| %w(total_minutes average_time_ms).each do |column| row[column] = Float(row[column]) end pp row end end @barrettclark RubyConf AU
  38. if __FILE__==$0 require 'bundler' Bundler.require require 'pp' require 'yaml' PgStatStatements.stats.take(5).each

    do |row| %w(total_minutes average_time_ms).each do |column| row[column] = Float(row[column]) end pp row end end @barrettclark RubyConf AU
  39. DATA TYPES @barrettclark RubyConf AU

  40. JSON JSONB Yep, JSON in your relational DB 4 @barrettclark

    RubyConf AU
  41. API PAYLOADS @barrettclark RubyConf AU

  42. @barrettclark RubyConf AU https://flic.kr/p/dvYf6M

  43. @barrettclark RubyConf AU https://flic.kr/p/76jcmc

  44. STORE THE PAYLOAD!! @barrettclark RubyConf AU

  45. class CreateSweetApiCall < ActiveRecord::Migration[5.0] def change create_table :sweet_api_calls, id: :uuid

    do |t| t.jsonb :payload t.timestamps end end end @barrettclark RubyConf AU
  46. [parks_development] # SELECT * FROM users LIMIT 2; -[ RECORD

    1 ]--------------------------------------------------------------------------------------------- id | 41ca9755-0901-4cf9-9a12-edcf026461f4 fname | Leanne lname | Graham color | 327427 payload | {"id": 1, "name": "Leanne Graham", "email": "Sincere@april.biz", "phone": "1-770-736-8031 x56442", "address": {"geo": {"lat": "-37.3159", "lng": "81.1496"}, "city": "Gwenborough", "suite": "Apt. 556", "street": "Kulas Light", "zipcode": "92998-3874"}, "company": {"bs": "harness real-time e-markets", "name": "Romaguera-Crona", "catchPhrase": "Multi-layered client-server neural-net"}, "website": "hildegard.org", "username": "Bret"} created_at | 2016-12-11 21:33:46.744415 updated_at | 2016-12-11 21:33:46.744415 -[ RECORD 2 ]--------------------------------------------------------------------------------------------- id | 18d94247-7c20-4308-be6c-a54a1db8e899 fname | Ervin lname | Howell color | 55a185 payload | {"id": 2, "name": "Ervin Howell", "email": "Shanna@melissa.tv", "phone": "010-692-6593 x09125", "address": {"geo": {"lat": "-43.9509", "lng": "-34.4618"}, "city": "Wisokyburgh", "suite": "Suite 879", "street": "Victor Plains", "zipcode": "90566-7771"}, "company": {"bs": "synergize scalable supply- chains", "name": "Deckow-Crist", "catchPhrase": "Proactive didactic contingency"}, "website": "anastasia.net", "username": "Antonette"} created_at | 2016-12-11 21:33:46.77968 updated_at | 2016-12-11 21:33:46.77968 @barrettclark RubyConf AU
  47. SELECT id, payload -> 'id' AS payload_id, fname, lname, payload

    -> 'name' AS name FROM users WHERE payload ->> 'id' = '9'; id | payload_id | fname | lname | name --------------------------------------+------------+--------+----------+------------------- 532d9168-fd8e-4165-8a95-dd86603f1fc4 | 9 | Glenna | Reichert | "Glenna Reichert" @barrettclark RubyConf AU
  48. @barrettclark RubyConf AU https://flic.kr/p/9wWC89

  49. CREATE INDEX ON users((payload->>'id')); @barrettclark RubyConf AU

  50. DATERANGE TSRANGE Two dates in one field! 5 @barrettclark RubyConf

    AU
  51. start_date end_date [start_date, end_date) @barrettclark RubyConf AU

  52. SELECT id, unique_carrier, flight_num, tail_num, origin, dest, dep_time, arr_time, dep_delay,

    arr_delay, actual_elapsed_time, UPPER(flight_tsrange) - LOWER(flight_tsrange) AS calculated_elapsed_time, flight_tsrange FROM reporting.june_departures LIMIT 3; @barrettclark RubyConf AU
  53. SELECT id, unique_carrier, flight_num, tail_num, origin, dest, dep_time, arr_time, dep_delay,

    arr_delay, actual_elapsed_time, UPPER(flight_tsrange) - LOWER(flight_tsrange) AS calculated_elapsed_time, flight_tsrange FROM reporting.june_departures LIMIT 3; @barrettclark RubyConf AU
  54. -[ RECORD 1 ]-----------+---------------------------------------------- id | 0865cef2-03fa-403c-9be0-f84a39242416 unique_carrier | WN

    flight_num | 706 origin | DTW dest | MDW actual_elapsed_time | 68 calculated_elapsed_time | 00:08:00 flight_tsrange | ["1999-06-01 00:42:00","1999-06-01 00:50:00") -[ RECORD 2 ]-----------+---------------------------------------------- id | 3c959cf1-e33d-466e-8c65-316165136f6d unique_carrier | DL flight_num | 2004 origin | AGS dest | SAV actual_elapsed_time | 34 calculated_elapsed_time | 00:34:00 flight_tsrange | ["1999-06-01 00:20:00","1999-06-01 00:54:00") -[ RECORD 3 ]-----------+---------------------------------------------- id | 227873c7-6d75-4a7d-892a-3690ffe80969 unique_carrier | AA flight_num | 1013 origin | DFW dest | COS actual_elapsed_time | 108 calculated_elapsed_time | 00:48:00 flight_tsrange | ["1999-06-01 00:07:00","1999-06-01 00:55:00") @barrettclark RubyConf AU
  55. -[ RECORD 1 ]-----------+---------------------------------------------- id | 0865cef2-03fa-403c-9be0-f84a39242416 unique_carrier | WN

    flight_num | 706 origin | DTW dest | MDW actual_elapsed_time | 68 calculated_elapsed_time | 00:08:00 flight_tsrange | ["1999-06-01 00:42:00","1999-06-01 00:50:00") -[ RECORD 2 ]-----------+---------------------------------------------- id | 3c959cf1-e33d-466e-8c65-316165136f6d unique_carrier | DL flight_num | 2004 origin | AGS dest | SAV actual_elapsed_time | 34 calculated_elapsed_time | 00:34:00 flight_tsrange | ["1999-06-01 00:20:00","1999-06-01 00:54:00") -[ RECORD 3 ]-----------+---------------------------------------------- id | 227873c7-6d75-4a7d-892a-3690ffe80969 unique_carrier | AA flight_num | 1013 origin | DFW dest | COS actual_elapsed_time | 108 calculated_elapsed_time | 00:48:00 flight_tsrange | ["1999-06-01 00:07:00","1999-06-01 00:55:00") @barrettclark RubyConf AU
  56. Operator Description = equal <> not equal < less than

    > greater than <= less than or equal >= greater than or equal @> contains range or element <@ range or element is contained by && overlap (have points in common) << strictly left >> strictly right &< does not extend to the right of &> does not extend to the left of -|- is adjacent to + union * intersection - difference @barrettclark RubyConf AU https://www.postgresql.org/docs/9.6/static/functions-range.html
  57. GIST INDEX =, &&, <@, @>, <<, >>, -|-, &<,

    and &> @barrettclark RubyConf AU
  58. SELECT COUNT (*) FROM reporting.june_departures WHERE flight_tsrange @> '1999-06-01 4:00’::timestamp;

    count ------- 81 @barrettclark RubyConf AU
  59. FOR MORE ON RANGE TYPES https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf @barrettclark RubyConf AU

  60. UUID Universal and unique 6 @barrettclark RubyConf AU

  61. -[ RECORD 1 ]-----------+---------------------------------------------- id | 0865cef2-03fa-403c-9be0-f84a39242416 unique_carrier | WN

    flight_num | 706 origin | DTW dest | MDW actual_elapsed_time | 68 calculated_elapsed_time | 00:08:00 flight_tsrange | ["1999-06-01 00:42:00","1999-06-01 00:50:00") id | payload_id | fname | lname | name --------------------------------------+------------+--------+----------+------------------- 532d9168-fd8e-4165-8a95-dd86603f1fc4 | 9 | Glenna | Reichert | "Glenna Reichert" -[ RECORD 1 ]------------------------------------- id | f5855584-f5b9-4067-896e-f622cbfe331c unique1 | 9410 unique2 | 193 two | 0 four | 2 ten | 0 twenty | 10 hundred | 10 thousand | 410 twothousand | 1410 fivethous | 4410 tenthous | 9410 odd | 20 even | 21 stringu1 | YXAAAA stringu2 | LHAAAA string4 | HHHHxx created_at | 2016-12-11 23:32:51.542842 updated_at | 2016-12-11 23:32:51.542842 @barrettclark RubyConf AU
  62. -[ RECORD 1 ]-----------+---------------------------------------------- id | 0865cef2-03fa-403c-9be0-f84a39242416 unique_carrier | WN

    flight_num | 706 origin | DTW dest | MDW actual_elapsed_time | 68 calculated_elapsed_time | 00:08:00 flight_tsrange | ["1999-06-01 00:42:00","1999-06-01 00:50:00") id | payload_id | fname | lname | name --------------------------------------+------------+--------+----------+------------------- 532d9168-fd8e-4165-8a95-dd86603f1fc4 | 9 | Glenna | Reichert | "Glenna Reichert" -[ RECORD 1 ]------------------------------------- id | f5855584-f5b9-4067-896e-f622cbfe331c unique1 | 9410 unique2 | 193 two | 0 four | 2 ten | 0 twenty | 10 hundred | 10 thousand | 410 twothousand | 1410 fivethous | 4410 tenthous | 9410 odd | 20 even | 21 stringu1 | YXAAAA stringu2 | LHAAAA string4 | HHHHxx created_at | 2016-12-11 23:32:51.542842 updated_at | 2016-12-11 23:32:51.542842 @barrettclark RubyConf AU
  63. class EnableUuidExtension < ActiveRecord::Migration[5.0] def change enable_extension 'uuid-ossp' end end

    @barrettclark RubyConf AU
  64. module Parks class Application < Rails::Application # Settings in config/environments/*

    take precedence over those specified here. # Application configuration should go into files in config/initializers # -- all .rb files in that directory are automatically loaded. config.generators do |g| g.orm :active_record, :primary_key_type => :uuid end end end @barrettclark RubyConf AU
  65. class CreateUsers < ActiveRecord::Migration[5.0] def change create_table :users, id: :uuid

    do |t| t.string :fname t.string :lname t.string :color t.jsonb :payload t.timestamps end end end @barrettclark RubyConf AU
  66. class ApplicationRecord < ActiveRecord::Base self.abstract_class = true default_scope -> {

    order(:created_at) } end @barrettclark RubyConf AU
  67. DATA INTEGRITY Benefits @barrettclark RubyConf AU

  68. PREMATURE OPTIMIZATION? YMMV @barrettclark RubyConf AU

  69. WORKING WITH DATA @barrettclark RubyConf AU

  70. GENERATE SERIES Make your own data 7 @barrettclark RubyConf AU

  71. " @barrettclark RubyConf AU

  72. SELECT * FROM generate_series(1, 10) AS n; n ---- 1

    2 3 4 5 6 7 8 9 10 (10 rows) Time: 1.113 ms @barrettclark RubyConf AU
  73. # @barrettclark RubyConf AU

  74. WITH hours AS ( SELECT * FROM generate_series( '1999-01-01 00:00'::timestamp,

    '1999-12-31 23:00'::timestamp, '1 hour') AS hourly ), flights AS ( SELECT id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM reporting.june_departures WHERE tail_num = 'N509' ORDER BY 2 ) SELECT ROW_NUMBER() OVER (ORDER BY hourly) AS row_id, id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM flights RIGHT JOIN hours ON tsrange(hours.hourly, hours.hourly + '1 hour') @> LOWER(flight_tsrange) WHERE tsrange('1999-06-01 00:00'::timestamp, '1999-06-01 23:59'::timestamp) @> hourly; @barrettclark RubyConf AU
  75. WITH hours AS ( SELECT * FROM generate_series( '1999-01-01 00:00'::timestamp,

    '1999-12-31 23:00'::timestamp, '1 hour') AS hourly ), flights AS ( SELECT id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM reporting.june_departures WHERE tail_num = 'N509' ORDER BY 2 ) SELECT ROW_NUMBER() OVER (ORDER BY hourly) AS row_id, id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM flights RIGHT JOIN hours ON tsrange(hours.hourly, hours.hourly + '1 hour') @> LOWER(flight_tsrange) WHERE tsrange('1999-06-01 00:00'::timestamp, '1999-06-01 23:59'::timestamp) @> hourly; COMMON TABLE EXPRESSION AKA: CTE OR “WITH QUERY” @barrettclark RubyConf AU
  76. WITH hours AS ( SELECT * FROM generate_series( '1999-01-01 00:00'::timestamp,

    '1999-12-31 23:00'::timestamp, '1 hour') AS hourly ), flights AS ( SELECT id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM reporting.june_departures WHERE tail_num = 'N509' ORDER BY 2 ) SELECT ROW_NUMBER() OVER (ORDER BY hourly) AS row_id, id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM flights RIGHT JOIN hours ON tsrange(hours.hourly, hours.hourly + '1 hour') @> LOWER(flight_tsrange) WHERE tsrange('1999-06-01 00:00'::timestamp, '1999-06-01 23:59'::timestamp) @> hourly; @barrettclark RubyConf AU
  77. WITH hours AS ( SELECT * FROM generate_series( '1999-01-01 00:00'::timestamp,

    '1999-12-31 23:00'::timestamp, '1 hour') AS hourly ), flights AS ( SELECT id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM reporting.june_departures WHERE tail_num = 'N509' ORDER BY 2 ) SELECT ROW_NUMBER() OVER (ORDER BY hourly) AS row_id, id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM flights RIGHT JOIN hours ON tsrange(hours.hourly, hours.hourly + '1 hour') @> LOWER(flight_tsrange) WHERE tsrange('1999-06-01 00:00'::timestamp, '1999-06-01 23:59'::timestamp) @> hourly; @barrettclark RubyConf AU
  78. WITH hours AS ( SELECT * FROM generate_series( '1999-01-01 00:00'::timestamp,

    '1999-12-31 23:00'::timestamp, '1 hour') AS hourly ), flights AS ( SELECT id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM reporting.june_departures WHERE tail_num = 'N509' ORDER BY 2 ) SELECT ROW_NUMBER() OVER (ORDER BY hourly) AS row_id, id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM flights RIGHT JOIN hours ON tsrange(hours.hourly, hours.hourly + '1 hour') @> LOWER(flight_tsrange) WHERE tsrange('1999-06-01 00:00'::timestamp, '1999-06-01 23:59'::timestamp) @> hourly; @barrettclark RubyConf AU
  79. WITH hours AS ( SELECT * FROM generate_series( '1999-01-01 00:00'::timestamp,

    '1999-12-31 23:00'::timestamp, '1 hour') AS hourly ), flights AS ( SELECT id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM reporting.june_departures WHERE tail_num = 'N509' ORDER BY 2 ) SELECT ROW_NUMBER() OVER (ORDER BY hourly) AS row_id, id, flight_tsrange, flight_num, actual_elapsed_time, origin, dest, distance, tail_num FROM flights RIGHT JOIN hours ON tsrange(hours.hourly, hours.hourly + '1 hour') @> LOWER(flight_tsrange) WHERE tsrange('1999-06-01 00:00'::timestamp, '1999-06-01 23:59'::timestamp) @> hourly; @barrettclark RubyConf AU
  80. row_id | id | flight_tsrange | flight_num | actual_elapsed_time |

    origin | dest | distance | tail_num --------+--------------------------------------+-----------------------------------------------+------------+---------------------+--------+------+----------+---------- 1 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 2 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 3 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 4 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 5 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 6 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 7 | 4c7b3993-bbb3-4d9a-a401-35b73ebcd2fd | ["1999-06-01 06:25:00","1999-06-01 07:32:00") | 1621 | 67 | LAS | SJC | 386 | N509 8 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 9 | 9f64b68d-6e0b-440c-9893-8e66dedab7ca | ["1999-06-01 08:05:00","1999-06-01 09:29:00") | 818 | 84 | SJC | SNA | 342 | N509 10 | a64e9fb1-f227-47fc-a791-6099e31d14f9 | ["1999-06-01 09:50:00","1999-06-01 11:00:00") | 754 | 70 | SNA | OAK | 371 | N509 11 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 12 | 974786e2-2480-4b14-9aec-068e9eab4d98 | ["1999-06-01 11:20:00","1999-06-01 12:25:00") | 540 | 65 | OAK | BUR | 325 | N509 13 | 1fda2bc0-fc0d-44ee-93dd-1299772f506d | ["1999-06-01 12:42:00","1999-06-01 13:51:00") | 1862 | 69 | BUR | SMF | 358 | N509 14 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 15 | f0115c49-edfe-49fb-ad48-cf59d00cfd9c | ["1999-06-01 14:11:00","1999-06-01 15:43:00") | 63 | 92 | SMF | SAN | 480 | N509 16 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 17 | 6f7c1a8d-aae9-4511-afb6-f5a846c3db1f | ["1999-06-01 16:02:00","1999-06-01 17:13:00") | 833 | 71 | SAN | OAK | 446 | N509 18 | 6dd6dcea-f020-4442-a0cf-011de3ab0650 | ["1999-06-01 17:30:00","1999-06-01 18:35:00") | 840 | 65 | OAK | LAX | 337 | N509 19 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 20 | 504925a9-f7ce-4381-b916-15973b19c75e | ["1999-06-01 19:00:00","1999-06-01 20:00:00") | 1637 | 60 | LAX | OAK | 337 | N509 21 | 535f21f0-67ef-4c25-8e3e-8028e89745ce | ["1999-06-01 20:30:00","1999-06-01 21:40:00") | 1231 | 70 | OAK | LAX | 337 | N509 22 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 23 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ 24 | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ | ¤ @barrettclark RubyConf AU
  81. @barrettclark RubyConf AU

  82. WITH series AS ( SELECT * FROM generate_series(1, 10) AS

    n ), evens AS ( SELECT even, count(*) AS even_counts FROM reporting.tenks WHERE even BETWEEN 1 AND 10 GROUP BY 1 ) SELECT n, COALESCE(even_counts,0) AS counts FROM evens RIGHT JOIN series ON n = even; n | counts ----+-------- 1 | 0 2 | 100 3 | 0 4 | 100 5 | 0 6 | 100 7 | 0 8 | 100 9 | 0 10 | 100 @barrettclark RubyConf AU
  83. WITH series AS ( SELECT * FROM generate_series(1, 10) AS

    n ), evens AS ( SELECT even, count(*) AS even_counts FROM reporting.tenks WHERE even BETWEEN 1 AND 10 GROUP BY 1 ) SELECT n, COALESCE(even_counts,0) AS counts FROM evens RIGHT JOIN series ON n = even; n | counts ----+-------- 1 | 0 2 | 100 3 | 0 4 | 100 5 | 0 6 | 100 7 | 0 8 | 100 9 | 0 10 | 100 @barrettclark RubyConf AU
  84. WITH series AS ( SELECT * FROM generate_series(1, 10) AS

    n ), evens AS ( SELECT even, count(*) AS even_counts FROM reporting.tenks WHERE even BETWEEN 1 AND 10 GROUP BY 1 ) SELECT n, COALESCE(even_counts,0) AS counts FROM evens RIGHT JOIN series ON n = even; n | counts ----+-------- 1 | 0 2 | 100 3 | 0 4 | 100 5 | 0 6 | 100 7 | 0 8 | 100 9 | 0 10 | 100 @barrettclark RubyConf AU
  85. WITH series AS ( SELECT * FROM generate_series(1, 10) AS

    n ), evens AS ( SELECT even, count(*) AS even_counts FROM reporting.tenks WHERE even BETWEEN 1 AND 10 GROUP BY 1 ) SELECT n, COALESCE(even_counts,0) AS counts FROM evens RIGHT JOIN series ON n = even; n | counts ----+-------- 1 | 0 2 | 100 3 | 0 4 | 100 5 | 0 6 | 100 7 | 0 8 | 100 9 | 0 10 | 100 @barrettclark RubyConf AU
  86. CREATE (OR REPLACE) VIEW Store a query in the database

    8 @barrettclark RubyConf AU
  87. @barrettclark RubyConf AU https://flic.kr/p/ftayRp

  88. RIDICULOUSLY OVERSIMPLIFIED EXAMPLE @barrettclark RubyConf AU

  89. CREATE OR REPLACE VIEW twos AS ( SELECT * FROM

    tenks WHERE even = 2 ); @barrettclark RubyConf AU
  90. Business logic in the database $ @barrettclark RubyConf AU

  91. SCENIC GEM @barrettclark RubyConf AU

  92. $ bundle exec rails g scenic:model twos invoke active_record create

    app/models/two.rb invoke test_unit create test/models/two_test.rb create test/fixtures/twos.yml create db/views/twos_v01.sql create db/migrate/20161225033113_create_twos.rb @barrettclark RubyConf AU
  93. $ bundle exec rails g scenic:model twos invoke active_record create

    app/models/two.rb invoke test_unit create test/models/two_test.rb create test/fixtures/twos.yml create db/views/twos_v01.sql create db/migrate/20161225033113_create_twos.rb @barrettclark RubyConf AU
  94. class CreateTwos < ActiveRecord::Migration def change create_view :twos end end

    @barrettclark RubyConf AU
  95. $ bundle exec rails c >> Two.first Two Load (12.9ms)

    SELECT "twos".* FROM "twos" ORDER BY "twos"."created_at" ASC LIMIT $1 [["LIMIT", 1]] => #<Two id: nil, unique1: 6701, unique2: 7, two: 1, four: 1, ten: 1, twenty: 1, hundred: 1, thousand: 701, twothousand: 701, fivethous: 1701, tenthous: 6701, odd: 3, even: 2, stringu1: "TXAAAA", stringu2: "HAAAAA", string4: "VVVVxx", created_at: "2016-12-25 03:20:42", updated_at: "2016-12-25 03:20:42”> >> Two.last Two Load (10.4ms) SELECT "twos".* FROM "twos" ORDER BY "twos"."created_at" DESC LIMIT $1 [["LIMIT", 1]] => #<Two id: nil, unique1: 4401, unique2: 9905, two: 1, four: 1, ten: 1, twenty: 1, hundred: 1, thousand: 401, twothousand: 401, fivethous: 4401, tenthous: 4401, odd: 3, even: 2, stringu1: "HNAAAA", stringu2: "ZQOAAA", string4: "HHHHxx", created_at: "2016-12-25 03:22:56", updated_at: "2016-12-25 03:22:56"> @barrettclark RubyConf AU
  96. @barrettclark RubyConf AU

  97. module Parks class Application < Rails::Application # Settings in config/environments/*

    take precedence over those specified here. # Application configuration should go into files in config/initializers # -- all .rb files in that directory are automatically loaded. config.generators do |g| g.orm :active_record, :primary_key_type => :uuid end config.active_record.schema_format = :sql end end @barrettclark RubyConf AU
  98. @barrettclark RubyConf AU https://flic.kr/p/5BtPUt

  99. CREATE (OR REPLACE) FUNCTION Create your own formulae 9 @barrettclark

    RubyConf AU
  100. CHALLENGE Given the value of homes and the current interest

    rate, select all records where the mortgage payment is over a certain number. @barrettclark RubyConf AU
  101. ➤ r is the monthly interest rate ➤ N is

    the number of monthly payments (loan’s term) ➤ P is the borrowed amount (principal) c = rP 1− (1− r)−N @barrettclark RubyConf AU
  102. CREATE OR REPLACE FUNCTION pmt( interest double precision, principal integer

    ) RETURNS numeric AS $$ SELECT ROUND( CAST( (interest / 100 / 12 * principal) / ( 1 - ( ( 1 + (interest / 100 / 12) ) ^ -360 ) ) AS numeric ), 0 ) $$ LANGUAGE SQL @barrettclark RubyConf AU
  103. class CreatePmtFunction < ActiveRecord::Migration def up sql = <<-SQL.strip_heredoc CREATE

    OR REPLACE FUNCTION pmt( interest double precision, principal integer ) RETURNS numeric AS $$ SELECT ROUND( CAST( (interest / 100 / 12 * principal) / ( 1 - ( ( 1 + (interest / 100 / 12) ) ^ -360 ) ) AS numeric ), 0 ) $$ LANGUAGE SQL SQL execute(sql) end def down execute("DROP FUNCTION pmt(double precision, integer);") end end @barrettclark RubyConf AU
  104. WITH mortgage_payments AS ( SELECT id, postal_code, pmt(4.25, principal::int) AS

    mortgage_payment FROM home_values ) SELECT * FROM mortgage_payments WHERE mortgage_payment > 100000 @barrettclark RubyConf AU
  105. MEDIAN @barrettclark RubyConf AU

  106. Q1 - upper quartile (upper median) Q2 - middle quartile

    (median) Q3 - lower quartile (lower median) 3 4 4 5 6 8 8 @barrettclark RubyConf AU
  107. class CreateMedianFunction < ActiveRecord::Migration def up sql = <<-SQL.strip_heredoc CREATE

    OR REPLACE FUNCTION _final_median(anyarray) RETURNS float8 AS $$ WITH q AS ( SELECT val FROM unnest($1) val WHERE VAL IS NOT NULL ORDER BY 1 ), cnt AS ( SELECT COUNT(*) AS c FROM q ) SELECT AVG(val)::float8 FROM ( SELECT val FROM q LIMIT 2 - MOD((SELECT c FROM cnt), 2) OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0) ) q2; $$ LANGUAGE SQL IMMUTABLE; CREATE AGGREGATE median(anyelement) ( SFUNC=array_append, STYPE=anyarray, FINALFUNC=_final_median, INITCOND='{}' ); SQL execute(sql) end def down # CASCADE will drop objects that depend on the function (median aggregate) execute("DROP FUNCTION _final_median(anyarray) CASCADE") end end @barrettclark RubyConf AU https://wiki.postgresql.org/wiki/Aggregate_Median
  108. STRUCTURE AND EXTENSIONS @barrettclark RubyConf AU

  109. SCHEMAS Multiple databases in your database 10 @barrettclark RubyConf AU

  110. Server Database Database Schema Schema Schema Schema Table Table Table

    @barrettclark RubyConf AU
  111. namespace :foo do task :bar do puts "hello" end end

    @barrettclark RubyConf AU
  112. PUBLIC @barrettclark RubyConf AU

  113. “ This is super interesting. When would I use this?

    -You @barrettclark RubyConf AU
  114. ETL Extract, Transform, Load @barrettclark RubyConf AU

  115. BACKUP & RESTORE @barrettclark RubyConf AU

  116. 3RD PARTY FUNCTIONS @barrettclark RubyConf AU

  117. POSTGIS Geospatial % 11 @barrettclark RubyConf AU

  118. INSTALLATION @barrettclark RubyConf AU

  119. POSTGRES.APP @barrettclark RubyConf AU

  120. ACTIVERECORD-POSTGIS-ADAPTER GEM @barrettclark RubyConf AU

  121. development: username: your_username adapter: postgis host: localhost schema_search_path: public, postgis

    @barrettclark RubyConf AU
  122. bundle exec rake db:gis:setup @barrettclark RubyConf AU

  123. & @barrettclark RubyConf AU

  124. class CreateAirports < ActiveRecord::Migration[5.0] def change create_table :airports, id: :uuid

    do |t| t.string :iata, limit: 4 t.string :airport t.string :city t.string :state t.string :country t.float :lat t.float :long t.st_point :lonlat, :geographic => true t.timestamps end add_index :airports, :iata, unique: true add_index :airports, :lonlat, :using => :gist end end @barrettclark RubyConf AU
  125. class CreateAirports < ActiveRecord::Migration[5.0] def change create_table :airports, id: :uuid

    do |t| t.string :iata, limit: 4 t.string :airport t.string :city t.string :state t.string :country t.float :lat t.float :long t.st_point :lonlat, :geographic => true t.timestamps end add_index :airports, :iata, unique: true add_index :airports, :lonlat, :using => :gist end end @barrettclark RubyConf AU
  126. class Airport < ApplicationRecord module Factories GEO = RGeo::Geographic.spherical_factory(:srid =>

    4326) end before_create :set_lonlat def longitude long end def latitude lat end private def set_lonlat self.lonlat = Factories::GEO.point(longitude, latitude) end end @barrettclark RubyConf AU
  127. class Airport < ApplicationRecord module Factories GEO = RGeo::Geographic.spherical_factory(:srid =>

    4326) end before_create :set_lonlat def longitude long end def latitude lat end private def set_lonlat self.lonlat = Factories::GEO.point(longitude, latitude) end end @barrettclark RubyConf AU
  128. class Airport < ApplicationRecord module Factories GEO = RGeo::Geographic.spherical_factory(:srid =>

    4326) end before_create :set_lonlat def longitude long end def latitude lat end private def set_lonlat self.lonlat = Factories::GEO.point(longitude, latitude) end end @barrettclark RubyConf AU
  129. SELECT ST_DistanceSphere( ST_GeomFromText('POINT(-122.3748433 37.61900194)',4326), -- SFO ST_GeomFromText('POINT(-73.87260917 40.77724306)',4326) -- LGA

    )::NUMERIC * 0.000621371 AS dist_miles; dist_miles ------------------------ 2573.09304783170447076 @barrettclark RubyConf AU
  130. sfo = Airport.where(:iata => 'SFO').first lga = Airport.where(:iata => 'LGA').first

    distance_meters = sfo.lonlat.distance(lga.lonlat) => 4145626.3057902646 distance_miles = distance_meters * 0.000621371 => 2575.9719632552024 @barrettclark RubyConf AU
  131. 3 CLOSEST AIRPORTS San Francisco @barrettclark RubyConf AU

  132. SELECT ST_Distance( lonlat, ST_GeomFromText('POINT(-122.3748433 37.61900194)',4326) ) AS distance_meters, iata, airport,

    city, state, country FROM airports WHERE ST_DWithin( -- geom 1 lonlat, -- geom 2 ST_GeomFromText('POINT(-122.3748433 37.61900194)',4326), -- distance in meters (200 miles) 200 * 1609.34 ) ORDER BY 1 LIMIT 3; distance_meters | iata | airport | city | state | country -----------------+------+-----------------------------+---------------+-------+--------- 0 | SFO | San Francisco International | San Francisco | CA | USA 16144.85676752 | HAF | Half Moon Bay | Half Moon Bay | CA | USA 16248.23050337 | SQL | San Carlos | San Carlos | CA | USA @barrettclark RubyConf AU
  133. MAPS @barrettclark RubyConf AU

  134. FOREIGN DATA WRAPPER Access other databases from your database 12

    @barrettclark RubyConf AU
  135. @barrettclark RubyConf AU https://flic.kr/p/kchD6Z

  136. @barrettclark RubyConf AU https://flic.kr/p/8ogC32

  137. MULTI-TENANT APP @barrettclark RubyConf AU

  138. BONUS TIP!! @barrettclark RubyConf AU

  139. .PSQLRC Customize psql 13 @barrettclark RubyConf AU

  140. \set QUIET 1 \pset null '¤' -- Customize prompts \set

    PROMPT1 '%[%033[1m%][%/] # ' \set PROMPT2 '... # ' -- Show how long each query takes to execute \timing -- Use best available output format \x auto -- Verbose error reports \set VERBOSITY verbose -- Use a separate history file per-database \set HISTFILE ~/.psql_history- :DBNAME \set HISTCONTROL ignoredups -- Autocomplete keywords in upper-case even if started lowercase \set COMP_KEYWORD_CASE upper \unset QUIET @barrettclark RubyConf AU
  141. POSTGRES TENK TEST TABLE -[ RECORD 1 ]------------------------------------- id |

    f5855584-f5b9-4067-896e-f622cbfe331c unique1 | 9410 unique2 | 193 two | 0 four | 2 ten | 0 twenty | 10 hundred | 10 thousand | 410 twothousand | 1410 fivethous | 4410 tenthous | 9410 odd | 20 even | 21 stringu1 | YXAAAA stringu2 | LHAAAA string4 | HHHHxx created_at | 2016-12-11 23:32:51.542842 updated_at | 2016-12-11 23:32:51.542842 @barrettclark RubyConf AU
  142. @barrettclark RubyConf AU

  143. THANK YOU @barrettclark RubyConf AU