Powering Rails Application With PostgreSQL

Powering Rails Application with PostgreSQL Nidhi Sarvaiya

Who am I? ➢ Developer at Icicle technologies from last 7 years. ➢ Ruby/Ruby on Rails developer since 2009 ➢ Official Handle @ice_on_rails ➢ Personal Handle @sarvaiya_nidhi

What Are We Covering Today? ➢ Why PostgreSQL ➢ Advance Inbuilt Data Types ➢ UUID ➢ Full Text Search ➢ Indexes ➢ Third Party Gems

Why PostgreSQL? ➢ First stable version was 6.0 released in 1996 ➢ Latest stable version 9.3.5 ➢ Fast and reliable ➢ Free, community support

Rails 4 Handshakes with PostgreSQL ➢ As of Rails 4, many PostgreSQL features are supported out of box by Active Record

Built In Data Types ➢ JSON ➢ Arrays ➢ hstore ➢ Range Types ➢ PostGis ➢ Network Types

JSON Scenario Storing different social media information in your Rails Application. Traditional Approach Each social media sites returns different type of information so we usually go for NoSQL database Alternative Store data in JSON format in PostgreSQL

JSON Implementation Migration create_table :socials do |t| t.json 'profile' end Querying Social.first.profile['name'] => nidhi Social.select(“profile”) Sample Twitter JSON { “username” :“sarvaiya_nidhi”, “name”:”nidhi”, “followers”:80 }

Array Scenario Need to add multiple tags and ratings for Book Traditional Approach Add Separate table and map it with Book Alternative Add array field in Book table to map multiple tags and ratings

Array Implementation Migration create_table :books do |t| t.string :title t.string :tags, array: true end Querying Get Books with specific tag Book.where("’fiction’ = ANY (tags)") Get Books with all tags or multiple tags Book.where("’fiction’ = ALL (tags)") Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"]) Sampe Book Data Book 1 <Book id: 1, title: "Brave New World", tags: ["fantasy", "fiction"] > Book 2 <Book id: 2, title: "The Hit", tags: ["suspense", "thriller"] >

Hstore HStore is a key value store within Postgres. Scenario Need to apply different configuration for different clients Traditional Approach Maintain multiple entries in settings table by adding Key/Value details for each client Alternative Use Hstore to maintain all setting for client in single record

HStore Implementation Migration def self.up execute "CREATE EXTENSION hstore" end create_table :clients do |t| t.hstore 'settings' end Querying client = Client.first client.settings # => { "background-color" => "blue", “font-size”:”14px" } Get Specific value - client.settings["-background-color"] = "blue" Sample Hstore Data settings: { "background-color" => "blue", “font-size”:”14px" }

Range Types ➢ daterange ➢ int4range ➢ int8range ➢numrange ➢ tsrange ➢ tstzrange Let’s look at some of the them with example ->

Range Type - daterange Scenario Implement functionality of booking hotels room for specific duration Traditional Approach Need to maintain two different fields capturing start date and end date for booking duration Alternative Use daterange data type to store duration details

daterange Implementation Migration create_table :rooms do |t| t.daterange 'duration' end Querying All Events on a given date: Room.where("duration @> ?::date", Date.new(2014, 8, 14)) Sample Data Room1 10/08/2014 - 14/08/2014 Room1 16/08/2014 - 21/08/2014

Range Type - int4range Scenario Implement functionality of capturing weather forecast Traditional Approach Need to maintain two different fields capturing high and low temperature details Alternative Use int4range data type to store temperature details

int4range Implementation Migration create_table :forecasts do |t| t.string :city t.int4range :hi_lo end Querying Forecast.create!(city: “mumbai”, hi_lo: 29..32) Forecast.first.hi..lo => 29..32 Sample Data Mumbai: 29..32 Delhi: 31..24

PostGis ➢ Geospatial extension to Postgres ➢Support for Geographic objects ➢New data types ➢Functions to work with those data types ➢ Spatial Index Support

Setup & Configuration ➢ Installing Postgis ➢ Add gems to support postgis with Active Record ○ activerecord-postgis-adaptor ○ rgeo ➢ Add postgis adaptor in database.yml Reference http://daniel-azuma.com/articles/georails

Postgis Data Types ➢ geometry - Any geometric type ➢ point - Point data ➢ line_string - LineString data ➢ polygon - Polygon data ➢ geometry_collection - Any collection type ➢ multi_point - A collection of Points ➢ multi_line_string - A collection of LineStrings ➢ multi_polygon - A collection of Polygons

Network DataType ➢ PostgreSQL comes with column types exclusively for IPv4, IPv6, and MAC addresses. ➢ Active Record datatypes - inet, cidr and macaddre Migration create_table :network_addresses do |t| t.inet :inet_address t.cidr :cidr_address t.macaddr :mac_address end

UUID (Universally Unique Identifier) ➢ Most of the applications now have API, so use UUID rather than integer for the ids ➢ The uuid column type represents a universally unique identifier (UUID), a 128-bit value that is generated by an algorithm that makes it highly unlikely that the same value can be generated twice. ➢ Sharding would be easier. Even re-merging all the data into a single database, if that's something you need at some point.

UUID ImplementaTion Enabling UUID Support def change enable_extension 'uuid-ossp' end Migration create_table :users, id: false do |t| t.primary_key :id, :uuid end or create_table :revisions do |t| t.column :identifier, :uuid end

Full Text Search ➢ Since PostgreSQL 8.3 ➢ TSVECTOR to represent text data ➢ TSQUERY to represent search predicates ➢ Use pg_search gem for Rails application

Indexes Support in PostgreSQL ➢B-Tree Indexes ○ Unique Index ○ Sorted Index ○ Partial Indexes ➢ Functional Indexes ➢ Multi Column Indexes ➢ GIST & GIN indexes

Gems ➢ Full Text Search Gem ○ pg_search - https://github.com/Casecommons/pg_search ➢Database Insight ○ pghero - https://github.com/ankane/pghero ➢ Postgres Extensions ○ postgres_ext - https://github.com/dockyard/postgres_ext

References http://edgeguides.rubyonrails.org/active_record _postgresql.html http://www.informit.com/articles/article.aspx?p= 2220311&seqNum=13

Powering Rails Application With PostgreSQL

More Related Content

What's hot

Similar to Powering Rails Application With PostgreSQL

Recently uploaded

Powering Rails Application With PostgreSQL