Postgres tips for Rails developers

Lukas Fittl Apr 28, 2017

About Citus

Citus is a distributed database that scales out PostgreSQL. An extension to Postgres, Citus is available as open source, as on-prem software, and as a fully-managed database as a service.

Sign up for our newsletter

Enjoy what you're reading? Sign-up to our newsletter to stay informed:

Other Recent Posts

Migrating from single-node Postgres to Citus How Citus works (a look at dynamic executors) Citus 7: Transactions, Framework Integration, and Postgres 10 More Articles

Like our blog, or have a question about Citus? Join us on Slack for a chat :)

This week at RailsConf, we found ourselves sharing a lot of tips for using PostgreSQL with Rails. We thought it might be worthwhile to write up many of these and share more broadly. Here you’ll find some tips that will help you in debugging and improving performance of your database from your Rails app.

And now, on to the code.

Manage long running queries with statement time-outs

Long running queries can have all sorts of negative impact on your database. Whether running for hours or even several seconds they can hold locks, queue up WAL, or just consume a lot of system resources. Postgres makes it easier to have a bit more safety around this with a default statement timeout. What’s nice is you can set a default such as 5 seconds shown here, any query that then runs longer than 5 seconds will be killed:

production:
   url: <%= DATABASE_URL %>
   variables:
     statement_timeout: 5000

Should you need a query to run longer within the database session you can set a custom statement timeout just valid for the current connection:

class MyAnalyticsJob < ActiveJob::Base
  queue_as :analytics
  def perform
    ActiveRecord::Base.connection.execute “SET statement_timeout = 600000” # 10 minutes
    # ...
  ensure
    ActiveRecord::Base.connection.execute “SET statement_timeout = 5000” # 5 seconds
  end
end

Finding misbehaving queries

Rails abstracts away a lot of things when interacting with your database. That can be both a good and a bad. Postgres can show you long running queries, but as your Rails app grows that doesn’t by itself give you everything you need to solve the problem. To know where the query originated from there’s one particularly handy gem marginalia which will log exactly where your query came from. Now when you see some query that is wrong, or running too slow, or could just be removed you know exactly where to go in your code to fix it:

Account Load (0.3ms)  SELECT `accounts`.* FROM `accounts` 
WHERE `accounts`.`queenbee_id` = 1234567890 
LIMIT 1 
/*application:BCX,controller:project_imports,action:show*/

Getting a high-level overview of database queries

Often you need a high-level picture of what is currently going on in your database. pg_stat_statements is a Postgres extension that comes pre-installed in cloud environments like Citus Cloud, and allows you to see which queries have been running since the last statistics reset, and how they were performing.

For example, to see the 10 most time consuming queries, and their average time, do the following:

SELECT query, total_time / calls AS avg_time
 FROM pg_stat_statements
ORDER BY total_time DESC 
LIMIT 10;

If you enable “track_io_timing” on your Postgres database, this also allows you to see whether you are bottlenecked on CPU or I/O performance. You can read more on pg_stat_statements here

Taking advantage of advanced features

By default Rails uses a file called schema.rb to store a copy of the database schema, typically used to initialize the database before running tests. Unfortunately, many advanced Postgres features, like functional and partial indices, as well as multi-column primary keys, can’t be represented in the DSL used for that file.

Instead, it makes sense to switch to having Rails generate and use the “db/structure.sql” file, which you can do like this:

    # Use SQL instead of Active Record's schema dumper when creating the database.
    # This is necessary if your schema can't be completely dumped by the schema dumper,
    # like if you have constraints or database-specific column types
    config.active_record.schema_format = :sql

Under the hood this uses Postgres’ pg_dump format, which can be a bit verbose at times, but ensures you get the complete database structure restored. If you find yourself running into issues with overly long diffs, you might want to take a look at activerecord-clean-db-structure.

Watch out for complex transactions locking against each other

Rails likes to put everything in a transaction, especially when using before_save hooks and multi-level relationships between models. There is one important caveat to watch out for with transactions, which can bite you as you scale out. In a transaction like the following:

BEGIN;
UPDATE organizations SET updated_at = ‘2017-04-27 11:31:03 -0700’ WHERE id = 123;
SELECT * FROM products WHERE store_id = 456;
--- More statements here
COMMIT;

The first UPDATE statement will start holding a row-level lock on the organizations row with id 123 as soon as you issue it, until the COMMIT happens.

Imagine another request for the same organization coming in (e.g. from a different user), making a similar transaction. Typically that other request will have to wait for the transaction to commit, in order to be able to proceed, slowing down the response time. To fix this, it often helps to rearrange the transaction so UPDATEs happen towards the end, as well as consider pulling out changes to timestamp fields to happen outside of the transaction, after the main work has been done.

In order to find problems like this earlier, you can set “log_lock_waits = on” in PostgreSQL.

Keep a handle on your database connections

Rails by default maintains a pool of connections to your database. When a new request comes in it will take one of the connections from the pool and give it to your application. As you scale your Rails app this can result in hundreds of open connections to your database, though in reality only a portion of them are doing work. The key here is to use a connection pooler like pgBouncer to reduce the active connections to your database. A connection pooler will open up connections when transactions are active as opposed to passing on the idle ones that aren’t doing any work.

Want to see more content from us?

We hope you found these tips for managing your Postgres database with Rails useful. If we missed any handy tips, just let us know @citusdata.

And if you’re interested in staying informed about other similar posts, leave your email below to sign-up for our monthly newsletter. No spam, just good content.



← Next article Previous article →