Thiago Pradi
• Lead developer at MetaFiscal
• Ruby developer for 5 years
• Author / contributor of Octopus (Database
sharding gem for ActiveRecord)
• Bachelor of computer science from FURB -
Brazil
Slide 3
Slide 3 text
Rails default stack
• ActiveRecord as ORM
• Supports different databases, like SQLite,
MySQL and PostgreSQL
• ActiveRecord “abstracts” the SQL
Language
Slide 4
Slide 4 text
PostgreSQL
• OpenSource database with more than 15
years
• Considered “The world's most advanced
open-source database”
• Successfully used by thousand of companies
around the world
• SQL follows SQL standard from ISO
Slide 5
Slide 5 text
Topics
• Features
• Tips & Tricks
Slide 6
Slide 6 text
HStore
• Data type to store key-value fields
• NoSQL fields with no extra complexity!
• Allows advanced queries on the fields
• Allows indexing
• Native on Rails 4, plugin available for older
versions
Slide 7
Slide 7 text
class CreateBlogs < ActiveRecord::Migration
def up
execute 'CREATE EXTENSION hstore'
create_table :blogs do |t|
t.column :contact_infos, :hstore
t.timestamps
end
end
def down
drop_table :blogs
execute 'DROP EXTENSION hstore'
end
end
Slide 8
Slide 8 text
class Blog < ActiveRecord::Base
store_accessor :contact_infos, :email, :twitter
end
blog = Blog.create
blog.contact_infos # => nil
# Accessor syntax
blog.email = '[email protected]'
blog.twitter = 'twitter.com/thiagopradi'
# Alternative syntax
blog.contact_infos[:facebook] = 'facebook.com/
thiagopradi'
Slide 9
Slide 9 text
# Save the object
blog.save
# UPDATE "blogs" SET "contact_infos" = $1,
# "updated_at" = $2 WHERE "blogs"."id" = 1
# [["contact_infos", {"email"=>"[email protected]",
# "twitter"=>"twitter.com/thiagopradi",
# "facebook"=>"facebook.com/thiagopradi"}],
+00:00]]
Slide 10
Slide 10 text
# Returns all objects with key twitter
> Blog.where("contact_infos ? 'twitter'").first
=> # Blog.where("contact_infos -> 'twitter' =
'twitter.com/thiagopradi'").first
=> #
Slide 11
Slide 11 text
Full Text Search
• Queries with ‘like %%’ are painfully slow
• Full Text Search for the rescue!
• Support out of box! No extra
dependencies (Sphinx/Lucene)
• Integrated to ActiveRecord with the gem
texticles, by @tenderlove
Slide 12
Slide 12 text
class CreatePosts < ActiveRecord::Migration
def change
create_table :posts do |t|
t.integer :blog_id, null: false
t.string :title, null: false
t.string :body, null: false
t.timestamps
end
end
end
# Post Load (2.9ms) SELECT "posts".*,
# ts_rank(to_tsvector('english', "posts"."title"),
# to_tsquery('english', 'Sample')) AS
"rank0.8922075761585498"
# FROM "posts" WHERE (to_tsvector('english',
"title")
# @@ to_tsquery('english', 'Sample')) ORDER BY
"rank0.8922075761585498" DESC
Slide 15
Slide 15 text
Supports...
• Indexes for faster queries
• Partial matches
• Trigram based search (similar words)
• Support dictionaries in different languages
(including portuguese / spanish)
Slide 16
Slide 16 text
MySQL also supports!
• But only with the MyISAM storage engine
• Unfortunately, the tables are not
transactioned with MyISAM :-(
Slide 17
Slide 17 text
No content
Slide 18
Slide 18 text
Partial Indexes
• Allows the user to build indexes with
conditions
• Useful in different situations, from ensure
uniqueness between rows or building
indexes on the most active data
• Integrated with Rails (Rails 4)
Slide 19
Slide 19 text
class CreateAccounts < ActiveRecord::Migration
def change
create_table :accounts do |t|
t.string :code, null: false
t.boolean :active, null: false
t.timestamps
end
add_index(:accounts, :code, unique: true,
where: "active")
end
end
Rollback for DDL
• Supports rollback for data definition
commands (create table, alter table)
• Helps the developer, not leaving “garbage”
when a migration fails
Slide 22
Slide 22 text
No content
Slide 23
Slide 23 text
No content
Slide 24
Slide 24 text
Prepared Statements
• Cache of parse / execution plan of each
query
• Out of box on Rails 3.x
• 1.5 to 10 times faster on complex queries
• Unless you’re using MySQL, so no
performance boost :-(
Slide 25
Slide 25 text
Window Functions
• Aggregation functions
• Allow grouping / partitioning of data
• It’s possible to do with MySQL, but without
native support
• Examples: row_number(), rank()
Slide 26
Slide 26 text
No content
Slide 27
Slide 27 text
No content
Slide 28
Slide 28 text
Safer type handling
• Raises error when the value doesn’t match
the field size
• Type comparison is safer than “others”
databases
Slide 29
Slide 29 text
Postgresql
Slide 30
Slide 30 text
MySQL
Slide 31
Slide 31 text
No content
Slide 32
Slide 32 text
Type comparison
Source: http://www.phenoelit.org/blog/archives/2013/02/05/mysql_madness_and_rails/
Experiment #1
Source: https://github.com/rapid7/metasploit-framework/blob/master/modules/auxiliary/
admin/http/rails_devise_pass_reset.rb
Slide 33
Slide 33 text
class User < ActiveRecord::Base
has_many :products
before_create :generate_access_token
private
def generate_access_token
self.auth_token = SecureRandom.hex
end
end
class Product < ActiveRecord::Base
belongs_to :user
end
Slide 34
Slide 34 text
class ProductsController < ApplicationController
respond_to :xml, :json
def create
@user = User.where('auth_token = ?',
params[:auth_token]).first
if @user.present?
@product =
@user.products.create(params[:product])
respond_with(@product)
else
head :bad_request
end
end
end
PostgreSQL
Started POST "/products" for 127.0.0.1 at
2013-03-08 02:43:43 -0300
Processing by ProductsController#create as XML
Parameters: {"auth_token"=>0, "product"=>{}}
User Load (3.9ms) SELECT "users".* FROM "users"
WHERE (auth_token = 0) ORDER BY "users"."id" ASC
LIMIT 1
PG::Error: ERROR: operator does not exist:
character varying = integer
LINE 1: SELECT "users".* FROM "users" WHERE
(auth_token = 0) ORDE...
Slide 38
Slide 38 text
MySQL
Started POST "/products" for 127.0.0.1 at
2013-03-08 02:40:11 -0300
Processing by ProductsController#create as XML
Parameters: {"auth_token"=>0, "product"=>{}}
User Load (0.8ms) SELECT `users`.* FROM `users`
WHERE (auth_token = 0) ORDER BY `users`.`id` ASC
LIMIT 1
(0.3ms) BEGIN
SQL (2.9ms) INSERT INTO `products`
(`created_at`, `updated_at`, `user_id`) VALUES
('2013-03-08 05:40:11', '2013-03-08 05:40:11', 1)
(0.7ms) COMMIT
Slide 39
Slide 39 text
No content
Slide 40
Slide 40 text
No content
Slide 41
Slide 41 text
No content
Slide 42
Slide 42 text
Results
• Mysql has a weird string arithmetic
• ??? To Fix
• Or, simply use PostgreSQL ;-)
Slide 43
Slide 43 text
Other features...
• Extension for Geospatial data (PostGIS)
• Hot StandBy
• Synchronous/Asynchronous Replication
Slide 44
Slide 44 text
Tips & Tricks
Slide 45
Slide 45 text
Transactions
• Use to ensure that your operations are
atomic!
• Assume that your data will be consistent if
something fails
Slide 46
Slide 46 text
ActiveRecord::Base.transaction do
create_header
create_records
update_reports
end
Slide 47
Slide 47 text
Constraints
• Makes your database consistent (not null,
field limits)
• Use unique index to ensure uniqueness of
fields
Concurrency Model
• Know what your database is doing under
the hood
• Some locks / constraints can make your
asynchronous application behave like serial
processing.
• Study about MVCC - Multi-Version
Concurrency Control
Slide 50
Slide 50 text
Don’t be afraid of
writing custom SQL
• Bulk insert
• Custom joins (left join, right join, outer
join)
Slide 51
Slide 51 text
Database Availability
• Daily backups, at least.
• Replicated database instances in different
geographic locations
• Bad things happen, assume this.
Slide 52
Slide 52 text
Final Tips
Slide 53
Slide 53 text
Choose WISELY your
database
Slide 54
Slide 54 text
Knowing your database
can improve...
• Performance
• Security
• Maintainability
• Developer Happiness! :-)
Slide 55
Slide 55 text
And remember...
Your database is the
best friend of your
application!