<?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 Sean Callahan on Medium]]></title>
        <description><![CDATA[Stories by Sean Callahan on Medium]]></description>
        <link>https://medium.com/@scalahansolo?source=rss-7abe4dfec51b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*UE43O9jCIGdEE415dA0FZQ.jpeg</url>
            <title>Stories by Sean Callahan on Medium</title>
            <link>https://medium.com/@scalahansolo?source=rss-7abe4dfec51b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 03:29:09 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@scalahansolo/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[Migrating to Postgres]]></title>
            <link>https://engineering.usemotion.com/migrating-to-postgres-3c93dff9c65d?source=rss-7abe4dfec51b------2</link>
            <guid isPermaLink="false">https://medium.com/p/3c93dff9c65d</guid>
            <category><![CDATA[orm]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[prisma]]></category>
            <category><![CDATA[cockroachdb]]></category>
            <dc:creator><![CDATA[Sean Callahan]]></dc:creator>
            <pubDate>Tue, 13 May 2025 18:53:28 GMT</pubDate>
            <atom:updated>2025-05-15T16:20:07.524Z</atom:updated>
            <content:encoded><![CDATA[<p>Since early 2022, Motion was on <a href="https://www.cockroachlabs.com/">CockroachDB</a>. Cockroach has many qualities going for it: effortless horizontal scaling, especially when dealing with multi-region set ups, extremely high availability, and a SQL-compatible interface. Early on, there were concerns about the eventuality of a multi-region setup (mandated by GDPR), and how exactly a traditional set up on Postgres would scale.</p><p>However, as Motion grew, so did our usage and costs. By 2024, Motion’s CockroachDB bill had 5x-ed to the mid 6 figures, and some of the cracks were beginning to show. None of our customers had required data localization yet, and we were still in a single region doing fairly simple transactional queries — so why pay the cost of a distributed database at all?</p><iframe src="https://cdn.embedly.com/widgets/media.html?type=text%2Fhtml&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;schema=twitter&amp;url=https%3A//x.com/VicVijayakumar/status/1921941839227003181&amp;image=" width="500" height="281" frameborder="0" scrolling="no"><a href="https://medium.com/media/3e828f0034c0103654ba676ce97723be/href">https://medium.com/media/3e828f0034c0103654ba676ce97723be/href</a></iframe><p>Fortunately for us, we had an ORM that made testing head to head relatively trivial.</p><h3>Migrations</h3><p>As the size of our database grew, we would frequently get into situations where Prisma would simply time out when applying migrations. We’d then have to log into Cockroach and manually run migrations one by one, concurrently of course.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/375/1*V4IRK-u8IfBuX3kZXY6z_g@2x.png" /><figcaption>The deploy was blocked for ~2 hours while we figured out why Cockroach was timing out</figcaption></figure><p>When I finally moved us to Postgres, I tested a very similar migration head to head (the name of the column is different, just for demo purposes). Here is Postgres applying this migration on a rewound instance of our database (to control for data size) in <em>ten seconds</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TY-5rg9MhbjgdwdzH-ALmw.gif" /><figcaption>Adding a new “foobar” column on the TeamMember table and running the Prisma migration.</figcaption></figure><p>The timeouts were leading to operational shortcuts. By 2024, the timeouts had become so acute that even really strong<em> </em>developers were actively doing things outside the DB purely out of fear of locking the entire system.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*m06jYj6uOi_rWJFw9DVyuA@2x.png" /></figure><p>Even worse, the migration issues were proving to be a blocker to upgrade even Cockroach versions. We were stuck on version 22 well past the end of life, which made support <em>even slower</em> to reply to us.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/671/1*u3t-_B3Ni1TywuiVnUNbow@2x.png" /></figure><p>In the end, we ended up just giving up and opting to move to Postgres, remaining on the defunct version 22 until the end.</p><h3>ETL</h3><p>The timeouts began affecting things outside of migrations — namely, ETL.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/419/1*bUaNVaL0Epsi98Gx5X6sjg@2x.png" /><figcaption>A typical error from Airbyte stalling</figcaption></figure><p>We’d become accustomed to waking to pages like this one:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/542/1*dxp1_R2A5K0eWlUjF8UV7A@2x.png" /></figure><p>Even when the ETL jobs worked and did not time out, the performance was <em>brutal</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/396/1*OZ2Tu52p0_ywpTQZnNmxqQ@2x.png" /></figure><p>Unfortunately, there’s <em>very</em> little support for any real ETL solutions that offer CRDB replication. As of this writing, there is still only <a href="https://docs.airbyte.com/integrations/sources/cockroachdb">Airbyte’s connector</a> (which was in alpha in 2024), and we discovered only after implementing that the connector had a memory leak.</p><h3>Query Speeds</h3><p>The query head to heads were interesting. Some queries were indeed faster on Cockroach, thanks to their optimizer:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/523/1*mXJh-tutDnCFeb5MCDWG5g@2x.png" /><figcaption>An example query where Cockroach was faster</figcaption></figure><p>For queries like the above, Cockroach would resolve in ~13 seconds while Postgres would take up to 20 seconds. It seems like the Cockroach query planner innately understands these queries and does aggregation while Postgres naively ends up doing a near full table scan. (This advantage seems to go away when comparing <a href="https://orm.drizzle.team/">Drizzle</a> instead of the SQL generated by Prisma. More on that in a separate blog post.)</p><p>But there were many situations when the “magic” of the Cockroach query planner seemed to cut the other way. Here is the Prisma code and the generated SQL, for which Cockroach opts to do a full table scan while Postgres does not:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/496/1*Qt72I_zEGbXs5n-x47AvJQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/645/1*O60KGpkQ9rYg2ngdBetGvA.png" /><figcaption>The typescript code and the corresponding SQL monstrosity that Prisma generates.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/832/1*JNu4SMQWbEIOwz6TcIQCHw.png" /><figcaption>Note the `AND 1=1` at the end, resulting in a scan of the entire table</figcaption></figure><p>The <em>vast</em> majority of our real world queries seemed to follow this general pattern where Prisma generates very convoluted SQL with numerous inclusions of various columns and joins. That, combined with the magical Cockroach optimizer, ultimately resulted in extremely high latencies. Here’s a real example of a query that is<em> twenty</em> times faster in Postgres than Cockroach.</p><pre>SELECT<br> &quot;teamTasks&quot;.&quot;id&quot;,<br> &quot;teamTasks&quot;.&quot;name&quot;,<br> &quot;teamTasks&quot;.&quot;description&quot;,<br> &quot;teamTasks&quot;.&quot;dueDate&quot;,<br> &quot;teamTasks&quot;.&quot;duration&quot;,<br> &quot;teamTasks&quot;.&quot;completedTime&quot;,<br> &quot;teamTasks&quot;.&quot;assigneeUserId&quot;,<br> &quot;teamTasks&quot;.&quot;priorityLevel&quot;,<br> &quot;teamTasks&quot;.&quot;statusId&quot;,<br> &quot;teamTasks&quot;.&quot;projectId&quot;,<br> &quot;teamTasks&quot;.&quot;createdByUserId&quot;,<br> &quot;teamTasks&quot;.&quot;createdTime&quot;,<br> &quot;teamTasks&quot;.&quot;updatedTime&quot;,<br> &quot;teamTasks&quot;.&quot;lastInteractedTime&quot;,<br> &quot;teamTasks&quot;.&quot;archivedTime&quot;,<br> &quot;teamTasks&quot;.&quot;workspaceId&quot;,<br> &quot;teamTasks&quot;.&quot;minimumDuration&quot;,<br> &quot;teamTasks&quot;.&quot;scheduledStart&quot;,<br> &quot;teamTasks&quot;.&quot;scheduledEnd&quot;,<br> &quot;teamTasks&quot;.&quot;startDate&quot;,<br> &quot;teamTasks&quot;.&quot;isChunkedTask&quot;,<br> &quot;teamTasks&quot;.&quot;isUnfit&quot;,<br> &quot;teamTasks&quot;.&quot;type&quot;,<br> &quot;teamTasks&quot;.&quot;needsReschedule&quot;,<br> &quot;teamTasks&quot;.&quot;deadlineType&quot;,<br> &quot;teamTasks&quot;.&quot;schedule&quot;,<br> &quot;teamTasks&quot;.&quot;parentChunkTaskId&quot;,<br> &quot;teamTasks&quot;.&quot;parentRecurringTaskId&quot;,<br> &quot;teamTasks&quot;.&quot;rank&quot;,<br> &quot;teamTasks&quot;.&quot;isFixedTimeTask&quot;,<br> &quot;teamTasks&quot;.&quot;slug&quot;,<br> &quot;teamTasks&quot;.&quot;previousSlugs&quot;,<br> &quot;teamTasks&quot;.&quot;endDate&quot;,<br> &quot;teamTasks&quot;.&quot;isBusy&quot;,<br> &quot;teamTasks&quot;.&quot;isAutoScheduled&quot;,<br> &quot;teamTasks&quot;.&quot;ignoreWarnOnPastDue&quot;,<br> &quot;teamTasks&quot;.&quot;scheduleOverridden&quot;,<br> &quot;teamTasks&quot;.&quot;snoozeUntil&quot;,<br> &quot;teamTasks&quot;.&quot;manuallyStarted&quot;,<br> &quot;teamTasks_chunks&quot;.&quot;data&quot; AS &quot;chunks&quot;,<br> &quot;teamTasks_labels&quot;.&quot;data&quot; AS &quot;labels&quot;,<br> &quot;teamTasks_blockedTasks&quot;.&quot;data&quot; AS &quot;blockedTasks&quot;,<br> &quot;teamTasks_blockingTasks&quot;.&quot;data&quot; AS &quot;blockingTasks&quot;<br>FROM<br> &quot;TeamTask&quot; &quot;teamTasks&quot;<br> LEFT JOIN LATERAL (<br>  SELECT<br>   coalesce(json_agg(json_build_array(&quot;teamTasks_chunks&quot;.&quot;id&quot;)), &#39;[]&#39;::json) AS &quot;data&quot;<br>  FROM<br>   &quot;TeamTask&quot; &quot;teamTasks_chunks&quot;<br>  WHERE<br>   &quot;teamTasks_chunks&quot;.&quot;parentChunkTaskId&quot; = &quot;teamTasks&quot;.&quot;id&quot;) &quot;teamTasks_chunks&quot; ON TRUE<br> LEFT JOIN LATERAL (<br>  SELECT<br>   coalesce(json_agg(json_build_array(&quot;teamTasks_labels&quot;.&quot;labelId&quot;)), &#39;[]&#39;::json) AS &quot;data&quot;<br>  FROM<br>   &quot;TeamTaskLabel&quot; &quot;teamTasks_labels&quot;<br>  WHERE<br>   &quot;teamTasks_labels&quot;.&quot;taskId&quot; = &quot;teamTasks&quot;.&quot;id&quot;) &quot;teamTasks_labels&quot; ON TRUE<br> LEFT JOIN LATERAL (<br>  SELECT<br>   coalesce(json_agg(json_build_array(&quot;teamTasks_blockedTasks&quot;.&quot;blockingId&quot;)), &#39;[]&#39;::json) AS &quot;data&quot;<br>  FROM<br>   &quot;TeamTaskBlocker&quot; &quot;teamTasks_blockedTasks&quot;<br>  WHERE<br>   &quot;teamTasks_blockedTasks&quot;.&quot;blockedId&quot; = &quot;teamTasks&quot;.&quot;id&quot;) &quot;teamTasks_blockedTasks&quot; ON TRUE<br> LEFT JOIN LATERAL (<br>  SELECT<br>   coalesce(json_agg(json_build_array(&quot;teamTasks_blockingTasks&quot;.&quot;blockedId&quot;)), &#39;[]&#39;::json) AS &quot;data&quot;<br>  FROM<br>   &quot;TeamTaskBlocker&quot; &quot;teamTasks_blockingTasks&quot;<br>  WHERE<br>   &quot;teamTasks_blockingTasks&quot;.&quot;blockingId&quot; = &quot;teamTasks&quot;.&quot;id&quot;) &quot;teamTasks_blockingTasks&quot; ON TRUE<br>WHERE (&quot;teamTasks&quot;.&quot;workspaceId&quot; in(<br>  SELECT<br>   &quot;workspaceId&quot; FROM &quot;WorkspaceMember&quot;<br>  WHERE (&quot;WorkspaceMember&quot;.&quot;userId&quot; = &#39;{{user_id}}&#39;<br>   AND &quot;WorkspaceMember&quot;.&quot;deletedTime&quot; IS NULL))<br> and(&quot;teamTasks&quot;.&quot;workspaceId&quot; in(&#39;{{workspace_id}}&#39;)<br>  and(&quot;teamTasks&quot;.&quot;id&quot; in(<br>    SELECT<br>     &quot;taskId&quot; FROM &quot;TeamTaskLabel&quot;<br>    WHERE<br>     &quot;TeamTaskLabel&quot;.&quot;labelId&quot; in(&#39;{{label_id}}&#39;))<br>    OR &quot;teamTasks&quot;.&quot;id&quot; NOT in(<br>     SELECT<br>      &quot;taskId&quot; FROM &quot;TeamTaskLabel&quot;))<br>    AND &quot;teamTasks&quot;.&quot;archivedTime&quot; IS NULL<br>    AND &quot;teamTasks&quot;.&quot;completedTime&quot; IS NULL))</pre><p>Most of our queries on the Team Tasks table were, on average, <em>three times</em> slower on Cockroach compared to Postgres.</p><h3>UI/UX Issues</h3><p>There were a handful of UI issues that also plagued us throughout the process.</p><ol><li>Unused Indices</li></ol><p>The UI for unused indices would show a mostly used indices, which periodically led to confusion by developers. We never got to the bottom of whether this was due to us using Prisma or not.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NKJcEE1_YKGLlfWucZi4PQ.png" /><figcaption>Cockroach’s list of recommendations, advocating developers dropped indexes that are still used</figcaption></figure><p>2. Cancelling running queries</p><p>By now we knew that running expensive queries on Cockroach was <em>extremely</em> scary. But since it’s a distributed cluster, cancelling a query isn’t so simple. With Postgres, you can simply hit cancel on TablePlus (or your preferred SQL client). On Cockroach, you have to actually log into the console and cancel the query, and pray all nodes cancel before they fall over. (At least once, they did not. You can probably guess what happened.)</p><p>3. Support</p><p>First, the support portal is a totally different website that doesn’t share auth with the main portal. Second, you have to re-input a lot of data they already know about you (cluster ID, etc). And by the time they respond it’s typically been a week. Normally that’s fine, but once they rolled out a bug which of course took us down immediately. That was <em>not</em> the time to log into a separate portal and input mundane details.</p><h3>VPC Issues</h3><p>Throughout the over two years we were using Cockroach, we consistently ran into periodic Tailscale connectivity issues.</p><pre>getaddrinfo ENOTFOUND internal-motion-dev-2022-10-grc.gcp-us-central1.cockroachlabs.cloud<br>PrismaClientInitializationError: Can&#39;t reach database server at &#39;internal-motion-prod-gnp.gcp-us-central1.cockroachlabs.cloud&#39;:&#39;26257&#39;</pre><p>No matter what we did though, these issues would suddenly arise, and, an hour later, just as suddenly disappear. These issues persisted in every environment (Airbyte connectors, CI, and our local TablePlus clients). We never solved it, and have never had similar issues with Postgres.</p><h3>The Migration</h3><p>By Jan 2024, our largest table had roughly 100 million rows. As mentioned earlier, while there were many tools built to <em>import</em> data into Cockroach, there were no ETL tools other than a very alpha Airbyte connector that kept timing out due to a memory leak.</p><p>So I decided to build a custom ETL solution.</p><p>Around this time, <a href="https://bun.sh/">Bun</a> was gaining popularity, so this migration was a convenient excuse to satisfy my curiosity. The Bun script roughly did the following steps:</p><ul><li>Read the database schema and all of its table information</li><li>Dump the data of each table to a dedicated file on disk</li><li>Spawn a Bun <a href="https://bun.sh/docs/api/spawn">child process</a> for each table in our schema</li><li>Each child process would then initiate a streaming connection of the data from the dumped data for that table</li><li>The stream was just a CSV stream of all the rows in the table</li><li>The “sink” for our stream would just insert the rows into Postgres</li></ul><p>I got to this point relatively quickly and should have known this was going too well to be true. It wasn’t until I started to run test migrations on our production database that I learned that Cockroach used slightly different byte encoding in JSON and array columns than Postgres did. The following few weeks involved using <a href="https://csv.js.org/">Csv-js</a> to put together a custom CSV parsing pipeline to transform all of the data from Cockroach to something that was compatible with Postgres, but also identical from a user perspective.</p><p>When the night of the migration came, I spun up the largest VM I could get my hands on GCP (128 core VM), turned on Motion’s maintenance mode, and let it rip. The production migration script from start to finish took roughly 15 minutes to run through the entire DB.</p><h3>Post Migration</h3><p>Everything you read above was done by <em>one</em> person (me), over the course of several weeks. In total, we were down for just under an hour from midnight to 1am pacific time — and of course, there was zero data loss. We could have been even more aggressive and limited the downtime to just 15 minutes, but we opted to be super safe and gradually ramp traffic back up.</p><p>Afterwards, we saw an immediate 33% drop in aggregated request latencies. But the exciting part was just beginning. Thanks to the Postgres ecosystem and tools like <a href="https://pganalyze.com/">PGAnalyze</a>, we fixed half a dozen unoptimized queries in just a few hours after the migration.</p><p>Despite being conservative and <em>over </em>provisioning our postgres cluster, we still ended up saving the business a little over $110,000 a year (would be even higher when factoring in the continued growth of traffic to the system).</p><p>If you enjoy solving these types of technical problems and having incredibly high impact, you might enjoy working with us at Motion! <a href="https://www.usemotion.com/careers">We’re hiring!</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3c93dff9c65d" width="1" height="1" alt=""><hr><p><a href="https://engineering.usemotion.com/migrating-to-postgres-3c93dff9c65d">Migrating to Postgres</a> was originally published in <a href="https://engineering.usemotion.com/">Motion Engineering Blog</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>