DEV Community: Dmitry Tsepelev

Pulling the trigger: How to update counter caches in your Rails app without Active Record callbacks

Dmitry Tsepelev — Tue, 26 Nov 2019 10:13:09 +0000

In this article, we experiment with triggers as a tool for keeping aggregated data consistent when using Active Record and your favorite SQL database. Instead of using sophisticated tools such as ElasticSearch for filtering and searching, we will demonstrate a simple approach that achieves the same result with some out-of-the-box database features. As a bonus, learn how to avoid nasty race conditions!

You can find all the examples in the gist.

Sometimes you need to sort and filter records in the database by some aggregated values. For instance, you might be building a paginated list of users in an admin panel, and you want to implement filtering by the number of orders and the total amount that users have spent on them. There are several tools like ElasticSearch, which are good at filtering by aggregates, but setting up a massive search engine and all required infrastructure to process a couple of columns sounds like an overkill. Let's find a more straightforward way!

Trigger finger

Imagine the following data model:

ActiveRecord::Schema.define do
  create_table "orders", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.decimal "amount"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["user_id"], name: "index_orders_on_user_id"
  end

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "orders", "users"
end

class User < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
  belongs_to :user
end

Let's see how we can filter and paginate users by their order total. We can easily achieve our goal with the vanilla SQL statement, but we will immediately run into performance issues. To demonstrate, let's fill the database with 10,000 users and 100,000 orders and use explain (you can find a single file implementation of this example in this gist):

User.insert_all(10_000.times.map { { created_at: Time.now, updated_at: Time.now } })

Order.insert_all(
  10_000.times.map do
    {
      user_id: rand(1...1000),
      amount: rand(1000) / 10.0,
      created_at: Time.now,
      updated_at: Time.now
    }
  end
)

ActiveRecord::Base.connection.execute <<~SQL
  EXPLAIN ANALYZE SELECT users.id, SUM(orders.amount), COUNT(orders.id)
  FROM users JOIN orders ON orders.user_id = users.id
  GROUP BY users.id
  HAVING SUM(orders.amount) > 100 AND COUNT(orders.id) > 1
  ORDER BY SUM(orders.amount)
  LIMIT 50
SQL

This is the result you might see:

Limit  (cost=3206.16..3206.29 rows=50 width=48) (actual time=59.737..59.746 rows=50 loops=1)
  ->  Sort  (cost=3206.16..3208.95 rows=1116 width=48) (actual time=59.736..59.739 rows=50 loops=1)
        Sort Key: (sum(orders.amount))
        Sort Method: top-N heapsort  Memory: 31kB
        ->  HashAggregate  (cost=2968.13..3169.09 rows=1116 width=48) (actual time=59.103..59.452 rows=1000 loops=1)
              Group Key: users.id
              Filter: ((sum(orders.amount) > '100'::numeric) AND (count(orders.id) > 1))
              ->  Hash Join  (cost=290.08..2050.73 rows=73392 width=48) (actual time=2.793..37.022 rows=100000 loops=1)
                    Hash Cond: (orders.user_id = users.id)
                    ->  Seq Scan on orders  (cost=0.00..1567.92 rows=73392 width=48) (actual time=0.011..11.650 rows=100000 loops=1)
                    ->  Hash  (cost=164.48..164.48 rows=10048 width=8) (actual time=2.760..2.760 rows=10000 loops=1)
                          Buckets: 16384  Batches: 1  Memory Usage: 519kB
                          ->  Seq Scan on users  (cost=0.00..164.48 rows=10048 width=8) (actual time=0.006..1.220 rows=10000 loops=1)
Planning Time: 0.237 ms
Execution Time: 64.151 ms

With a bigger database, it will take even more time! We need to find a better solution, the one that scales. Let's denormalize our database and store orders_amount in the separate user_stats table:

class CreateUserStats < ActiveRecord::Migration[6.0]
  def change
    create_table :user_stats do |t|
      t.integer :user_id, null: false, foreign_key: true
      t.decimal :orders_amount
      t.integer :orders_count

      t.index :user_id, unique: true
    end
  end
end

