Our PostgreSQL storage currently stores snapshots in three tables:
- snapshot (id, internal id)
- snapshot_branch (internal id, name, target, target type)
- snapshot_branches (snapshot internal id, snapshot_branch internal id)
For large snapshots, this makes paginated queries (depending on the branch name) very inefficient: PostgreSQL has to fetch all branches and sort them out before outputting results.
We should experiment with a model equivalent to that of Cassandra, with one snapshot table and an inline snapshot_branches table that we can index using (snapshot id, branch name).