title: Why is Postgres transaction block so much slower? And how to solve?
body: <p>Hoping someone with deeper postgres/database knowledge can share some insight.</p>
  
  <p>Background:</p>
  
  <p>I have a postgres v11 database running with some data. Now there is about 200k operations that I want to run on it as a single transaction (they should either all succeed or no changes made). The transactions include a mix of INSERT and UPDATE statements that also involve some triggers in the database.</p>
  
  <p>If I run the query in a transaction then it takes about 70 minutes</p>
  
  <pre><code>BEGIN;
  // statement
  // statement
  // statement
  // ...
  COMMIT;
  </code></pre>
  
  <p>If I remove the surrounding transaction block and simply run the queries then it will take 7 minutes.</p>
  
  <pre><code>// statement
  // statement
  // statement
  // ...
  </code></pre>
  
  <p>Now I suspect the performance difference is related to transaction keeping some temporary tables which run into buffer size or indexing issues resulting in the slowdown, but since I don't have deep understanding of Postgres internals I don't actually know. I have tried changing various WAL settings, but without any significant difference.</p>
  
  <p>So I have 2 questions:</p>
  
  <ol>
  <li>Why is the transaction so much slower?</li>
  <li>How can I run the batch of operations with the possibility of rollback without sacrificing performance to this degree?</li>
  </ol>
  
  <hr>
  
  <p><strong>EDIT 1</strong></p>
  
  <hr>
  
  <p>Extra information about tables and queries</p>
  
  <p>I will try to give as much information as I can without revealing specifics (which I am not allowed).</p>
  
  <p>There are 5 main tables related to this transaction - <code>accounts</code>, <code>currencies</code>, <code>transactions</code>, <code>transaction_logs</code>, <code>balance</code>.</p>
  
  <p><code>transactions</code> table has 3 foreign keys - 2 to <code>accounts</code> and 1 to <code>currencies</code>.</p>
  
  <p><code>balances</code> table has 2 foreign keys - 1 to <code>accounts</code> and 1 to <code>currencies</code>.</p>
  
  <p><code>transaction_logs</code> table has 3 foreign keys - 1 to <code>transactions</code>, 1 to <code>accounts</code>, 1 to <code>currencies</code>.</p>
  
  <p>The transaction batch starts by inserting all relevant accounts and currencies. (about 10k accounts and 2 currencies)</p>
  
  <p>Next it is a mix of inserts and updates of the transactions table - inserts are in groups up to 300 rows in a single insert, while updates are based on PK so only 1 row at a time. In total there are about 140k inserts and 60k updates.</p>
  
  <p>Both inserts and updates in turn execute triggers which update the balances table and insert 2 rows into transaction_log table.</p>
  
  <p>A typical insert looks like</p>
  
  <p><code>INSERT INTO transactions (from_account_id, to_account_id, currency_id, amount, metadata, status) VALUES (1, 1, 1, 10.00, '{"json":"blob"}', 'pending');</code></p>
  
  <p>A typical update looks like</p>
  
  <p><code>UPDATE transactions SET status = 'finished' WHERE id = 1;</code></p>
  
  <p>The programming environment is <code>node.js</code> and driver is <code>pg</code>.</p>
  
  <hr>
  
  <p><strong>EDIT 2</strong></p>
  
  <hr>
  
  <p>Apologies to everyone for the delay in providing more information - I was trying to create a closed test environment reproducing the error. And while doing this I managed to get closer to the crux of the problem. The structure itself seems irrelevant. <strong>The slowdown is caused by updating the same row over and over again within the same transaction.</strong> </p>
  
  <p>The simplest reproduction I managed was creating a table with 2 fields, adding a few rows and then repeatedly updating the first row. Without a transaction the operation time stays relatively constant. Within a transaction however it starts climbing - at around 100k updates it is around 3 times higher than in the beginning. On my machine the total runtime difference for 100k updates test was 2.5x between a transaction and non-transaction version.</p>
  
  <p>If however your data is more varied - not updating the same row, but different rows, then the problem does not occur and with a nicely distributed data the transaction is actually faster.</p>
  
  <p>PS. Tested this with v12 as well and performs roughly the same.</p>
  
  <p><em>Does anyone have any ideas why this is happening?</em></p>
  
  <hr>
  
  <p><strong>EDIT 3</strong></p>
  
  <hr>
  
  <p>I have created a repository to demonstrate this issue <a href="https://github.com/DeadAlready/pg-test" rel="nofollow noreferrer">https://github.com/DeadAlready/pg-test</a></p>