[Backend] How to Correctly Use lazyload, selectinload and joinedload in SQLAlchemy

In SQLAlchemy, relationships are a powerful pattern to map parent and child tables. They allow easy access to related attributes from both parent and child objects. However, how these related objects are loaded plays a crucial role in query performance and efficiency.

The loading of relationships can be categorized into three main types:

• Lazy loading: Related objects are not loaded initially. They are fetched later via a separate query only when the attribute is first accessed.
• Eager loading: Related objects are loaded immediately along with the main query, either through a SQL JOIN or via separate queries executed upfront.
• No loading: Relationship loading is disabled — the attribute remains empty or raises an error if accessed, thus preventing unintended lazy loading.

Depending on use case and data structure, we can choose different loading strategies to optimize performance. In this article, I will compare three commonly used loading styles:

• [lazyload()](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#sqlalchemy.orm.lazyload)
• [selectinload()](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#sqlalchemy.orm.selectinload)
• [joinedload()](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html#sqlalchemy.orm.joinedload)

Lazyload

Lazy loading means related data is loaded only when it is first accessed, triggering a separate query for each access. While this can be efficient for accessing a few related objects, it often causes performance issues during bulk operations due to the N+1 query problem.

For example, consider a Project as a parent table and Model as a child table linked by a foreign key project_id in the Model table. Both classes are connected via a relationship:

# In Projectclass
models = relationship("Model", back_populates="projects")
# In Model class
projects = relationship(“Project”, back_populates=”models”)

When we access project.models for the first time, a query is issued to load the related models. If we then iterate over multiple projects and access their models in a loop like this:

model_list = []
for model in project.models:
    model_list.append(model.display_name)

This will trigger a separate SQL query for each project’s models, resulting in multiple queries (N queries for N projects), which severely impacts performance.

To avoid this N+1 query problem, it’s recommended to use **selectinload or `joinedload`**, which eagerly load related data more efficiently.

Selectinload

Selectinload is an eager loading strategy where related objects are loaded immediately but in a separate, batched query rather than via a JOIN. This reduces the number of queries compared to lazy loading by fetching all related objects in a single additional query using an IN clause.

In SQLAlchemy ORM, we specify selectinload with the options() method to eagerly load relationships. For example, to eagerly load the projects relationship of a Workspace object with only specific columns selected, we can write:

query = db.query(Workspace).filter(Workspace.id == id).options(
      selectinload(Workspace.projects).options(
          load_only(Project.id, Project.display_name, Project.deleted_at)
      ),
  )

In my case, get_workspace_by_id() function in router called the above code. Actually, only the workspace data is loaded not the project. Later, when router access workspace.projects , SQLAlchemy triggers a second SQL query to fetch all related projects for the loaded workspace(s) in one go, using a query with a WHERE workspace_id IN (...) condition:

// the initial SQL fetches the Workspace row(s)
SELECT workspace.id AS workspace_id, workspace.name AS workspace_name, workspace.display_name AS workspace_display_name, workspace.image_url AS workspace_image_url, workspace.key_arn AS workspace_key_arn, workspace.queue_name AS workspace_queue_name, workspace.pricing_plan AS workspace_pricing_plan, workspace.status AS workspace_status, workspace.created_by AS workspace_created_by, workspace.description AS workspace_description, workspace.wpk AS workspace_wpk, workspace.ewsk_wdk AS workspace_ewsk_wdk, workspace.ewdk_wmk AS workspace_ewdk_wmk, workspace.policy AS workspace_policy, workspace.created_at AS workspace_created_at, workspace.updated_at AS workspace_updated_at 
FROM workspace 
WHERE workspace.id = %(id_1)s 

//  second SQL query to fetch all related projects
SELECT project.workspace_id AS project_workspace_id, project.id AS project_id, project.display_name AS project_display_name, project.deleted_at AS project_deleted_at 
FROM project 
WHERE project.workspace_id IN (%(primary_keys_1)s)

{'primary_keys_1': 'GoEBVZF1S0entpJqemkVg'}

Joinedload

This form of loading applies a JOIN to the given SELECT statement so that related rows are loaded in the same result set. In ORM query, using option to specify joinedload joining with Workspace.users(users represents user_workspace table).

query =  db.query(Workspace).filter(Workspace.id == id).options(
  joinedload(Workspace.users)
)

Different from Selectinload, when router call the cruds entry, SQL directly query workspace with user_workspace by OUTER JOIN in SELECT statement.

SELECT anon_1.workspace_id AS anon_1_workspace_id, anon_1.workspace_name AS anon_1_workspace_name 
FROM (SELECT workspace.id AS workspace_id, workspace.name AS workspace_name,  
FROM workspace 
WHERE workspace.id = %(id_1)s 
LIMIT %(param_1)s) AS anon_1 LEFT OUTER JOIN user_workspace AS user_workspace_1 ON anon_1.workspace_id = user_workspace_1.workspace_id

{'id_1': 'ohOa6BGPS5SiyNPpqLfipg', 'param_1': 1}

When do use Selectinload and Joinedload ?

Depending on the size of joined table, choose the proper join method. Take project table as an example:

Summary

• lazyload: Loads related data only on first access, issuing a separate query for each, which can cause performance issues in bulk operations.
• selectinload: Best for many-to-one or large collections when querying many parent objects.
• joinedload: Best for one-to-one or small collections when querying single or few objects.

To improve query efficiency and reduce the load on the database, I recommend using load_only to specify only the necessary fields in the query. This helps avoid loading unused columns and reduces overhead.

Additionally, if you need to filter based on a field from a joined table, you can use with_loader_criteria. For example, if the Dataset table has a relationship with the Model table, and you want to query all datasets that are not associated with deleted models, you can write:

query = db.query(Dataset).filter(Dataset.id == dataset_id).options(
      selectinload(Dataset.collab_models).options(
          load_only(Model.id, Model.display_name)
      ),
      with_loader_criteria(Model, Model.deleted_at == None),
  )

Thanks for reading — any suggestions are welcome!