<story><title>PostgreSQL 14</title><url>https://www.postgresql.org/about/news/postgresql-14-released-2318/</url></story><parent_chain><item><author>hyper_reality</author><text>PostgreSQL is one of the most powerful and reliable pieces of software I&amp;#x27;ve seen run at large scale, major kudos to all the maintainers for the improvements that keep being added.&lt;p&gt;&amp;gt; PostgreSQL 14 extends its performance gains to the vacuuming system, including optimizations for reducing overhead from B-Trees. This release also adds a vacuum &amp;quot;emergency mode&amp;quot; that is designed to prevent transaction ID wraparound&lt;p&gt;Dealing with transaction ID wraparounds in Postgres was one of the most daunting but fun experiences for me as a young SRE. Each time a transaction modifies rows in a PG database, it increments the transaction ID counter. This counter is stored as a 32-bit integer and it&amp;#x27;s critical to the MVCC transaction semantics - a transaction with a higher ID should not be visible to a transaction with a lower ID. If the value hits 2 billion and wraps around, disaster strikes as past transactions now appear to be in the future. If PG detects it is reaching that point, it complains loudly and eventually stops further writes to the database to prevent data loss.&lt;p&gt;Postgres avoids getting anywhere close to this situation in almost all deployments by performing routine &amp;quot;auto-vacuums&amp;quot; which mark old row versions as &amp;quot;frozen&amp;quot; so they are no longer using up transaction ID slots. However, there are a couple situations where vacuum will not be able to clean up enough row versions. In our case, this was due to long-running transactions that consumed IDs but never finished. Also it is possible but highly inadvisable to disable auto-vacuums. Here is a postmortem from Sentry who had to deal with this leading to downtime: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;blog.sentry.io&amp;#x2F;2015&amp;#x2F;07&amp;#x2F;23&amp;#x2F;transaction-id-wraparound-in-postgres&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;blog.sentry.io&amp;#x2F;2015&amp;#x2F;07&amp;#x2F;23&amp;#x2F;transaction-id-wraparound-...&lt;/a&gt;&lt;p&gt;It looks like the new vacuum &amp;quot;emergency mode&amp;quot; functionality starts vacuuming more aggressively when getting closer to the wraparound event, and as with every PG feature highly granular settings are exposed to tweak this behaviour (&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;about&amp;#x2F;featurematrix&amp;#x2F;detail&amp;#x2F;360&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;about&amp;#x2F;featurematrix&amp;#x2F;detail&amp;#x2F;360&amp;#x2F;&lt;/a&gt;)</text></item></parent_chain><comment><author>jedberg</author><text>&amp;gt;  Also it is possible but highly inadvisable to disable auto-vacuums.&lt;p&gt;When I was running my first Postgres cluster (the reddit databases), I had no idea what vacuuming was for.  All I knew was that every time it ran it slowed everything down.  Being dumb, I didn&amp;#x27;t bother to read the docs, I just disabled the auto vacuum.&lt;p&gt;Eventually writes stopped and I had to take a downtime to do a vacuum.  Learned a few important lessons that day.  I also then set it up to do an aggressive vacuum every day at 3am, which was the beginning of low traffic time, so that the auto-vacuuming didn&amp;#x27;t have as much work to do during the day.</text></comment>
<story><title>PostgreSQL 14</title><url>https://www.postgresql.org/about/news/postgresql-14-released-2318/</url></story><parent_chain><item><author>hyper_reality</author><text>PostgreSQL is one of the most powerful and reliable pieces of software I&amp;#x27;ve seen run at large scale, major kudos to all the maintainers for the improvements that keep being added.&lt;p&gt;&amp;gt; PostgreSQL 14 extends its performance gains to the vacuuming system, including optimizations for reducing overhead from B-Trees. This release also adds a vacuum &amp;quot;emergency mode&amp;quot; that is designed to prevent transaction ID wraparound&lt;p&gt;Dealing with transaction ID wraparounds in Postgres was one of the most daunting but fun experiences for me as a young SRE. Each time a transaction modifies rows in a PG database, it increments the transaction ID counter. This counter is stored as a 32-bit integer and it&amp;#x27;s critical to the MVCC transaction semantics - a transaction with a higher ID should not be visible to a transaction with a lower ID. If the value hits 2 billion and wraps around, disaster strikes as past transactions now appear to be in the future. If PG detects it is reaching that point, it complains loudly and eventually stops further writes to the database to prevent data loss.&lt;p&gt;Postgres avoids getting anywhere close to this situation in almost all deployments by performing routine &amp;quot;auto-vacuums&amp;quot; which mark old row versions as &amp;quot;frozen&amp;quot; so they are no longer using up transaction ID slots. However, there are a couple situations where vacuum will not be able to clean up enough row versions. In our case, this was due to long-running transactions that consumed IDs but never finished. Also it is possible but highly inadvisable to disable auto-vacuums. Here is a postmortem from Sentry who had to deal with this leading to downtime: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;blog.sentry.io&amp;#x2F;2015&amp;#x2F;07&amp;#x2F;23&amp;#x2F;transaction-id-wraparound-in-postgres&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;blog.sentry.io&amp;#x2F;2015&amp;#x2F;07&amp;#x2F;23&amp;#x2F;transaction-id-wraparound-...&lt;/a&gt;&lt;p&gt;It looks like the new vacuum &amp;quot;emergency mode&amp;quot; functionality starts vacuuming more aggressively when getting closer to the wraparound event, and as with every PG feature highly granular settings are exposed to tweak this behaviour (&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;about&amp;#x2F;featurematrix&amp;#x2F;detail&amp;#x2F;360&amp;#x2F;&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.postgresql.org&amp;#x2F;about&amp;#x2F;featurematrix&amp;#x2F;detail&amp;#x2F;360&amp;#x2F;&lt;/a&gt;)</text></item></parent_chain><comment><author>mattashii</author><text>&amp;gt; Each time a transaction modifies rows in a PG database, it increments the transaction ID counter.&lt;p&gt;It&amp;#x27;s a bit more subtle than that: each transaction that modifies, deletes or locks rows will update the txID counter. Row updates don&amp;#x27;t get their own txID assigned.&lt;p&gt;&amp;gt; It looks like the new vacuum &amp;quot;emergency mode&amp;quot; functionality starts vacuuming more aggressively when getting closer to the wraparound&lt;p&gt;When close to wraparound, the autovacuum daemon stops cleaning up the vacuumed tables&amp;#x27; indexes, yes. That saves time and IO, at the cost of index and some table bloat, but both are generally preferred over a system-blocking wraparound vacuum.</text></comment>