Tweet
Tweet

Slide 1

Slide 1 text

Postgres & Rails 4 NoSQL in your SQL Database 13೥4݄20೔౔༵೔

Slide 2

Slide 2 text

13೥4݄20೔౔༵೔

Slide 3

Slide 3 text

Postgres 9.2 13೥4݄20೔౔༵೔

Slide 4

Slide 4 text

Postgres 9.2 Rails 4 13೥4݄20೔౔༵೔

Slide 5

Slide 5 text

13೥4݄20೔౔༵೔

Slide 6

Slide 6 text

• Installing PostgreSQL 13೥4݄20೔౔༵೔

Slide 7

Slide 7 text

• Installing PostgreSQL • (Re)-Introducing PostgreSQL 13೥4݄20೔౔༵೔

Slide 8

Slide 8 text

• Installing PostgreSQL • (Re)-Introducing PostgreSQL • Installing Rails 4 13೥4݄20೔౔༵೔

Slide 9

Slide 9 text

• Installing PostgreSQL • (Re)-Introducing PostgreSQL • Installing Rails 4 • What's New in Rails 4 13೥4݄20೔౔༵೔

Slide 10

Slide 10 text

Installing PostgreSQL 13೥4݄20೔౔༵೔

Slide 11

Slide 11 text

13೥4݄20೔౔༵೔

Slide 12

Slide 12 text

13೥4݄20೔౔༵೔

Slide 13

Slide 13 text

13೥4݄20೔౔༵೔

Slide 14

Slide 14 text

Mac http://postgresapp.com Linux http://postgresql.org/ download Windows http://postgresql.org/ download 13೥4݄20೔౔༵೔

Slide 15

Slide 15 text

13೥4݄20೔౔༵೔

Slide 16

Slide 16 text

$ psql 13೥4݄20೔౔༵೔

Slide 17

Slide 17 text

13೥4݄20೔౔༵೔

Slide 18

Slide 18 text

\l – List all Databases \d – List all Tables \x – Turn on/off Expanded Mode \? – List all of the Slash Commands 13೥4݄20೔౔༵೔

Slide 19

Slide 19 text

13೥4݄20೔౔༵೔

Slide 20

Slide 20 text

13೥4݄20೔౔༵೔

Slide 21

Slide 21 text

(Re)-Introducing PostgreSQL 13೥4݄20೔౔༵೔

Slide 22

Slide 22 text

{ } 13೥4݄20೔౔༵೔

Slide 23

Slide 23 text

13೥4݄20೔౔༵೔

Slide 24

Slide 24 text

make your application performant by putting as much of your application logic in your database as possible 13೥4݄20೔౔༵೔

Slide 25

Slide 25 text

13೥4݄20೔౔༵೔

Slide 26

Slide 26 text

• hstore 13೥4݄20೔౔༵೔

Slide 27

Slide 27 text

• hstore • plv8 13೥4݄20೔౔༵೔

Slide 28

Slide 28 text

• hstore • plv8 • JSON Data Type 13೥4݄20೔౔༵೔

Slide 29

Slide 29 text

• hstore • plv8 • JSON Data Type • Full Text Indexing 13೥4݄20೔౔༵೔

Slide 30

Slide 30 text

• hstore • plv8 • JSON Data Type • Full Text Indexing • soundex 13೥4݄20೔౔༵೔

Slide 31

Slide 31 text

• hstore • plv8 • JSON Data Type • Full Text Indexing • soundex • PostGIS 13೥4݄20೔౔༵೔

Slide 32

Slide 32 text

hstore Key-Value Store Data Type 13೥4݄20೔౔༵೔

Slide 33

Slide 33 text

hstore 13೥4݄20೔౔༵೔

Slide 34

Slide 34 text

hstore CREATE EXTENSION hstore; 13೥4݄20೔౔༵೔

Slide 35

Slide 35 text

hstore 13೥4݄20೔౔༵೔

Slide 36

Slide 36 text

hstore UPDATE tab SET h = h || ('c' => '3'); 13೥4݄20೔౔༵೔

Slide 37

Slide 37 text

hstore UPDATE tab SET h = h || ('c' => '3'); UPDATE tab SET h = delete(h, 'k1'); 13೥4݄20೔౔༵೔

Slide 38

Slide 38 text

hstore 13೥4݄20೔౔༵೔

Slide 39

