Only keep four months of raw filings data in PG

[PaulClark2]

What we're after: We need to develop an overall strategy to improve raw filing performance

Ideas:

1. Reduce volume of data: We only need to keep a certain amount of raw filing data beyond a certain point. We need to get a clarification on what that time period is exactly. Once we determine how far back we need to go, we should clear out unneeded raw filing data.
2. Add indexes

Raw vs. processed filters documentation: 🔒 https://docs.google.com/document/d/16DWIquTYpbdtCCDrXYLXXRrHQXS51sX_8cLY75spLA0/ 🔒

Related issues:
#3627
#3636

Things to consider:

• How much raw filings data should we keep in API
• Make a new ticket to check if the database performance for efiling improve after removing legacy data. Include a list of datatables that display raw data.

[AmyKort]

@PaulClark2 and I met to discuss. Paul will reach out to the Press Office and Public Records to confirm we aren't aware of situations where users would look at raw filings that are more than one year old.

[PaulClark2]

We only need to keep three months of raw electronic data.

To do:

• ask Salient to modify their upload process to include deleting data older than three months
• will this cause short-term problems for the read-replicas

[PaulClark2]

Discussed this with the Press Office. They are good with keeping as little as 3 months of electronic filing data.

I'm going to move this ticket to 9.6 so that EFO and the DB team can work on it after the mid-year filing deadline.

[lbeaufort]

Thanks @paul! Re: read-replica issues, we could possibly do this off-hours to minimize performance impact.

[lbeaufort]

From @rjayasekera

Today I talked with Mahi about deleting eFile/pFile records prior to 5/1/2019 from AWS-DEV instance. He is currently busy with processing large amount of ActBlue amendments (about 25 mil records). He can get to this task after that, probably 1st or 2nd week of August.

[pkfec]

@paul will follow up with Salient team

[PaulClark2]

Salient will start working on this Tuesday, 9/3

[PaulClark2]

Salient is working on this.

[PaulClark2]

Mahi and Rohan will start testing the delete process on DEV. We'll delete 5,000 reports per day during this test.

[patphongs]

Still troubleshooting deletion process for all raw filings greater than 4 months. Moving to 10.2 to be completed then.

[patphongs]

Right now we have 9 months of data for raw filing. We would like to delete and only keep 4 months of data.

[PaulClark2]

Closing in favor of #4004