Virtual fields in Ecto: Eliminating N + 1 queries

There exists a plethora of things I find annoying in this world and slow sites certainly top the charts! But what causes increased latency when accessing web applications? Well, there are a couple of reasons:

• the distance between the client(your browser) and the server
• whether you’re caching your content or not
• heavy traffic hence reducing the server’s response time

The above reasons might cause noticeable performance issues but if the site is database-driven, poorly written SQL can quickly become a huge bottleneck in terms of performance. Every query you send to your database takes time and resources to process, so if you’re making thousands of requests, the time it takes to get results back becomes enormous.

The dreaded N + 1 query problem occurs when you write your SQL is such a way that you first fetch a list of records, then subsequently do another query for each of those records. This means that if the first query returns N records, you then make N further queries — so you end up with N + 1 queries in total. This becomes a very huge problem when you for example fetch 10,000 records, you’ll be doing 10,001 queries!!

Fortunately, there exists strategies to eliminate this kind of problem and this is what I’m going to be talking about in this article with the focus being on Ecto and Elixir ecosystem. Stay tuned :)

The Problem

Let’s use the classical example of an application that allows users to post content. The schema might look something like:

defmodule MyApp.Posts.Post do
  @moduledoc false

  use Ecto.Schema

  import Ecto.Changeset
  import Ecto.Query, warn: false

  alias MyApp.Accounts.User

  @type attrs :: map()
  @type t :: %__MODULE__{}

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "posts" do
    field :body, :string
    field :title, :string

    belongs_to :user, User

    timestamps()
  end

  @spec changeset(t(), attrs()) :: Ecto.Changeset.t()
  def changeset(%__MODULE__{} = post, attrs \\ %{}) do
    post
    |> cast(attrs, [:body, :title, :user_id])
    |> validate_required([:body, :title])
    |> validate_length(:title, max: 255)
  end
end

We of course want users to be able to bookmark any post that they want to go through later. So lets try to think about it. A bookmark belongs to a specific user and identifies a particular post. Let’s say the bookmarks schema looks like this:

defmodule MyApp.Bookmarks.Bookmark do
  @moduledoc false

  use Ecto.Schema

  import Ecto.Changeset

  alias MyApp.Accounts.User
  alias MyApp.Posts.Post

  @type attrs :: map()
  @type t :: %__MODULE__{}

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "bookmarks" do
    belongs_to :post, Post
    belongs_to :user, User

    timestamps()
  end

  @spec changeset(t(), attrs()) :: Ecto.Changeset.t()
  def changeset(%__MODULE__{} = bookmark, attrs \\ %{}) do
    bookmark
    |> cast(attrs, [:post_id, :user_id])
    |> validate_required([:post_id, :user_id])
    |> unique_constraint([:user_id, :post_id],
      name: :bookmarks_user_id_post_id_index,
      message: "already bookmarked"
    )
  end
end

A Bookmark must have a unique combination of a post_id and user_id.

In our home page, we load all posts for both kinds of users; the users who have signed in and those that haven’t. We want to implement a bookmark feature that allows signed-in users to bookmark or unbookmark posts right there in the homepage!

The less performant approach

This is what I first thought was the way to go about this. I would fetch my posts the normal way:

alias MyApp.Posts.Post

  def list_posts(limit) do
    Post
    |> limit(^limit)
    |> preload([:user])
    |> order_by([p], {:desc, p.inserted_at})
    |> Repo.all()
  end

After listing posts, for each post:

  ~H"""
   <div class={[
      "flex gap-2",
      Bookmarks.post_bookmarked?(@id, @user_id) && "hidden"
    ]}>
      <span>Bookmark</span>
    </div>
    <div class={[
      "flex gap-2",
      !Bookmarks.post_bookmarked?(@id, @user_id) && "hidden"
    ]}>
      <span>Unbookmark</span>
    </div>
   """

@spec get_bookmark(post_id(), user_id()) :: bookmark() | nil
  def get_bookmark(post_id, user_id) do
    Bookmark
    |> where([b], b.post_id == ^post_id and b.user_id == ^user_id)
    |> Repo.one()
  end

  @spec post_bookmarked?(post_id(), user_id()) :: boolean()
  def post_bookmarked?(post_id, user_id) do
    case get_bookmark(post_id, user_id) do
      nil -> false
      _bookmark -> true
    end
  end

The @id is the post_id. In other words, for each post I am calling the post_bookmarked?/2 function to check whether the post is bookmarked or not and hiding the respective buttons in each case.

This works but introduces the N + 1 problem. If I have 1000 posts, I’ll do 1 initial query then 1000 more just to check if the post was bookmarked!

The better approach using a virtual field

What if I can add another attribute to the post schema that will be part of each returned post struct and identify the post as bookmarked or not? This ‘other attribute’ cannot be persisted to the database since different users will bookmark different posts. When this is the case, the attribute has to be virtual.

 schema "posts" do
    field :body, :string
    # new virtual field 
    field :bookmarked?, :boolean, virtual: true

Now we have to find a way to fill this virtual field in the list_post/1 function. For that, let’s tinker with the function a little bit:

  def list_posts(limit, bookmark_filters \\ %{}) do
    post_query()
    |> limit(^limit)
    |> preload([:user])
    |> order_by([p], {:desc, p.inserted_at})
    |> add_bookmark_field(bookmark_filters[:bookmarks_user_id])
    |> Repo.all()
  end

  defp add_bookmark_field(query, nil), do: query

  defp add_bookmark_field(query, user_id) do
    select_merge(query, [p], %{
      bookmarked?:
        exists(
          from(b in Bookmark,
            where: b.drop_id == parent_as(:post).id and b.user_id == ^user_id
          )
        )
    })
  end

  defp post_query do
    from post in Post, as: :post
  end

I’ve binded the post to :post so that I can refer to it later. Inside add_bookmark_field/2, we use exists/1 to check whether the subquery returns 1 or more rows and if so, we return true. The parent_as lets you refer to the parent binding :post inside a subquery.

With **select_merge, you start with either a default or an existing result (usually a map or struct) and then add extra key/value pairs** into that result. So we’ll add the extra key bookmarked? using select_merge/3.

Now when calling list_posts/2, I’ll pass a map with the current_user_id:

 list_posts(40, %{bookmarks_user_id: current_user.id})

All posts will be retrieved with the bookmarked? field set to the correct value. We did all this with a single query! A single complex query is always more performant than many simple queries!