# Cheat Sheet for Rails + PostgreSQL Count Performance - Canonical: https://haseebeqx.com/posts/cheat-sheet-for-rails-postgresql-count-performance/ - Published: 2024-07-31 The convenience and power of Active Record allows you to interact with your database in a more Ruby-like way, making it easier to write efficient and readable code. However, as with any complex system, performance can sometimes take a hit when using Active Record. One common scenario where performance may suffer is when counting records in your database. In any Rails app, A simple `count` method call can quickly become a bottleneck, especially for larger datasets. In this post, we'll dive into some optimization techniques for using Active Record's `count` method in your Rails app. So, what happens behind the scenes when you call `count` on an Active Record query? In short, `count` is translated into a SQL query that returns the total number of rows that match your conditions. While this might seem simple, it can quickly become performance-intensive, especially for larger datasets. When you call `count`, the database has to scan every row in the table to determine the total count. Here are a few tips that are helpful when doing count: ## 1. Never Use `count` For Checking Existence In many rails applications I have worked on, I found this repeated pattern. Using `count` to check if a record exists. It is inefficient and can lead to performance issues, Here's why: 1. Unnecessary full table scan: .count typically performs a full table scan, counting all matching records. 2. Overkill for existence check: You only need to know if at least one record exists, not the total count. 3. Slower performance: .count is slower than alternatives, particularly on large tables. Instead, use `exists?`: `exists?` is optimized for checking existence, It stops after finding the first matching record. Also, it won't select any value for DB. It generates an SQL similar to this: ``` SELECT 1 AS one FROM "users" WHERE status = 'active' LIMIT 1; ``` This looks very efficient. ```ruby # Inefficient User.where(status: 'active').count > 0 # Efficient User.where(status: 'active').exists? ``` ## 2. Know When To Use `size` The `size` method is very similar to `count`. It will also return the count. There is one difference, The `size` is implemented as follows. - If the collection is loaded, it returns the size of the loaded array. - If the collection is not loaded, it executes a COUNT query. Whereas `count` always queries the database, which can be slower, especially for large tables. Use `count` when you specifically want to get the count from the database and not rely on any pre-loaded data. Use `size` when you want to avoid unnecessary database queries and prefer to use the already loaded data if available. Also, note that `size` is beneficial only after loading the items to count. ```ruby # Don't do this posts = Post.includes(:comments) posts.each do |post| puts post.comments.count end # Instead use this posts = Post.includes(:comments) posts.each do |post| puts post.comments.size end ``` In this case, since comments have been already loaded. Using `size` will return the count without an additional database query. Here, we prevented the N+1 query issue by using `size`. By contrast, `#count` always performs a new SQL COUNT query, regardless of whether the collection is loaded or not. This can lead to unnecessary database hits if used carelessly. ## 3. Counter Cache Counter caching is a technique used in Rails to improve performance when dealing with frequent count operations on associations. It involves storing the count of associated objects in a dedicated column of the parent model, eliminating the need for expensive COUNT queries. When you have a `has_many` association and frequently need to count the number of associated records, counter caching can significantly reduce database load. Instead of executing a COUNT query each time, Rails can simply read the cached value from the parent record. To implement counter caching: 1. Add a counter cache column to the parent model in a migration: ```ruby add_column :authors, :books_count, :integer, default: 0 ``` 2. Enable `counter_cache` from the child model: ```ruby class Book < ApplicationRecord belongs_to :author, counter_cache: true end ``` Rails will automatically increment or decrement the `books_count` column when books are added or removed from an author. This is commonly used for tasks like displaying counts in lists or index pages and ordering or when you need filtering based on association counts. It is an effective way to reduce database load in high-traffic applications. However, be cautious when using counter caches with large datasets or frequently updated associations, as they can introduce additional write operations. Also know that since it relies on rails callbacks to increment and decrement counts, You have to manually update when doing operations like `update_all`. ## 4. Estimated count You can use this trick when you want to get a total number of items in a table and you only want an estimate count that only needs to be eventually consistent. PostgreSQL stores stats about your tables for various reasons. Here we are making use of that. ```ruby class Post < ApplicationRecord def self.estimated_count query = <<-SQL SELECT (reltuples / relpages) * (pg_relation_size('#{table_name}') / current_setting('block_size')::integer) AS count FROM pg_class WHERE relname = '#{table_name}'; SQL result = ActiveRecord::Base.connection.execute(query).first result.first['count'].to_i end end ``` Here, we use data stored by PostgreSQL `pg_class` system catalog table, which stores statistics about the tables. `reltuples` column in `pg_class` provides an estimate of the number of rows in a table, and `relpages` will give that of pages. Here’s a breakdown of the query: - `current_setting('block_size')::integer`: Fetches the PostgreSQL block size (usually 8KB) and converts it to an integer. - `pg_relation_size('#{table_name}')`: Returns the size of the table in bytes - `reltuples / relpages`: This ratio gives the average number of tuples per page. - `pg_relation_size('#{table_name}') / current_setting('block_size')::integer`: This calculates the total number of pages in the table by dividing the total size of the table by the block size. Multiplying these two values provides an estimated total row count. You can also make it a concern: ```ruby module FastCountable extend ActiveSupport::Concern class_methods do def estimated_count query = <<-SQL SELECT (reltuples / relpages) * (pg_relation_size('#{table_name}') / current_setting('block_size')::integer) AS count FROM pg_class WHERE relname = '#{table_name}'; SQL result = ActiveRecord::Base.connection.execute(query).first result['count'].to_i end end end ``` ## 5. Making Use of Index Only Scan for Faster Counting Index-Only Scans in PostgreSQL is a query optimization technique that can significantly improve the performance of certain types of queries. An Index-Only Scan is a method where PostgreSQL can retrieve the required information directly from the index without needing to access the actual table data. This is possible when all the columns needed for the query are present in the index itself. When a query can be satisfied entirely by using the information stored in an index, PostgreSQL uses an Index-Only Scan. So that the database only needs to read the index pages, avoiding the need to fetch data from the table pages. This reduces I/O operations, resulting in faster query execution. Suppose we have a `Sale` model with a column `product_id` and the following index. ```ruby class AddIndexToSalesProductId < ActiveRecord::Migration[7.0] def change add_index :sales, :product_id end end ``` When we do `Sale.distinct.count(:product_id)` It will generate the following SQL: `SELECT COUNT(DISTINCT product_id) FROM sales;` If our table has a large enough amount of data. If we do an `EXPLAIN ANALYZE` on this query, It will be slow since it will do a sequential scan. Now consider this, ```ruby Sale.select("DISTINCT product_id").size # SELECT COUNT(*) FROM (SELECT DISTINCT product_id FROM "sales") subquery_for_count ``` This will only work if we use the `#size` method. (not `#count`). See the generated query. Now the database will be able to do an index only scan resulting in better performance. This is because the database engine was able to identify it could leverage the above index to find all of the distinct `product_id`s. ## 6. Cache Infrequently Updated Counts For counts that don't change frequently, caching can significantly improve performance. Instead of querying the database every time a count is needed, you can store the count in the cache and update it only when necessary. ```ruby class Post < ApplicationRecord has_many :comments def cached_comments_count Rails.cache.fetch("post/#{id}/comments_count", expires_in: 1.hour) do comments.count end end def update_cached_comments_count Rails.cache.write("post/#{id}/comments_count", comments.count) end end ``` In this example, `cached_comments_count` retrieves the count from the cache if it exists. If not, it calculates the count and stores it in the cache for 1 hour. The `update_cached_comments_count method` can be called whenever a comment is added or removed to keep the cache up-to-date. We can update the cached count in a controller like this. ```ruby class CommentsController < ApplicationController def create @post = Post.find(params[:post_id]) @comment = @post.comments.create(comment_params) @post.update_cached_comments_count # ... ``` This approach is helpful for counts that are frequently accessed but infrequently updated. It reduces database load and improves response times, especially for pages that display multiple counts. ## 7. Don't Count Separately When Using `find_in_batches` When doing `find_in_batches`, Don't forget you can count in parallel. You don't have to count separately. Also, make sure you use the `#size` method here and not `#count`. Here is how you should do it: ```ruby total_count = 0 Model.find_in_batches(batch_size: 1000) do |batch| total_count += batch.size end ``` ## 8. Make Use of Grouped Count Suppose you have to count by different enum values of a DB column. Usually, you do this. ```ruby draft_count = Article.where(status: "draft").count published_count = Article.where(status: "published").count unspecified_count = Article.where(status: nil).count ``` Here instead of having multiple queries, You can make use `#group` method in active record. ```ruby Article.group("COALESCE(status, 'unspecified')").count # { "draft" => 2, "published" => 1, "unspecified" => 0 } ``` This way, we are only sending one query into the DB and the database doesn't have to load everything multiple times. ## Conclusion Optimizing Active Record's `count` operations is crucial for maintaining the performance of your Rails application, especially as your dataset grows. From using `exists?` for existence checks, leveraging `size` appropriately, implementing counter caches, to utilizing PostgreSQL's estimated counts and index-only scans - you can significantly reduce database load and improve response times. Remember, the key is to choose the right tool for each specific scenario. Sometimes, a simple cached value or an estimated count can be more beneficial than an exact, real-time count. In other cases, using `group` counts or batched operations can help you efficiently process large datasets. As with all optimizations, it's important to profile your application and identify where count operations are causing bottlenecks. Use these techniques judiciously, always considering the trade-offs between accuracy, performance, and implementation complexity.