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.


Trying to do a fast and reliable insert over an intermittent connection will be a headache. I would:

  1. Use something like rsync with compression to get your endpoint files up to a centralized, reliable server.
  2. Once you believe the files are good, do a bulk load from that centralized location to the DB. You could then use the COPY command, a flat file foreign table wrapper, or get fancier with an ETL tool.
  • nice, that fits neatly with what i'm building now, can just move the in-memory 'files' up to a real server file first. Thank you – Richard Jul 6 '17 at 13:22
  • Great. If this answer was helpful, please upvote and mark answered :) – CalZ Jul 6 '17 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.