Postgres & Rails 4: NoSQL in your SQL Database

Postgres 9.2 brings the best of NoSQL to SQL, with features like hstore, PLV8, and the JSON datatype. Come learn how Rails 4 takes advantage of these and other advanced Postgres features, and how you can make your application more flexible and performant. This workshop is great for anyone new to either PostgreSQL or Rails 4.

Transcript

1. Postgres & Rails 4 NoSQL in your SQL Database 13೥4݄20೔౔༵೔
2. 13೥4݄20೔౔༵೔
3. Postgres 9.2 13೥4݄20೔౔༵೔
4. Postgres 9.2 Rails 4 13೥4݄20೔౔༵೔
5. 13೥4݄20೔౔༵೔
6. • Installing PostgreSQL 13೥4݄20೔౔༵೔
7. • Installing PostgreSQL • (Re)-Introducing PostgreSQL 13೥4݄20೔౔༵೔
8. • Installing PostgreSQL • (Re)-Introducing PostgreSQL • Installing Rails 4 13೥4݄20೔౔༵೔
9. • Installing PostgreSQL • (Re)-Introducing PostgreSQL • Installing Rails 4 • What's New in Rails 4 13೥4݄20೔౔༵೔
10. Installing PostgreSQL 13೥4݄20೔౔༵೔
11. 13೥4݄20೔౔༵೔
12. 13೥4݄20೔౔༵೔
13. 13೥4݄20೔౔༵೔
14. Mac http://postgresapp.com Linux http://postgresql.org/ download Windows http://postgresql.org/ download 13೥4݄20೔౔༵೔
15. 13೥4݄20೔౔༵೔
16. $ psql 13೥4݄20೔౔༵೔
17. 13೥4݄20೔౔༵೔
18. \l – List all Databases \d – List all Tables \x – Turn on/off Expanded Mode \? – List all of the Slash Commands 13೥4݄20೔౔༵೔
19. 13೥4݄20೔౔༵೔
20. 13೥4݄20೔౔༵೔
21. (Re)-Introducing PostgreSQL 13೥4݄20೔౔༵೔
22. { } 13೥4݄20೔౔༵೔
23. 13೥4݄20೔౔༵೔
24. make your application performant by putting as much of your application logic in your database as possible 13೥4݄20೔౔༵೔
25. 13೥4݄20೔౔༵೔
26. • hstore 13೥4݄20೔౔༵೔
27. • hstore • plv8 13೥4݄20೔౔༵೔
28. • hstore • plv8 • JSON Data Type 13೥4݄20೔౔༵೔
29. • hstore • plv8 • JSON Data Type • Full Text Indexing 13೥4݄20೔౔༵೔
30. • hstore • plv8 • JSON Data Type • Full Text Indexing • soundex 13೥4݄20೔౔༵೔
31. • hstore • plv8 • JSON Data Type • Full Text Indexing • soundex • PostGIS 13೥4݄20೔౔༵೔
32. hstore Key-Value Store Data Type 13೥4݄20೔౔༵೔
33. hstore 13೥4݄20೔౔༵೔
34. hstore CREATE EXTENSION hstore; 13೥4݄20೔౔༵೔
35. hstore 13೥4݄20೔౔༵೔
36. hstore UPDATE tab SET h = h || ('c' => '3'); 13೥4݄20೔౔༵೔
37. hstore UPDATE tab SET h = h || ('c' => '3'); UPDATE tab SET h = delete(h, 'k1'); 13೥4݄20೔౔༵೔
38. hstore 13೥4݄20೔౔༵೔
39. hstore SELECT (each(h)).key, (each(h)).value 13೥4݄20೔౔༵೔
40. hstore SELECT (each(h)).key, (each(h)).value INTO stat FROM table; 13೥4݄20೔౔༵೔
41. hstore SELECT (each(h)).key, (each(h)).value INTO stat FROM table; a b c foo 42 NULL 13೥4݄20೔౔༵೔
42. plv8 V8 Javascript Engine in SQL 13೥4݄20೔౔༵೔
43. JSON Data Type Store & Index JSON Objects in SQL 13೥4݄20೔౔༵೔
44. Full Text Indexing For Lightning-Fast Lexical Search 13೥4݄20೔౔༵೔
45. 13೥4݄20೔౔༵೔
46. • Parse Documents / Fields into Tokens 13೥4݄20೔౔༵೔
47. • Parse Documents / Fields into Tokens • Stem Tokens 13೥4݄20೔౔༵೔
48. • Parse Documents / Fields into Tokens • Stem Tokens • Strip Stop Words 13೥4݄20೔౔༵೔
49. • Parse Documents / Fields into Tokens • Stem Tokens • Strip Stop Words • Normalize Synonyms, Alternate Spellings, & Common Misspellings 13೥4݄20೔౔༵೔
50. • Parse Documents / Fields into Tokens • Stem Tokens • Strip Stop Words • Normalize Synonyms, Alternate Spellings, & Common Misspellings • Create Indexes using GIN 13೥4݄20೔౔༵೔
51. • Parse Documents / Fields into Tokens • Stem Tokens • Strip Stop Words • Normalize Synonyms, Alternate Spellings, & Common Misspellings • Create Indexes using GIN • Search for Full & Partial Results w/ Location in Document 13೥4݄20೔౔༵೔
52. soundex Phonological Calculations 13೥4݄20೔౔༵೔
53. soundex 13೥4݄20೔౔༵೔
54. soundex SELECT soundex('Anne'), soundex('Ann'), difference('Ann', 'Anne'); 13೥4݄20೔౔༵೔
55. soundex SELECT soundex('Anne'), soundex('Ann'), difference('Ann', 'Anne'); soundex soundex difference A500 A500 4 13೥4݄20೔౔༵೔
56. soundex 13೥4݄20೔౔༵೔
57. soundex SELECT soundex('Anne'), soundex('Andrew'), difference('Ann', 'Andrew'); 13೥4݄20೔౔༵೔
58. soundex SELECT soundex('Anne'), soundex('Andrew'), difference('Ann', 'Andrew'); soundex soundex difference A500 A536 2 13೥4݄20೔౔༵೔
59. soundex 13೥4݄20೔౔༵೔
60. soundex SELECT soundex('Anne'), soundex('Margaret'), difference('Ann', 'Margaret'); 13೥4݄20೔౔༵೔
61. soundex SELECT soundex('Anne'), soundex('Margaret'), difference('Ann', 'Margaret'); soundex soundex difference A500 M626 0 13೥4݄20೔౔༵೔
62. PostGIS World-Class Geospatial Indexing & Querying 13೥4݄20೔౔༵೔
63. 13೥4݄20೔౔༵೔
64. • Geometry types: points, linestrings, polygons, multipoints, multilinestrings, multipolygons, & geometrycollections. 13೥4݄20೔౔༵೔
65. • Geometry types: points, linestrings, polygons, multipoints, multilinestrings, multipolygons, & geometrycollections. • Spatial operators: area, distance, length, & perimeter. 13೥4݄20೔౔༵೔
66. • Geometry types: points, linestrings, polygons, multipoints, multilinestrings, multipolygons, & geometrycollections. • Spatial operators: area, distance, length, & perimeter. • Spatial operators: union, difference, symmetric difference, & buffers 13೥4݄20೔౔༵೔
67. • Geometry types: points, linestrings, polygons, multipoints, multilinestrings, multipolygons, & geometrycollections. • Spatial operators: area, distance, length, & perimeter. • Spatial operators: union, difference, symmetric difference, & buffers • R-Tree over GIST Indexes 13೥4݄20೔౔༵೔
68. • Geometry types: points, linestrings, polygons, multipoints, multilinestrings, multipolygons, & geometrycollections. • Spatial operators: area, distance, length, & perimeter. • Spatial operators: union, difference, symmetric difference, & buffers • R-Tree over GIST Indexes • Index selectivity support, for mixed spatial/ non-spatial queries. 13೥4݄20೔౔༵೔
69. Installing Rails 4 13೥4݄20೔౔༵೔
70. 13೥4݄20೔౔༵೔
71. 13೥4݄20೔౔༵೔
72. $ gem install rails --pre 13೥4݄20೔౔༵೔
73. 13೥4݄20೔౔༵೔
74. $ rails new myapp 13೥4݄20೔౔༵೔
75. 13೥4݄20೔౔༵೔
76. What's New In Rails 4 13೥4݄20೔౔༵೔
77. 13೥4݄20೔౔༵೔
78. • Cache Digests / "Russian Doll" 13೥4݄20೔౔༵೔
79. • Cache Digests / "Russian Doll" • Turbo Links 13೥4݄20೔౔༵೔
80. • Cache Digests / "Russian Doll" • Turbo Links • Live Streaming 13೥4݄20೔౔༵೔
81. • Cache Digests / "Russian Doll" • Turbo Links • Live Streaming • Mass Assignment Protection 13೥4݄20೔౔༵೔
82. • Cache Digests / "Russian Doll" • Turbo Links • Live Streaming • Mass Assignment Protection • Encrypted Cookies 13೥4݄20೔౔༵೔
83. 13೥4݄20೔౔༵೔
84. • hstore Support 13೥4݄20೔౔༵೔
85. • hstore Support • New Datatype Support: Array, MACADDR, INET, CIDR 13೥4݄20೔౔༵೔
86. hstore Support Direct Ruby Hash Serialization 13೥4݄20೔౔༵೔
87. create_table :examples do |t| t.hstore :dictionary end 13೥4݄20೔౔༵೔
88. class CreateArticles < ActiveRecord::Migration def change execute "create extension hstore" create_table :articles do |t| t.string :name t.text :content t.date :published_on t.string :tags, array: true t.hstore :properties t.timestamps end end end 13೥4݄20೔౔༵೔
89. article = Article.new article.properties = { author: "John Doe", length: 250 } article.save 13೥4݄20೔౔༵೔
90. activerecord-postgres-hstore 13೥4݄20೔౔༵೔
91. Advanced Data Type Support Using the Right Tool for the Job 13೥4݄20೔౔༵೔
92. • INET • CIDR • MACADDR • UUID 13೥4݄20೔౔༵೔
93. create_table :examples do |t| t.uuid :unique_id t.inet :ip_address t.cidr :subnet t.macaddr :mac_address t.integer :array, :array => true end 13೥4݄20೔౔༵೔
94. inetExample = InetExample.new inetExample.ip_address = '127.0.0.0/24' inetExample.ip_address # => #<IPAddr: IPv4:127.0.0.0/255.255.255.0> inetExample.save 13೥4݄20೔౔༵೔
95. Array Data Type Support Direct Ruby Array Serialization 13೥4݄20೔౔༵೔
96. class CreateArticles < ActiveRecord::Migration def change execute "create extension hstore" create_table :articles do |t| t.string :name t.text :content t.date :published_on t.string :tags, array: true t.hstore :properties t.timestamps end end end 13೥4݄20೔౔༵೔
97. article = Article.new article.tags = [ "Non-Fiction", "News", "Opinion" ] article.save 13೥4݄20೔౔༵೔
98. 13೥4݄20೔౔༵೔
99. • The Database is not just a "Big Hash in the Sky" 13೥4݄20೔౔༵೔
100. • The Database is not just a "Big Hash in the Sky" • Shift computation and logic to the database to maximize performance 13೥4݄20೔౔༵೔
101. • The Database is not just a "Big Hash in the Sky" • Shift computation and logic to the database to maximize performance • Use advanced Postgres data types for simpler data modeling & abstraction 13೥4݄20೔౔༵೔
102. 13೥4݄20೔౔༵೔
103. • http://railscasts.com 13೥4݄20೔౔༵೔
104. • http://railscasts.com • http://postgresql.org 13೥4݄20೔౔༵೔
105. • http://railscasts.com • http://postgresql.org • http://devcenter.heroku.com 13೥4݄20೔౔༵೔
106. Postgres & Rails 4 NoSQL in your SQL Database 13೥4݄20೔౔༵೔