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)


Before:

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

 

After:

/* 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? 

 

FacebookTwitterGoogle+