Slide 39 text

hstore SELECT (each(h)).key, (each(h)).value 13೥4݄20೔౔༵೔

Slide 40

Slide 40 text

hstore SELECT (each(h)).key, (each(h)).value INTO stat FROM table; 13೥4݄20೔౔༵೔

Slide 41

Slide 41 text

hstore SELECT (each(h)).key, (each(h)).value INTO stat FROM table; a b c foo 42 NULL 13೥4݄20೔౔༵೔

Slide 42

Slide 42 text

plv8 V8 Javascript Engine in SQL 13೥4݄20೔౔༵೔

Slide 43

Slide 43 text

JSON Data Type Store & Index JSON Objects in SQL 13೥4݄20೔౔༵೔

Slide 44

Slide 44 text

Full Text Indexing For Lightning-Fast Lexical Search 13೥4݄20೔౔༵೔

Slide 45

Slide 45 text

13೥4݄20೔౔༵೔

Slide 46

Slide 46 text

• Parse Documents / Fields into Tokens 13೥4݄20೔౔༵೔

Slide 47

Slide 47 text

• Parse Documents / Fields into Tokens • Stem Tokens 13೥4݄20೔౔༵೔

Slide 48

Slide 48 text

• Parse Documents / Fields into Tokens • Stem Tokens • Strip Stop Words 13೥4݄20೔౔༵೔

Slide 49

Slide 49 text

• Parse Documents / Fields into Tokens • Stem Tokens • Strip Stop Words • Normalize Synonyms, Alternate Spellings, & Common Misspellings 13೥4݄20೔౔༵೔

Slide 50

Slide 50 text

• Parse Documents / Fields into Tokens • Stem Tokens • Strip Stop Words • Normalize Synonyms, Alternate Spellings, & Common Misspellings • Create Indexes using GIN 13೥4݄20೔౔༵೔

Slide 51

Slide 51 text

• 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೔౔༵೔

Slide 52

Slide 52 text

soundex Phonological Calculations 13೥4݄20೔౔༵೔

Slide 53

Slide 53 text

soundex 13೥4݄20೔౔༵೔

Slide 54

Slide 54 text

soundex SELECT soundex('Anne'), soundex('Ann'), difference('Ann', 'Anne'); 13೥4݄20೔౔༵೔

Slide 55

Slide 55 text

soundex SELECT soundex('Anne'), soundex('Ann'), difference('Ann', 'Anne'); soundex soundex difference A500 A500 4 13೥4݄20೔౔༵೔

Slide 56

Slide 56 text

soundex 13೥4݄20೔౔༵೔

Slide 57

Slide 57 text

soundex SELECT soundex('Anne'), soundex('Andrew'), difference('Ann', 'Andrew'); 13೥4݄20೔౔༵೔

Slide 58

Slide 58 text

soundex SELECT soundex('Anne'), soundex('Andrew'), difference('Ann', 'Andrew'); soundex soundex difference A500 A536 2 13೥4݄20೔౔༵೔

Slide 59

Slide 59 text

soundex 13೥4݄20೔౔༵೔

Slide 60

Slide 60 text

soundex SELECT soundex('Anne'), soundex('Margaret'), difference('Ann', 'Margaret'); 13೥4݄20೔౔༵೔

Slide 61

Slide 61 text

soundex SELECT soundex('Anne'), soundex('Margaret'), difference('Ann', 'Margaret'); soundex soundex difference A500 M626 0 13೥4݄20೔౔༵೔

Slide 62

Slide 62 text

PostGIS World-Class Geospatial Indexing & Querying 13೥4݄20೔౔༵೔

Slide 63

Slide 63 text

13೥4݄20೔౔༵೔

Slide 64

Slide 64 text

• Geometry types: points, linestrings, polygons, multipoints, multilinestrings, multipolygons, & geometrycollections. 13೥4݄20೔౔༵೔

Slide 65

Slide 65 text

• Geometry types: points, linestrings, polygons, multipoints, multilinestrings, multipolygons, & geometrycollections. • Spatial operators: area, distance, length, & perimeter. 13೥4݄20೔౔༵೔

Slide 66

Slide 66 text

• 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೔౔༵೔

Slide 67

Slide 67 text

• 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೔౔༵೔

Slide 68

Slide 68 text

• 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೔౔༵೔

Slide 69

Slide 69 text

