-1

I'm running a large ETL operation on > 100,000 jsonl files. I'm using RabbitMQ to insert filepaths into a queue and then using 25 workers to transform the files and perform batch inserts into a number of Postgres tables.

I've noticed that if I crank the number of workers up, I may end up with less data at the end, but no errors pop up.

Is it possible to "overload" the database instance and lose data?

Notes:

  • At the end, the largest table should have 2B rows
  • Average table size is 250M rows
  • Using the npm package node-postgres to handle the inserts
  • 2
    Are you asking if it's possible that Postgres reports a successful commit to your application without actually executing the DML statement? It is possible, theoretically speaking, but it's much more probable that there's an error in node-postgres, your code, or your data verification method. – mustaccio Apr 11 at 15:49
  • Out of curiosity, how long does it take to count all those rows? – dezso Apr 11 at 16:03
  • 1
    I don't think that you will ever get into the situation where Postgres silently discards data from an insert (unless you ask it to by e.g. using on conflict do nothing). If something goes wrong you will at least get an error – a_horse_with_no_name Apr 11 at 16:06
  • @dezso I don't even want to know. I get these tables converted to Parquet files immediately and do all the fun stuff in Spark. – sean Apr 11 at 16:36
  • 2
    Wouldn’t it be easier to directly create the parquet files? – eckes Apr 11 at 17:15
1

If you are running with synchronous_commit off and the database crashes, it is possible that when it comes back up the most recently committed transactions will be missing. But that is not very credible here, as if the database crashed you would almost surely know about it.

It would sever any existing connections, which would surely be noticed unless the crash happened right after the very last commit was done, and so no one tried to use a connection anymore. (Or unless the code you are using silently ignores severed connections and just reopens them--but even then the crash would at least be noted in the PostgreSQL server log file. And if your code silently ignores errors, there is no limit to the number of ways you could end up with missing data)

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.