<?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 Kevin Breidenbach on Medium]]></title>
        <description><![CDATA[Stories by Kevin Breidenbach on Medium]]></description>
        <link>https://medium.com/@kevinbreidenbach?source=rss-8d693c1d894a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*O4x0tZUX6MtdBRI7.</url>
            <title>Stories by Kevin Breidenbach on Medium</title>
            <link>https://medium.com/@kevinbreidenbach?source=rss-8d693c1d894a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 03:14:41 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@kevinbreidenbach/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[PostgreSQL: When Partitions Break]]></title>
            <link>https://medium.com/@kevinbreidenbach/postgresql-when-partitions-break-b98805eecb00?source=rss-8d693c1d894a------2</link>
            <guid isPermaLink="false">https://medium.com/p/b98805eecb00</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[fastpass-locks]]></category>
            <category><![CDATA[table-partitioning]]></category>
            <dc:creator><![CDATA[Kevin Breidenbach]]></dc:creator>
            <pubDate>Mon, 17 Mar 2025 00:51:35 GMT</pubDate>
            <atom:updated>2025-03-17T01:51:46.752Z</atom:updated>
            <content:encoded><![CDATA[<p>The ability to partition RDMBS schemas is a good thing. Partitions can improve performance and distribute data, but care is needed in building a partition strategy that works for both the data and the applications using it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-sPeTaGQ29PlHziHKo0CMQ.jpeg" /></figure><p>Rather than go into too much detail, what I can say is that for this particular schema range partitioning wasn’t an option as we knew that queries couldn’t be segregated in such a way that would make sense for the data. Instead hash partitioning was selected as it would work well with the UUID columns that the schema relied on.</p><p>The app that used this schema was high volume and write heavy, and everything worked well; initially, at least.</p><p>Let’s pretend the following diagram represented the schema:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/721/1*0SagO6VLGqdIo_Bx6rxAKg.png" /></figure><p>The hash partition was based on the primary key of each table. In this case there were ten partitions for each table. The data was evenly distributed across the tables and the size on disk had grown to 5 TB in around six months.</p><p>All was running smoothly, but then some requirements came that changed how the application worked. While it was still write heavy, reads would be done to make some determinations from the data immediately after the insert had completed.</p><p>At that point, we started to see locking on the database that was causing extreme slowness. Statements would timeout, and back-off retries would mean the application fell behind.</p><h4>Can you make my computer run faster?</h4><p>After a lot of analysis it was found that we were suffering from FastPass lock exhaustion. Other heavier lock types were then coming into play, and our system which once ran like clockwork was now, on occasion, causing the database to behave as if it were bedridden with a severe case of influenza.</p><p>The queries that were the perpetrators of our slowness issues were those that would cause queries to cross partitions. This would deplete the FastPass locks much more quickly as each partition would need it’s own locks. To quickly alleviate this we rewrote some of the queries to minimize or remove all cross partition work, for example we rewrote the following query:</p><pre>SELECT B.b_id FROM B<br>JOIN A_to_B AB ON AB.b_id = B.b_id<br>WHERE AB.a_id IN (..., ..., ...);</pre><p>to something that didn’t cause cross partitioning to happen:</p><pre>SELECT B.b_id FROM B<br>JOIN A_to_B AB ON AB.b_id = B.b_id<br>WHERE AB.a_id = ...;</pre><p>This, though, meant we had to run multiple queries to achieve the same as we did with the original single query. The obvious answer was parallel queries, which worked well but increased CPU usage on the PostgreSQL server. So, we increased the instance size (which would hopefully be temporary) and decided we would change the partition strategy.</p><h4>Where there’s a will…</h4><p>It’s not easy to change a hash partition, in fact the only way you can really do it is to create a new table and migrate the data. Despite having increased the instance size, along with migrating one table at a time using COPY for existing data, along with triggers to copy new data at the same time, the database slowed — even without the lock issue.</p><p>The next attempt was to create a new instance of the database and use replication to get the data from the “old” to the “new”. Seems simple with replication. The new database had the schema sans partitions, and we set up the publisher to tell it send data from the root rather than each partition:</p><pre>CREATE PUBLICATION a_pub FOR TABLE A WITH (publish_via_partition_root = true);</pre><p>And so publishers were started for each table in the schema, on the new database subscribers were set up to pull the data:</p><pre>CREATE SUBSCRIPTION a_subscription<br>    CONNECTION &#39;old-instance-connection-settings&#39;<br>    PUBLICATION a_pub<br>    WITH (copy_data = true);</pre><p>While copy_data = true is the default, it is nice to specify it explicitly so there’s no confusion (as you’ll see later).</p><p>Replication started and there was hope that this would get us where we needed to be, but no, the “old” database — which the app still needed to use — was again running slowly.</p><p>Then came a new and bright idea💡</p><p>We deleted all the subscribers and publishers to clean up the replication slots, then started the publisher on the “old” database again. On the “new” database we again started subscription, but with copy_data = false (I told you we’d see this later):</p><pre>CREATE SUBSCRIPTION a_subscription<br>    CONNECTION &#39;old-instance-connection-settings&#39;<br>    PUBLICATION a_pub<br>    WITH (copy_data = false);</pre><p>This meant that all new data coming in to the “old” database would be replicated to the “new” database, but what about the existing data? Well we then grabbed a snapshot of the “old” database and started that up in a new instance. On the “snapshot” instance we again started publishing:</p><pre>CREATE PUBLICATION a_snapshot_pub FOR TABLE A WITH (publish_via_partition_root = true);</pre><p>Then on the “new” instance we started a subscription to that publisher with:</p><pre>CREATE SUBSCRIPTION a_snapshot_subscription<br>    CONNECTION &#39;snapshot-instance-connection-settings&#39;<br>    PUBLICATION a_snapshot_pub<br>    WITH (copy_data = true);</pre><p>Now we were getting both the existing data from the “snapshot” instance, and the incoming data from the “old” instance. It’s now just a waiting game for the data to complete.</p><h4>Are we there yet?</h4><p>Nearly. Once the replication is complete we can switch the app to use the “new” instance and decommission both the “old” and “snapshot” databases (which will please our finance people, as this is <em>expensive</em>). But how can we know when we’re there?</p><p>Luckily, we can find out how replication is proceeding with a nice query:</p><pre>SELECT sub.subname AS subscription_name, rel.relname AS table_name, subrel.srsubstate<br>FROM pg_subscription_rel subrel<br>JOIN pg_class rel ON subrel.srrelid = rel.oid<br>JOIN pg_subscription sub ON subrel.srsubid = sub.oid;</pre><p>Which will give you a output like:</p><pre>subscription_name       | table_name | srsubstate<br>-------------------------------------------------<br>a_subscription          | a          | r<br>a_snapshot_subscription | a          | d</pre><p>The important column there is srsubstate. If that is d it means that copy replication is still ongoing, once that switches to r we are on live replication, but since the “snapshot” instance is not getting new data we know that is complete. So when all snapshot subscriptions are r we can decommission that instance and plan to switch over to the new database.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b98805eecb00" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>