Skip to content

File storage recomputes three full-table aggregates on every Browse render #299

Description

@antosubash

/file-storage/ runs six sequential queries per render, three of which deliberately ignore the active
filters and therefore scan the whole file_storage_storedfile table
(modules/file_storage/file_storage/endpoints/views.py:50-91):

  • list_files — one page plus a COUNT(*) over the filter
    (modules/file_storage/file_storage/queries.py:55-82), re-run a second time when page is past
    the end (views.py:56-59).
  • content_type_facetsGROUP BY content_type over every row
    (queries.py:84-96).
  • uploader_facetsGROUP BY created_by over every row (queries.py:99-115).
  • used_bytesSUM(size_bytes) over every row, with the docstring explicitly noting it ignores
    the filters (queries.py:118-128).
  • resolve_uploader_labels — one more query to name the rows and the facet values
    (views.py:66-68).

None is cached, none is bounded by the page size, and they run serially on one session. The reasoning
for ignoring the filters is right — a facet list that hides its own alternatives is a dead end, and a
usage figure that shrinks when you type in the search box describes nothing — but it means the cost
of the screen grows with the bucket rather than with the page, on every single render including the
ones that only changed ?page=.

What would fix it: cache the three unfiltered aggregates per process with a short TTL and invalidate
them on FileUploaded / delete (the numbers already tolerate being seconds stale — they describe the
bucket, not the page), or maintain used_bytes and the facet counts as a small rollup table the
upload and delete paths update. Failing either, at minimum run the independent aggregates
concurrently rather than serially.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions