Introducing pg_decorator

This is a technical post by our back-end developer and database guru Ervin.

Ruby is wonderful, Rails allows rapid prototyping and quick releases. You start a new project, and in a few minutes you already have something to show.  Scaffolds, migrations, scopes and models appear very fast. Everybody is happy until long after the release. But then things start to slow down and you turn your attention to logs. Firstly you double-check  (using bullet for example) that there is no  n+1 queries and everything is preloaded. Later you try to organize things so that only 1 or 2 queries hit the database per http request, maybe offloading user and session handling to in-memory store. Later you offload long operations to background jobs, or maybe even separate applications/scripts. And everything is OK again.

Fast-forward to the point where your user/customer count has multiplied and you start noticing that database load is reflecting user increase. Most possibly there is something with your queries, and some of them may need attention.  You start to log your queries and do some research on database logs. Very soon the list of slowest queries is ready, but amazingly you did not write some of them. Quick grep of log and query is NOT found.  Huh? Is that your super-search gem building a very complex query and running it against raw connection, bypassing activerecord-sql log, at same time assuming you have created index, that he asked you to do in readme-you-never-read? You may never know.

Wouldn’t it be wonderful, if every query would contain ‘trace’, pinpointing you to the code you need to refactor? Search engine showed that there is somebody who also considered this and marginalia gem was created for mysql, and later ported to sqlite3 and postgres. Sadly postgresql support was incomplete, several activerecord internal queries still hit the database without annotation, and sometimes, I want to have the ruby script interacting with the database without activerecord, just a simple ruby script that moves stuff around in the database using pg gem directly.

So I created a pg_decorator that hooks on PG gem directly, adding /* sql comment containing your app name and a single line of stack trace */  in front of each query.

Queries as seen by the database (and thus appearing in the database log)


SELECT "client".* FROM "client"  WHERE x=1,y=2,z='n' LIMIT 1



/* MyWorld /app/models/client.rb:123:in `most_valued' */
SELECT "client".* FROM "client"  WHERE x=1,y=2,z='n' LIMIT 1


Our database knows who’s been naughty, does yours? 


By On April 3, 2013

  1. @David, as I’m reading through the comments I was glad to finally see someone mentioned the elephant in the room. I’ve never been a proponent nor seen the benefit in putting SQL in code other than making it easier on the developers in the ORM scope. However, encapsulation of SQL on the server can still allow for ORM while letting the person(s) responsible for that code direct access to the code as well as performance improvements. Embedded SQL hidden in the app only leads to bottlenecks that are difficult to find and thus lead to elaborate band-aids to track back. I’d be interested to hear from Ervin to see why they have SQL in the app and hopefully the SELECT * is just an example! 🙂

    Great article though and very informative! Lead me to a great deal of research on the issue!

  2. re: David
    this tool is less useful for cases of Raw SQL in code (in such case if any query appears on slow log it would be simply grepped in code). But if SQL is generated through abstraction and meta-programming (and few helper libs) grep would show nothing, and that’s why you can use a-little-help from stack trace comment.

  3. Raw SQL queries in your code?! Oh the horror! Why don’t people treat their databases with the same abstraction, encapsulation and data-hiding principles as every other part of their tech?

    Still, if you have to be in that world, this is a nice to have, for sure!

  4. Keep these posts coming. I love the glimpse into the workings of Toggl, and it builds confidence to know the level of care with which this service is built.

  5. I agree with @Toby, it’s nice to see a post like this pop up about the engineering going into the product you’re using. I’d bet that a lot of businesses using Toggl are most likely web related, and with that, some individuals are bound to be techie. While posts about updates are nice, seeing featured posts such as this and beyond, perhaps into the design and planning stages are nice too.

    It’s great to see how a successful company works, and adds a human element to a virtual entity. Nice touch, guys.

  6. Not exactly the blog you’d expect to see, granted. I’m also not exactly a dev – but do find myself working through code regularly. Very helpful to see the odd insightful post pop up without looking for it.

    On balance, very glad I clicked on the notification! Interesting post, thanks!