In part one of this series, we learned how to implement batch updates and advanced inserts in Ecto to dramatically improve database performance.

But implementing these optimizations is only the first step. Ensuring they continue to work effectively in production requires professional monitoring and observability.

This guide will show you how to use AppSignal for Elixir to monitor your Ecto application's performance when dealing with batch data operations.

Prerequisites

• An Elixir/Phoenix application. If you don't have one, fork the e-commerce Phoenix app we'll be using throughout this tutorial.
• Elixir, Phoenix framework, and PostgreSQL installed on your local development environment.
• An AppSignal account. Sign up for a free trial if you don't have one.
• Basic experience working with Elixir and the Phoenix framework.

Next, let's learn how to set up AppSignal for Elixir in our example app.

Setting Up AppSignal for Elixir

Open up the app's mix.exs file and add the AppSignal for Elixir package:

# mix.exs
def deps do
  [
    ...
    {:appsignal, "~> 2.8"},
    {:appsignal_phoenix, "~> 2.0"}
  ]
end

Note: If you have a Phoenix application, make sure you also add the AppSignal for Phoenix package, which depends on the AppSignal package.

Then run mix deps.get to add the packages.

Next, run mix appsignal.install YOUR_PUSH_API_KEY to complete the installation. When you run this command, you will be prompted to configure how your app will be named in the AppSignal dashboard. You can also decide how the AppSignal for Elixir package will be integrated into your app, either through a configuration file (recommended), or an environment variable.

Assuming the installation goes smoothly, you should start seeing some default metrics on the AppSignal dashboard after a few minutes:

And that's all set up.

Understanding Ecto Performance Metrics

With AppSignal installed, we can now move on to learning about some key performance indicators that AppSignal for Elixir monitors by default, namely query execution time, throughput, and N+1 queries.

Query Execution Time and Throughput

Execution time tells you how long individual database operations take, while throughput indicates how many queries your application processes over time.

The AppSignal dashboard provides a convenient timeline visualization that breaks down request execution across different layers of your Phoenix app. You can see how a single request is distributed across the Phoenix endpoint and template, the router, and Ecto.

In the example shown in the screenshot above, the GET request to fetch products completed successfully, taking a total of 6ms, out of which Ecto takes up 2ms — around 33% of the total execution time.

When monitoring query time and throughput, these are some things to watch for:

• Consistent execution times - You're looking for consistent execution times across similar queries. If you start to experience longer execution times (compared to what you have noted as the average), you may need to look at optimizing Ecto queries.
• Declining throughput while execution time increases - This metric could point to a growing bottleneck in your app's Ecto queries.
• Query run times - Let's say you have two Ecto queries. One query takes around 10ms to execute but is being called a thousand times in an hour, while the other query takes 100ms, but is called 5 times in an hour. In such a scenario, you might think that the query that takes up more time should be prioritized for optimization, but if you turn your attention to the other and cut query time by 20%, you'll probably impact your app's performance much more than by fixing the longer query.

N+1 Queries

N+1 queries occur when an initial query triggers additional queries for each returned record.

Using the e-commerce app as an example, consider the code snippet below:

# lib/shopflow/catalog.ex
 
defmodule Shopflow.Catalog do
  ...
  def list_products_with_n_plus_one do
    products = Repo.all(Product)  # 1 query for products
 
    Enum.map(products, fn product ->
      supplier = Repo.get(Supplier, product.supplier_id)  # N queries for suppliers
      %{product | supplier: supplier}
    end)
  end
  ...
end

Here, the list_products_with_n_plus_one function fetches all products, then makes additional database calls to fetch associated suppliers, a classic N+1 query (as you can see in the log outputs below):

[debug] Processing with ShopflowWeb.ProductController.index/2
  Parameters: %{}
  Pipelines: [:browser]
[debug] QUERY OK source="products" db=3.9ms queue=0.2ms idle=648.6ms
SELECT p0."id", p0."name", p0."description", p0."sku", p0."price", p0."is_active", p0."deleted_at", p0."category_id", p0."supplier_id", p0."inserted_at", p0."updated_at" FROM "products" AS p0 []
↳ Shopflow.Catalog.list_products_with_n_plus_one/0, at: lib/shopflow/catalog.ex:125
[debug] QUERY OK source="suppliers" db=1.3ms queue=0.1ms idle=659.2ms
SELECT s0."id", s0."name", s0."contact_email", s0."contact_phone", s0."is_active", s0."inserted_at", s0."updated_at" FROM "suppliers" AS s0 WHERE (s0."id" = $1) ["f861e835-6618-4613-bd74-56667be8c01c"]

The impact can be seen in the time Ecto takes to process the request — a huge 42% of the total time taken by the product listing request:

See the measured impact on this unoptimized query:

Next, let's improve the query by using Ecto.preload/3.

First, edit the function like so:

# lib/shopflow/catalog.ex
 
defmodule Shopflow.Catalog do
  ...
  def list_products do
    Product
    |> Repo.all()
    |> Repo.preload(:supplier)
  end
  ...
end

And on reloading the products list, we can see how Ecto.preload/3 cuts down the impact metric:

To a large extent, the AppSignal for Elixir integration will take care of monitoring most parts of Ecto. However, if we want to get deeper insights or coverage for scenarios that are unique to the application at hand, then we need to add custom instrumentation.

Beyond Basics — Monitoring Ecto for Elixir with AppSignal Custom Instrumentation

In part one, we learned how to insert and modify large datasets using Ecto's insert_all/3 and the Ecto.Multi module.

