feat(datastore): calculate deduplicated repository size (#486)
This change introduces a new method on the repository store that allows calculating the deduplicated size of a repository.
A new set of test fixtures were introduced to simulate realistic scenarios for this exercise and others that will follow (group and top-level namespace size calculations).
Related to #486 (closed).
Database Review
Please see #493 (closed) for an explanation of how the deduplicated size of repositories should be calculated, and therefore the rationale behind this query.
Query
SELECT
sum(q.size)
FROM ( WITH RECURSIVE cte AS (
-- all manifests that are tagged
-- AND/OR
-- referenced by a tagged manifest list at any depth (recursion)
SELECT
m.id AS manifest_id
FROM
manifests AS m
WHERE
m.top_level_namespace_id = $1
AND m.repository_id = $2
AND EXISTS (
SELECT
FROM
tags AS t
WHERE
t.top_level_namespace_id = m.top_level_namespace_id
AND t.repository_id = m.repository_id
AND t.manifest_id = m.id)
UNION
SELECT
mr.child_id AS manifest_id
FROM
manifest_references AS mr
JOIN cte ON mr.parent_id = cte.manifest_id
WHERE
mr.top_level_namespace_id = $1
AND mr.repository_id = $2)
-- the deduplicated set of layers referenced by such manifests
SELECT DISTINCT ON (l.digest)
l.size
FROM
layers AS l
JOIN cte ON l.top_level_namespace_id = $1
AND l.repository_id = $2
AND l.manifest_id = cte.manifest_id) AS q
Query Plan
The query plan was extracted from a read-only replica in production, using one of the biggest repositories, which at the time of writing, has 74 manifests with 1016 layers and 84 tags: https://explain.depesz.com/s/78rX
The planning time was 1.124ms and the execution time 1.470ms. The planner has chosen to do a sequential scan instead of an index scan in all involved tables, but I believe that is only because there are not enough rows in each partition to warrant an index scan. All tables have at least one index applicable to the (top_level_namespace_id, repository_id)
pair, so the index scan should become the norm down the road.