How to fix a slow implicit query on pg_attribute table in Rails

Question

In our production environment, we noticed frequent spikes (~every 1 hour) in our Rails application. Digging deeper, it's due to the following query which cumulatively runs in >1.5 s (called 100x) in a single HTTP request.

SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod FROM pg_attribute a
LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = ?::regclass AND a.attnum > ? AND NOT a.attisdropped 
ORDER BY a.attnum

We don't have code calling that table explicitly but seems it's called by Rails to figure out the attributes for each model. "Unexpected SQL queries to Postgres database on Rails/Heroku" is related.

But shouldn't it be called non-repetitively by Rails?

How do we speed this up?

Answer 1

In production, each Rails process will run that query once for each table/model it encounters. That's once per rails s, not per request: if you're seeing it repeatedly, I'd investigate whether your processes are being restarted frequently for some reason.

To eliminate those runtime queries entirely, you can generate a schema cache file on your server:

RAILS_ENV=production rails db:schema:cache:dump

(Rails 4: RAILS_ENV=production bin/rake db:schema:cache:dump)

That command will perform the queries immediately, and then write their results to a cache file, which future Rails processes will directly load instead of inspecting the database. Naturally, you'll then need to regenerate the cache after any future database schema changes.