Rails on PostgreSQL

I'm slowly catching up with my podcast backlog and came across a Pivotal Labs talk from May 2009. In this talk Josh Susser and Damon McCormick are presenting on Scaling a Rails App with Postgres . It's a little dated now - this talk was given was when PostgreSQL 8.4 was in beta - but, still, lots of good stuff. Here are some notes:

• They started with an existing Rails app with lots of data, so they had some constraints - not greenfield development.


• Around the 5-6 minute mark there's a good discussion of PostgreSQL's query optimizer and how it analyzes a table's data distribution. One takeaway (mentioned around 16:20) is to run vacuum more often on a particular table if there are a lot of writes.


• 10:00 How to set STATISTICS for a particular table.


• 11:00 Using partial indexes.


• 14:00 Indexing on expressions.


• 18:10-23:00 A nice discussion of the EXPLAIN output.


• 23:45 Here they talk about wide columns. I've seen this in MySQL as well, where splitting text data out into a separate table yielded some good speedups.


• 26:10 Some discussion of pg_bench.


• 30:20 Discusses the PostgreSQL log analyzer pgFouine.


• 35:30 How long does it take to add an index to large tables? They saw times of up to an hour for tables with millions of rows.


• 36:30 clustering your data in order to get PostgreSQL to write it more efficiently.


• 37:30-48:00 A thorough discussion of partitioning tables via table inheritance. They used an ActiveRecord model (39:23) with a bunch of utility methods. They also had a cron to periodically create new partitions. At 45:15 they make a nice distinction between using partial indexes and partitions - one advantage is that a partition's indexes can be different than its parents indexes. At 49:00 they mention maybe doing a plugin, not sure if that happened.


• 52:00 Some discussion of full text search via tsearch.


• 53:00 PostgreSQL's lack of built in replication outside of WAL shipping, Slony, etc. Thank goodness 9.0 will address this!


• 54:00 Some props to Engine Yard on their PostgreSQL support.

Good stuff all around, and thanks to Pivotal for posting these great talks!