[Large Databases]: Paginate the delete blockers

[totallynotleo]

Describe the bug
Opening a high-level record with a lot of linked records can cause Specify worker to crash.
Our (perhaps underpowered) test server will run out of memory (and hit high-cpu) trying to generate the Linked records query to run in the database. The database itself doesn't have issues running the query to get the linked records, but (our current running theory) generating the WHERE IN () for the Linked Records button maxes out the Specify worker and crashes our entire server.

To Reproduce
Steps to reproduce the behavior:

1. Go to any of the trees at a high level (i.e. Life, Earth)
2. Click to edit the record
3. The linked records button will begin to automatically generate the results for the Linked records button
4. Doing so causes the server to run out of memory until it SIGKILLs the process.

Expected behavior
I can't think of an expected behavior (other than not taking down the server), but maybe there should be a warning or limit to the number of records fetched when running the linked records query, or an optimisation to the backend query generation so it can handle larger records. Alternatively not running the linked query every time a record is opened but only when chosen would avoid accidentally clicked open on a high-level record and causing the issue.

Screenshots
Opening this record from the Geography tree broke our test server for ~30 minutes fetching the Linked Records. The actual query on the database ran quickly but creating it used all available memory. I am aware that it's probably not best to have >900k locality records but that's the way our instance is set up. We've noticed significant slowdowns on anything with >50k linked records, and crashes at >200k.

If applicable, add screenshots to help explain your problem.

