How to Make Ruby on Rails Active Admin and Large PostgreSQL Tables Friends Again

If you’ve stumbled upon this article, your Active Admin has probably decided to hang for a minute before showing a simple index page for one of your tables. Let me guess: this is a large table with hundreds of thousands of records. But why does the size of the table matter if all we need is to show a couple of first rows?

Well, it appears that size matters not for Active Admin, but for PostgreSQL itself. To be more specific — for total row counting.

Backstory: concurrency model in PostgreSQL is implemented using technique called MVCC (multiversion concurrency control), which, to ensure correctness of the result in concurrent environments, makes running something as simple as select count(*) from items; to go through all rows in items table. And that’s not something you can turn off.

Active Admin shows total count of rows on each index page, which makes performance unacceptable. Let’s fix it.

The easiest thing we can do is to forbid Active Admin call count on the model:

index(pagination_total: false) do
  # ...
end

It fixes performance instantly, but you lose ability to see any counts at all. If it’s alright — you can skip the rest of this article and commit your fix. But if users of your application need some numbers (and information without numbers is quite often useless), you’ll have to dig deeper.

Luckily, PostgreSQL has a special table called pg_class to store some metadata. reltuples column is particularly interesting for us, because it stores estimated count of records in tables. In our database, this estimation differs from real values for not more than 2%, which is totally fine for our needs.

So, let’s create a small concern that can be included into any model to support estimated count:

Note the line number 8: PostgreSQL returns big numbers in scientific format, and “2.500000E+06” is probably not what you want to have on UI.

Now we have to show it on index pages. Active Admin sidebars get rendered under the list of filters and seem to be a nice place for our needs. Let’s create a helper method that could be used at any index page to add a sidebar with count:

There’s a couple of interesting things to note in the code above. First of all, the dsl parameter (instance of ActiveAdmin::ResourceDSL) is what you have as self inside ActiveAdmin.register method. It has the sidebar method that (surprisingly) renders the sidebar we need. only: :index parameter tells not to render the sidebar on show page.

Now, we should use estimated_count method only for cases when no filters are applied by the user. Otherwise, simple count method can be used, because filtering either goes through each row anyway, or uses indexes. In both cases count will not give any performance overhead. To find out whether filters are applied, we can check the q request parameter (used by ransack gem under Active Admin’s hood) — line number 5. All that’s left is to form a string and put it into a span inside our sidebar.

Let’s call the helper method:

And this is how it looks:

That’s it. But if you are a perfectionist (or, in my case, work with a very good QA), you’ll notice that “last” button in pagination section below the table on index pages stopped working properly. Well, there’s no total count, so it doesn’t know how many pages exist and, as a result, cannot link to the last one. You can think of some dirty hacks like modifying code of kaminari gem used by Active Admin for pagination, but doing so you’ll shoot yourself in the foot sooner than you think. So let’s just hide unusable buttons in active_admin.scss:

.index.admin_items .pagination .last { display: none; }

Now we’re done.

PS. Lifehack: From time to time ask users of your Active Admin what their common usage of filters is and add indexes to your tables. In 21 century, time is much more expensive than hard drive space or CPU usage in your cloud.