1

I am loading some data into my postgres database using a python script. These data are all in huge csv files, so I read them lazily and call INSERT statements on batches of data. However, these data are all "connected" in some sense, so it is nice to maintain the entire data loading pipeline as a single transaction, so that if some part of it fails, then it can all be rolled back.

My question is: I'm taking pains to keep these huge csv files out-of-memory in my python script but, since the whole data load is a single transaction, does all of the data end up in-memory on the postgres side of things? Does a single INSERT transaction have to fit all of its data entirely in-memory?

2 Answers 2

1

PostgreSQL writes data from uncommitted transaction to WAL and the data files just like any other data. The data don't have to be kept in memory, nor do the WAL files have to be kept around after they have been archived and contain no data more recent than the latest checkpoint. So you don't have to worry on that account.

In case you wonder how PostgreSQL can rollback a large transaction if it does not have the data from that transaction in memory: PostgreSQL never actually performs a rollback. If you run ROLLBACK (or an error aborts the transaction), all that happens is that the transaction is marked as "aborted" in the commit log, so that all the data it wrote become invisible. The data from an aborted transaction remain stored in the table until the next autovacuum run removes them.

The risk of long-running transactions in PostgreSQL is a different one:

  1. you will hold locks for a long time, which is bad for concurrency and makes deadlocks more likely

  2. VACUUM cannot clean up any row versions that became invisible after the long-running transaction started, which can lead to table bloat if there is much concurrent DML activity

2

No, but it does have to fit in the space available for WAL files.

2
  • 2
    It doesn't have to do even that. There is no problem removing a WAL file which has uncommitted transactions in it. (With logical replication, maybe there would be a problem--I've never tried that. But open transactions are no problem with archiving, physical streaming, or checkpointing).
    – jjanes
    Commented Apr 6 at 0:20
  • Yeah, the logical replication slot is the only limitation; it will require wal from the beginning of the transaction. If there are no logical replication slots, then postgresql will be able to delete old wals regardless of running transactions.
    – Melkij
    Commented Apr 6 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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