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
vacuummore 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
- 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
- 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
- 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!