Top level namespace based database partitioning
Changes
- Introduces a new
top_level_namespacesdatabase entity; - Changes the partitioning key from
repository_idtotop_level_namespace_idfor all partitioned tables; - Partitions the
repositoriestable.
This is done without changing the app logic or the REST API, as we're able to infer a namespace path from the repository path (split("<path>", "/")[0]) and obtain the corresponding top_level_namespace_id behind the scenes.
Note: I can't split the changes in this MR in multiple smaller MRs without breaking all of the tests because this is a major low-level foundational change. As consequence, this MR is way larger than desirable.
Rationale
There is no namespace entity in the registry context. A root repository (what we call a namespace) is treated like any other. Additionally, all the current API operations are scoped by repository (its full path). Therefore, the obvious partitioning strategy is to do it by repository, which is what we have done so far.
However, recently, we learned that a namespace level aggregation will likely be required to support new features and API operations, such as:
- Calculate the storage space used by all repositories under a given namespace;
- List repositories under a given namespace;
- Allow referencing manifests across different repositories under a namespace.
These are much harder with repository-based partitioning.
Additionally:
- Blobs are deduplicated on the storage backend and shared across all repositories/namespaces. In the future, if we start facing major scale/performance concerns with online GC, we may have to consider moving to a per-namespace blob pool;
- If we ever go down the road of database sharding, doing it by top-level namespace is likely the way to go;
- On the Rails side, everything is tied to a top-level namespace, so in theory, having the same grouping on the registry side will make it easier to accommodate future feature requests.
It's better to try and perform this change before heading to production, as it would be extremely hard to implement after the fact.
Tests
Setup
All tests were performed from a GCP VM, using a PostgreSQL 12 CloudSQL instance (all within the same zone) with default settings and the following specs:
- 8 vCPU
- 16 GB RAM
- 30 GB SSD
- 2.000 MB/s network bandwidth
Test Database
We have used a database populated with the dev.gitlab.org registry data (10TB) and then inflated with a single run of bde39394, making it ~20x larger in row count.
Note: If you looked at !566 (merged) before, you'll notice that the statistics are slightly different here. Although the partition count remains the same (64), no two runs of the script used to inflate the test database produce the same data output, thus the slight (negligible) differences.
Individual tables and partitions size
Query
SELECT
relname AS "table",
nspname AS "schema",
pg_size_pretty(pg_relation_size(c.oid)) AS "relation size",
pg_size_pretty(pg_total_relation_size(c.oid)) AS "total size"
FROM
pg_class AS c
LEFT JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
WHERE
nspname IN ('public', 'partitions')
AND c.relkind IN ('p', 'r')
ORDER BY
relname,
nspname DESC;Result
table | schema | relation size | total size
------------------------------+------------+---------------+------------
blobs | public | 0 bytes | 0 bytes
blobs_p_0 | partitions | 3448 kB | 7696 kB
blobs_p_1 | partitions | 3432 kB | 7688 kB
blobs_p_10 | partitions | 3408 kB | 7656 kB
blobs_p_11 | partitions | 3400 kB | 7656 kB
blobs_p_12 | partitions | 3424 kB | 7696 kB
blobs_p_13 | partitions | 3416 kB | 7672 kB
blobs_p_14 | partitions | 3440 kB | 7720 kB
blobs_p_15 | partitions | 3424 kB | 7576 kB
blobs_p_16 | partitions | 3432 kB | 7672 kB
blobs_p_17 | partitions | 3432 kB | 7704 kB
blobs_p_18 | partitions | 3432 kB | 7624 kB
blobs_p_19 | partitions | 3440 kB | 7800 kB
blobs_p_2 | partitions | 3416 kB | 7544 kB
blobs_p_20 | partitions | 3464 kB | 7728 kB
blobs_p_21 | partitions | 3440 kB | 7760 kB
blobs_p_22 | partitions | 3448 kB | 7816 kB
blobs_p_23 | partitions | 3416 kB | 7672 kB
blobs_p_24 | partitions | 3408 kB | 7672 kB
blobs_p_25 | partitions | 3440 kB | 7696 kB
blobs_p_26 | partitions | 3400 kB | 7576 kB
blobs_p_27 | partitions | 3384 kB | 7624 kB
blobs_p_28 | partitions | 3400 kB | 7640 kB
blobs_p_29 | partitions | 3400 kB | 7584 kB
blobs_p_3 | partitions | 3432 kB | 7656 kB
blobs_p_30 | partitions | 3464 kB | 7824 kB
blobs_p_31 | partitions | 3424 kB | 7720 kB
blobs_p_32 | partitions | 3416 kB | 7672 kB
blobs_p_33 | partitions | 3400 kB | 7608 kB
blobs_p_34 | partitions | 3408 kB | 7616 kB
blobs_p_35 | partitions | 3408 kB | 7640 kB
blobs_p_36 | partitions | 3400 kB | 7504 kB
blobs_p_37 | partitions | 3424 kB | 7680 kB
blobs_p_38 | partitions | 3432 kB | 7688 kB
blobs_p_39 | partitions | 3448 kB | 7672 kB
blobs_p_4 | partitions | 3400 kB | 7640 kB
blobs_p_40 | partitions | 3424 kB | 7680 kB
blobs_p_41 | partitions | 3400 kB | 7720 kB
blobs_p_42 | partitions | 3400 kB | 7552 kB
blobs_p_43 | partitions | 3416 kB | 7584 kB
blobs_p_44 | partitions | 3424 kB | 7672 kB
blobs_p_45 | partitions | 3432 kB | 7624 kB
blobs_p_46 | partitions | 3416 kB | 7640 kB
blobs_p_47 | partitions | 3432 kB | 7768 kB
blobs_p_48 | partitions | 3424 kB | 7656 kB
blobs_p_49 | partitions | 3432 kB | 7672 kB
blobs_p_5 | partitions | 3440 kB | 7600 kB
blobs_p_50 | partitions | 3432 kB | 7768 kB
blobs_p_51 | partitions | 3440 kB | 7768 kB
blobs_p_52 | partitions | 3456 kB | 7736 kB
blobs_p_53 | partitions | 3424 kB | 7768 kB
blobs_p_54 | partitions | 3416 kB | 7688 kB
blobs_p_55 | partitions | 3408 kB | 7664 kB
blobs_p_56 | partitions | 3448 kB | 7752 kB
blobs_p_57 | partitions | 3416 kB | 7728 kB
blobs_p_58 | partitions | 3440 kB | 7656 kB
blobs_p_59 | partitions | 3408 kB | 7664 kB
blobs_p_6 | partitions | 3408 kB | 7560 kB
blobs_p_60 | partitions | 3392 kB | 7592 kB
blobs_p_61 | partitions | 3456 kB | 7696 kB
blobs_p_62 | partitions | 3400 kB | 7544 kB
blobs_p_63 | partitions | 3400 kB | 7488 kB
blobs_p_7 | partitions | 3440 kB | 7712 kB
blobs_p_8 | partitions | 3408 kB | 7632 kB
blobs_p_9 | partitions | 3408 kB | 7600 kB
gc_blob_review_queue | public | 194 MB | 464 MB
gc_blobs_configurations | public | 0 bytes | 0 bytes
gc_blobs_configurations_p_0 | partitions | 264 kB | 1040 kB
gc_blobs_configurations_p_1 | partitions | 256 kB | 1016 kB
gc_blobs_configurations_p_10 | partitions | 264 kB | 1016 kB
gc_blobs_configurations_p_11 | partitions | 256 kB | 1000 kB
gc_blobs_configurations_p_12 | partitions | 272 kB | 1048 kB
gc_blobs_configurations_p_13 | partitions | 256 kB | 1032 kB
gc_blobs_configurations_p_14 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_15 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_16 | partitions | 264 kB | 1048 kB
gc_blobs_configurations_p_17 | partitions | 256 kB | 1048 kB
gc_blobs_configurations_p_18 | partitions | 272 kB | 1072 kB
gc_blobs_configurations_p_19 | partitions | 264 kB | 1024 kB
gc_blobs_configurations_p_2 | partitions | 264 kB | 1072 kB
gc_blobs_configurations_p_20 | partitions | 272 kB | 1072 kB
gc_blobs_configurations_p_21 | partitions | 256 kB | 1016 kB
gc_blobs_configurations_p_22 | partitions | 272 kB | 1056 kB
gc_blobs_configurations_p_23 | partitions | 264 kB | 1008 kB
gc_blobs_configurations_p_24 | partitions | 264 kB | 1040 kB
gc_blobs_configurations_p_25 | partitions | 256 kB | 1032 kB
gc_blobs_configurations_p_26 | partitions | 256 kB | 976 kB
gc_blobs_configurations_p_27 | partitions | 256 kB | 1016 kB
gc_blobs_configurations_p_28 | partitions | 264 kB | 1048 kB
gc_blobs_configurations_p_29 | partitions | 264 kB | 1040 kB
gc_blobs_configurations_p_3 | partitions | 264 kB | 1064 kB
gc_blobs_configurations_p_30 | partitions | 256 kB | 1016 kB
gc_blobs_configurations_p_31 | partitions | 272 kB | 1048 kB
gc_blobs_configurations_p_32 | partitions | 264 kB | 1024 kB
gc_blobs_configurations_p_33 | partitions | 264 kB | 1024 kB
gc_blobs_configurations_p_34 | partitions | 264 kB | 1056 kB
gc_blobs_configurations_p_35 | partitions | 264 kB | 1000 kB
gc_blobs_configurations_p_36 | partitions | 264 kB | 1040 kB
gc_blobs_configurations_p_37 | partitions | 264 kB | 992 kB
gc_blobs_configurations_p_38 | partitions | 264 kB | 1064 kB
gc_blobs_configurations_p_39 | partitions | 264 kB | 1008 kB
gc_blobs_configurations_p_4 | partitions | 264 kB | 1040 kB
gc_blobs_configurations_p_40 | partitions | 256 kB | 1016 kB
gc_blobs_configurations_p_41 | partitions | 264 kB | 1056 kB
gc_blobs_configurations_p_42 | partitions | 264 kB | 1048 kB
gc_blobs_configurations_p_43 | partitions | 256 kB | 1024 kB
gc_blobs_configurations_p_44 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_45 | partitions | 272 kB | 1040 kB
gc_blobs_configurations_p_46 | partitions | 264 kB | 1048 kB
gc_blobs_configurations_p_47 | partitions | 264 kB | 1040 kB
gc_blobs_configurations_p_48 | partitions | 272 kB | 1056 kB
gc_blobs_configurations_p_49 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_5 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_50 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_51 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_52 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_53 | partitions | 256 kB | 1000 kB
gc_blobs_configurations_p_54 | partitions | 264 kB | 1032 kB
gc_blobs_configurations_p_55 | partitions | 256 kB | 992 kB
gc_blobs_configurations_p_56 | partitions | 256 kB | 984 kB
gc_blobs_configurations_p_57 | partitions | 264 kB | 1024 kB
gc_blobs_configurations_p_58 | partitions | 256 kB | 984 kB
gc_blobs_configurations_p_59 | partitions | 248 kB | 984 kB
gc_blobs_configurations_p_6 | partitions | 264 kB | 1040 kB
gc_blobs_configurations_p_60 | partitions | 248 kB | 968 kB
gc_blobs_configurations_p_61 | partitions | 256 kB | 1024 kB
gc_blobs_configurations_p_62 | partitions | 264 kB | 1072 kB
gc_blobs_configurations_p_63 | partitions | 256 kB | 984 kB
gc_blobs_configurations_p_7 | partitions | 256 kB | 1000 kB
gc_blobs_configurations_p_8 | partitions | 264 kB | 1024 kB
gc_blobs_configurations_p_9 | partitions | 264 kB | 1016 kB
gc_blobs_layers | public | 0 bytes | 0 bytes
gc_blobs_layers_p_0 | partitions | 6056 kB | 23 MB
gc_blobs_layers_p_1 | partitions | 5960 kB | 23 MB
gc_blobs_layers_p_10 | partitions | 6032 kB | 23 MB
gc_blobs_layers_p_11 | partitions | 5864 kB | 22 MB
gc_blobs_layers_p_12 | partitions | 5992 kB | 23 MB
gc_blobs_layers_p_13 | partitions | 6144 kB | 23 MB
gc_blobs_layers_p_14 | partitions | 6120 kB | 23 MB
gc_blobs_layers_p_15 | partitions | 5976 kB | 23 MB
gc_blobs_layers_p_16 | partitions | 6040 kB | 23 MB
gc_blobs_layers_p_17 | partitions | 6056 kB | 23 MB
gc_blobs_layers_p_18 | partitions | 5920 kB | 23 MB
gc_blobs_layers_p_19 | partitions | 6096 kB | 23 MB
gc_blobs_layers_p_2 | partitions | 5952 kB | 23 MB
gc_blobs_layers_p_20 | partitions | 6000 kB | 23 MB
gc_blobs_layers_p_21 | partitions | 6104 kB | 23 MB
gc_blobs_layers_p_22 | partitions | 5928 kB | 23 MB
gc_blobs_layers_p_23 | partitions | 6016 kB | 23 MB
gc_blobs_layers_p_24 | partitions | 6016 kB | 23 MB
gc_blobs_layers_p_25 | partitions | 6032 kB | 23 MB
gc_blobs_layers_p_26 | partitions | 5968 kB | 23 MB
gc_blobs_layers_p_27 | partitions | 6040 kB | 23 MB
gc_blobs_layers_p_28 | partitions | 5976 kB | 23 MB
gc_blobs_layers_p_29 | partitions | 5808 kB | 22 MB
gc_blobs_layers_p_3 | partitions | 5960 kB | 23 MB
gc_blobs_layers_p_30 | partitions | 6512 kB | 25 MB
gc_blobs_layers_p_31 | partitions | 6064 kB | 23 MB
gc_blobs_layers_p_32 | partitions | 6056 kB | 23 MB
gc_blobs_layers_p_33 | partitions | 5784 kB | 22 MB
gc_blobs_layers_p_34 | partitions | 5952 kB | 23 MB
gc_blobs_layers_p_35 | partitions | 5944 kB | 22 MB
gc_blobs_layers_p_36 | partitions | 5920 kB | 23 MB
gc_blobs_layers_p_37 | partitions | 6000 kB | 23 MB
gc_blobs_layers_p_38 | partitions | 5960 kB | 23 MB
gc_blobs_layers_p_39 | partitions | 6080 kB | 23 MB
gc_blobs_layers_p_4 | partitions | 6096 kB | 23 MB
gc_blobs_layers_p_40 | partitions | 6240 kB | 24 MB
gc_blobs_layers_p_41 | partitions | 5936 kB | 23 MB
gc_blobs_layers_p_42 | partitions | 5928 kB | 23 MB
gc_blobs_layers_p_43 | partitions | 6128 kB | 23 MB
gc_blobs_layers_p_44 | partitions | 6008 kB | 23 MB
gc_blobs_layers_p_45 | partitions | 6104 kB | 23 MB
gc_blobs_layers_p_46 | partitions | 6032 kB | 23 MB
gc_blobs_layers_p_47 | partitions | 6216 kB | 24 MB
gc_blobs_layers_p_48 | partitions | 5896 kB | 22 MB
gc_blobs_layers_p_49 | partitions | 6048 kB | 23 MB
gc_blobs_layers_p_5 | partitions | 6056 kB | 23 MB
gc_blobs_layers_p_50 | partitions | 5896 kB | 23 MB
gc_blobs_layers_p_51 | partitions | 5952 kB | 23 MB
gc_blobs_layers_p_52 | partitions | 6128 kB | 23 MB
gc_blobs_layers_p_53 | partitions | 5936 kB | 23 MB
gc_blobs_layers_p_54 | partitions | 6048 kB | 23 MB
gc_blobs_layers_p_55 | partitions | 5992 kB | 23 MB
gc_blobs_layers_p_56 | partitions | 6168 kB | 24 MB
gc_blobs_layers_p_57 | partitions | 5888 kB | 22 MB
gc_blobs_layers_p_58 | partitions | 6136 kB | 23 MB
gc_blobs_layers_p_59 | partitions | 5984 kB | 23 MB
gc_blobs_layers_p_6 | partitions | 5968 kB | 23 MB
gc_blobs_layers_p_60 | partitions | 6056 kB | 23 MB
gc_blobs_layers_p_61 | partitions | 6000 kB | 23 MB
gc_blobs_layers_p_62 | partitions | 6072 kB | 23 MB
gc_blobs_layers_p_63 | partitions | 5952 kB | 23 MB
gc_blobs_layers_p_7 | partitions | 6184 kB | 24 MB
gc_blobs_layers_p_8 | partitions | 5992 kB | 23 MB
gc_blobs_layers_p_9 | partitions | 6040 kB | 23 MB
gc_manifest_review_queue | public | 11 MB | 28 MB
gc_review_after_defaults | public | 8192 bytes | 32 kB
gc_tmp_blobs_manifests | public | 12 MB | 25 MB
layers | public | 0 bytes | 0 bytes
layers_p_0 | partitions | 7920 kB | 33 MB
layers_p_1 | partitions | 7072 kB | 30 MB
layers_p_10 | partitions | 0 bytes | 56 kB
layers_p_11 | partitions | 11 MB | 47 MB
layers_p_12 | partitions | 4664 kB | 20 MB
layers_p_13 | partitions | 1008 kB | 4456 kB
layers_p_14 | partitions | 11 MB | 49 MB
layers_p_15 | partitions | 12 MB | 49 MB
layers_p_16 | partitions | 16 MB | 68 MB
layers_p_17 | partitions | 4648 kB | 20 MB
layers_p_18 | partitions | 4408 kB | 18 MB
layers_p_19 | partitions | 2136 kB | 9320 kB
layers_p_2 | partitions | 16 MB | 71 MB
layers_p_20 | partitions | 6512 kB | 28 MB
layers_p_21 | partitions | 11 MB | 46 MB
layers_p_22 | partitions | 8192 bytes | 112 kB
layers_p_23 | partitions | 7040 kB | 30 MB
layers_p_24 | partitions | 7832 kB | 33 MB
layers_p_25 | partitions | 4864 kB | 20 MB
layers_p_26 | partitions | 7176 kB | 30 MB
layers_p_27 | partitions | 8192 bytes | 112 kB
layers_p_28 | partitions | 2360 kB | 10224 kB
layers_p_29 | partitions | 5416 kB | 23 MB
layers_p_3 | partitions | 4032 kB | 17 MB
layers_p_30 | partitions | 25 MB | 108 MB
layers_p_31 | partitions | 8176 kB | 34 MB
layers_p_32 | partitions | 13 MB | 57 MB
layers_p_33 | partitions | 12 MB | 52 MB
layers_p_34 | partitions | 6416 kB | 27 MB
layers_p_35 | partitions | 35 MB | 150 MB
layers_p_36 | partitions | 2520 kB | 11 MB
layers_p_37 | partitions | 8640 kB | 36 MB
layers_p_38 | partitions | 4600 kB | 19 MB
layers_p_39 | partitions | 0 bytes | 56 kB
layers_p_4 | partitions | 1000 kB | 4352 kB
layers_p_40 | partitions | 10 MB | 44 MB
layers_p_41 | partitions | 0 bytes | 56 kB
layers_p_42 | partitions | 16 MB | 68 MB
layers_p_43 | partitions | 17 MB | 74 MB
layers_p_44 | partitions | 2592 kB | 11 MB
layers_p_45 | partitions | 0 bytes | 56 kB
layers_p_46 | partitions | 3200 kB | 14 MB
layers_p_47 | partitions | 8312 kB | 35 MB
layers_p_48 | partitions | 0 bytes | 56 kB
layers_p_49 | partitions | 9104 kB | 38 MB
layers_p_5 | partitions | 8936 kB | 37 MB
layers_p_50 | partitions | 1040 kB | 4512 kB
layers_p_51 | partitions | 5216 kB | 22 MB
layers_p_52 | partitions | 18 MB | 78 MB
layers_p_53 | partitions | 18 MB | 78 MB
layers_p_54 | partitions | 0 bytes | 56 kB
layers_p_55 | partitions | 3576 kB | 15 MB
layers_p_56 | partitions | 8192 bytes | 112 kB
layers_p_57 | partitions | 5448 kB | 23 MB
layers_p_58 | partitions | 1464 kB | 6384 kB
layers_p_59 | partitions | 1256 kB | 5544 kB
layers_p_6 | partitions | 0 bytes | 56 kB
layers_p_60 | partitions | 2808 kB | 12 MB
layers_p_61 | partitions | 0 bytes | 56 kB
layers_p_62 | partitions | 0 bytes | 56 kB
layers_p_63 | partitions | 8104 kB | 34 MB
layers_p_7 | partitions | 14 MB | 59 MB
layers_p_8 | partitions | 11 MB | 47 MB
layers_p_9 | partitions | 2992 kB | 13 MB
manifest_references | public | 0 bytes | 0 bytes
manifest_references_p_0 | partitions | 0 bytes | 32 kB
manifest_references_p_1 | partitions | 0 bytes | 32 kB
manifest_references_p_10 | partitions | 0 bytes | 32 kB
manifest_references_p_11 | partitions | 0 bytes | 32 kB
manifest_references_p_12 | partitions | 0 bytes | 32 kB
manifest_references_p_13 | partitions | 0 bytes | 32 kB
manifest_references_p_14 | partitions | 0 bytes | 32 kB
manifest_references_p_15 | partitions | 0 bytes | 32 kB
manifest_references_p_16 | partitions | 0 bytes | 32 kB
manifest_references_p_17 | partitions | 0 bytes | 32 kB
manifest_references_p_18 | partitions | 0 bytes | 32 kB
manifest_references_p_19 | partitions | 0 bytes | 32 kB
manifest_references_p_2 | partitions | 0 bytes | 32 kB
manifest_references_p_20 | partitions | 0 bytes | 32 kB
manifest_references_p_21 | partitions | 0 bytes | 32 kB
manifest_references_p_22 | partitions | 0 bytes | 32 kB
manifest_references_p_23 | partitions | 0 bytes | 32 kB
manifest_references_p_24 | partitions | 0 bytes | 32 kB
manifest_references_p_25 | partitions | 0 bytes | 32 kB
manifest_references_p_26 | partitions | 0 bytes | 32 kB
manifest_references_p_27 | partitions | 0 bytes | 32 kB
manifest_references_p_28 | partitions | 0 bytes | 32 kB
manifest_references_p_29 | partitions | 0 bytes | 32 kB
manifest_references_p_3 | partitions | 0 bytes | 32 kB
manifest_references_p_30 | partitions | 0 bytes | 32 kB
manifest_references_p_31 | partitions | 0 bytes | 32 kB
manifest_references_p_32 | partitions | 0 bytes | 32 kB
manifest_references_p_33 | partitions | 0 bytes | 32 kB
manifest_references_p_34 | partitions | 0 bytes | 32 kB
manifest_references_p_35 | partitions | 0 bytes | 32 kB
manifest_references_p_36 | partitions | 0 bytes | 32 kB
manifest_references_p_37 | partitions | 0 bytes | 32 kB
manifest_references_p_38 | partitions | 0 bytes | 32 kB
manifest_references_p_39 | partitions | 0 bytes | 32 kB
manifest_references_p_4 | partitions | 0 bytes | 32 kB
manifest_references_p_40 | partitions | 0 bytes | 32 kB
manifest_references_p_41 | partitions | 0 bytes | 32 kB
manifest_references_p_42 | partitions | 0 bytes | 32 kB
manifest_references_p_43 | partitions | 0 bytes | 32 kB
manifest_references_p_44 | partitions | 0 bytes | 32 kB
manifest_references_p_45 | partitions | 0 bytes | 32 kB
manifest_references_p_46 | partitions | 0 bytes | 32 kB
manifest_references_p_47 | partitions | 0 bytes | 32 kB
manifest_references_p_48 | partitions | 0 bytes | 32 kB
manifest_references_p_49 | partitions | 0 bytes | 32 kB
manifest_references_p_5 | partitions | 0 bytes | 32 kB
manifest_references_p_50 | partitions | 0 bytes | 32 kB
manifest_references_p_51 | partitions | 0 bytes | 32 kB
manifest_references_p_52 | partitions | 0 bytes | 32 kB
manifest_references_p_53 | partitions | 0 bytes | 32 kB
manifest_references_p_54 | partitions | 0 bytes | 32 kB
manifest_references_p_55 | partitions | 0 bytes | 32 kB
manifest_references_p_56 | partitions | 0 bytes | 32 kB
manifest_references_p_57 | partitions | 0 bytes | 32 kB
manifest_references_p_58 | partitions | 0 bytes | 32 kB
manifest_references_p_59 | partitions | 0 bytes | 32 kB
manifest_references_p_6 | partitions | 0 bytes | 32 kB
manifest_references_p_60 | partitions | 0 bytes | 32 kB
manifest_references_p_61 | partitions | 0 bytes | 32 kB
manifest_references_p_62 | partitions | 0 bytes | 32 kB
manifest_references_p_63 | partitions | 0 bytes | 32 kB
manifest_references_p_7 | partitions | 0 bytes | 32 kB
manifest_references_p_8 | partitions | 0 bytes | 32 kB
manifest_references_p_9 | partitions | 0 bytes | 32 kB
manifests | public | 0 bytes | 0 bytes
manifests_p_0 | partitions | 2816 kB | 16 MB
manifests_p_1 | partitions | 2400 kB | 13 MB
manifests_p_10 | partitions | 0 bytes | 64 kB
manifests_p_11 | partitions | 4184 kB | 22 MB
manifests_p_12 | partitions | 1696 kB | 9216 kB
manifests_p_13 | partitions | 360 kB | 2112 kB
manifests_p_14 | partitions | 4232 kB | 23 MB
manifests_p_15 | partitions | 4344 kB | 23 MB
manifests_p_16 | partitions | 5920 kB | 31 MB
manifests_p_17 | partitions | 1760 kB | 9416 kB
manifests_p_18 | partitions | 1496 kB | 8784 kB
manifests_p_19 | partitions | 744 kB | 4408 kB
manifests_p_2 | partitions | 5960 kB | 33 MB
manifests_p_20 | partitions | 2328 kB | 13 MB
manifests_p_21 | partitions | 4112 kB | 22 MB
manifests_p_22 | partitions | 8192 bytes | 128 kB
manifests_p_23 | partitions | 2712 kB | 14 MB
manifests_p_24 | partitions | 2840 kB | 15 MB
manifests_p_25 | partitions | 1736 kB | 9688 kB
manifests_p_26 | partitions | 2560 kB | 14 MB
manifests_p_27 | partitions | 8192 bytes | 144 kB
manifests_p_28 | partitions | 872 kB | 4856 kB
manifests_p_29 | partitions | 1944 kB | 11 MB
manifests_p_3 | partitions | 1432 kB | 8280 kB
manifests_p_30 | partitions | 38 MB | 121 MB
manifests_p_31 | partitions | 2848 kB | 16 MB
manifests_p_32 | partitions | 4728 kB | 26 MB
manifests_p_33 | partitions | 4520 kB | 24 MB
manifests_p_34 | partitions | 2296 kB | 12 MB
manifests_p_35 | partitions | 13 MB | 69 MB
manifests_p_36 | partitions | 2296 kB | 7368 kB
manifests_p_37 | partitions | 3088 kB | 17 MB
manifests_p_38 | partitions | 1624 kB | 9304 kB
manifests_p_39 | partitions | 0 bytes | 64 kB
manifests_p_4 | partitions | 336 kB | 2088 kB
manifests_p_40 | partitions | 3760 kB | 20 MB
manifests_p_41 | partitions | 0 bytes | 64 kB
manifests_p_42 | partitions | 5776 kB | 31 MB
manifests_p_43 | partitions | 6400 kB | 34 MB
manifests_p_44 | partitions | 912 kB | 5256 kB
manifests_p_45 | partitions | 0 bytes | 64 kB
manifests_p_46 | partitions | 1184 kB | 6496 kB
manifests_p_47 | partitions | 2968 kB | 16 MB
manifests_p_48 | partitions | 0 bytes | 64 kB
manifests_p_49 | partitions | 3272 kB | 18 MB
manifests_p_5 | partitions | 3200 kB | 17 MB
manifests_p_50 | partitions | 384 kB | 2256 kB
manifests_p_51 | partitions | 1888 kB | 10 MB
manifests_p_52 | partitions | 6648 kB | 36 MB
manifests_p_53 | partitions | 6832 kB | 36 MB
manifests_p_54 | partitions | 0 bytes | 64 kB
manifests_p_55 | partitions | 1248 kB | 7144 kB
manifests_p_56 | partitions | 8192 bytes | 144 kB
manifests_p_57 | partitions | 2016 kB | 11 MB
manifests_p_58 | partitions | 544 kB | 3136 kB
manifests_p_59 | partitions | 456 kB | 2776 kB
manifests_p_6 | partitions | 0 bytes | 64 kB
manifests_p_60 | partitions | 1080 kB | 5896 kB
manifests_p_61 | partitions | 0 bytes | 64 kB
manifests_p_62 | partitions | 0 bytes | 64 kB
manifests_p_63 | partitions | 2976 kB | 16 MB
manifests_p_7 | partitions | 5040 kB | 27 MB
manifests_p_8 | partitions | 4056 kB | 22 MB
manifests_p_9 | partitions | 1144 kB | 6208 kB
media_types | public | 8192 bytes | 48 kB
namespaces | public | 16 kB | 80 kB
repositories | public | 0 bytes | 0 bytes
repositories_p_0 | partitions | 8192 bytes | 80 kB
repositories_p_1 | partitions | 8192 bytes | 80 kB
repositories_p_10 | partitions | 0 bytes | 40 kB
repositories_p_11 | partitions | 16 kB | 112 kB
repositories_p_12 | partitions | 8192 bytes | 80 kB
repositories_p_13 | partitions | 8192 bytes | 80 kB
repositories_p_14 | partitions | 16 kB | 112 kB
repositories_p_15 | partitions | 16 kB | 112 kB
repositories_p_16 | partitions | 16 kB | 128 kB
repositories_p_17 | partitions | 8192 bytes | 80 kB
repositories_p_18 | partitions | 8192 bytes | 80 kB
repositories_p_19 | partitions | 8192 bytes | 80 kB
repositories_p_2 | partitions | 16 kB | 112 kB
repositories_p_20 | partitions | 8192 bytes | 80 kB
repositories_p_21 | partitions | 16 kB | 128 kB
repositories_p_22 | partitions | 8192 bytes | 80 kB
repositories_p_23 | partitions | 8192 bytes | 80 kB
repositories_p_24 | partitions | 8192 bytes | 80 kB
repositories_p_25 | partitions | 8192 bytes | 80 kB
repositories_p_26 | partitions | 8192 bytes | 80 kB
repositories_p_27 | partitions | 8192 bytes | 80 kB
repositories_p_28 | partitions | 8192 bytes | 80 kB
repositories_p_29 | partitions | 8192 bytes | 80 kB
repositories_p_3 | partitions | 8192 bytes | 80 kB
repositories_p_30 | partitions | 16 kB | 112 kB
repositories_p_31 | partitions | 8192 bytes | 80 kB
repositories_p_32 | partitions | 16 kB | 112 kB
repositories_p_33 | partitions | 16 kB | 112 kB
repositories_p_34 | partitions | 8192 bytes | 80 kB
repositories_p_35 | partitions | 32 kB | 152 kB
repositories_p_36 | partitions | 16 kB | 112 kB
repositories_p_37 | partitions | 8192 bytes | 80 kB
repositories_p_38 | partitions | 8192 bytes | 80 kB
repositories_p_39 | partitions | 0 bytes | 40 kB
repositories_p_4 | partitions | 8192 bytes | 80 kB
repositories_p_40 | partitions | 16 kB | 112 kB
repositories_p_41 | partitions | 0 bytes | 40 kB
repositories_p_42 | partitions | 16 kB | 112 kB
repositories_p_43 | partitions | 16 kB | 112 kB
repositories_p_44 | partitions | 8192 bytes | 80 kB
repositories_p_45 | partitions | 0 bytes | 40 kB
repositories_p_46 | partitions | 8192 bytes | 80 kB
repositories_p_47 | partitions | 8192 bytes | 80 kB
repositories_p_48 | partitions | 0 bytes | 40 kB
repositories_p_49 | partitions | 8192 bytes | 80 kB
repositories_p_5 | partitions | 8192 bytes | 80 kB
repositories_p_50 | partitions | 8192 bytes | 80 kB
repositories_p_51 | partitions | 8192 bytes | 80 kB
repositories_p_52 | partitions | 24 kB | 136 kB
repositories_p_53 | partitions | 24 kB | 136 kB
repositories_p_54 | partitions | 0 bytes | 40 kB
repositories_p_55 | partitions | 8192 bytes | 80 kB
repositories_p_56 | partitions | 8192 bytes | 80 kB
repositories_p_57 | partitions | 8192 bytes | 80 kB
repositories_p_58 | partitions | 8192 bytes | 80 kB
repositories_p_59 | partitions | 8192 bytes | 80 kB
repositories_p_6 | partitions | 0 bytes | 40 kB
repositories_p_60 | partitions | 8192 bytes | 80 kB
repositories_p_61 | partitions | 0 bytes | 40 kB
repositories_p_62 | partitions | 0 bytes | 40 kB
repositories_p_63 | partitions | 8192 bytes | 80 kB
repositories_p_7 | partitions | 16 kB | 112 kB
repositories_p_8 | partitions | 16 kB | 112 kB
repositories_p_9 | partitions | 8192 bytes | 80 kB
repository_blobs | public | 0 bytes | 0 bytes
repository_blobs_p_0 | partitions | 4600 kB | 16 MB
repository_blobs_p_1 | partitions | 4184 kB | 14 MB
repository_blobs_p_10 | partitions | 0 bytes | 40 kB
repository_blobs_p_11 | partitions | 6680 kB | 23 MB
repository_blobs_p_12 | partitions | 2704 kB | 9568 kB
repository_blobs_p_13 | partitions | 592 kB | 2208 kB
repository_blobs_p_14 | partitions | 6896 kB | 24 MB
repository_blobs_p_15 | partitions | 6936 kB | 24 MB
repository_blobs_p_16 | partitions | 9664 kB | 33 MB
repository_blobs_p_17 | partitions | 2776 kB | 9896 kB
repository_blobs_p_18 | partitions | 2576 kB | 9280 kB
repository_blobs_p_19 | partitions | 1280 kB | 4616 kB
repository_blobs_p_2 | partitions | 9936 kB | 34 MB
repository_blobs_p_20 | partitions | 3840 kB | 13 MB
repository_blobs_p_21 | partitions | 6488 kB | 22 MB
repository_blobs_p_22 | partitions | 8192 bytes | 80 kB
repository_blobs_p_23 | partitions | 4216 kB | 14 MB
repository_blobs_p_24 | partitions | 4640 kB | 16 MB
repository_blobs_p_25 | partitions | 2864 kB | 10096 kB
repository_blobs_p_26 | partitions | 4208 kB | 15 MB
repository_blobs_p_27 | partitions | 8192 bytes | 80 kB
repository_blobs_p_28 | partitions | 1416 kB | 5104 kB
repository_blobs_p_29 | partitions | 3224 kB | 11 MB
repository_blobs_p_3 | partitions | 2376 kB | 8432 kB
repository_blobs_p_30 | partitions | 16 MB | 53 MB
repository_blobs_p_31 | partitions | 4816 kB | 17 MB
repository_blobs_p_32 | partitions | 7952 kB | 27 MB
repository_blobs_p_33 | partitions | 7280 kB | 25 MB
repository_blobs_p_34 | partitions | 3768 kB | 13 MB
repository_blobs_p_35 | partitions | 21 MB | 72 MB
repository_blobs_p_36 | partitions | 1592 kB | 5384 kB
repository_blobs_p_37 | partitions | 5040 kB | 18 MB
repository_blobs_p_38 | partitions | 2712 kB | 9568 kB
repository_blobs_p_39 | partitions | 0 bytes | 40 kB
repository_blobs_p_4 | partitions | 600 kB | 2168 kB
repository_blobs_p_40 | partitions | 6192 kB | 21 MB
repository_blobs_p_41 | partitions | 0 bytes | 40 kB
repository_blobs_p_42 | partitions | 9600 kB | 33 MB
repository_blobs_p_43 | partitions | 10 MB | 36 MB
repository_blobs_p_44 | partitions | 1504 kB | 5320 kB
repository_blobs_p_45 | partitions | 0 bytes | 40 kB
repository_blobs_p_46 | partitions | 1904 kB | 6808 kB
repository_blobs_p_47 | partitions | 4920 kB | 17 MB
repository_blobs_p_48 | partitions | 0 bytes | 40 kB
repository_blobs_p_49 | partitions | 5336 kB | 18 MB
repository_blobs_p_5 | partitions | 5312 kB | 18 MB
repository_blobs_p_50 | partitions | 624 kB | 2320 kB
repository_blobs_p_51 | partitions | 3048 kB | 10 MB
repository_blobs_p_52 | partitions | 11 MB | 38 MB
repository_blobs_p_53 | partitions | 11 MB | 38 MB
repository_blobs_p_54 | partitions | 0 bytes | 40 kB
repository_blobs_p_55 | partitions | 2104 kB | 7368 kB
repository_blobs_p_56 | partitions | 8192 bytes | 80 kB
repository_blobs_p_57 | partitions | 3168 kB | 11 MB
repository_blobs_p_58 | partitions | 872 kB | 3040 kB
repository_blobs_p_59 | partitions | 752 kB | 2696 kB
repository_blobs_p_6 | partitions | 0 bytes | 40 kB
repository_blobs_p_60 | partitions | 1632 kB | 5824 kB
repository_blobs_p_61 | partitions | 0 bytes | 40 kB
repository_blobs_p_62 | partitions | 0 bytes | 40 kB
repository_blobs_p_63 | partitions | 4760 kB | 16 MB
repository_blobs_p_7 | partitions | 8296 kB | 28 MB
repository_blobs_p_8 | partitions | 6624 kB | 23 MB
repository_blobs_p_9 | partitions | 1752 kB | 6112 kB
schema_migrations | public | 8192 bytes | 32 kB
tags | public | 0 bytes | 0 bytes
tags_p_0 | partitions | 360 kB | 1064 kB
tags_p_1 | partitions | 320 kB | 944 kB
tags_p_10 | partitions | 0 bytes | 32 kB
tags_p_11 | partitions | 512 kB | 1472 kB
tags_p_12 | partitions | 208 kB | 640 kB
tags_p_13 | partitions | 48 kB | 216 kB
tags_p_14 | partitions | 528 kB | 1512 kB
tags_p_15 | partitions | 544 kB | 1544 kB
tags_p_16 | partitions | 736 kB | 2056 kB
tags_p_17 | partitions | 216 kB | 656 kB
tags_p_18 | partitions | 200 kB | 608 kB
tags_p_19 | partitions | 96 kB | 344 kB
tags_p_2 | partitions | 768 kB | 2160 kB
tags_p_20 | partitions | 296 kB | 848 kB
tags_p_21 | partitions | 512 kB | 1456 kB
tags_p_22 | partitions | 8192 bytes | 64 kB
tags_p_23 | partitions | 328 kB | 968 kB
tags_p_24 | partitions | 360 kB | 1032 kB
tags_p_25 | partitions | 224 kB | 664 kB
tags_p_26 | partitions | 328 kB | 960 kB
tags_p_27 | partitions | 8192 bytes | 64 kB
tags_p_28 | partitions | 112 kB | 360 kB
tags_p_29 | partitions | 256 kB | 776 kB
tags_p_3 | partitions | 192 kB | 600 kB
tags_p_30 | partitions | 3520 kB | 8776 kB
tags_p_31 | partitions | 592 kB | 1656 kB
tags_p_32 | partitions | 608 kB | 1752 kB
tags_p_33 | partitions | 560 kB | 1568 kB
tags_p_34 | partitions | 288 kB | 848 kB
tags_p_35 | partitions | 1616 kB | 4464 kB
tags_p_36 | partitions | 184 kB | 560 kB
tags_p_37 | partitions | 392 kB | 1136 kB
tags_p_38 | partitions | 216 kB | 656 kB
tags_p_39 | partitions | 0 bytes | 32 kB
tags_p_4 | partitions | 48 kB | 216 kB
tags_p_40 | partitions | 480 kB | 1360 kB
tags_p_41 | partitions | 0 bytes | 32 kB
tags_p_42 | partitions | 728 kB | 2040 kB
tags_p_43 | partitions | 792 kB | 2208 kB
tags_p_44 | partitions | 120 kB | 400 kB
tags_p_45 | partitions | 0 bytes | 32 kB
tags_p_46 | partitions | 152 kB | 488 kB
tags_p_47 | partitions | 376 kB | 1112 kB
tags_p_48 | partitions | 0 bytes | 32 kB
tags_p_49 | partitions | 416 kB | 1216 kB
tags_p_5 | partitions | 408 kB | 1192 kB
tags_p_50 | partitions | 48 kB | 216 kB
tags_p_51 | partitions | 240 kB | 728 kB
tags_p_52 | partitions | 848 kB | 2360 kB
tags_p_53 | partitions | 848 kB | 2352 kB
tags_p_54 | partitions | 0 bytes | 32 kB
tags_p_55 | partitions | 160 kB | 504 kB
tags_p_56 | partitions | 8192 bytes | 64 kB
tags_p_57 | partitions | 248 kB | 768 kB
tags_p_58 | partitions | 72 kB | 272 kB
tags_p_59 | partitions | 64 kB | 232 kB
tags_p_6 | partitions | 0 bytes | 32 kB
tags_p_60 | partitions | 128 kB | 416 kB
tags_p_61 | partitions | 0 bytes | 32 kB
tags_p_62 | partitions | 0 bytes | 32 kB
tags_p_63 | partitions | 368 kB | 1072 kB
tags_p_7 | partitions | 632 kB | 1760 kB
tags_p_8 | partitions | 512 kB | 1456 kB
tags_p_9 | partitions | 136 kB | 472 kBAverage and total partition size per table
Query
SELECT
substring(c.relname, 0, position('_p_' IN c.relname)) AS "parent table name",
pg_size_pretty(avg(pg_relation_size(c.oid))) AS "avg partition relation size",
pg_size_pretty(avg(pg_total_relation_size(c.oid))) AS "avg partition total size",
pg_size_pretty(sum(pg_relation_size(c.oid))) AS "sum partition relation size",
pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS "sum partition total size"
FROM
pg_class AS c
LEFT JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
LEFT JOIN pg_stat_user_tables AS s ON (s.relname = c.relname)
WHERE
n.nspname = 'partitions'
AND c.relkind = 'r'
GROUP BY
"parent table name" ORDER BY "sum partition total size" DESC;| parent table name | avg partition relation size | avg partition total size | sum partition relation size | sum partition total size |
|---|---|---|---|---|
layers |
6967 kB | 29 MB | 435 MB | 1872 MB |
gc_blobs_layers |
6022 kB | 23 MB | 376 MB | 1470 MB |
manifests |
2989 kB | 15 MB | 187 MB | 943 MB |
repository_blobs |
4122 kB | 14 MB | 258 MB | 905 MB |
blobs |
3423 kB | 7665 kB | 214 MB | 479 MB |
gc_blobs_configurations |
262 kB | 1028 kB | 16 MB | 64 MB |
tags |
359 kB | 1025 kB | 22 MB | 64 MB |
repositories |
9856 bytes | 85 kB | 616 kB | 5456 kB |
manifest_references |
0 bytes | 32 kB | 0 bytes | 2048 kB |
Average and total partition row count per table
Query
SELECT
substring(c.relname, 0, position('_p_' IN c.relname)) AS "parent table name",
round(avg(s.n_live_tup)) AS "avg partition row count",
sum(s.n_live_tup) AS "sum partition row count"
FROM
pg_class AS c
LEFT JOIN pg_namespace AS n ON (n.oid = c.relnamespace)
LEFT JOIN pg_stat_user_tables AS s ON (s.relname = c.relname)
WHERE
n.nspname = 'partitions'
AND c.relkind = 'r'
GROUP BY
"parent table name" ORDER BY "sum partition row count" DESC;| parent table name | avg partition row count | sum partition row count |
|---|---|---|
gc_blobs_layers |
60937 | 3899959 |
layers |
60937 | 3899959 |
repository_blobs |
41698 | 2668695 |
blobs |
41452 | 2652935 |
tags |
3837 | 245567 |
manifests |
2616 | 167442 |
gc_blobs_configurations |
2616 | 167439 |
repositories |
49 | 3114 |
manifest_references |
0 | 0 |
Total database size
Query
SELECT pg_size_pretty(pg_database_size(current_database()));6.35 GB
Results
Query plans were included as comments in the diff for all queries used by the API and the online garbage collector.
Edited by João Pereira