Catching N+1 queries before they ship

The most common reason a page gets slow is not a slow query. It’s a fast query run a hundred times. You list 20 products and show each one’s category, and the page runs 1 query for the products and 20 more for the categories. Nobody wrote 21 queries on purpose. The ORM ran them, one each time a template read product.category.

This is the N+1 problem. Renox doesn’t fix it after the fact. It is designed so that N+1 queries are hard to write and easy to notice.

No lazy relations

In Eloquent or ActiveRecord, product.category looks like a field but runs a query. Renox has no such relations. Rust has no reflection to build them from, and hiding a query behind a field access is how N+1 starts.

A model has the foreign key as a plain field. When you need one related row, you write a method, and the query is visible where it is called:

impl Product {
    pub async fn category(&self, db: &Db) -> Result<Option<Category>> {
        match self.category_id {
            Some(id) => Category::find(db, id).await,
            None => Ok(None),
        }
    }
}

The .await makes it hard to miss. A call like product.category(&db).await inside a loop over 20 rows looks like 20 round trips, because it is.

A page’s relations, one query each

For a list, renox::db::relations loads the related rows of the whole page at once and returns maps keyed by id:

use renox::db::relations::{Pivot, belongs_to, has_many};

pub const PRODUCT_TAGS: Pivot = Pivot::new("product_tags", "product_id", "tag_id");

async fn index(State(db): State<Db>, Page(page): Page) -> Result<View> {
    let products = Product::query().order_by("name").paginate(&db, page, 20).await?;   // 2
    let mut categories =
        belongs_to::<Category, _, _>(&db, &products.items, |p| p.category_id).await?;   // 1
    let mut reviews = has_many(&db, &products.items,
        Review::query().order_by_desc("stars"), "product_id", |r| r.product_id).await?; // 1
    let mut tags = PRODUCT_TAGS.load_for::<Tag, _>(&db, &products.items).await?;      // 2

    let cards = products.map(|product| ProductCard {
        category: product.category_id.and_then(|id| categories.remove(&id)),
        reviews: reviews.remove(&product.id).unwrap_or_default(),
        tags: tags.remove(&product.id).unwrap_or_default(),
        product,
    });
    Ok(view("products/index.html", context! { products => cards }))
}

That’s six queries whether the page shows 20 products or 100. The template then reads plain data ({{ card.category.name }}). There is no relation in it that could run a query when it’s read.

The loaders take the page’s rows, so they work the same after get(), paginate() or chunk(). A pivot with its own columns, such as a member’s role in an organization, loads with load_with_pivot.

Counts and sums without loading rows

Lists often need a number per row, such as “3 members” or “2 open requests”. The data grid in Renox has columns for that, each built as a SQL subquery. Here is a client list in a back office:

Grid::new("clients")
    .column(Column::text("name", "Client"))
    .column(Column::count_of("members", "Members", "organization_user", "organization_id"))
    .column(Column::count_of("projects", "Projects", "projects", "organization_id"))

Because they are subqueries, the grid can sort, filter and search on them, and a page fetches each one in a single query. When the number needs a condition the grid can’t express (“open requests” means some statuses but not others), the handler fetches it for the page’s ids in one query and adds it to each row with page.extend. The pattern is the same: one query per kind of data, never one per row.

The debug page flags it

While you develop (APP_DEBUG=true and APP_ENV=local), /_renox/debug lists the last 50 requests with their status, time, the view they rendered and every SQL statement they ran. A statement that ran three or more times in one request is flagged as a likely N+1.

You don’t have to look for the problem yourself. You open the page you just built, then the debug page, and the repeated query is marked. The debug page is never mounted in production or in tests.

A test that counts

To keep a page fast later, put the promise in a test. capture_queries returns every statement a future ran, requests included:

use renox::db::capture_queries;

#[renox::test]
async fn the_product_list_runs_a_fixed_number_of_queries() {
    let app = TestApp::new(shop::app()).await;
    Product::factory().count(50).create(app.db()).await;

    let (response, queries) = capture_queries(app.get("/products")).await;
    response.assert_ok();
    assert!(queries.len() <= 8, "{queries:#?}");
}

Fifty products or five, the number stays the same. If someone adds a query inside a loop a year from now, this test fails and prints the queries.

Why it matters more with SQLite

With SQLite in the same process, a query is a function call rather than a network round trip, so even 21 queries are often fast. But “often fast” doesn’t scale: a list grows from 20 rows to 500, and every request adds up. The approach is the same with SQLite or PostgreSQL: know how many queries a page runs, and keep that number fixed.