Crash Report
There was no specific error message from Specify for this (other than a timeout message at one stage.
There logs for the container itself show that it runs out of memory and SIGKILLs:

System Information:

Specify 7 System Information - 2025-10-28T05_47_03.613Z.txt

Please fill out the following information manually:

• OS: Windows
• Browser: Chrome
• Specify 7 Version: 7.11.2.1
• Database Name: specify_staging
• Collection name: QLD Herbarium
• User Name: running on my own account [leo]
• URL: [e.g. https://ku_fish-v7.test.specifysystems.org/specify/workbench/215/]

Reported By
Queensland Herbarium

Additional context
Our test server is running RHEL 9, with the specify docker composition set up in Podman.

Let me know if there's any other information that would be helpful.

[grantfitzsimmons]

@totallynotleo Thanks for the report! Is it possible for you to share the database with us for evaluation? See https://discourse.specifysoftware.org/t/send-a-backup-to-specify/767#p-1237-h-1-specify-backup-2

If not, could you provide some more information on how much memory your server has? Thank you!

[totallynotleo]

Hi Grant,

I've sent through a copy of the database via the hightail server.
Our dev server where we having the issue is a RHEL9 server running in Azure:
Azure VM Size: Standard D2as v4 (2 vcpus, 8 GiB memory)
with a data disk for all of containers on a 128GB ssd.

It's not particularly powerful, but it does manage to run our test instance just fine unless someone opens a record with a lot of linked items.

I think given the way that the Linked Records button will always run a behind-the-scenes query for any record that gets opened, there should probably either be some way to disable the feature altogether in the settings, or maybe have it set up to not fetch the count until prompted (like a first click to get the count, a second click to show linked records - I think this would be the ideal UX for us, but might be confusing for other people) Even if we doubled the RAM or CPUs on our server it would only take 2 or 3 people looking at a big record or someone opening a record a few times by accident to really slow down the server.

Thanks for your help!

[totallynotleo]

Hello,
We moved our PROD Specify 7 instance to 7.11.2.1 in mid-december and this bug ended up rearing up again and actually causing a full PROD crash.

A bit of our investigation and what we've realised:

1. Technically this bug could've been cause earlier, had someone tried to click delete on a high level taxa or geography tree item. Obviously, nobody had pulled up the "Life" taxon before and tried clicking delete (thankfully), so it never came up. Once it was in PROD, someone opened a high level record within a week of the update which triggered the Linked Records button...
2. The issue is being caused by the delete_blockers function getting a lot of objects and running a flatten on a very large amount of data.
3. This caused the worker to run at 100% memory like when we reported this in testing - unlike in our testing however it didn't trigger a SIGKILL for some reason, so we were unable to get into the server to shut it down for a few hours.

We ended up putting in 2 workarounds to mitigate this issue on our end:

1. Limiting the max memory usage by the Specify worker itself in our container config so that even if it did get triggered it wouldn't lock us out of the server
2. Manually blocking the API calls in our nginx config to delete_blockers on any records with (in our case) more than 700k sibiling records - something like this:

    location ~ ^/delete_blockers/delete_blockers/taxon/(1\/|107\/|519...>
        deny all;
    }

This has been a sufficient workaround to avoid any new crashes, but does mean that any time a high level record is opened an error message pops up due to the 403.

The actual solution should probably be to either not trigger the delete_blockers function immediately upon opening a record for the Linked Records button, or to optimize the way that delete_blockers runs to be more performant.

Let me know if there's any other info you need from me on this issue or if you have any questions.

[melton-jason]

Thank you for the update @totallynotleo! Nice investigation work.

I did my own investigation into this Issue and other Performance-related issues, so hopefully I can provide a little more context with the Issue and propose some potential solutions that we're looking into.

You're absolutely right the underlying endpoint causing the slowdown is /delete_blockers/<table_name>/<record_id>/ (where <table_name> and <record_id> are replaced with their respective values).
More specifically, the endpoint is called once per record, when the record is directly viewed in the browser.

Currently, the Linked Records and Delete Button share a single /delete_blockers/ request when a record's form is viewed. So every time a form is displayed or navigated to,, it should send out a single /delete_blockers/ request.

Below is an example of tracking the network request against the root node of the Taxon tree on our sp7demofish instance by using Google Chrome's DevTools:

demofish_issue_7515.mov

Cause of the Regression

I don't believe it was/is the introduction of the Linked Records button (see #6328) which causes this performance impact.
The underlying behavior from a frontend point of view is consistent from before and after the Linked Records button was introduced.
e.g., Specify 7 was still calling a single /delete_blockers/ endpoint when a record was displayed or navigated to.

The somewhat recent change in behavior which I believe is causing this change was actually introduced in v7.10.0 by Allow cascading deletion of tree nodes and unused children (#5324), to fix #1653.

The changes in that PR change the Delete Blocker behavior for trees: instead of deletion being blocked by parent nodes, it was changed so that deletion would cascade to children nodes, meaning Specify would attempt to delete all subtrees when deleting a tree.
This leads to performance impacts because when you want to see if deleting a node is possible, Specify now has to check all children of the node and see if those children can be deleted (and each of those children need to check their children and so on).
When you're checking deletion for the root node, Specify has to exhaustively check every node in the tree.
Previously, Specify would only need to check a node's immediate children (the existence of a child node would mean a node is referenced and deletion would be blocked).

Below is a video from a local instance I took on v7.9.6.2 which does experience significantly slower behavior on versions v7.10.0+ because of this behavior.

local_v7962.mov

As I show in the Performance Observations and Metrics section further below, the exact same delete_blocker query takes over 9 seconds and ~400 MB of memory in versions past v7.10.0!

Which Specify 7 version did you update from? If it is from before v7.10.0, then I believe it was actually a change in v7.10.0 which is causing this slow behavior you are now experiencing.

Workaround!

If you are able to update your Specify instance, I pushed a branch for you based on v7.11.2.1 that reverts #5324.
This means you will be unable to delete tree nodes with children without first deleting their children, but Specify 7 will no longer recursively fetch blockers for all of a nodes children.
The Docker Image is specifyconsortium/specify7-service:7515-blocker-fix.

You can view the changes from v7.11.2.1 here: v7.11.2.1...7515-blocker-fix

You can update your specify instance and worker by using specifyconsortium/specify7-service:7515-blocker-fix as the image. The rest of your configuration can generally stay the same; you would be able to remove the direct handling of requests in your NGINX configuration and can optionally increase your memory limits for the main Specify 7 instance and the Worker.

Below is a video with my local instance on that branch:

local_with_fix.mov

⚠️ I can't provide any guarantees regarding the branch, any implications it may have with when a proper fix will be included in an official public release, or the feasibility of applying the same fix to other Specify 7 releases. The branch exists completely outside of our normal release or fix procedure. I'm pretty sure the branch is sound, and I did some brief local testing, but please do your own testing and vetting with such an experimental and quick change 😅
It is really just something I could do pretty quickly, hopefully it helps if you're able to integrate it!

Outside of that branch, I unfortunately can't really provide an easy or satisfactory workaround for this issue at this moment.

I think stopping those large, tree delete blocker queries at the reverse proxy (NGINX) is clever! If you are unable to use a custom branch/version and don't like the error messages associated with outright denying a request, you might be able to craft a valid response to the endpoint that would still prohibit deletion. I'm (perhaps naively) thinking along the lines of sending a static response that would make a record a delete blocker of itself. Then no error would be raised, the request would be directly handled by NGINX, and deletion would still not be possible in the browser (e.g., you would have to delete the record to delete the record).

This could theoretically be accomplished by using a template file and NGINX's HTTP Substitution Module, populating the necessary fields with the ID of the record that's requesting its Delete Blockers fetched.

Something like the following should be valid JSON for a /delete_blockers/taxon/<id> endpoint:
(Modify the array of ids as needed: these indicate the ids of the blocking resources).

[
    {
        "table": "Taxon",
        "field": "parent",
        "ids": [
            1,
            2,
            3
        ]
    }
]

If you need it for reference, the backend code for the endpoint for v7.11.2.1 can be accessed here.

⚠️ Note that I have not attempted any of the above suggestion: just that I think it's fairly possible if you want to look into a somewhat better user experience in the meantime.

Potential Solutions (and what we're working on internally!)

On our side of things, we are still in the design phase: we're collecting and considering requirements, evaluating and brainstorming possible solutions; so a solution has yet to see any real active development and a release in which it will be included is not yet determined.

The first solution I'm proposing for fixing the performance Issue(s) related to delete blockers is to paginate the delete blockers.
That is, fetch the delete blockers in "chunks" and sections rather than all at once. This would even enable us to (if needed) fetch up to a certain number of blocking resources, and potentially prompt the user to fetch additional blockers.

Secondly, there are some query optimizations and overrides that apply specifically to trees. Specifically, we could override the current behavior and leverage tree node numbers for a tree-specific delete blocker optimization.

We could theoretically very easily defer fetching a record's delete blockers until the Delete Button or Linked Records button is clicked: this would definitely provide a better user experience in most cases (when the Delete Button or Linked Records is not clicked), although wait times for delete blockers when they are needed could be significantly longer-- which might be a fine trade off, system memory considered. This wouldn't be permanent solution, as it doesn't address the underlying issue with the resource usage when the delete blockers are fetched, but it can be considered for a temporary bandaid (and can easily be applied to only tree records, such that other parts of the application would behave as normal).

Performance Observations and Metrics

Below is the same example of tracking the network request against the root node of the Taxon tree on our sp7demofish instance:

demofish_issue_7515.mov

The delete blocker request in the video took 8.63 seconds to process by the backend, and its very likely to have taken a significant amount of resources as well.

Below is a video of the impact of such a request on server resources in my local environment with a near copy of the same database. When working with this Issue, I'm running Specify v7.11.2.1 in a pseudo production environment (e.g., using Gunicorn + Django as the WSGI to handle requests rather than Django in its development runserver mode).
I'm running Docker on an Apple M1 on macOS Sonoma (14.3) via the Apple Virtualization backend with the following constraints: 8 CPUs, 7.9 GB Memory, 1.5 GB swap memory, and 256 GB disk space.

local_issue_7515.mov

Nearly that request alone took Specify from its idle ~330 MB to 715 MB according to Docker's stats. This is a relatively small tree of 16,239 Taxon nodes associated with 63,011 Determinations.

I have recorded and observed a much more drastic impact on larger databases. On a database (similar in size to the one you have sent via hightail) with 106,673 Taxon records associated with 1,020,562 Determinations, I have observed a peak memory usage of 5.19 GB.

Below is an image with some resulting metrics which came about by profiling the delete_blocker endpoint on the aforementioned larger database by following the workflow:

• Starting Specify 7 and idling--to use as a base-line; the memory that is strictly required to even start a Specify 7 instance.
• Going to the Root node in the Taxon tree and opening its DataEntry form (this will trigger the initial fetch for delete blockers)
• Going to the root node of the Geography tree and opening its DataEntry form
• Going back to the Taxon tree and opening the root node's DataEntry form

After each operation in the workflow, I gave Specify 7 some time to settle down to an "idle" state, where only the essential database queries were made.

My interpretation of the sustained high memory usage after each operation is the result of Django's internal caching of QuerySets, as well as some views/endpoints being explicitly stored in the Local Memory Cache (largely the data stored in this server-side cache is configuration related: report runner status, schema localization, datamodel, app resources and view sets, etc.)

Of course these numbers would vary depending on the setup and the database, but I think it is representative of the memory consumption of a "larger" database (100,000+ Taxon records, 1 million+ Determinations, 400+ Geography records, 900,000+ Localities, etc.) solely in the context of fetching the delete blockers.