This post is by Hashrocket from Hashrocket
Click here to view on the original site: Original Post
Last week, I was called to jump on a Rails project that needed some performance optimizations. Our chosen approach was to take a bulk lookup-and-update process out of ActiveRecord land and move everything into Postgres. The performance improvement was huge for large record sets. At the high end, we saw request times go from > 5 minutes to sub 2 seconds.
I had used SQL and Postgres before, but I wasn't totally familiar with its full set of features. At first, I paired with a coworker who has a deeper knowledge of Postgres. After a few days of writing queries and refactoring the test suite, everything was green and I was ready to deploy to staging for testing.
Here's some interesting SQL and PostgreSQL things I learned along the way.
Although not exclusive to PostgreSQL, CTE's, or Common Table Expressions, are a way to write reusuable queries in a Continue reading “A Handful of Useful Features in PostgreSQL & SQL”