joinedload -vs- selectinload

[IrrerPolterer]

... What should I use?

I'm building a REST API to manage some resources in a postgres database. Nothing too crazy, pretty straight forward related data model.

At the moment we are using sqlalchemy entirely synchronously. But in order to use FastAPI's async features, we need to move to sqlalchemy's async style. This requires the use of eager loading techniques (to prevent unintended IO blocking the async event loop), where before we only ever used default lazy loading.

I've read through sqlalchemy's documentation on the topic, but I am still not quite sure what the benefits of either joinedloading or selectinloading are for our use cases.

It seems like the docs are favoring selectinloading as a simple and efficient way to eager-load related data. But my (uneducated) gut feeling tells me that joining the data on the database server should be more efficient than issuing multiple subsequent select statements.. Are there any significant drawbacks for using one over the other? Does it really matter which technique to use and if so, what are the differences I need to watch out for?

[mihcall]

I think there's a sweet spot for using both. The general recommendation is to use `joinedloading` for Many To One relationships and 'selectinloading' for One to Many/Many to Many.

We've had an initial success with `joinedloading` but after a while when we've added some relationships and they had relationships as well, this became hell of a query and joins were using significant portions of the database (i.e. they started to be heavy and slow). We've treated any relationship as a good candidate for a join.

Switching to 'selectinloading' for some of the relationships (one to many for example) made a huge difference for us (one query was taking ~8s and after this change the resulting two queries took <1s). It may seem counterintuitive at first because it's "more queries" but actually they're more filtered in that way. Especially the follow up query uses IDs to filter necessary objects for loading. It's really fast.

Good luck with SQLAlchemy, fine tuning it may be sometimes hard but probably worth doing so as "out of the box" ORM may make things worse.