Installing Rails 4 13೥4݄20೔౔༵೔

Slide 70

Slide 70 text

13೥4݄20೔౔༵೔

Slide 71

Slide 71 text

13೥4݄20೔౔༵೔

Slide 72

Slide 72 text

$ gem install rails --pre 13೥4݄20೔౔༵೔

Slide 73

Slide 73 text

13೥4݄20೔౔༵೔

Slide 74

Slide 74 text

$ rails new myapp 13೥4݄20೔౔༵೔

Slide 75

Slide 75 text

13೥4݄20೔౔༵೔

Slide 76

Slide 76 text

What's New In Rails 4 13೥4݄20೔౔༵೔

Slide 77

Slide 77 text

13೥4݄20೔౔༵೔

Slide 78

Slide 78 text

• Cache Digests / "Russian Doll" 13೥4݄20೔౔༵೔

Slide 79

Slide 79 text

• Cache Digests / "Russian Doll" • Turbo Links 13೥4݄20೔౔༵೔

Slide 80

Slide 80 text

• Cache Digests / "Russian Doll" • Turbo Links • Live Streaming 13೥4݄20೔౔༵೔

Slide 81

Slide 81 text

• Cache Digests / "Russian Doll" • Turbo Links • Live Streaming • Mass Assignment Protection 13೥4݄20೔౔༵೔

Slide 82

Slide 82 text

• Cache Digests / "Russian Doll" • Turbo Links • Live Streaming • Mass Assignment Protection • Encrypted Cookies 13೥4݄20೔౔༵೔

Slide 83

Slide 83 text

13೥4݄20೔౔༵೔

Slide 84

Slide 84 text

• hstore Support 13೥4݄20೔౔༵೔

Slide 85

Slide 85 text

• hstore Support • New Datatype Support: Array, MACADDR, INET, CIDR 13೥4݄20೔౔༵೔

Slide 86

Slide 86 text

hstore Support Direct Ruby Hash Serialization 13೥4݄20೔౔༵೔

Slide 87

Slide 87 text

create_table :examples do |t| t.hstore :dictionary end 13೥4݄20೔౔༵೔

Slide 88

Slide 88 text

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೔౔༵೔

Slide 89

Slide 89 text

article = Article.new article.properties = { author: "John Doe", length: 250 } article.save 13೥4݄20೔౔༵೔

Slide 90

Slide 90 text

activerecord-postgres-hstore 13೥4݄20೔౔༵೔

Slide 91

Slide 91 text

Advanced Data Type Support Using the Right Tool for the Job 13೥4݄20೔౔༵೔

Slide 92

Slide 92 text

• INET • CIDR • MACADDR • UUID 13೥4݄20೔౔༵೔

Slide 93

Slide 93 text

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೔౔༵೔

Slide 94

Slide 94 text

inetExample = InetExample.new inetExample.ip_address = '127.0.0.0/24' inetExample.ip_address # => # inetExample.save 13೥4݄20೔౔༵೔

Slide 95

Slide 95 text

Array Data Type Support Direct Ruby Array Serialization 13೥4݄20೔౔༵೔

Slide 96

Slide 96 text

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೔౔༵೔

Slide 97

Slide 97 text

article = Article.new article.tags = [ "Non-Fiction", "News", "Opinion" ] article.save 13೥4݄20೔౔༵೔

Slide 98

Slide 98 text

13೥4݄20೔౔༵೔

Slide 99

Slide 99 text

• The Database is not just a "Big Hash in the Sky" 13೥4݄20೔౔༵೔

Slide 100

Slide 100 text

• The Database is not just a "Big Hash in the Sky" • Shift computation and logic to the database to maximize performance 13೥4݄20೔౔༵೔

Slide 101

Slide 101 text

• 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೔౔༵೔

Slide 102

Slide 102 text

13೥4݄20೔౔༵೔

Slide 103

Slide 103 text

• http://railscasts.com 13೥4݄20೔౔༵೔

Slide 104

Slide 104 text

• http://railscasts.com • http://postgresql.org 13೥4݄20೔౔༵೔

Slide 105

Slide 105 text

• http://railscasts.com • http://postgresql.org • http://devcenter.heroku.com 13೥4݄20೔౔༵೔

Slide 106

Slide 106 text

Postgres & Rails 4 NoSQL in your SQL Database 13೥4݄20೔౔༵೔