I have a bunch of remote autonomous data harvesting systems running, each collects approx 28,800 records over an 8 hour run. The remote systems are Rasp Pi based, running Python, logging to MariaDB on a Synology NAS. I need to pump the data up to my Postgres server that is collating all the data. To add to the mix, the remote systems are running off 4G connections that drop in and out on an irregular basis (sometimes 8hrs offline...) so I can't rely on a scheduled job to do the trick. I have python scripts running that transfer each record on an individual basis as a straight insert (these check and handle loss of connection etc) but that is way way too slow.
The data is massaged on its way to Postgres at the minute, to add in local identifiers etc but they could be incorporated into the local db to make the tables compatible. My obvious bottleneck is the 4G but there is nothing I can do about that, I figured there must be a much more efficient method than inserting each row, would a parameterised SP give a significant gain ? or a bulk table load from a local csv (with batched records e.g. 1000 at a time)...
Any pointers to routes to explore much appreciated, thank you
Update: I had googled previously and couldn't find a solution that seemed appropriate but more digging yielded some ideas.. Well on the way to a solution: using Python StringIO to build a 'file' and the postgres copy_from functionality I can bulk load 500 records in under a second. Still need to 'tune' the batching and other bits but seriously faster, the data massaging can still happen in the file build so didn't have to mod the base tables.
Still interested in other routes as I'm sure this one may yet build me a brick wall to bang my head against... again.