Rails on PostgreSQL

Another Rails and PostgreSQL job, this time at Paperless Post (via the Github jobs board). Looks like they have a few more open positions. And you'd work with Aaron Quint, who's their CTO. Good times!

Saw this ad for a Rails and PostgreSQL job at Eleven Learning. The company name is a "Spinal Tap" reference... well played.

A while back I posted a link to a talk by Gleb Arshinov that he gave at the SF PUG. This talk was on "PostgreSQL for high performance Rails apps", and was full of fine suggestions from their experiences with their Rails apps.

Gleb is back again, this time on May 21 2010 at PGCon where he and Alexander Dymo talked about PostgreSQL as a secret weapon for Rails apps. Some of the same ground is covered (use SQL DDL vs ActiveRecord create_table, etc), but there's lots of new information too. Here are some notes:

• 1:10 They're using PostgreSQL 8.4, nginx, and mongrel


• 4:00-6:00 Talks about dropping down into SQL via ActiveRecord


• 6:30 Use include to eliminate N+1 queries.


• 7:30 Watch for things like acts_as_tree that reintroduce lots of queries in exchange for the improvement in abstraction.


• 9:00 One query, 12 joins - complicated, but query time goes from 8 seconds to 60 ms.


• 14:00-17:00 A technique for recording SQL queries; this helps ensure you're not running unexpected queries


• 19:00 Suggests use straight SQL for DDL rather than the ActiveRecord DSL


• 20:00 Use constraints, FKs, etc to preserve data integrity - "anything you don't have a constraint on will get corrupted"


• 23:00 Don't use CASCADE since app won't know about the deletions


• 28:00 Keep a log of times for the most frequent user requests. Alex suggests using integration tests for this; code is at 29:10 and 29:30.


• 32:30 A technique for loading data with ActiveRecord's select option with PostgreSQL arrays to save on object creation. Questions from the audience about normalization vs efficiency.


• 38:50 Role/user/privilege checking can be slow; shows a technique for using PostgreSQL's bool_or and GROUP BY to get the data in one fell swoop. Query time went from 2+ seconds to 64 ms.


• 42:00 Do analytics in the database. Saw speed improve from 90s to 5s and saved tons of RAM.


• 44:40 Some excellent new PostgreSQL features that are either here now or are on the way (replication, windowing functions)


• 46:30 Demonstrates a problem with PostgreSQL's LIMIT and OFFSET when used with subselects. Some discussion of pagination with the audience. Here's an excellent discussion of pagination alternatives written by Justin French.


• 50:30 How to force PostgreSQL to use a subselect vs a join; the example goes from 605ms to 325 ms.


• 52:20 Be careful with generate_series. Apparently these functions cannot generate hints for the planner.


• 55:30 General props to PostgreSQL community.


• 59:40 Need to test queries both in cold state and hot state; they saw 14x speed difference.


• 1:01:40 Tune PostgreSQL - shared_buffers, work_mem, autovacuum, etc. Rely on community knowledge for initial configuration.

Lots of good stuff there, enjoy!