Now we should decide how to keep orders_count and orders_amount in sync. ActiveRecord callbacks do not look like a proper place to handle such operations, because we want to have our stats updated even when data is changed with a plain SQL (e.g., in the migration). There is a built-in counter_cache option for the belongs_to association, but it cannot help us with orders_amount. Triggers to the rescue!

A trigger is a function that is automatically invoked when INSERT, UPDATE, or DELETE is performed on the table.

To work with triggers from our Rails app, we can use gems like hair_trigger, fx, or even write them by hand. In this example, we use hair_trigger, which can generate migrations for trigger updates using only the latest version of the SQL procedure.

Heads up! There is a known hair_trigger issue with Rails 6 and Zeitwerk, if you face it–feel free to use my fork for now. Don't forget to switch back when the fix is out!

Let's add our trigger to the Order model. We want to perform the UPSERT: if there is no row with the matching user_id in the user_stats table–we add a new row, otherwise–update the existing one (make sure there is a unique constraint on the user_id column):

class Order < ActiveRecord::Base
  belongs_to :user

  trigger.after(:insert) do
    <<~SQL
      INSERT INTO user_stats (user_id, orders_amount, orders_count)
      SELECT
        NEW.user_id as user_id,
        SUM(orders.amount) as orders_amount,
        COUNT(orders.id) as orders_count
      FROM orders WHERE orders.user_id = NEW.user_id
      ON CONFLICT (user_id) DO UPDATE
      SET
        orders_amount = EXCLUDED.orders_amount,
        orders_count = EXCLUDED.orders_count;
    SQL
  end
end

Now we should generate the migration with rake db:generate_trigger_migration, run migrations with rails db:migrate, and run the application.

Off to the races

It might seem to be working, but what if we try to insert multiple orders in parallel? (you can run the following code as a rake task or check my implementation here)

user = User.create

threads = []

4.times do
  threads << Thread.new(user.id) do |user_id|
    user = User.find(user_id)
    user.orders.create(amount: rand(1000) / 10.0)
  end
end

threads.each(&:join)

inconsistent_stats = UserStat.joins(user: :orders)
                             .where(user_id: user.id)
                             .having("user_stats.orders_amount <> SUM(orders.amount)")
                             .group("user_stats.id")

if inconsistent_stats.any?
  calculated_amount = UserStat.find_by(user: user).orders_amount
  real_amount = Order.where(user: user).sum(:amount).to_f

  puts
  puts "Race condition detected:"
  puts "calculated amount: #{calculated_amount}"
  puts "real amount: #{real_amount}."
else
  puts
  puts "Data is consistent."
end

There is a huge chance that there will be a race condition, but why? The problem is that the trigger runs inside the current transaction, and the default isolation level is READ COMMITTED, which cannot handle race conditions.

PostgreSQL supports four levels of transaction isolation–READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE

The obvious solution is to use a stricter SERIALIZABLE isolation level, but, unfortunately, an isolation level cannot be changed inside a running transaction. Creating a new explicit transaction every time we work with orders does not sound right either, so let's try another approach for making sure our triggers are always executed in sequence–advisory locks.

The only thing we need to change is to add lock PERFORM pg_advisory_xact_lock(NEW.user_id); at the beginning of our procedure code:

class Order < ActiveRecord::Base
  belongs_to :user

  trigger.after(:insert) do
    <<~SQL
      PERFORM pg_advisory_xact_lock(NEW.user_id);

      INSERT INTO user_stats (user_id, orders_amount, orders_count)
      SELECT
        NEW.user_id as user_id,
        SUM(orders.amount) as orders_amount,
        COUNT(orders.id) as orders_count
      FROM orders WHERE orders.user_id = NEW.user_id
      ON CONFLICT (user_id) DO UPDATE
      SET
        orders_amount = EXCLUDED.orders_amount,
        orders_count = EXCLUDED.orders_count;
    SQL
  end
end

It's way faster! The updated version of the code is here, if you run it, you'll see that race condition is gone, and the app can handle parallel requests. Let's add the index to the orders_amount column in the user_stats table, change the query, and compare the performance:

EXPLAIN ANALYZE SELECT user_id, orders_amount, orders_count
FROM user_stats
WHERE orders_amount > 100 AND orders_count > 1
ORDER BY orders_amount
LIMIT 50

