Skip to content

Updating request queue metada performs full table scan in SQL storage #1533

Description

@ericvg97

Updating the metadata performs this subquery (in a bigger update query) which performs a full table scan, making all my crawlers much slower (and making the tasks timeout)

SELECT count(*) FROM request_queue_records
WHERE request_queue_id = 'AO2Uinca6RHO1Mo6X'
AND is_handled IS true

With the other two subqueries it also happens:

SELECT count(*) FROM request_queue_records
        WHERE request_queue_id = 'AO2Uinca6RHO1Mo6X'
          AND is_handled IS false
SELECT count(*) FROM request_queue_records
        WHERE request_queue_id = 'AO2Uinca6RHO1Mo6X'

this is a bottleneck as all crawler instances want to update this row at the same time + the query is slow.
Besides adding an index which would make the query much faster, is there the option to not update this metadata? Is it used for something?
Thi

Activity

  1. added
    t-toolingIssues with this label are in the ownership of the tooling team.
    on Nov 5, 2025
  2. ericvg97 commented on Nov 5, 2025

    @ericvg97
    Author

    I added this index and there is no table scan anymore

    CREATE INDEX idx_request_queue_records_queueid_handled ON public.request_queue_records USING btree (request_queue_id, is_handled)
    

    although (without having no idea on what the metadata is used for) I would be super interested in deleting this query altogether if it is only for analytic purposes

  3. Mantisus commented on Nov 5, 2025

    @Mantisus
    Collaborator

    Hi @ericvg97, thanks for catching that. And for your research into the issue.

    Unfortunately, at this stage, I don't see a way to completely remove this query. Although the metadata record is indeed a bottleneck when attempting concurrent access to it.

    Metadata is part of the publish API storage and also acts as a queue state synchronizer between clients.

    This request synchronizes metadata, preventing inconsistent data that may occur in certain cases.

  4. added theissue type on Nov 5, 2025
  5. ericvg97 commented on Nov 5, 2025

    @ericvg97
    Author

    oh I see, and what do you think about adding the index?

  6. Mantisus commented on Nov 5, 2025

    @Mantisus
    Collaborator

    Yes, of course, I will test the queue with the index and if I don't see any potential problems, I will add it or something similar. And I will see if we can make this query less frequently.

  7. ericvg97 commented on Nov 5, 2025

    @ericvg97
    Author

    Perfect, thanks! I am also having a similar problem with the datasets. Adding a new entry triggers an update in the datasets table (only metadata) and it also blocks my crawlers... Any suggestions? I was thinking on creating multiple datasets, but I am not saving different types of data, just the urls I visit.

  8. Mantisus commented on Nov 5, 2025

    @Mantisus
    Collaborator

    I will consider optimizing metadata record updates.
    This is an issue for Dataset and RequestQueue, as there are counters that require updating.

  9. ericvg97 commented on Nov 6, 2025

    @ericvg97
    Author

    what would you think about calculating these values for RequestQueue on demand? Is it possible that these counts are much more written than read? The change could be minimal, just deleting the counts in the request_queue and doing the query in the get_data or get_metadata method instead of when adding requests (or retrieving them) from the queue.

    (For Datasets I understand it is not that easy because modified_at can't be infered in the database records table, but we could either store a timestamp in the DatasetRecords table (and also compute modified_at on query time))

  10. Mantisus commented on Nov 6, 2025

    @Mantisus
    Collaborator

    I am considering a slightly larger update that will change the approach to working with metadata records.
    I am still in the process of implementing it, and it will require preliminary testing to ensure that it will indeed improve the situation.

  11. ericvg97 commented on Nov 6, 2025

    @ericvg97
    Author

    Thanks!! We can be beta testers if you need it

  12. added a commit that references this issue on Feb 10, 2026
    df1347a
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

t-toolingIssues with this label are in the ownership of the tooling team.

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions