<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Julia on Medium]]></title>
        <description><![CDATA[Stories by Julia on Medium]]></description>
        <link>https://medium.com/@shared_mean?source=rss-634a9a3d0562------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*dmbNkD5D-u45r44go_cf0g.png</url>
            <title>Stories by Julia on Medium</title>
            <link>https://medium.com/@shared_mean?source=rss-634a9a3d0562------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 05:34:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@shared_mean/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Ruby on Rails & PostgreSQL query performance]]></title>
            <link>https://medium.com/@shared_mean/ruby-on-rails-postgresql-query-performance-9ce6caae9f8a?source=rss-634a9a3d0562------2</link>
            <guid isPermaLink="false">https://medium.com/p/9ce6caae9f8a</guid>
            <category><![CDATA[ruby-on-rails]]></category>
            <category><![CDATA[rubygems]]></category>
            <category><![CDATA[ruby]]></category>
            <category><![CDATA[ruby-on-rails-development]]></category>
            <category><![CDATA[transactions]]></category>
            <dc:creator><![CDATA[Julia]]></dc:creator>
            <pubDate>Mon, 22 Apr 2024 06:14:42 GMT</pubDate>
            <atom:updated>2024-04-22T06:14:42.567Z</atom:updated>
            <content:encoded><![CDATA[<p>The aim of the research is the question we have been discussing on the project. I’m fully confident that Ruby on Rails developers face that case pretty often, but don’t attach importance to it. I suppose my article will be useful for both beginners and experienced RoR engineers. Enjoy!</p><blockquote><em>We have a set of SQL queries. One of them may cause a DB error. There are 3 ways to send this set of queries to PostgreSQL: </em><strong>ActiveRecord::Base.connection.execute</strong><em> method, </em><strong>BEGIN &amp; COMMIT</strong> <em>in SQL statement</em><strong>, ActiveRecord::Base.transaction</strong> <em>method</em></blockquote><blockquote><em>The question is, </em><strong><em>how will these approaches work?</em></strong></blockquote><p>Let’s investigate our problem using the following set of queries:</p><pre>UPDATE employees SET last_name = &#39;&#39; where id = 1; <br>UPDATE employees SET last_name = sss where id = 1; # this will cause a mistake</pre><ol><li>We can use <strong>execute</strong> method</li></ol><pre>ActiveRecord::Base.connection.execute(<br>  &lt;&lt;-SQL<br>    UPDATE employees SET last_name = &#39;&#39; where id = 1;<br>    UPDATE employees SET last_name = sss where id = 1;<br>  SQL<br>)</pre><p>2. We can use <strong>BEGIN &amp; COMMIT</strong></p><pre>ActiveRecord::Base.connection.execute(<br>  &lt;&lt;-SQL<br>    BEGIN;<br>      UPDATE employees SET last_name = &#39;&#39; where id = 1;<br>      UPDATE employees SET last_name = sss where id = 1;<br>    COMMIT;<br>  SQL<br>)</pre><p>3. We can use <strong>transaction</strong> method</p><pre>ActiveRecord::Base.transaction do<br>  ActiveRecord::Base.connection.execute(<br>    &lt;&lt;-SQL<br>      UPDATE employees SET last_name = &#39;&#39; where id = 1;<br>      UPDATE employees SET last_name = sss where id = 1;<br>    SQL<br>  )<br>end</pre><p>Can you answer the main question right away? Let’s dig deeper and check the performance of each approach. By the way, my current Ruby version is 3.1.4, Rails version is 6.1.7.5.</p><h4>execute method</h4><p>As you can see on the picture below, execute method does not wrap the statements into a transaction, it sends the set of queries to <strong>pg gem</strong> <strong>async_exec </strong>method as it is.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rAFPZxCRrE2S8doX_Too8Q.png" /><figcaption><a href="https://github.com/rails/rails/blob/v6.1.7.5/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb#L39">https://github.com/rails/rails/blob/v6.1.7.5/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb#L39</a></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tvhaf_cW6goJfnv_NuY8AQ.png" /><figcaption>the value of <strong>sql</strong> variable that is sent to <strong>async_exec</strong> method</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*9GQDF9mBDhf9XCYZ2i2jNA.png" /><figcaption>The next step is: pg gem sends the set of queries to PostgreSQL.</figcaption></figure><p>Let’s check PostegreSQL logs. The main question is: has our set of statements been wrapped into a transaction? As shown on the screen below, PostgreSQL got just 2 statements.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y4OfJVZXeBi6VQaRTPLCIQ.png" /></figure><p>But the main point is that the changes that have been updated in the first statement haven’t been applied. That seems strange, isn’t it? We have 2 statements, the first one is correct, the second causes an error. We do not have a transaction, however, no changes have been applied. Why did it happen?</p><p>The answer is in the PostgreSQL documentation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KbRcfslrc0TlsVx_STtdxg.png" /><figcaption><a href="https://www.postgresql.org/docs/current/tutorial-transactions.html">https://www.postgresql.org/docs/current/tutorial-transactions.html</a></figcaption></figure><p>All in all, the <strong>execute</strong> method leads to the following result:</p><ul><li><strong>execute</strong> method won’t wrap set of statements into a transaction</li><li>changes won’t be applied because PostgreSQL has an implicit BEGIN and COMMIT wrapped around every SQL statement</li></ul><h4><strong>BEGIN &amp; COMMIT</strong></h4><p>After adding BEGIN and COMMIT into the SQL statement, we’ll get the same error:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wYwt1lj39YJPrcMj6rf_1A.png" /></figure><p>Seems like it works absolutely like the previous approach. Let’s try to send any other request to DB using this connection:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OxU6olcV0gjhqk_x8ajDlg.png" /><figcaption>A bolt from the blue!</figcaption></figure><p>Due to implicit BEGIN and COMMIT in PostgreSQL, no expressions after an error in the set of queries will be applied — neither COMMIT nor ROLLBACK, which has been passed at the end of the statement. In this way, a transaction will be opened but not closed. That causes further errors.</p><p>As a result, the <strong>BEGIN &amp; COMMIT</strong> leads to the following outcome:</p><ul><li>You need to handle statement errors to avoid the error of the uncompleted transaction block</li></ul><h4>transaction method</h4><p>The <strong>ActiveRecord::Base.transaction</strong> provides really useful functionality to work with transactions. I’ll consider in detail the method’s features in my next article. Speaking of our current research, it provides additional queries which are sent to PostgreSQL:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FU_NNycMfJxMhdW81Fgkgg.png" /></figure><p>When we send statements inside <strong>ActiveRecord::Base.transaction </strong>block, we see the following result in console:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*y-2GkmkJJzmfiHsg" /></figure><p>The <strong>transaction </strong>method is pretty smart. It gets the DB answer and sends <strong>COMMIT</strong> or <strong>ROLLBACK</strong> back to PostgreSQL.</p><p>All in all, the <strong>transaction</strong> method leads to the following result:</p><ul><li>Automatically sends <strong>BEGIN</strong>, <strong>COMMIT</strong> &amp; <strong>ROLLBACK</strong></li><li>Handle errors</li><li>Provides other helpful features</li></ul><p>I hope you liked this post and found something useful in there!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9ce6caae9f8a" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>