Limit  (cost=0.29..22.99 rows=50 width=40) (actual time=0.059..11.241 rows=50 loops=1)
  ->  Index Scan using index_user_stats_on_orders_amount on user_stats  (cost=0.29..3438.69 rows=7573 width=40) (actual time=0.058..11.2 rows=50 loops=1)
        Index Cond: (orders_amount > '100'::numeric)
        Filter: (orders_count > 1)
Planning Time: 0.105 ms
Execution Time: 11.272 ms

Lock-free alternative

There is a way (suggested by Sergey Ponomarev) to achieve the same result without locks and make it work faster—use deltas (you can find the full implementation here):

class Order < ActiveRecord::Base
  belongs_to :user

  trigger.after(:insert) do
    <<~SQL
      INSERT INTO user_stats (user_id, orders_amount, orders_count)
      SELECT
        NEW.user_id as user_id,
        NEW.amount as orders_amount,
        1 as orders_count
      ON CONFLICT (user_id) DO UPDATE
      SET
        orders_amount = user_stats.orders_amount + EXCLUDED.orders_amount,
        orders_count = user_stats.orders_count + EXCLUDED.orders_count;
    SQL
  end
end

The trick here is not to use any subqueries, so race conditions would not be possible. As a bonus, you'll get better performance when inserting new records. This approach might come handy for simple cases like the one described in this article, but when you are dealing with more complex logic, you might want to resort to locks (imagine that orders have statuses, we need to cache counts of orders in each status and orders can be updated).

Loop instead of UPSERT

In previous examples, we use UPSERT, which was introduced in PostgreSQL 9.5, but what if we use the older version? Let's review how the trigger works again: it tries to insert a new row into the user_stats table and, if a conflict happens, it updates the existing row. In the real-world application there will be conflicts most of the time (to be precise–insert happens only once for each user). We can use this fact and rewrite our trigger in the following way (the example of a loop inside the trigger is here):

class Order < ActiveRecord::Base
  belongs_to :user

  trigger.after(:insert) do
    <<~SQL
      <<insert_update>>
      LOOP
        UPDATE user_stats
        SET orders_count = orders_count + 1,
            orders_amount = orders_amount + NEW.amount
        WHERE user_id = NEW.user_id;

        EXIT insert_update WHEN FOUND;

        BEGIN
          INSERT INTO user_stats (
            user_id, orders_amount, orders_count
          ) VALUES (
            NEW.user_id, 1, NEW.amount
          );

          EXIT insert_update;
        EXCEPTION
          WHEN UNIQUE_VIOLATION THEN
            -- do nothing
        END;
      END LOOP insert_update;
    SQL
  end
end

In this case, we have inverted our logic: trigger tries to update the existing row, and if it misses—the new row gets inserted.

Working with aggregated data is hard: when you have a lot of counter (and other kinds of) caches, it makes sense to use a special tool for that. However, for simple cases, we can stay with good old database triggers: when configured properly, they are quite performant!

Persisted queries in GraphQL: Slim down Apollo requests to your Ruby application

Dmitry Tsepelev — Thu, 07 Nov 2019 13:24:52 +0000

Learn how to reduce the size of network requests from the Apollo client in the front-end to the GraphQL Ruby back-end with the help of persisted queries. In this article, we will show how these queries work and set them up both on a client and a server with the help of our graphql-ruby-persisted_queries Ruby gem.

One of the benefits of using GraphQL is its flexibility: back-end describes the data model using types, so front-end can only get the data it needs. The problem is that the amount of data required to render a page in a real-world application can be significant, and the query to fetch this data will get out of hand pretty quick. What could help here?

Read how Relay deals with persisted queries here.

First of all, the amount and the variety of often-used queries in your application are limited: usually, front-end knows precisely what it needs from the back-end for every particular view. Popular GraphQL frameworks like Apollo and Relay use that fact to persist queries in the back-end so that a front-end can send just a unique ID over the wire, instead of the full query in all its verboseness. This article will focus on the Apollo implementation on persisted queries.

If you have the Pro Version of graphql-ruby–you already have built-in support for persisted queries.

Most of Ruby applications that implement GraphQL server use a gem called graphql-ruby, in this article we are going to find out how to make it work with persisted query IDs coming from the Apollo Client. By default, graphql-ruby uses POST requests, as query strings tend to become very long, and it makes it hard to configure HTTP caching. If we switch to persisted queries, we will be able to turn on GET requests too and unleash the power of HTTP caching!

