A Handful of Useful Features in PostgreSQL & SQL


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.

CTE's

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”