I spoke with Legal and it doesn't seem that they would have a problem with deleting these old tables.

It might be worth keeping the last X years of tables just as a failsafe (since compiling them is a pain) but the ones from 2017 and earlier don't need to stick around and create additional debt.

Where in the code are these tables created? I don't recall the core code needing to create separate tables for elections. I know WMF has occasionally created tables (see ./cli/wm-scripts) but those are not for every single election either. And at least the present code (same link) only relates to 2022 and later.

I do see them being removed: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/SecurePoll/+/750803

Obviously, they are still accessible in VCS and can be restored and run again if needed.

Mentioned in SAL (#wikimedia-operations) [2024-01-23T11:11:53Z] <Amir1> dropping pif_edits table from all wikis (T355594)

Mentioned in SAL (#wikimedia-operations) [2024-01-23T11:22:49Z] <Amir1> dropping bv2011_edits table from all wikis (T355594)

Mentioned in SAL (#wikimedia-operations) [2024-01-23T12:02:59Z] <Amir1> dropping bv2009_edits table from all wikis (T355594)

Mentioned in SAL (#wikimedia-operations) [2024-01-23T12:13:35Z] <Amir1> dropping bv2015_edits table from all wikis (T355594)

Now number of securepolls tables have been dropped to 5479. Roughly a 4,000 tables redaction.

In T355594#9479474, @Huji wrote:

Where in the code are these tables created? I don't recall the core code needing to create separate tables for elections. I know WMF has occasionally created tables (see ./cli/wm-scripts) but those are not for every single election either. And at least the present code (same link) only relates to 2022 and later.

Wikitech documentation details the process: https://wikitech.wikimedia.org/wiki/SecurePoll

For a global election we need to ensure voters are eligible no matter which project they made their edits on. Since that could be hundreds of separate projects (in theory), we need to make these tables on every wiki. It is far from an ideal solution of course but that's the reasoning for it right now.

We reduced a bit of maintenance debt, for future devs need to find a better solution to this.

Has a "one set of tables per wiki instead of per election" approach been considered? We could add an election_id column to each table. I think this would be much more scalable in the long run than a separate set of tables for each election.

It's because the columns depends on the election. You might have a different criteria that could not be fit inside the table.

OTOH, it can turn into a json blob instead (depends on the query patterns but I highly doubt you'd need to query anything beside looking up values of a given user)

Or it could be pivoted into a long format (one row for each property, as opposed to one column for each property).

Can we at least prefix each table with securepoll_ or whatever (in future) so they're more visibly grouped together in reports?

In T355594#10207120, @Jdforrester-WMF wrote:

Can we at least prefix each table with securepoll_ or whatever (in future) so they're more visibly grouped together in reports?

We can update the technical docs to advise that; I've boldly done so on Wikitech.

As for the task itself I briefly spoke with @Ladsgroup and I think generally we can just drop these tables after some arbitrary time after the election is over (and the people elected are seated, etc., just in case we need to re-tally). He mentioned a year, and I think that's fine, since we can just re-run the scripts if necessary.

In T355594#10235308, @jrbs wrote:

In T355594#10207120, @Jdforrester-WMF wrote:

Can we at least prefix each table with securepoll_ or whatever (in future) so they're more visibly grouped together in reports?

We can update the technical docs to advise that; I've boldly done so on Wikitech.

Thanks, that'd be great!

As for the task itself I briefly spoke with @Ladsgroup and I think generally we can just drop these tables after some arbitrary time after the election is over (and the people elected are seated, etc., just in case we need to re-tally). He mentioned a year, and I think that's fine, since we can just re-run the scripts if necessary.

Ack. +1

Thank you, I turn on the chainsaw now.