The idea behind persisting queries is pretty straightforward–the back-end should look for a special query parameter, containing the unique ID of the query, find the query in the store (we'll talk about it later, imagine that it's just a key-value store) and use it to prepare the response.

Relay has a compiler, and it knows all the queries in the app, so it can put them to a dedicated text file along with their md5 hashes when --persist-output option is provided. You can save these queries to your store, and you're done: back-end is ready to consume IDs instead of full queries.

Apollo has no compiler, and there is no built-in way to get the list of queries. In this case, queries should be saved to a store at runtime, and there is a special apollo-link-persisted-queries library that enables this feature. First, change your frontend configuration like so:

import { createPersistedQueryLink } from "apollo-link-persisted-queries";
import { createHttpLink } from "apollo-link-http";
import { InMemoryCache } from "apollo-cache-inmemory";
import ApolloClient from "apollo-client";

const link = createPersistedQueryLink().concat(createHttpLink({ uri: "/graphql" }));
const client = new ApolloClient({
  cache: new InMemoryCache(),
  link: link,
});

Now, let's talk about the back-end. With the configured link, client will make an attempt to send the unique ID (sha256 is used in this case) in the extension param, e.g.:

{
  extensions: {
    persistedQuery: {
      version: 1,
      sha256Hash: "688787d8ff144c502c7f5cffaafe2cc588d86079f9de88304c26b0cb99ce91c6"
    }
  }
}

When server finds the ID in the store—it serves the query if it were sent in full, otherwise–server returns { errors: [{ message: "PersistedQueryNotFound" }] } in the response payload. In this case client will send the full query along with unique ID and back-end will persist it to the store.

Now we need to implement our back-end functionality in the GraphqlController:

class GraphqlController < ApplicationController
  PersistedQueryNotFound = Class.new(StandardError)

  def execute
    query_str = persisted_query || params[:query]

    result = GraphqlSchema.execute(
      query_str,
      variables: ensure_hash(params[:variables]),
      context: {},
      operation_name: params[:operationName]
    )

    render json: result
  rescue PersistedQueryNotFound
    render json: { errors: [{ message: "PersistedQueryNotFound" }] }
  end

  private

  def persisted_query
    return if params[:extensions].nil?

    hash = ensure_hash(params[:extensions]).dig("persistedQuery", "sha256Hash")

    return if hash.nil?

    if params[:query]
      store.save_query(hash, params[:query])
      return
    end

    query_str = store.fetch_query(hash)

    raise PersistedQueryNotFound if query_str.nil?

    query_str
  end

  def store
    MemoryStore.instance
  end

  def ensure_hash(ambiguous_param)
    # ...
  end
end

Now we need to implement the MemoryStore (as we know, we need a very simple key-value storage with read and write operations), and that's it, minimalistic back-end support of persisted queries is ready:

class MemoryStore
  def self.instance
    @instance ||= new
  end

  def initialize
    @storage = {}
  end

  def fetch_query(hash)
    @storage[hash]
  end

  def save_query(hash, query)
    @storage[hash] = query
  end
end

And that is it! If you configured your client properly and changed your GraphqlController, it should work! So you don't have to copy all the boilerplate above between projects, I released a little gem called graphql-ruby-persisted_queries, which implements all the features we discussed earlier, and more:

Redis storage;
hash verification;
hash function configuration;
Relay support is on its way!

But did not we mention GET requests earlier? Now, we can turn them on, just open up routes.rb and add the following line:

get "/graphql", to: "graphql#execute"

We also need to slightly change the initialization of apollo-link-persisted-queries, which should send queries without query string as GET, and full queries as POST:

const link = createPersistedQueryLink({ useGETForHashedQueries: true }).concat(
  createHttpLink({ uri: "/graphql" })
);

Persisted queries can help you reduce the amount of traffic between your GraphQL server and its clients by storing query strings at the back-end. If you have the pro version of graphql-ruby–you're all set, otherwise, take a look at the open-source alternative graphql-ruby-persisted_queries we have just cooked up.

Read more dev articles on https://evilmartians.com/chronicles!

