perf: N+1 queries on thing table causing 3.8M single-row DB lookups

Feature Request

Database performance: N+1 queries on `thing` table causing 3.8M single-row lookups

Summary

pg_stat_statements profiling on production reveals SELECT * FROM thing WHERE key=$1 is the highest call-volume query on the database with 3.8 million calls in the observation window, totalling ~2.5M ms of DB time. The batched equivalent (SELECT ... WHERE key IN (...)) exists and is used in some paths but only accounts for 134k calls — a 28:1 ratio of unbatched to batched lookups.

Evidence

From pg_stat_statements on production (ol-db0):

query                                   | calls   | mean_exec_time | total_exec_time
----------------------------------------+---------+----------------+----------------
SELECT * FROM thing WHERE key=$1        | 3830328 | 0.66ms         | 2,537,916ms
SELECT thing.key FROM thing ... IN ($1) |  134057 | 11.44ms        | 1,533,958ms

The individual lookups are fast (0.66ms mean) but the volume is the problem. The server has 15GB RAM against a 892GB database, so cache pressure is real — stddev of 17ms on a 0.66ms mean indicates frequent cache misses costing 20-30ms per cold lookup.

Expected behaviour

Things should be loaded in batches using the existing WHERE key IN (...) path wherever possible, reducing round-trips and I/O pressure.

Steps to reproduce

Any page that loads a list of works/editions/authors and then resolves each one individually — likely in openlibrary/plugins/worksearch, openlibrary/plugins/books, or any template that iterates and calls web.ctx.site.get() in a loop.

Suggested fix

Audit call sites of web.ctx.site.get(key) (single-key) and replace loops with web.ctx.site.get_many(keys) (batched) where the keys are known upfront. The infobase layer already supports bulk fetching.

Impact

Directly reduces DB query volume and I/O pressure
No infrastructure changes required
Likely the highest ROI performance fix available given current hardware constraints

Environment

PostgreSQL 18.3
Database size: 892GB
Server RAM: 15GB
shared_buffers: 4GB

Breakdown

Implementation Details (for maintainers)

Related files

Refer to this map of common Endpoints:
*

Requirements Checklist

Checklist of requirements that need to be satisfied in order for this issue to be closed:

[ ]

Stakeholders

Instructions for Contributors

Before creating a new branch or pushing up changes to a PR, please first run these commands to ensure your repository is up to date, as the pre-commit bot may add commits to your PRs upstream.

@mekarpeles

⚠️ Contributors, this issue will be ready to work on once:

Triage — maintainers will add Priority: * and Lead: @* to replace Needs: Triage and Needs: Lead
Needs: Breakdown — the Breakdown section contains unfilled template placeholders; a task checklist with related files is needed before this is actionable

Context

From codebase research:

