Elixir Gotchas: Ecto.Query Preloads

When I learned Ecto, the default way of fetching associations of a record was to use preload:

from planet in Planet,
where: planet.id == ^planet_id,
preload: [:buildings, :events]

Of course, you can always use a join in the query, but why make the query more complex when the preload syntax is so simple?

from planet in Planet,
where: planet.id == ^planet_id,
join: planet_buildings in assoc(planet, :buildings),
# left_join is used because it's possible to have no planet events,
# which would make the query return an empty result using an inner join.
left_join: planet_events in assoc(planet, :events),
preload: [buildings: planet_buildings, events: planet_events]

This second version is objectively harder to read, and while some posts might lead you to believe there is no difference between the two approaches, not only is there a difference, it can be massive when reading multiple preloads.

The easiest way to test is to spin up a local Phoenix server and run both queries, checking the logs to see what's going on at the database layer. Trying out the version with joins on an existing project of mine, this is what gets logged:

# version using joins
[debug] QUERY OK source="planets" db=0.6ms idle=1549.2ms

So far so good. We make one query, we get one result. Now let's try the version without joins:

# simpler version with no explicit joins in the query
[debug] QUERY OK source="planets" db=0.3ms idle=1292.4ms
[debug] QUERY OK source="planet_buildings" db=0.2ms idle=1292.9ms
[debug] QUERY OK source="planet_events" db=0.2ms idle=293.1ms

Analysis

The total time spent in the database is almost identical between the two versions, and the SQL produces the same results. The major difference is that while the join version goes to the database once, the simpler preload version goes to the database three times: one to fetch the main record (planet), and once more for each association in the preload list.

While it's easy to dismiss the roundtrip time when running both the application and database locally, in a production environment there could be up to 1ms of latency between the application server and the database. It's not hard to understand this results in increased latency: if you're doing multiple roundtrips to the database, the request that prompted the query will wait longer than it had to if you used the join version.

A large amount of preloads in the query results in a higher number of round trips to the database, which only compounds the difference between the two versions.

Increased latency can potentially impact the application in a different way. When working with Ecto.Multi or transactions more generally, especially when running databases at a high isolation level, longer lived transactions are more likely to be aborted or rolled back if there is high load on the tables that are part of the query.

Documentation to the rescue

The main issue here was that I made the version without join my default when writing Ecto queries, which I felt was validated by many StackOverflow and ElixirForum answers about preloading associations. However, the official documentation for preload is quite explicit about how it works (adapted):

This example will fetch the planet from the database and then do a separate query returning all buildings associated with the given planet.

Immediately after this mention, there's an example for the join version to demonstrate how to fetch a record with associations in a single database query.💯