SuffolkLITLab / EfileProxyServer

A proxy server that acts as an Efiling Service Provider, connecting guided interviews to Efiling Managers on Tyler Technologies implementation of ECF 4.0
https://suffolklitlab.org/docassemble-AssemblyLine-documentation/docs/efiling/overview/
MIT License
3 stars 2 forks source link

Split OptionalServiceCode table into 2 tables to save space #99

Closed BryceStevenWilley closed 1 year ago

BryceStevenWilley commented 2 years ago

The optional service codes are by far the largest tables, and now that we are running 3 (soon to be 6) different domains (jurisidiction and stage / test), it's starting to take up a lot of space. The issue is that for each individual optional service, there are 1000's of filing codes, and each service / filing code combination takes up another row and repeats the whole name / all the other data points. We need to split up the table into two to reduce the extra disk usage (we're at 7.3GB for 2.5 domains), and should also improve performance too.

The key will be to figure out a way to decouple the exact codes zip we get from Tyler into the two different tables, without changing how we handle all the tables too much.

BryceStevenWilley commented 2 years ago

Did a little more digging here:

Splitting optional services gets us down to ~2.4GB + index, and shirking some additional columns that don't need to be text gets us to 1.98GB + index.

For the filing and filing component tables, shrinking the other columns reduces disk usage by 181MB and 116MB respectively.

Will need to take some dumps and example tables, this is a much bigger migration than previous DB schema upgrades. It shouldn't be critical right now, but it will be very soon.

BryceStevenWilley commented 1 year ago

This was helpful, but not as much as it should have been. Still, the issue is done for now.