Wrapping JSON-based ActiveRecord attributes with classes

Dmitry Tsepelev — Tue, 26 Feb 2019 19:05:58 +0000

Posted in Martian Chronicles

Have you ever tried to denormalize your DB structure a little bit by using a JSON-based column? It goes without saying that great ~~power~~ flexibility comes with great responsibility, but in some cases, this approach does a good job and simplifies things a lot.

For instance, imagine that you are building a feature for yet another e-commerce site, which allows users to filter products by a set of rules and order them automatically each night. Obviously, we want to specify the count of products to order, but how can we represent the filtering rules (e.g. min/max price, publication date etc)? We could have a column for each rule, but JSON column looks more promising. We are not planning to filter by any of these columns, however, there is a big chance that we will add more rules in future and JSON column allows us to avoid migrating our database each time.

This is how we usually add JSONB column to the PostgreSQL table:

create_table :auto_buyers do |t|
  t.integer :count, null: false
  t.jsonb :rules, null: false, default: {}
end

This attribute is just a hash:

buyer = AutoBuyer.create(count: 1, rules: { max_price: 10 })

if item.price < buyer.rules["max_price"]
  # buy item...
end

What if we want something more complex, for instance, validate such fields? This looks a bit messy:

class AutoBuyer < ApplicationRecord
  validate do
    if rules["max_price"] && rules["min_price"] && rules["min_price"] > rules["max_price"]
      errors.add(:max_price, "should be greater then min price")
    end
  end
end

Imagine that ActiveRecord model has its own behavior and we are adding more complexity with such validations—in this case, it might make sense to move the logic around the JSON attribute to the separate class and it would also be nice to work with attributes, not the hash. Attributes API can save us!

However, the documentation is confusing enough and it’s hard to get things right from the first attempt. Let's try to do it start with defining the class for representing our hash as an object:

class AutoBuyerRules
  include ActiveModel::Model
  include ActiveModel::Attributes

  attribute :min_price, :integer
  attribute :max_price, :integer
end

After that, we need to define the attribute class to wrap up the hash to this class:

class AutoBuyerRulesType < ActiveModel::Type::Value
  def type
    :jsonb
  end

  # rubocop:disable Style/RescueModifier
  def cast_value(value)
    case value
    when String
      decoded = ActiveSupport::JSON.decode(value) rescue nil
      AutoBuyerRules.new(decoded) unless decoded.nil?
    when Hash
      AutoBuyerRules.new(value)
    when AutoBuyerRules
      value
    end
  end
  # rubocop:enable Style/RescueModifier

  def serialize(value)
    case value
    when Hash, AutoBuyerRules
      ActiveSupport::JSON.encode(value)
    else
      super
    end
  end

  def changed_in_place?(raw_old_value, new_value)
    cast_value(raw_old_value) != new_value
  end
end

And finally, let's tell our ActiveRecord model to use this new attribute and try it out:

class AutoBuyer < ApplicationRecord
  attribute :rules, AutoBuyerRulesType.new
end

buyer = AutoBuyer.create(count: 1, rules: { max_price: 10 })

if item.price < buyer.rules.max_price
  # buy item...
end

It looks way better, but should we use so much code each time we want to get this behavior? Not really, please welcome store_model gem!

To make the long story short (you can find the long store at the README) let's take a look at the same example:

class AutoBuyerRules
  include StoreModel::Model

  attribute :min_price, :integer
  attribute :max_price, :integer

  validate :min_price, presence: true
end

class AutoBuyer < ApplicationRecord
  attribute :rules, AutoBuyerRules.to_type

  validate :rules, store_model: { merge_errors: true }
end

buyer = AutoBuyer.new
puts buyer.valid? # => false
puts buyer.errors.messages # => { min_price: ["can't be blank"] }

That's it, you don't need to include a ton of modules or define any custom types by hand! It also comes with validation support—you can validate store model fields and then merge these errors to the parent record (or define any custom strategy).

Let's recap:

JSON columns are good for the data that change often
you can handle them as hashes, but it can be verbose
you can use Attribute API to wrap them with objects or use store_model gem
if you want to do the same thing, but have attributes defined on the ActiveRecord model itself—consider using store_attribute or jsonb_accessor

Read more dev articles on https://evilmartians.com/chronicles!