Separate contributors table into two if it is updated in two halves to reduce contention for it

Note

Migrated from augurlabs/augur#3363
Originally opened by @MoralCode on 2025-10-31

By my understanding the contributors table is updated in essentially two parts: half is updated by tasks that fetch data from github (core), and the other half is updated by data from git (facade).

when only half is updated, the other half of the data is null.

This is not a great database design, especially given that this table in particular is already very highly contested (#122).

I think this table should be split into two because each of the processes seems to only deal with a separate, only slightly overlapping, set of data anyway - a relationship that may be better served by a foreign key.

Note

Migrated comment from original
Originally posted by @MoralCode on 2025-10-31

the only challenge I see with this is how the migration will work. To do the migration without data loss would require at least making a copy of half of the table into a new table before deleting the data. This would mean disk space for somewhere between a half and a whole copy of that table would be needed.

In my experience this table is one of the largest in Augur. so that may be... interesting

Note

Migrated comment from original
Originally posted by @PredictiveManish on 2025-11-01

I am going to give a push to this!

@MoralCode

Note

Migrated comment from original
Originally posted by @PredictiveManish on 2025-11-01

Thanks @MoralCode for the clear explanation.
I agree that the current contributors table design causes both data inconsistency (partial nulls) and contention between the core and facade tasks.

Splitting it into two logical tables - one for Git data and one for GitHub data linked through a foreign key - sounds like a cleaner long-term solution.

The migration challenge makes sense, especially given how large the current table is. Would it make sense to explore a staged approach, for example:

- Creating the new tables alongside the existing one

- Backfilling data gradually (perhaps repository by repository)

- Then switching references once consistency checks pass?

Also, given that Git and GitHub contributors overlap only partially, it might be helpful to define a matching strategy early (e.g., by email or name normalization) to minimize orphan rows when we join the two tables later.

For migration, if there’s a staging DB or test dataset available, I can try running a small-scale migration locally to estimate the space overhead.

Would love to know more about this!

Note

Migrated comment from original
Originally posted by @MoralCode on 2025-11-01

I think a phased approach like that would probably end up being a little bit worse, because you'd still have the problem of temporarily raising how much data is needed to store duplicate copies of information, but you'd be doing it over a longer period of time, requiring people with large instances to make more disk space available for longer.

I suspect this may not be as big of an issue in general as I'm thinking because larger instances will probably have more headroom or more ability to just buy more storage in AWS or something. And smaller instances probably won't have enough data to migrate to have it really take up that much more space than a Augur collection run would normally take.

I'd also worry about the risk of starting a big migration like this and then it never getting finished and ending up with duplicate tables for reasons only known to people who've been on the project for a very long time.

@MoralCode

Note

Migrated comment from original
Originally posted by @guptapratykshh on 2026-01-10

I have analyzed issue and completely agree with @MoralCode. The phased approach would create more problems than it solves.

Proposed Solution: One-shot migration to split contributors into two tables:

contributors- core identity (email, name, login, etc.)
contributor_platform_data - platform-specific fields (all gh_* and gl_* columns)

This approach completes in 2-6 hours versus weeks of elevated disk usage, zero risk of incomplete migration creating permanent technical debt, atomic operation(either succeeds completely or rolls back cleanly) and no code complexity supporting dual schemas

Eliminates table contention between github and facade tasks, no more partial null data when only one process runs, cleaner architecture, easier to add new platforms

I have prepared implementation docs, including the complete Alembic migration script, testing strategy, and deployment guide. happy to share if helpful.

Note

Migrated comment from original
Originally posted by @MoralCode on 2026-01-12

As much as that planning is helpful, I think this is large enough of a core change that one of the core maintainers should probably do that process themselves

I'm also worried about how much extra disk would be needed to make this change for users with large databases.

Note

Migrated comment from original
Originally posted by @MoralCode on 2026-03-10

closing . this is a very aggressive change that i think may be better suited to handle some other way, the table will also get smaller as platform support is added and we can reevaluate after some of these collection processes have had a review

all of the data in the gitlab columns, at least for our instance (40k repos) is not present:

SELECT * FROM augur_data.contributors
        WHERE gl_username is NOT null
union ALL
SELECT * FROM augur_data.contributors
        WHERE gl_username is NOT null  
union ALL 
SELECT * FROM augur_data.contributors
        WHERE gl_web_url is NOT null
union ALL
SELECT * FROM augur_data.contributors
        WHERE gl_avatar_url is NOT null
union ALL
SELECT * FROM augur_data.contributors
        WHERE gl_state is NOT null
union ALL    
SELECT * FROM augur_data.contributors
        WHERE gl_username is NOT null

reopening with slightly different scope

This new scope is essentially redundant with #156 so im putting it back

Navigation Menu

Repository navigation

Separate contributors table into two if it is updated in two halves to reduce contention for it #128

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Separate contributors table into two if it is updated in two halves to reduce contention for it #128

Description

Activity

collectoss-issue-migrator commented on Apr 30, 2026

collectoss-issue-migrator commented on Apr 30, 2026

collectoss-issue-migrator commented on Apr 30, 2026

collectoss-issue-migrator commented on Apr 30, 2026

collectoss-issue-migrator commented on Apr 30, 2026

collectoss-issue-migrator commented on Apr 30, 2026

collectoss-issue-migrator commented on Apr 30, 2026

MoralCode commented on May 5, 2026

MoralCode commented on May 5, 2026

MoralCode commented on Oct 4, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions