Earlier quoted context omitted.
Yeah, I see this a lot. I think it's especially easy to introduce this kind of "accidentally quadratic" behaviour using magical ORMs like Django's, where an innocent-looking attribute access like user.groups can trigger a database query ... access user.groups inside a loop and things get bad quickly. In the case of groups and permissions there's probably only a few of each, so fetching all of them is probably fine. B…
Django's prefetch_related mechanism essentially implements the pattern you describe here for you: https://docs.djangoproject.com/en/3.2/ref/models/querysets/#...
users = User.objects.all()
for u in user:
print(u.name, len(u.comments))
users = select('SELECT id, name FROM users WHERE id IN $1', user_ids)
for u in user:
num_comments = get('SELECT COUNT(*) FROM comments WHERE user_id = $1', u.id)
print(u.name, num_comments)
The other thing I don't like about (most) ORMs is they fetch all the columns by default, even if the code only uses one or two of them. I know most ORMs provide a way to explicitly specify the columns you want, but the easy/simple default is to fetch them all.
I get the value ORMs provide: save a lot of boilerplate and give you nice classes with methods for your tables. I wonder if there's a middle ground where you couldn't do obviously bad things with the ORM without explicitly opting into them. Or even just a heuristic mode for development where it yelled loudly if it detected what looked like an N+1 issue or other query inside a loop.