Postgresql on Rails

Postgresql on Rails talk. RubyConf Uruguay,

Transcript

1. PostgreSQL on Rails Thiago Pradi
2. 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
3. Rails default stack • ActiveRecord as ORM • Supports different databases, like SQLite, MySQL and PostgreSQL • ActiveRecord “abstracts” the SQL Language
4. 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
5. Topics • Features • Tips & Tricks
6. 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
7. 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
8. class Blog < ActiveRecord::Base store_accessor :contact_infos, :email, :twitter end blog = Blog.create blog.contact_infos # => nil # Accessor syntax blog.email = 'tchandy@gmail.com' blog.twitter = 'twitter.com/thiagopradi' # Alternative syntax blog.contact_infos[:facebook] = 'facebook.com/ thiagopradi'
9. # Save the object blog.save # UPDATE "blogs" SET "contact_infos" = $1, # "updated_at" = $2 WHERE "blogs"."id" = 1 # [["contact_infos", {"email"=>"tchandy@gmail.com", # "twitter"=>"twitter.com/thiagopradi", # "facebook"=>"facebook.com/thiagopradi"}], +00:00]]
10. # Returns all objects with key twitter > Blog.where("contact_infos ? 'twitter'").first => #<Blog id: 1, ... # Returns all objects where the key ‘twitter’ has the value ‘twitter.com/thiagopradi’ > Blog.where("contact_infos -> 'twitter' = 'twitter.com/thiagopradi'").first => #<Blog id: 1, ...
11. 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
12. 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
13. > Post.create blog: Blog.first, title: "Sample Title", body: "Sample Body" => #<Post id: 1, ..> > Post.search(title: 'Sample').load => #<ActiveRecord::Relation [#<Post id: 1, ...>]>
14. # 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
15. Supports... • Indexes for faster queries • Partial matches • Trigram based search (similar words) • Support dictionaries in different languages (including portuguese / spanish)
16. MySQL also supports! • But only with the MyISAM storage engine • Unfortunately, the tables are not transactioned with MyISAM :-(
18. 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)
19. 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
20. > Account.create code: "10", active: true => #<Account id: 6, code: "10", active:true, ..> > Account.create code: "10", active: false => #<Account id: 7, code: "10", active:false, ..> > Account.create code: "10", active: false => #<Account id: 8, code: "10", active:false, ..> > Account.create code: "10", active: true ActiveRecord::RecordNotUnique: PG::Error: ERROR: duplicate key value violates unique constraint "index_accounts_on_code" DETAIL: Key (code)=(10) already exists.
21. Rollback for DDL • Supports rollback for data definition commands (create table, alter table) • Helps the developer, not leaving “garbage” when a migration fails
24. 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 :-(
25. Window Functions • Aggregation functions • Allow grouping / partitioning of data • It’s possible to do with MySQL, but without native support • Examples: row_number(), rank()
28. Safer type handling • Raises error when the value doesn’t match the field size • Type comparison is safer than “others” databases
29. Postgresql
30. MySQL
32. 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
33. 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
34. 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
35. > User.create name: "Thiago", email: "thiago.pradi@gmail.com" => #<User id: 1, name: "Thiago", ...> > User.create name: "Lucas", email: "lucaspradi1@gmail.com" => #<User id: 2, name: "Lucas", ...>
36. <?xml version="1.0" encoding="UTF-8"?> <auth_token type="integer">0</auth_token> Sample XML
37. 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...
38. 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
42. Results • Mysql has a weird string arithmetic • ??? To Fix • Or, simply use PostgreSQL ;-)
43. Other features... • Extension for Geospatial data (PostGIS) • Hot StandBy • Synchronous/Asynchronous Replication
44. Tips & Tricks
45. Transactions • Use to ensure that your operations are atomic! • Assume that your data will be consistent if something fails
46. ActiveRecord::Base.transaction do create_header create_records update_reports end
47. Constraints • Makes your database consistent (not null, field limits) • Use unique index to ensure uniqueness of fields
48. create_table :accounts do |t| t.string :code, null: false t.boolean :active, null: false, default: true t.integer :max_value, null: false, limit: 8 t.timestamps end add_index(:accounts, :code, unique: true, where: "active")
49. 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
50. Don’t be afraid of writing custom SQL • Bulk insert • Custom joins (left join, right join, outer join)
51. Database Availability • Daily backups, at least. • Replicated database instances in different geographic locations • Bad things happen, assume this.
52. Final Tips
53. Choose WISELY your database
54. Knowing your database can improve... • Performance • Security • Maintainability • Developer Happiness! :-)
55. And remember... Your database is the best friend of your application!
56. Thank You / ¡gracias! • thiago.pradi@gmail.com • twitter.com/thiagopradi • www.thiagopradi.net • www.metafiscal.com.br