In this section, we'll take the examples we started with in part one and apply AppSignal for Elixir's custom instrumentation.

Monitoring Ecto Batch Inserts

In part one, we learned about the different parts that make up Ecto, like the schema, changeset, repo, and how they interact when working with batch operations.

For this example, we'll go further by learning how to insert bulk data into the database using CSV files. Then we'll explore how to instrument these batch data operations using AppSignal for Elixir's custom instrumentation.

Before diving into any code examples, it's important to get an overview of the CSV import process I've just mentioned.

The simplified CSV import will proceed as follows:

• User clicks on a CSV import button on the frontend, which opens up a dialog for the CSV import to happen.
• The request is sent to the router, then on to the controller, which then leverages a CsvImporter module to process the CSV and insert valid product records into the database.

Next, let's take a look at some of the parts involved in the process since this will help us understand where to leverage the custom instrumentation later on.

To start us off is the user interface:

<!-- lib/shopflow_web/controllers/product_html/index.html.heex -->
...
 
<button onclick="document.getElementById('import-modal').classList.remove('hidden')">
  Import CSV
</button>
 
<!-- Modal with file upload -->
<.simple_form for={%{}} action={~p"/products/import"} multipart={true}>
  <.input field={f[:csv_file]} type="file" accept=".csv" />
</.simple_form>
...

Next is the router:

# lib/shopflow_web/router.ex
...
post "/products/import", ProductController, :import
..

Then the controller:

# lib/shopflow_web/controllers/product_controller.ex
...
def import(conn, %{"csv_file" => upload}) do
  case validate_csv_file(upload) do
    {:ok, file_path} -> # Process the file
    {:error, reason} -> # Show error message
  end
end
...

And finally, the CSV import module, with a focus on the bit that does the database insert:

# lib/shopflow/catalog/csv_importer.ex
...
Repo.transaction(fn ->
  # Add timestamps and UUIDs
  products_for_insert = Enum.map(products_data, fn product_data ->
    product_data
    |> Map.put("id", Ecto.UUID.generate())
    |> Map.put("inserted_at", now)
    |> Map.put("updated_at", now)
  end)
 
  # Actual bulk insert
  {count, _} = Repo.insert_all(Product, products_for_insert)
  count
end)
...

When using Repo.insert_all/3, you should note that autogenerated attributes like record IDs, as well as inserted_at and updated_at, will not be automatically generated for you, so make sure you do this manually.

With that in place, you might be wondering why we even need custom instrumentation when the AppSignal integration already gives us excellent Ecto monitoring?

Why Use Custom Instrumentation for Your Elixir App?

For starters, take a look at one of the products' controller request samples, with a focus on Ecto's metrics (the products controller is where the CSV import happens):

And this one:

As you can see, even though AppSignal for Elixir covers basic Ecto metrics, to get specific data on the CSV import process would require setting up custom metrics.

Let's learn how to do that next.

Custom Instrumenting Ecto Batch Inserts

Custom instrumentation allows you to add detailed monitoring to specific parts of your Ecto operations that aren't automatically tracked by AppSignal for Elixir's default integration.

By adding custom instrumentation, you can track metrics like:

• How long bulk operations take to complete.
• Success and failure rates for batch processes.
• Custom metrics related to your specific use case

And more.

For now, let's instrument the CSV import to measure the frequency of CSV import failures, a metric that might negatively impact user experience.

Here's a brief snippet of the instrumented import_csv/1 function with success/failure counters:

# lib/shopflow/catalog/csv_importer.ex
...
 
def import_csv(file_path) do
    result = with {:ok, csv_data} <- read_and_parse_csv(file_path),
         {:ok, headers} <- validate_headers(csv_data),
         {:ok, products_data} <- validate_and_transform_data(csv_data, headers) do
      insert_products(products_data)
    end
 
    # Track failures with AppSignal
    case result do
      {:error, _reason} ->
        Appsignal.increment_counter("csv_import.failure")
        Appsignal.add_distribution_value("csv_import.error_rate", 1)
      {:ok, _} ->
        Appsignal.increment_counter("csv_import.success")
        Appsignal.add_distribution_value("csv_import.error_rate", 0)
      _ ->
        :ok
    end
 
    result
  end
  ...

In this code example, we're using two AppSignal functions to collect custom metrics for our CSV import operation:

• Appsignal.increment_counter("csv_import.success") - This increments a counter metric each time a CSV import completes successfully. Counters are perfect for tracking the total number of events over time, giving us visibility into import frequency and success rates.
• Appsignal.add_distribution_value("csv_import.error_rate", 0) - This adds a value of 0 to a distribution metric when imports succeed. Distribution metrics help us track statistical data like averages, percentiles, and patterns. By recording 0 for successful imports (and 1 for failures), we can calculate error rates and monitor import reliability trends over time.

These custom metrics complement AppSignal's automatic Ecto instrumentation by providing business-specific insights that help us understand not just database performance, but also the success patterns of the CSV import operation.

The results are seen in the custom dashboard below:

Note: The process of adding custom dashboards is not included in this tutorial, but you can follow this guide to learn more.

Wrapping Up

In this guide, we explored how to effectively monitor Ecto performance using AppSignal for Elixir, building on the batch data techniques we learned in part one.

We covered the setup process, examined a few key performance metrics like query execution time, throughput, and N+1 queries, and learned how to implement custom instrumentation for scenarios that require deeper monitoring.

With the lessons covered in this tutorial, you now have the skills to identify and measure various performance bottlenecks in your Ecto applications.

Happy coding!