Postgres: Repercussions to killing a large transaction?

Question

I have a very large multi-million row transaction that I ended up needing to kill.

This transaction scanned a very large number of rows and created new rows in a new table if certain conditions were met.

This was in a commit block and did not complete before I killed the process— are there any repercussions to killing the process and restarting the server? I do not even see the tables in the db (presumably because the commit never happened). Can I just immediately try to do my migration again?

Answer 1

The answer depends on how you “killed” the transaction.

• If you hit Ctrl+C or canceled the query with pg_cancel_backend or pg_terminate_backend, the transaction will have rolled back normally.

  Any table you created in the session will be gone.

  If you modified rows in pre-existing tables, the new rows will be dead and autovacuum will remove them.

  At worst, you will have some bloat in some tables that will be reused by the next attempt at your transaction.

• Similarly, if you used a regular kill to kill the backend process of the session, everything will be fine.

• If you used kill -9 to kill the session's backend process, PostgreSQL will have gone into crash recovery.

  Your database will be consistent after crash recovery, but it is possible that some files (belonging to newly created tables) will be left behind. Such orphans take up space and are never removed, and the only safe way to get rid of that wasted space is to dump the database and restore it to a new database cluster.

Answer 2

Theoretically, yes. You should be able to just go ahead and try again. It might mean that some of the cleanup hasn't been performed yet, so there are some partial tables floating around, taking up memory, but nothing that should impact your data quality.