• Engine Yard
  • LivingSocial
  • VMware
  • Heroku
  • Rackspace Hosting
  • Blue Box Group
  • JetBrains
  • New Relic
  • Percona
  • Pivotal Labs
  • Rails Dog
  • WyeWorks
  • Chargify

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at RailsConf, contact Yvonne Romaine at yromaine@oreilly.com.

Download the RailsConf Sponsor/Exhibitor Prospectus

Contact Us

View a complete list of RailsConf contacts.

KnowSQL: Database Tricks To Make Your Life Easier

Nick Gauthier (410 Labs)
Location: Ballroom III
Presentation: external link
Average rating: ***..
(3.79, 29 ratings)

This talk is made up of 6 discrete chapters:

Chapter 1: Indexing [Beginner]. We will cover what to index in your database and how to index it in Rails. All the code is backed up with benchmarks that show the kind of performance boost you can expect to see in your application when you index correctly.

Chapter 2: Foreign Keys [Beginner]. Foreign keys are important for data integrity. We’ll show how Rails model validations can be circumvented and why it’s important to put foreign keys on your data. We’ll show how to use the ruby gem foreigner to add foreign keys in your Rails migrations.

Chapter 3: Geospatial Extensions [Intermediate]. Geospatial data has quickly become a key feature of many sites, especially social networks. We’ll show how to use geospatial extensions in the database to store geospatial information. We’ll also cover geospatial indexing with code examples to show how attendees can query data quickly and accurately.

Chapter 4: Full Text Search [Intermediate]. Almost every site has a search bar, and it has to work well. Third party search solutions can be difficult to setup and maintain, but we can get up and running quickly with full text search in the database. We’ll have benchmarks to show it’s fast, and also illustrate the quality results you can get when your search is backing by a tokenizing and stemming search system that supports ranking and automatic highlighting of results.

Chapter 5: Joins, Aggregation, and Views [Advanced]. AREL has given us a new way to view the database. What used to be a cumbersome and ugly way of doing complicated queries (find_by_sql) can now be elegant, chainable, and powerful with AREL. We’ll step through and example of how you would go about constructing a complicated query by joining multiple tables and aggregating the data. Then we’ll show how to create a view on that data and access it via ActiveRecord.

Chapter 6: On the shoulders of giants [Advanced]. This chapter focuses specifically on the contrib section of PostgreSQL. There are a lot of great tricks here, like using chkpass to supply a “password” column type that automatically encrypts passwords and lets you query them (no more salt+hash!). hstore lets you store hashes and intarray lets you store arrays in the database, and they’re both indexed so you can query the data without pulling it out of every row. isn lets you store isbn, upc, ismn, and issn ids and handles validation and check bits for you automatically. xml2 lets your store xml in the database and supports xml validation and xpath querying on the column.

Photo of Nick Gauthier

Nick Gauthier

410 Labs

Nick Gauthier is a Ruby on Rails performance junkie. Last year he focused on ways to speed up Rails test suites, and now he’s focusing on speeding up your application in production. He’s been using Rails since 2.0 dropped and Linux long enough to remember slackware and the 2.4 kernel. He’s the creator and maintainer of the test-performance gem Hydra.

Comments on this page are now closed.


Jason Fox
05/17/2011 7:24pm EDT

Would have been nice to know that it was mainly a Postgres focused discussion beforehand. The content was also really for the uninitiated.