Issue N+1 query problem in dynlinks.py (query_keys, ol_get_many_as_dict, get_isbn_editiondict_map) #12350 ("N+1 query problem in dynlinks.py") is open and covers overlapping ground in a specific subsystem — the scope of this issue is broader (the full thing table), making these complementary rather than duplicates.
openlibrary/plugins/upstream/mybooks.py:77 contains an explicit TODO: # TODO: should do in one web.ctx.get_many fetch — the loan loop at lines 74–82 calls web.ctx.site.get(loan["book"]) per loan.
openlibrary/plugins/openlibrary/code.py:386 contains [web.ctx.site.get(key) for key in things] — a list comprehension that issues one query per item.
Grepping the codebase finds ~47 Python files with web.ctx.site.get( calls and 22 files already using web.ctx.site.get_many(. The batched API is fully implemented in the infobase layer (vendor/infogami/infogami/infobase/dbstore.py uses WHERE key IN (...)).

Issue Assessment

Open questions

Is the pg_stat_statements snapshot from a representative traffic window (weekday peak vs. off-hours)?
Do any of the 3.8M calls originate from scheduled jobs or import scripts rather than web request handlers? That would affect where to focus the audit.
What's the target: eliminate all unbatched loops, or only those with a list size ≥ N?

Action items

Fill in the Breakdown section: list the specific files/functions to audit, define acceptance criteria (e.g., target reduction in pg_stat_statements total_exec_time or call ratio), and identify stakeholders.
Clarify whether this should track as Type: Bug given that the pattern produces measurable production degradation.

Suggested breakdown

Given the codebase research, a concrete task checklist could be:

Audit all web.ctx.site.get() call sites to classify each as single-key (acceptable) vs. loop (N+1 candidate) — a grep scan across the repo is sufficient. Output: annotated list of loop call sites with estimated impact.
Fix mybooks.py:74–82 — replace per-loan site.get() with a single site.get_many([loan["book"] for loan in myloans]) call. Has a confirming TODO comment. → Good First Issue candidate: self-contained, existing TODO, ≤ 2 hours, no deep codebase knowledge required.
Fix code.py:386 — replace list comprehension with site.get_many(things).
Fix remaining high-traffic loop call sites identified in the audit (likely 3–8 more locations).
Add or update tests that assert batched loading is used where expected (currently no test coverage of query count at the infobase level).
Measure post-fix impact via pg_stat_statements to verify the unbatched-to-batched ratio improves.

Full triage checklist (maintainers / Pam)

Note

This comment was automatically generated by Pam, Open Library's Project AI Manager, on behalf of @mekarpeles. Pam is designed to provide status visibility, perform basic project management functions and relevant codebase research, and provide actionable feedback so contributors aren't left waiting.

Hi, I'd like to work on this issue. Could you assign it to me?

@CipherZ3r0

@CipherZ3r0 could you look into this one?

@RayBB

@RayBB Yes

@RayBB

@RayBB

N+1 candidates

I found potential 11 loop-shaped site.get() calls

These are the 11 places where we can potentially convert N individual get() calls into a single get_many() call, either directly or with a small restructuring:

openlibrary/plugins/openlibrary/api.py:684
```
editions = [web.ctx.site.get(key) for key in edition_keys]
```
- Edition keys are already known. Direct get_many() replacement.

openlibrary/plugins/openlibrary/bulk_tag.py:39

works = i.work_ids.split(",")
...
for work in works:
            w = web.ctx.site.get(f"/works/{work}")

            current_subjects = {
                # XXX : Should an empty list be the default for these?
                "subjects": uniq(w.get("subjects", "")),
                "subject_people": uniq(w.get("subject_people", "")),
                "subject_places": uniq(w.get("subject_places", "")),
                "subject_times": uniq(w.get("subject_times", "")),
            }

All the Work IDs are known upfront. Batch fetch before the mutation loop.

openlibrary/views/loanstats.py:205

        for leaderboard in stats["leaderboard"].values():
            for item in leaderboard:
                key = f"/works/OL{item['work_id']}W"
                if key in solr_docs:
                    item["work"] = solr_docs[key]
                else:
                    item["work"] = web.ctx.site.get(key)

First we collect Solr-miss keys and batch fetch them.

openlibrary/plugins/upstream/edits.py:250 and openlibrary/plugins/upstream/edits.py:255

    @staticmethod
    def create_title(mr_type: int, olids: list[str]) -> str:
        if mr_type == CommunityEditsQueue.TYPE["WORK_MERGE"]:
            for olid in olids:
                book = site.get().get(f"/works/{olid}")
                if book and book.title:
                    return book.title
        elif mr_type == CommunityEditsQueue.TYPE["AUTHOR_MERGE"]:
            for olid in olids:
                author = site.get().get(f"/authors/{olid}")
                if author and author.name:
                    return author.name
        return "Unknown record"

Batch work and Author lookups; preserve the existing first-match behavior.

openlibrary/plugins/upstream/mybooks.py:84 and openlibrary/plugins/upstream/mybooks.py:88

for loan in myloans:
    book_key = loan["book"]
    if book := site.get().get(book_key):
        for _ in range(5):
            if getattr(getattr(book, "type", None), "key", None) == "/type/redirect":
                book_key = book.location
                book = site.get().get(book_key)
            else:
                break
        if book:
            book.loan = loan
            works = getattr(book, "works", None)
            work_key = works[0].key if works and len(works) > 0 else book.key
            loaned_at = loan.get("loaned_at") or 0.0
            merged_books[work_key] = (book, float(loaned_at), True)

Same initial batching; redirect resolution may still require sequential lookups.
Batch the initial book lookups before processing loans.

openlibrary/plugins/upstream/mybooks.py:621-622 and 624

   def get_notes(self, limit: int = RESULTS_PER_PAGE, page: int = 1) -> list:
       notes = Booknotes.get_notes_grouped_by_work(self.username, limit=limit, page=page)

       for entry in notes:
           entry["work_key"] = f"/works/OL{entry['work_id']}W"
           entry["work"] = self._get_work(entry["work_key"])
           entry["work_details"] = self._get_work_details(entry["work"])
           entry["notes"] = {i["edition_id"]: i["notes"] for i in entry["notes"]}
           entry["editions"] = {k: site.get().get(f"/books/OL{k}M") for k in entry["notes"] if k != Booknotes.NULL_EDITION_VALUE}
       return notes

(624) Edition keys are known upfront; batch and build a key → Thing mapping.
(621, 622)Additional N+1 found: _get_work() performs one site.get() per entry in get_notes() / get_observations(). Work keys can be collected and batch-loaded.

openlibrary/catalog/add_book/init.py:233

def find_matching_work(e):
    """
    Looks for an existing Work representing the new import edition by
    comparing normalized titles for every work by each author of the current edition.
    Returns the first match found, or None.

    :param dict e: An OL edition suitable for saving, has a key, and has full Authors with keys
                   but has not yet been saved.
    :rtype: None or str
    :return: the matched work key "/works/OL..W" if found
    """
    seen = set()
    for a in e["authors"]:
        q = {"type": "/type/work", "authors": {"author": {"key": a["key"]}}}
        work_keys = list(site.get().things(q))
        for wkey in work_keys:
            w = site.get().get(wkey)
            if wkey in seen:
                continue
            seen.add(wkey)
            if not w.get("title"):
                continue
            if mk_norm(w["title"]) == mk_norm(e["title"]):
                assert w.type.key == "/type/work"
                return wkey

Work keys are known per author; batch them before the comparison loop.

openlibrary/catalog/add_book/init.py:568

def find_threshold_match(rec: dict, edition_pool: dict[str, list[str]]) -> str | None:
    """
    Find the best match for rec in edition_pool and return its key.
    :param dict rec: the new edition we are trying to match.
    :param dict edition_pool: edition key matches, output of build_pool(import record)
    :return: None or the edition key '/books/OL...M' of the best edition match for enriched_rec in edition_pool
    """
    seen = set()
    for edition_keys in edition_pool.values():
        for edition_key in edition_keys:
            if edition_key in seen:
                continue
            thing = None
            while not thing or is_redirect(thing):
                seen.add(edition_key)
                thing = site.get().get(edition_key)
                if thing is None:
                    break
                if is_redirect(thing):
                    edition_key = thing["location"]
            if thing and editions_match(rec, thing):
                return edition_key
    return None

→ Only partially batchable. Initial keys can potentially be batched, but redirect resolution is dynamic/sequential. Needs more care before changing.

Proposal

migrating one at a time And leave add_book/init.py:568 for last because of the redirect-chain behavior.

lazy=True and revision=xx

I also found several other legitimate uses of:

site.get(key, lazy=True)

These won't be treated as normal N+1 replacements because lazy=True intentionally changes when the document is loaded.

Confirmed uses include:

openlibrary/plugins/upstream/utils.py:442
openlibrary/plugins/upstream/models.py:991
openlibrary/plugins/upstream/models.py:997
openlibrary/plugins/upstream/models.py:1006
openlibrary/plugins/upstream/models.py:1012

These involve things such as version/author references and previous revisions.

Proposal: no touching any related to lazy or revision once for this round will look into them in next phases

So for the current task, I'd scope the work to the 11 N+1 candidates above, with lazy=True or revision=xx as a separate investigation.

@CipherZ3r0

@CipherZ3r0 this is great work! Really in depth analysis.
I propose you open separate PRs for each of the first three and then we can go from there.
They seem the most fixable and we can get into a good pattern.
In your PRs please include a short screen recording using https://recordly.dev/ or similar showing the before/after that it still works.

Also, it would be awesome if you could do some small analysis on the performance for each one if possible (I know it can be hard with local data but see what you can do). Some of these may have smaller benefits but it's good to have the hygene in our codebase so AI doesn't replicate these negative patterns elsewhere.

@RayBB

@RayBB first three are done!!

@CipherZ3r0 this is great work! Really in depth analysis. I propose you open separate PRs for each of the first three and then we can go from there.

for the 4th

    @staticmethod
    def create_title(mr_type: int, olids: list[str]) -> str:
        if mr_type == CommunityEditsQueue.TYPE["WORK_MERGE"]:
            for olid in olids:
                book = site.get().get(f"/works/{olid}")
                if book and book.title:
                    return book.title
        elif mr_type == CommunityEditsQueue.TYPE["AUTHOR_MERGE"]:
            for olid in olids:
                author = site.get().get(f"/authors/{olid}")
                if author and author.name:
                    return author.name
        return "Unknown record"

potential approach to this is

@staticmethod
def create_title(mr_type: int, olids: list[str]) -> str:
    if mr_type == CommunityEditsQueue.TYPE["WORK_MERGE"]:
        books = site.get().get_many([f"/works/{olid}" for olid in olids])
        for book in books:
            if book and book.title:
                return book.title
    elif mr_type == CommunityEditsQueue.TYPE["AUTHOR_MERGE"]:
        authors = site.get().get_many([f"/authors/{olid}" for olid in olids])
        for author in authors:
            if author and author.name:
                return author.name
    return "Unknown record"

behavior comparison:
before:

look for one work at a time and when it finds book and book.title it returns so it can be after 10 queries or 1 so number of request hitting DB is not fix.

After:

it will fetch all the data of works in single DB hit and then it will go through those data one at a time.

This approach will reduce the number of request hitting database but it will take temp ram storage more.

I'm not sure how caching is working but probably we can store that data in cache which can possibly come in handy.

so my question is should we change it or keep it as it is?

That's a good question. Do you know how many olids are likely to be passed into this? I imagine almost every time the first entry of author or work has a title. As such, we probably don't need to change this one since it'll create more database work on average. Do you agree?

I think 5 definitely has some room for improvement though! Wanna try that?

@RayBB

I am not sure about how many olids are likely to be passed into this and also not sure about will it get title on very first try but i do agree that if it will get it on first try then there's no meaning to change it to .get_many() it will create unnecessary work for DB.

so i will skip 4th for now and will work on 5th
@RayBB

@RayBB

@RayBB Hey, I looked in find_threshold_match() for the next part of #12432. The repeated site.get().get(edition_key) calls can be batched with get_many(), but redirects make this a bit more complicated since an edition can resolve through multiple redirect hops.

I see a possible approach where we batch the lookups hop-by-hop and then evaluate the resolved editions in the original edition_pool order. However, preserving the existing seen behavior across redirect chains makes the implementation considerably more complex.

What approach would you prefer here? Should we go with the hop-by-hop batching approach, look for a simpler optimization, or just leave this path as it is given the added complexity?

fix: part 4 #12432 — batch mybooks book lookups (#13597)

fix: part 5 #12432 — batch mybooks notes lookups (#13609)

Merge pull request #13625 from CipherZ3r0/12432/batch-find-matching-w…

Navigation Menu

Repository navigation

perf: N+1 queries on thing table causing 3.8M single-row DB lookups #12432

Feature Request

Database performance: N+1 queries on `thing` table causing 3.8M single-row lookups

Summary

Evidence

Expected behaviour

Steps to reproduce

Suggested fix

Impact

Environment

Breakdown

Related files

Requirements Checklist

Stakeholders

Instructions for Contributors

Context

Issue Assessment

6 remaining items

N+1 candidates

Proposal

lazy=True and revision=xx

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

perf: N+1 queries on thing table causing 3.8M single-row DB lookups #12432

Description

Feature Request

Database performance: N+1 queries on thing table causing 3.8M single-row lookups

Summary

Evidence

Expected behaviour

Steps to reproduce

Suggested fix

Impact

Environment

Breakdown

Related files

Requirements Checklist

Stakeholders

Instructions for Contributors

Activity

mekarpeles commented on Apr 22, 2026

Context

Issue Assessment

WeiGuang-2099 commented on Apr 22, 2026

6 remaining items

RayBB commented on Sep 4, 2026

CipherZ3r0 commented on Sep 4, 2026

CipherZ3r0 commented on Sep 5, 2026

N+1 candidates

Proposal

lazy=True and revision=xx

RayBB commented on Sep 6, 2026

CipherZ3r0 commented on Sep 10, 2026

RayBB commented on Sep 10, 2026

CipherZ3r0 commented on Sep 10, 2026

CipherZ3r0 commented on Sep 14, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Database performance: N+1 queries on `thing` table causing 3.8M single-row lookups