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!

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