Ask HN: What problem are you close to solving and how can we help?

The system is a bunch of batch jobs that are scheduled to run at different intervals. These jobs can be modelled as an acyclic directed graph of steps. They basically download files from vendors and map the rows inside them into a generic format (for generating reports). There are a lot of vendors and each vendor can have a different file format containing different fields -- hence requiring custom business logic to populate (map) the corresponding generic file (like aggregating fields, fetching values from DB, etc.). Also these vendors' files sometimes contain errors, or are dropped late for download, etc. -- failures can happen and these failed instances of jobs should be able to rerun.

Existing system is built using Spring Batch and Spring Integration. The problems with the existing system are:

1. there are more than 200 jobs and most of them have their own custom logic during mapping -- cannot be generified

2. lot of manual work needed to onboard new vendors

3. jobs are synchronous and run only on one node, typically for lots of hours

4. rerunning jobs is a nightmare

Dream state for this system:

1. Dynamically add jobs to the runtime using generic components that can be reused -- maybe through an API / UI

2. Preferably, multiple records from a single file be processed across distributed nodes to generate a single output generic file

3. Rerunning should be easier

I am a noob to CS. I did a good bit of research for the past month. Found a few data-science tools in Python -- which is a no-no for a production system. Also, I know that the steps cannot be made generic after some extent since custom mapping logic is required for almost every vendor. But asking to see what is possible. Any help to point to prospective tools and technologies to solve the above will be much appreciated.

Thanks

- Change the field type from JSON to JSONB (better storage and the rest) https://www.postgresql.org/docs/13/datatype-json.html

-Learn the json in-build functions and see if one of them can replace one you made ad-hoc

- Seriously, replace json with normal tables for the most common stuff. That alone will speed up things massively. Maybe keep the old json around in case, but remove when it become old(?)

- Use views. Views allow to abstract over your databaee and allow to change internals

- If a big things is searching and that searching is nkind of complex/flexible, add FTS with proper indexing to your json then use it as first filter layer:

    SELECT .. FROM table WHERE id IN (SELECT .. FROM search_table WHERE FTS_query) AND ...other filters

- If your query do heavy calculations and your query planner show it, consider move them into a trigger and write the solved result into a table, and query that table instead. I need to loans calculations that requiere sub-second answers and this is how I solve it.

And for your query planner investigation, this handy tool is great:

https://tatiyants.com/pev/#/plans

Perhaps you could somehow attach the piezoelectric component or bars to a micrometer [1] which is designed for accurate and repeatable measurement?

[1] https://en.wikipedia.org/wiki/Micrometer