<?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 Jay Patel on Medium]]></title>
        <description><![CDATA[Stories by Jay Patel on Medium]]></description>
        <link>https://medium.com/@pjay5334?source=rss-ca7aad95f241------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*TxbUZmchqWbKU_cVv1SY8A.jpeg</url>
            <title>Stories by Jay Patel on Medium</title>
            <link>https://medium.com/@pjay5334?source=rss-ca7aad95f241------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 13:45:25 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@pjay5334/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[Beyond Test Cards: A Fake Payment Provider for Testing at Scale]]></title>
            <link>https://eng.lyft.com/beyond-test-cards-a-fake-payment-provider-for-testing-at-scale-c69dddadb115?source=rss-ca7aad95f241------2</link>
            <guid isPermaLink="false">https://medium.com/p/c69dddadb115</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[payments]]></category>
            <category><![CDATA[fintech]]></category>
            <category><![CDATA[load-testing]]></category>
            <category><![CDATA[infrastructure]]></category>
            <dc:creator><![CDATA[Jay Patel]]></dc:creator>
            <pubDate>Tue, 10 Nov 2026 20:55:38 GMT</pubDate>
            <atom:updated>2026-11-10T20:55:38.930Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EV9F9SVDWLCKZJ_hghiIRg.jpeg" /><figcaption>Image generated with Google Gemini, 2026</figcaption></figure><p><em>Written by </em><a href="https://www.linkedin.com/in/saveliy-kolesnikov/"><strong>Savelii Kolesnikov</strong></a><strong>, </strong><a href="https://www.linkedin.com/in/jay-p-73461749/"><strong>Jay Patel</strong></a>, and <a href="https://ua.linkedin.com/in/ivanna-sivak/uk"><strong>Ivanna Sivak</strong></a></p><h3>Background</h3><p>Lyft processes billions in payments annually, and that volume continues to grow. Agentic coding has drastically accelerated feature development, but it raises the stakes for the one phase that cannot be rushed: ensuring the highest standards of reliability and correctness in critical payment systems.</p><p>Every payment platform eventually hits the same wall. Unit tests cover business rules, integration tests validate boundaries, and load tests push upstream limits, but standard testing stops the moment a request reaches a third-party processor.</p><p>Processor test cards are built for simple flows: a card either passes every payment action, from authorization to capture to refund, or fails them all. Real payment flows often have one step fail while the others succeed.</p><p>In this post, we’ll explain why test cards break down and how we built our own fake payment provider to simulate any processor behavior on demand. Unlike a typical mock, it runs safely in production, so we can load test our payment stack and trial new workflows on simulated riders before real ones.</p><h3>Test cards don’t get you there with rideshare</h3><p>Unlike paying for a product on an e-commerce website or your favorite streaming platform, Lyft, as a rideshare company, has a unique set of challenges with payments. While we can put a hold on the card on a ride request, the final charge does not always match the original hold amount on the card. We can use test cards provided by processors; however, they either succeed with all charge actions (authorization, increment, capture, direct charge, refund, etc.) or fail them all. We hit a wall if we want to test the following scenarios:</p><ul><li>A card that succeeds auth, fails capture, and then succeeds on the direct charge fallback</li><li>A card that succeeds every charge flow but fails on refund</li><li>A card that succeeds auth and increment, fails capture at the incremented amount, and succeeds when we fall back to capturing the original auth amount</li><li>The processor itself is down</li><li>The processor is still processing the charge and returns a non-terminal (pending) response</li><li>The processor returns a response, but our services have timed out</li></ul><p>None of the above scenarios are exotic. These are the exact paths our retry and fallback code was written for, and they were hard to exercise before production.</p><h3>What the limitations of test cards cost us</h3><p>These limitations cost us across the company. Without realistic failure paths, validating our own retry and fallback logic was hard. Anything touching intermediate payment failures was difficult to validate. Our end-to-end acceptance tests could only cover all success or all failure, never the middle. And because test cards are shackled to processor sandboxes, our <a href="https://eng.lyft.com/simulatedrides-how-lyft-uses-load-testing-to-ensure-reliable-service-during-peak-events-644dcb654454">simulated rides load testing framework</a> had to bypass the payment flow entirely. We’ll come back to this.</p><p>Every one of those costs points at the same root cause: the thing standing between us and confidence lives behind a processor’s API, on a release cycle we don’t control. The first instinct is to ask the processors themselves to fix it — better sandboxes, more test cards, more scenarios. That doesn’t scale. We work with several processors, each with different sandbox coverage, and sandboxes aren’t built to model our retry and fallback logic. We’d be maintaining a compatibility matrix instead of a test suite, one column per processor.</p><p>Instead of continuing to work around the sandboxes provided to us, we decided to take full control of our testing environment by building our own solution — a Fake Payment Provider designed specifically to meet the complex needs of rideshare testing.</p><h3>Architecture</h3><h3>Why build our own Fake Payment Provider</h3><p>The constraint that had been making our lives hard turned out to be the thing that saved us. Working with several processors forces you to hide all of them behind one interface, and an interface is something we could implement ourselves.</p><p>They are different companies with different APIs and their own quirks. Strip all of that away and they do the same handful of things. Tokenize. Authorize. Increment. Capture. Charge. Refund. Void.</p><p>So adding a Fake Provider is easy to describe. Implement those methods. Route some users to it. Done.</p><p>And that really is most of the design. The router resolves a provider and calls it. Nothing above the provider layer knows that this one never opens a socket. Because every processor’s response is already translated into one internal representation, the fake doesn’t have to mimic any processor’s API. It only has to produce outcomes our platform already understands. That boundary cuts both ways. Because the fake sits behind the provider interface, it never exercises the code that talks to each processor: building requests, parsing responses, and mapping their errors into ours. Those paths still rely on processor sandboxes, and closing that gap with record and replay is next on our list.</p><p>Easy to describe is not the same as easy to build. Translating this concept into practice required addressing three key questions:</p><p>◾ Access Control: Who is allowed to reach the fake provider?</p><p>◾ State Persistence: Where does the fake keep its scenario behavior across calls?</p><p>◾ Scenario Modeling: How is that test behavior actually defined and configured?</p><h3>Access Control: Only simulated users get to touch it</h3><p>Everything to do with the fake payment provider stays inside a fenced-off group of test users. In our system, they are called simulatedusers. The fake Low Value Token (LVT) is generated by us and accepted only for simulated test accounts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j29EwLgEIgKPP5a5JHaE-A.png" /><figcaption>Routing rules: the fake provider is only ever a candidate for simulated users</figcaption></figure><p>Notice what is not in that picture. The router has no test mode and no special case. It has rules, and for a real rider the fake never becomes a candidate in the first place. Multiple independent checks across our services ensure fake payment methods can only ever be used by the internal simulated test accounts.</p><h3>State Persistence: The scenario rides inside the token</h3><p>A processor that is not real still has to remember how it intends to behave. Which scenario are we reproducing right now? What should the next action do? What comes back for each one?</p><p>That is state, and state has to live somewhere. There were three places to put it.</p><p>◾ A new service. More infrastructure than the milestone justified. Worse than that, it would have been a data path production does not have. We would have been testing a system that does not exist.</p><p>◾ The request. A magic amount. A header. A field called test_behavior. This is the obvious one, and it breaks on the second call. Captures and refunds are started by other services. Retries arrive hours later and read their state from the ledger. Nobody is carrying your header by then. Making it work would mean every service on the path forwarding a test-only field, which is exactly the parallel test-only path we were trying not to build.</p><p>◾ The token. We already had something that travels the whole path and gets persisted on the way. So the scenario went into the token.</p><blockquote>A short detour, because the rest of this section leans on it. Payment systems try hard never to hold a card number, so every card gets swapped for a <a href="https://en.wikipedia.org/wiki/Tokenization_(data_security)">token</a>, a stand-in string. Think of a hotel. Your booking reference can’t open a single door, but the front desk swaps it for a key card that opens yours for the rest of the stay and says nothing about who you are. Card tokens work the same way. When you add a card, the app receives the booking reference: an LVT, which on its own cannot charge anything. The tokenization service plays the front desk and swaps it for the key card: a high value token, or HVT, which gets stored and used for every charge after that.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1Ks0JRysJbr97tASoJDyJg.jpeg" /><figcaption>Decoding a fake LVT: card details plus a scenario name</figcaption></figure><p>You hand that to the wallet service the same way you would hand over a real card LVT. The tokenization service decodes it, mints a high value token, and stores it exactly as it stores a token from a real processor.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7-jjF_B3UpsKZRBn_9E3bA.png" /><figcaption>Adding a card: from card to token</figcaption></figure><p>Two nice properties came out of this. The outcome became a property of the instrument rather than the transaction. That is how a real card behaves. And follow on operations read the scenario from the parent authorization rather than from the token, so the scenario cannot change halfway through a charge lifecycle.</p><h3>Scenario Modeling: A scenario is just a dictionary</h3><p>At charge time the provider decodes the scenario name and looks it up. A scenario is a map from action to an ActionSpec. The spec says whether the action succeeds, how slow it is, and what error comes back when it fails.</p><p>Failures also carry a network decline code, a two-digit number like 51 for insufficient funds or 05 for do not honor. The platform reads it to decide what to do next, including whether a charge is worth retrying on a different processor.</p><pre>Charge.Action.AUTH: PayinActionSpec(<br>    success_predicate=ACTION_FAILURE,<br>    error_code=&quot;insufficient_funds&quot;,<br>    error_message=&quot;Auth declined&quot;,<br>    network_decline_code=&quot;51&quot;,<br>    delay_ms=100,<br>    retry_specs=...<br>),</pre><p>Success is a predicate and not a flag.</p><pre>ACTION_SUCCESS: Callable[[&#39;PayinActionContext&#39;], bool] = lambda ctx: True<br>ACTION_FAILURE: Callable[[&#39;PayinActionContext&#39;], bool] = lambda ctx: False<br>ACTION_CHAOS: Callable[[&#39;PayinActionContext&#39;], bool] = lambda ctx: random.random() &lt; 0.5</pre><p>Two of those are boring on purpose. The third one is not. ACTION_CHAOS flips a coin on every call. A cleanly broken processor is easy to write code for. The one that fails half the time is what actually hurts you at three in the morning, and now we can summon it whenever we like.</p><p>High latency is a scenario, too. Set <strong>delay_ms</strong> past the caller’s timeout and you get the scariest case of all: the processor succeeded, but we gave up waiting. Now we can test what happens next. The fake doesn’t try to be clever about it. It sleeps, succeeds, and forgets. Everything after that, the caller retrying and the platform reconciling what actually happened, runs through our real payment code, which is exactly the code we wanted to test.</p><p>The part we like most is <strong>retries</strong>. An ActionSpec can carry a map of ActionSpecs keyed by attempt number. The model refers to itself, and that recursion is the whole retry mechanism.</p><p>Below is a complete card. It authorizes cleanly. It fails the capture once. It succeeds when the platform tries again.</p><pre>SOFT_DECLINE_RETRY_SUCCESS_SCENARIO = PayinFakeScenario(<br>    name=&quot;card:fake_provider:soft_decline_retry_success&quot;,<br>    actions={<br>        # Step 1: AUTH succeeds<br>        Charge.Action.AUTH: PayinActionSpec(<br>            success_predicate=ACTION_SUCCESS,<br>            delay_ms=100<br>        ),</pre><pre>        # Step 2 &amp; 3: CAPTURE fails on first attempt, succeeds on retry<br>        Charge.Action.AUTH_CAPTURE: PayinActionSpec(<br>            success_predicate=ACTION_FAILURE,<br>            error_code=&quot;transient_error&quot;,<br>            error_message=&quot;Temporary processing error&quot;,<br>            network_decline_code=&quot;91&quot;,<br>            delay_ms=80,<br>            retry_specs={<br>                2: PayinActionSpec(<br>                    success_predicate=ACTION_SUCCESS,<br>                    delay_ms=100<br>                )<br>            }<br>        )<br>    }<br>)</pre><p>Read it top to bottom, and you have the entire card. Reproducing that on demand in a processor sandbox is not something sandboxes are built for. In our case, it is a few dictionary entries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RITNGu3feLCUnxInqiDyLw.png" /><figcaption>Each charge: decode the token, look up the scenario, pick the spec for this attempt</figcaption></figure><p>The provider decides nothing. It decodes the token, looks up the scenario, picks the spec for this action, and checks which attempt this is. Note where that attempt number comes from. It is read off the stored charge and not passed in by whoever is calling.</p><p>So that is the architecture. A provider anyone can plug in. A fence only simulatedusers cross. A dictionary that decides what happens next.</p><p>The interesting part is what people started doing with it.</p><h3>What does Fake Provider unlock for us?</h3><h3>Staging: Pick your fake scenario from a menu</h3><p>Here is the part that changed how we work day to day. You open the rider CLI. You make yourself a simulated rider. You run one command and a list comes up.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/892/1*91stRQDmMRVbsMV51PXJEw.png" /><figcaption>Choosing a fake scenario from the rider CLI</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HgtugChBeQLkFX9t7nWkjQ.png" /><figcaption>Selecting a card that declines every authorization</figcaption></figure><p>Three checkmarks and you are done. That rider now has a card that declines every authorization, and you did not have to ask anybody for it. No sandbox credentials. No waiting on a processor. No pinging someone on our team to find out which test card number does the thing you want.</p><p>So what do you do when the one you need is not among them?</p><p>◾ You are an engineer working on the retry path. You need a capture that fails once and then succeeds. That one is on the list. You need one that fails twice and then succeeds. That one is not.</p><p>◾ You are a quality engineer testing some edge case where the authorization is declined and the direct charge goes through anyway. Very specific. Almost certainly not on the list.</p><p>◾ You are a product manager and you want to see your own flow the way a rider sees it when the card is refused. Or a marketer who wants to test an offer tied to a specific card issuer.</p><p>Before AI, the answer was to go and write the scenario yourself. Define the scenario. Register it. Add a card payload with a fresh number. Generate the token. Add it to the menu. Five steps in a repository you have probably never opened.</p><p>Now the answer is a skill. Open the repository that holds the scenarios. Run the skill that adds one. Describe the card you want in a sentence.</p><pre>&gt; /fake-provider-add-scenario</pre><pre>  I need a card where the authorization succeeds but the capture fails twice<br>  with a soft decline and then goes through on the third attempt.</pre><p>It writes all of it! The AI generated scenarios still go through a human code review.</p><p>Your part of that takes a minute. One sentence is the whole contribution. Everything after it is the agent walking the repositories that Fake Provider is spread across and writing the same five things you would have written by hand.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aHYyE7BThQB3QQq5SfvkxQ.png" /><figcaption>One sentence to the /fake-provider-add-scenario skill creates a new scenario</figcaption></figure><p>That is a good place to be, but it is not where we want to stop. All of that work only exists because scenarios ship as library code. Turn Fake Provider into a service and a new card becomes data instead of a config change in multiple repositories.</p><h3>Production: Load test your services</h3><p>How do you stress-test a payment system when you can’t charge real credit cards?</p><p>Payment services were previously outside the scope of our load tests. Simulated rides short-circuited before they ever reached a real charge. No authorizations fired. No charges processed.</p><p>Fake Provider changed that entirely. By provisioning simulated users with a fake card, simulated rides now flow through the entire stack. Fake Provider transactions are tagged and kept separate from real payment activity.</p><p>This approach can answer questions we simply couldn’t before:</p><p>◾ Does the charge process slow down or start timing out once real load hits it?</p><p>◾ Is there a sane, deliberate limit on how long the system waits before giving up on a slow request?</p><p>◾ Does something that works in a developer’s local setup behave the same way once real production traffic routes through it?</p><p>◾ Where is the charge-lifecycle latency ceiling?</p><p>◾ Does auth/capture keep pace, or do retries start stacking under load?</p><p>◾ Does a fraud/risk check become the bottleneck, or does a rate limiter just mask the real capacity ceiling?</p><p>But how do you set up hundreds or thousands of users in advance for load testing? Well, every test needs the same starting point: a user who already has a payment method that works. Create a brand new one for every single run and you’re testing account setup, not payments. So there’s a pool instead — a set of test users, reused run after run.</p><p>Pools drift, though. A card expires. A test leaves a user mid-scenario and never puts them back. Something changes upstream and a card that used to work stops working. Left alone, a pool slowly rots, and the first symptom is usually a test failing for a reason that has nothing to do with the code anyone just shipped.</p><p>Here comes the Resource Manager service that we mentioned in the previous <a href="https://eng.lyft.com/simulatedrides-how-lyft-uses-load-testing-to-ensure-reliable-service-during-peak-events-644dcb654454#:~:text=An%20additional%20Resource%20Manager%20service%20exposes%20an%20interface%20for%20using%20test%20assets%20in%20simulations">article</a>. It constantly runs health checks and automatically fixes issues. Since we want load tests and other scenarios to actually succeed, we configured the fixer to make a fake-success card the default payment method for these users. Still, we take a selective approach: we can leave this check off for pools meant to test failed transactions or other corner cases. The health check’s configuration works less like one global rule and more like a per-pool setting — different pools, different defaults. It lets us get rid of old cards gradually and replace them with fake ones for simulated test users only.</p><p>Adding payments to simulated rides meaningfully increased load on our critical payment services in production. This let us verify that both our application tier and our datastores scale smoothly under the surge: autoscaling responded to demand without throttling for DynamoDB, and connection pools stayed healthy for Aurora Postgres. For the flows we pushed the hardest (authorization, capture, and direct charges), scaling kept pace end to end.</p><h3>Production: Test new workflows</h3><p>A new payment workflow — a new retry strategy, a new fallback path, a new integration with a processor — can pass every test in staging and still behave differently in production, where the surrounding infrastructure isn’t the same. Because simulatedusers can be routed through Fake Provider even in production, we can point a new workflow at simulated riders first and let them exercise the exact new code path end to end — same services, same config, same scaling behavior real riders will hit, all without a real card or a real dollar involved. If the new capture-retry logic has a bug, our simulation platform finds it, not a rider.</p><p>This gives us a few things we didn’t have before:</p><p>◾ A canary population to test our new workflow on in production before we’re confident enough to expose it to real traffic.</p><p>◾ The ability to pick the scenario the simulateduser rides through — a soft decline, a slow processor, a chaos card — so the canary isn’t just “does it work”; it’s “does it work under the specific failure mode this workflow was built to handle.”</p><p>◾ A gate before graduation: a new workflow only gets promoted to real riders once it’s been proven out against simulated ones running the same code in the same environment.</p><p>In practice, this changes how we roll out significant payment changes. We exercise the new path with simulated riders and load test it at scale first, so by the time real riders ramp on, the workflow has already handled production-level load.</p><h3>Looking ahead</h3><p>Fake Provider has already changed how we test payments: broader failure coverage in staging (manual and automated), payment services included in load tests, and a safe place for new workflows to prove themselves before real riders see them. But every scenario today lives as a dictionary entry in library code, spread across a handful of repositories. That’s been fast to build and easy to reason about, and it’s also the ceiling. As the list of scenarios grows and more teams want to define their own, “a few dictionary entries in a repo you have to know about” turns from a feature into a bottleneck. The next step is turning Fake Provider from a library into a service — a new scenario becomes a piece of data submitted through an API, not a change reviewed and deployed across multiple repositories. That shift is what opens up the rest of this list:</p><p>◾ <strong>Record and replay: </strong>Capture real request/response pairs from the processor’s sandbox environment and replay them as scenarios, so Fake Provider’s behavior tracks what processors actually do, including the quirks a hand-written approximation tends to miss.</p><p>◾<strong> Async completion notifications: </strong>Fire the delayed webhook that carries a processor’s real outcome on a schedule we control (on time, late, out of order, or never), so we can exercise the code that has to handle all of it.</p><p>◾<strong> New payment methods: </strong>Simulate payment methods beyond cards.</p><p>◾ <strong>Disputes: </strong>Simulate the full chargeback lifecycle (chargeback, evidence submission, resolution), the one flow we still can’t fully test outside a real processor sandbox.</p><p>◾ <strong>Card updates: </strong>Simulate a card-updater feed landing mid-lifecycle, the way real processors tell us about an expired or reissued card.</p><p>◾ <strong>Rate limits and outages: </strong>Simulate a processor rate limiting us or going down entirely, so fallback routing and circuit breakers get validated under real pressure.</p><p>Stay tuned as we build out the next iteration of payments resilience!</p><h3>Acknowledgements</h3><p>We would like to thank the DevEnvs team (Maura Killeen, Levi Meng, Roy Tang, Chelsea Xu, and Alex You) for their huge support in turning simulated rides into real load on the payment stack.</p><p><em>Lyft is hiring! If you love the idea of solving ambiguous problems in technology and infrastructure, visit</em><a href="https://www.lyft.com/careers"><em> Lyft Careers</em></a><em> to see our openings.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c69dddadb115" width="1" height="1" alt=""><hr><p><a href="https://eng.lyft.com/beyond-test-cards-a-fake-payment-provider-for-testing-at-scale-c69dddadb115">Beyond Test Cards: A Fake Payment Provider for Testing at Scale</a> was originally published in <a href="https://eng.lyft.com/">Lyft Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[From Python 3.8 to Python 3.10: Our Journey Through a Memory Leak]]></title>
            <link>https://eng.lyft.com/from-python3-8-to-python3-10-our-journey-through-a-memory-leak-1fd9b43cc01e?source=rss-ca7aad95f241------2</link>
            <guid isPermaLink="false">https://medium.com/p/1fd9b43cc01e</guid>
            <category><![CDATA[memory-leak]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[infrastructure]]></category>
            <dc:creator><![CDATA[Jay Patel]]></dc:creator>
            <pubDate>Mon, 15 Dec 2025 19:30:48 GMT</pubDate>
            <atom:updated>2026-11-04T03:29:03.694Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Image generated with ChatGPT (OpenAI), 2025." src="https://cdn-images-1.medium.com/max/1024/1*QWBsfrlv7BNM8sZwFAsTfQ.png" /><figcaption><em>Image generated with ChatGPT (OpenAI), 2025.</em></figcaption></figure><h3>Intro</h3><p>When working with Python, memory management often feels like a solved problem. The garbage collector quietly does its job, and unlike C or C++, we rarely think about malloc or free. This doesn’t mean that there are no memory leaks in Python. Reference cycles, unreleased resources like connection pooling, global caches, etc can slowly inflate your process’s memory footprint. You might not notice it at first, until your worker starts OOM-ing, latency creeps up, or container restarts become mysteriously frequent.</p><p>In this post, we’ll share the story of a real-world memory leak we encountered during a Python upgrade — how we discovered it, the tools and techniques we used to investigate, and the lessons we learned.</p><h3>What happened after upgrading to Python 3.10?</h3><p>Back in the summer of 2024, we had an initiative at Lyft to upgrade all of our Python services from v3.8 to 3.10 as v3.8 was scheduled to be EoL by the end of 2024. You can find more details on how our awesome Backend Foundations team at Lyft does Python upgrade across hundreds of repos at scale <a href="https://eng.lyft.com/python-upgrade-playbook-1479145d52f4">here</a>. The upgrade involved two phases: the first phase was to upgrade all the dependencies to be Python 3.10 compatible, and the second phase was to upgrade the services to Python 3.10. The dependency upgrades went smoothly for all services and then the phase to upgrade all services to Python 3.10 rolled out. While all services were running Python 3.10 smoothly, there was one service for which the upgrade in the test environment caused a flurry of latency spikes, resulting in timeouts for downstream services.</p><figure><img alt="Graph: Increasing 5xx caused by timeouts after upgrading to Python 3.10" src="https://cdn-images-1.medium.com/max/1024/1*eZCQ8TRmMvT5_wSFiksPrA.png" /><figcaption><em>Increasing 5xx caused by timeouts after upgrading to Python 3.10</em></figcaption></figure><p>After profiling the APIs with increased latency with stats, we found that the source of latency were repository queries to the dynamo tables. Specifically, we had <a href="https://pynamodb.readthedocs.io/en/stable/">pynamodb</a> based repository queries which would spin up a bunch of greenlets to fetch data from multiple tables and combine the result which was showing increased timeouts. The individual queries themselves were fine; however, it was the thread join which took the longest time causing the worker to timeout (default = 30 seconds).</p><figure><img alt="Graph: Individual Dynamo queries taking &lt; 100 ms to finish" src="https://cdn-images-1.medium.com/max/1024/1*1Nh7ZK1j2fI0fWNNw9hUew.png" /><figcaption><em>Individual Dynamo queries taking &lt; 100 ms to finish</em></figcaption></figure><figure><img alt="Graph: Gevent thread join takes 30 secs" src="https://cdn-images-1.medium.com/max/1024/1*rOsCnNVg66HEXNy1HqGCIg.png" /><figcaption>Gevent thread join takes 30 secs</figcaption></figure><p>The other interesting thing we found was memory consumption slowly creeping up with time in all of the pods.</p><figure><img alt="Graph: Memory usage % of all pods" src="https://cdn-images-1.medium.com/max/1024/1*eTRt4KtT7amhI-lDska_rQ.png" /><figcaption>Memory usage % of all pods</figcaption></figure><p>At this point, we weren’t sure if there was something up with gevent/greenlet causing the memory leak or the memory leak causing the latency since decreased memory availability can cause increasing page fetches from the disk. We first checked if the <a href="https://www.gevent.org/monitoring.html#blocking">gevent monitoring thread</a> detected any event loop blocks, which could potentially cause these timeouts. We then pivoted to find out the root cause of the memory leak. Fortunately, Lyft has an internal library which can help profile memory which is based on <a href="https://docs.python.org/3/library/tracemalloc.html">tracemalloc</a>.</p><h3>Memory profiling tool</h3><p>The Lyft memory profiling tool is based on <a href="https://docs.python.org/3/library/tracemalloc.html">tracemalloc</a>. To capture the memory trace for a given gunicorn process, we registered the worker process to listen to USR2 signal during the application initialization phase.</p><pre># app/__init__.py<br><br>MemoryProfiler().register_handlers()<br><br># mem_profiler.py pseudo code<br><br>class MemoryProfiler: <br>    def __init__(self) -&gt; None:<br>        self._state_machine = self._profiling_state_machine()<br><br>     def register_handlers(self) -&gt; None:<br>        # Register gunicorn worker to listen to USR2 to dump traces<br>        signal.signal(signal.SIGUSR2, self.handle_signal)  <br><br>    def handle_signal(self, signum: signal.Signals, frame: FrameType) -&gt; None:<br>        next(self._state_machine)<br><br>    def _profiling_state_machine(self) -&gt; Generator[None, None, None]:<br>        while True:<br>            try:<br>                self.start_tracing() # tracemalloc.start()<br>                self.memory_dump() # Create snapshot1<br>                yield<br>                self.memory_dump() # Create snaphot2,compare with snapshot1, and dump the difference in a file <br>            finally:<br>                if tracemalloc.is_tracing():<br>                    tracemalloc.stop()</pre><h3>Let’s start the tracing!</h3><p>Ok, now that we had the memory profiler setup, we are ready for some tracing to find the source of the leak. To start the tracing, we send <strong>USR2</strong> signal to the gunicorn process in the K8s pod to start tracing and send the signal again after some time interval to capture the stack trace with highest memory usage.</p><pre>ps aux</pre><figure><img alt="Command line output: Initial process list before sending USR2 signal" src="https://cdn-images-1.medium.com/max/1024/1*LWqnkq8g2_BB5cXEBuyozg.png" /><figcaption><em>Initial process list before sending USR2 signal</em></figcaption></figure><p>Now, we will send a USR2 signal to worker with pid 12</p><pre>kill -USR2 12</pre><p>Upon checking the process list again….</p><pre>ps aux</pre><figure><img alt="Command line output: Tracing killing the gunicorn worker with PID=12" src="https://cdn-images-1.medium.com/max/1024/1*27fhy9fAMFix-1-BorV4pA.png" /><figcaption><em>Tracing killing the gunicorn worker with PID=12</em></figcaption></figure><p>… <strong>we observed that the gunicorn process we planned to trace got killed </strong>🙁</p><p>It took several hours of debugging and a journey back to one of my <a href="https://dartmouth.smartcatalogiq.com/en/2023/orc/departments-programs-undergraduate/computer-science/cosc-computer-science-undergraduate/cosc-58">favorite class</a> to find the root of the issue — <a href="https://docs.gunicorn.org/en/stable/settings.html#preload-app">preload</a>. To understand why preload caused the process to be killed, we first need to understand how gunicorn works.</p><h3>Gunicorn</h3><p><a href="https://gunicorn.org/">Gunicorn</a> works on the pre-fork model. There is a leader process which forks a bunch of workers. There are two ways to fork the workers:</p><p><strong>No Preload</strong></p><figure><img alt="Graph: Gunicorn forked workers with no preload" src="https://cdn-images-1.medium.com/max/1024/1*GIDNjLI8fwZoU7vTSsA6Og.png" /><figcaption><em>Gunicorn forked workers with no preload</em></figcaption></figure><p>When the leader process forks a worker, the worker has its own application code. This results in the worker process having a larger memory footprint than the leader.</p><pre>smem -a - sort=pid -k</pre><figure><img alt="Command line output: Service with no preload: Worker PSS mem = ~203MB" src="https://cdn-images-1.medium.com/max/1024/1*3CskCAq2yV0Lz2RKHAUhow.png" /><figcaption><em>Service with no preload: Worker PSS mem = ~203MB</em></figcaption></figure><p><strong>With Preload</strong></p><figure><img alt="Graph: Gunicorn forked workers with preload" src="https://cdn-images-1.medium.com/max/1024/1*xK8Eol5qNjHq5XRHV-FHqQ.png" /><figcaption><em>Gunicorn forked workers with preload</em></figcaption></figure><p>Preload is a memory optimization based on the concept of<a href="https://en.wikipedia.org/wiki/Copy-on-write"> copy-on-write</a>. Essentially, the workers share the imports and application code with the leader and only modified pages are written to the worker’s memory.</p><pre>smem -a - sort=pid -k</pre><figure><img alt="Command line output: Service with preload: Worker PSS mem reduced to ~41MB!!" src="https://cdn-images-1.medium.com/max/1024/1*howZgiwphM8tW7Yz5P5P2Q.png" /><figcaption><em>Service with preload: Worker PSS mem reduced to ~41MB!!</em></figcaption></figure><p><strong>So how does preload play a role with USR2 signal killing the process?</strong></p><p>If you remember, we registered the signal during the app initialization by calling <strong>register_handlers().</strong></p><pre># app/__init__.py<br><br>MemoryProfiler().register_handlers()  <br><br><br># mem_profiler.py<br><br>class MemoryProfiler:<br><br>    def register_handlers(self) -&gt; None:<br>        # Register gunicorn worker to listen to USR2 to dump traces<br>        signal.signal(signal.SIGUSR2, self.handle_signal)</pre><p>Since the app had preload=True, only the leader process was registering the USR2 signal to handle the tracing. The worker process did not register due to copy-on-write and that causes any <strong>kill -USR2</strong> to actually kill the process!</p><h3>Let’s start the tracing again (with no preload)!</h3><p>Now that we have figured out that preload caused the process to be killed, we turn off the preload option and start the tracing again.</p><pre>ps aux </pre><figure><img alt="Command line output: Initial process list before sending USR2 signal" src="https://cdn-images-1.medium.com/max/1024/1*WWqAGDxKxuOaC7xd9CpZ3A.png" /><figcaption><em>Initial process list before sending USR2 signal</em></figcaption></figure><pre>kill -USR2 12</pre><figure><img alt="Command line output: Successful USR2 signal not killing the gunicorn worker" src="https://cdn-images-1.medium.com/max/1024/1*LtPhlX5-R_IWxkIovsQR5w.png" /><figcaption><em>Successful USR2 signal not killing the gunicorn worker</em></figcaption></figure><p>The worker does not get killed!</p><p>We created a script which iterates through all the K8s pods and sends a USR2 signal to all the workers to start the tracing and resends the signal to stop the tracing after a certain time interval. The trace had a lot of false positives since it collects dumps which may not necessarily be the source of the leak, but have not been garbage collected yet.</p><h3>Root causing</h3><p>The most interesting (and common) memory dump trace after sifting through hundreds of them was the following:</p><figure><img alt="Stack trace dump from memory profiler" src="https://cdn-images-1.medium.com/max/1024/1*mFiA_yzHXa2mzw463nnO5Q.png" /><figcaption><em>Stack trace dump from memory profiler</em></figcaption></figure><p>If you remember the initial conclusion we had with the following graph, we knew that the increase in timeouts had something to do with pynamodb and gevent/greenlets since we saw thread joins taking a long time:</p><figure><img alt="Graph: Our initial observation of gevent thread join takes 30 secs" src="https://cdn-images-1.medium.com/max/1024/1*rOsCnNVg66HEXNy1HqGCIg.png" /><figcaption>Our initial observation of <em>gevent thread join takes 30 secs</em></figcaption></figure><p>The stack trace combined with the graph above, narrowed down the issue to pynamo/botocore. After digging online, we found the following <a href="https://github.com/urllib3/urllib3/issues/3061">issue with urllib3 v1.26.16</a>. Essentially, in a highly concurrent environment using <strong>gevent</strong>, connections were not being returned to the pool which caused the pool to sit at its max size and block further requests. This particular stack trace confirmed our suspicion:</p><figure><img alt="Stack trace showing botocore/urllib3/connectionpool" src="https://cdn-images-1.medium.com/max/1024/1*wcV7ev9Zs45RSLesKlQsfw.png" /><figcaption>Stack trace showing botocore/urllib3/connectionpool</figcaption></figure><p>The root cause of the issue was some incompatibility between <a href="https://docs.python.org/3/library/weakref.html"><strong>weakref.finalize</strong></a> and gevent’s monkey patching causing non-deterministic deadlock which made the issue hard to reproduce. The immediate fix was to downgrade the urllib3 version to <strong>1.26.15</strong>, after which the timeouts and the memory leak were gone!! <strong>The actual fix which ensures urllib3 connection pooling is cooperative was released in April 2025 and we have seen no issues upgrading both gevent to </strong><a href="https://github.com/gevent/gevent/issues/1769"><strong>v25.4.1</strong></a><strong> as well as urllib3 to 1.26.16+.</strong></p><p>It is unclear though why the Python version upgrade exposed the issue. <strong>In fact, urllib3 upgrade was not part of the dependency upgrade we had done to prepare for the Python 3.10 upgrade! </strong>We had actually been running Python 3.8 with urllib v1.26.16 for about a year without any problem. Ironically, we had upgraded to v1.26.16 specifically because it <a href="https://pypi.org/project/urllib3/1.26.16/">logged</a> the total connections whenever connection pools were full.</p><h3>Tip</h3><ol><li>If you run into memory leaks which are affecting the live production system, you can use gunicorn’s <a href="https://docs.gunicorn.org/en/stable/settings.html#max-requests"><strong>max-request</strong> </a>settings which recycles the worker processes after N requests. This ensures your process or container does not run into OOM. While this helps mitigate the issue, it is critical to continue investigating the source of the memory leak.</li><li>Gevent <a href="https://www.gevent.org/monitoring.html#memory-usage">monitoring thread</a> has an option to print trace for greenlets which exceed a certain memory threshold. While I have personally never tried this, it could help find objects which are holding large amounts of memory, but not necessarily the source of a leak.</li></ol><h3><strong>Closing Notes</strong></h3><p><strong>There is no silver bullet to debugging memory leaks</strong>; it is a hard issue to debug them. There a few things you can look for eg. unbounded global caches, unreleased resources tied to database/network pooling, recently upgraded libraries, etc. If you check the actual gevent/urllib3 <a href="https://github.com/urllib3/urllib3/issues/3061">issue</a>, none of them talked about memory leaks, only timeouts. We just happened to run into a memory leak and try to find the root cause of it 😀</p><p>Lyft is hiring! If you’re passionate about efficient database connection management, visit <a href="https://www.lyft.com/careers">Lyft Careers</a> to see our openings.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1fd9b43cc01e" width="1" height="1" alt=""><hr><p><a href="https://eng.lyft.com/from-python3-8-to-python3-10-our-journey-through-a-memory-leak-1fd9b43cc01e">From Python 3.8 to Python 3.10: Our Journey Through a Memory Leak</a> was originally published in <a href="https://eng.lyft.com/">Lyft Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Beyond Query Optimization: Aurora Postgres Connection Pooling with SQLAlchemy & RDSProxy]]></title>
            <link>https://eng.lyft.com/beyond-query-optimization-aurora-postgres-connection-pooling-with-sqlalchemy-rdsproxy-200db7f562d7?source=rss-ca7aad95f241------2</link>
            <guid isPermaLink="false">https://medium.com/p/200db7f562d7</guid>
            <category><![CDATA[connection-pooling]]></category>
            <category><![CDATA[infrastructure]]></category>
            <category><![CDATA[aurora]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Jay Patel]]></dc:creator>
            <pubDate>Tue, 20 May 2025 13:04:18 GMT</pubDate>
            <atom:updated>2025-05-20T15:23:28.960Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Written by </em><a href="https://www.linkedin.com/in/jay-p-73461749/"><em>Jay Patel</em></a><em> and </em><a href="https://www.linkedin.com/in/crestonjamison/"><em>Creston Jamison</em></a><em>.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/848/1*8-pRKqJFIeOYAbomjyIL8g.png" /><figcaption>Photo by <a href="https://commons.wikimedia.org/wiki/User:RRZEicons">RRZEicons</a> UNDER <a href="https://creativecommons.org/licenses/by-sa/3.0/deed.en">CC BY-SA 3.0 DEED</a></figcaption></figure><p>Efficient database connection management is often overlooked when optimizing database performance and scaling. When we think about database performance, we focus on query optimization and indexing while underestimating the impact of managing database connections effectively. Without connection pooling, each request may incur the costly overhead of repeatedly opening and closing connections, leading to performance bottlenecks and resource exhaustion. In modern web scale applications, a well-implemented connection pool can mean the difference between smooth scalability and crippling database overload. At Lyft, we saw several benefits of adopting a proxy based connection pooler which we will discuss extensively in this blog post.</p><h3>Connection Pooling</h3><h4>What is connection pooling?</h4><p>In an ideal world, our application process or threads could scale infinitely and each have a dedicated database (DB) connection. In reality, this would be wildly inefficient and a waste of resources. Yes, application threads frequently use database connections, but that is not all they do. If an application thread is making an API call to an external service, it is not doing any database work. Therefore, it is more efficient for these database connections to be shared amongst your application threads so the DB connections can only be used when needed.</p><p>It is also advantageous to minimize the number of connections to a relational database like PostgreSQL. The consistency Postgres guarantees relies on locking and shared memory structures. Therefore, fewer connections to the database allow less contention for these limited resources.</p><p>This is where connection pooling comes in. It creates a pool of connections that your application threads can “check-out” and use when needed. After a database call is complete, the application thread returns the connection back to the pool.</p><h4>Why use connection pooling?</h4><ol><li><strong>Reduces Connection Overhead:</strong> Opening and closing database connections is expensive in terms of time and resources. A connection pool keeps a set of ready-to-use connections, avoiding the need to repeatedly establish and tear down connections.</li><li><strong>Improves Performance &amp; Latency:</strong> By reusing existing connections, applications can serve requests faster, reducing the time spent waiting for new database connections to be established.</li><li><strong>Controls the Number of Concurrent Connections:</strong> Without pooling, an application might create too many concurrent connections, overwhelming the database and leading to degraded performance or even crashes. Each Postgres connection is a process that spawns on establishment, resulting in overhead for the lock manager to coordinate the locking between different transactions.</li><li><strong>Optimizes Resource Utilization:</strong> Connection pooling ensures that database resources (e.g., memory, CPU) are used efficiently by avoiding unnecessary connection creation and destruction.</li><li><strong>Avoids Connection Leaks:</strong> Poorly-managed connections (i.e., those not closed properly) can lead to resource leaks. A connection pool manages the lifecycle of connections, ensuring they are properly closed or reused.</li><li><strong>Improves Application Availability:</strong> Connection poolers like RDSProxy, reduces the unavailability of the database significantly when a <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.howitworks.html#rds-proxy-failover">failover</a> occurs by <a href="https://aws.amazon.com/rds/proxy/">66%</a> according to AWS.</li></ol><h3>Flask SQLAlchemy DB connection setup</h3><p>Before we jump to RDSProxy, it is important to understand how a DB connection is set up with SQLAlchemy. At Lyft, we use <a href="https://flask-sqlalchemy.readthedocs.io/en/stable/">Flask SQLAlchemy</a> to power our API server and connect to Aurora Postgres DB. We use the following <a href="https://flask-sqlalchemy.readthedocs.io/en/stable/config/">config</a> in settings.py for SQLAlchemy:</p><pre>AURORA_WRITER_BIND = &#39;aurora_writer_bind&#39;<br>AURORA_READER_BIND = &#39;aurora_reader_bind&#39;<br><br>SQLALCHEMY_BINDS = {<br>   AURORA_WRITER_BIND: &#39;postgresql://{username}:{password}@{host}:{port}/{db}&#39;.format(<br>    username=&#39;scott&#39;,<br>    password=&#39;tiger&#39;,<br>    host=&#39;aurora.writer.com&#39;,<br>    port=5432,<br>    db=&#39;mydb&#39;<br>),<br>  AURORA_READER_BIND: &#39;postgresql://{username}:{password}@{host}:{port}/{db}&#39;.format(<br>    username=&#39;scott&#39;,<br>    password=&#39;tiger&#39;,<br>    host=&#39;aurora.reader.com&#39;,<br>    port=5432,<br>    db=&#39;mydb&#39;)<br>}<br><br>SQLALCHEMY_DATABASE_URI = SQLALCHEMY_BINDS[AURORA_WRITER_BIND] # Default bind</pre><p>The Flask APIs can now use the reader or the writer DB:</p><pre>def my_write_api():<br>  db.session.set_bind(AURORA_WRITER_BIND):<br> // API Logic<br><br><br>def my_read_api():<br>  db.session.set_bind(AURORA_READER_BIND):<br> // API Logic</pre><p>For connection pooling, here is an example configuration:</p><pre>SQLALCHEMY_MAX_OVERFLOW = 5<br>SQLALCHEMY_POOL_SIZE = 10<br>SQLALCHEMY_POOL_TIMEOUT = 5</pre><h4>How does it work?</h4><ol><li>When an application queries the database, it checks the pool for an available connection for the bind.</li><li>If a connection is available, it uses it.</li><li>If no connections are available, and the pool size is below SQLALCHEMY_POOL_SIZE, a new connection is created (lazy instantiation).</li><li>If more connections are needed, SQLAlchemy can create up to SQLALCHEMY_MAX_OVERFLOW additional connections.</li><li>These overflow connections do not stay in the pool. They are closed when released.</li><li>If both SQLALCHEMY_POOL_SIZE and SQLALCHEMY_MAX_OVERFLOW limits are reached, new connection requests will wait for an available connection (up to SQLALCHEMY_POOL_TIMEOUT seconds) before failing.</li></ol><h4>Limitations of application-based connection pooling</h4><p>If SQLAlchemy can create connection pools, what is the problem?</p><p>At Lyft, we use <a href="https://gunicorn.org/">gunicorn</a> with <a href="https://www.gevent.org/">gevent</a> to create multiple worker processes to handle requests efficiently and deploy them using Kubernetes. This means that each pod will have multiple gunicorn processes and each process will have the Flask app running with a max SQLALCHEMY_POOL_SIZE + SQLALCHEMY_MAX_OVERFLOW connection pools, i.e. max_connection_pool_per_pod = num_gunicorn_process * (SQLALCHEMY_POOL_SIZE + SQLALCHEMY_MAX_OVERFLOW). Please note that this represents the max connection pool per gunicorn process. In reality, it will be less than that given that the load would be distributed across multiple pods.</p><p>The application-based connection pooling has the following shortcomings:</p><ul><li><strong>Application Scaling:</strong> In the configuration above, we will have a max of <strong>4*(10 + 5) = 60</strong> total connection pool count for reader and the same for writer if there are 4 gunicorn processes spawned in the pod. If the application scales out, the number of connections to the database also increases. So more pods, more connections, more Postgres processes, more overhead for the lock manager to run concurrent queries! Our application running ~40 pods during peak hours would easily create 1400+ connections to the writer DB. This is especially problematic considering Aurora has a single writer.</li><li><strong>Database Maintenance:</strong> Whenever a database maintenance operation is run which causes locking (DDL, partition management, etc.), the query latency and timeouts increase. This would cause a retry storm from downstream services resulting in more connection pools getting created if not available and overwhelms the lock manager.</li></ul><h3>Proxy-based connection pooling</h3><p>Application-based connection pools are great for applications to reuse database connections. However, as mentioned above, you can still run into several issues. First, if you keep deploying additional pods, you will eventually run out of database connections. As opposed to waiting for a connection to be freed, the database simply denies the connection request. Second, at least with Aurora Postgres, Postgres’ process-based model means it has to spawn an additional process for each new connection request. This increases the amount of time it takes before a new connection can service a request. All of these processes also expend database resources. This is where a <em>separate proxy</em> layer that handles pooling and efficiently manages database connections for all applications can be leveraged.</p><p>Probably the most well-known external connection pooler for Postgres is <a href="https://www.pgbouncer.org/">pgBouncer</a>. It uses a thread-based model making it a highly efficient connection pooler. It maintains connections to your database and accepts connections from your application with low latency. Plus, if you use transaction pooling, you can have many more client application connections compared to the number of database server connections (known as multiplexing) as each transaction is assigned to one of the server connections.</p><p>However, Amazon AWS also offers a proxy solution that requires less maintenance and management called <a href="https://aws.amazon.com/rds/proxy/">RDSProxy</a>. We choose this option for our proxy-based pooler solution.</p><h3>Setup AWS RDSProxy</h3><p>Setting up RDSProxy can be done through the AWS console, through the AWS API, or via an Infrastructure as Code tool like Terraform. Here are the steps we followed to set up our RDSProxy:</p><ol><li><a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-network-prereqs.html">Network</a>: Plan for the new proxy to be placed in the same VPC as your databases and ensure your application instances can connect to it as well.</li><li><a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-secrets-arns.html">AWS Secrets Manager</a>: RDSProxy requires using AWS Secrets Manager. A secret containing the username and password must be stored for each database user you want to connect through RDSProxy.</li><li><a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-iam-setup.html">Configure AWS IAM Authentication</a>. Your policy needs to include access to the Secrets Manager secret(s) and associate it with the RDS service.</li><li><a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-creating.html">Create Proxy</a>: With these in place, you can now create the proxy. Be sure to specify the Secrets you created from step 2 and the IAM policy you created in step 3. You also define the target database cluster you want to connect to as well as if you want a read-only endpoint.</li><li><a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-connecting.html">Connecting</a>: Once this is complete, you should be able to test a connection through the proxy using one of the proxy endpoints and the user and password you set up in the Secrets Manager secret. We used the Postgres client psql for a quick test of connectivity.</li></ol><h3>RDSProxy</h3><h4>Setting up SQLAlchemy with RDSProxy</h4><p>Recall that we were using reader and writer binds earlier to connect to the reader and writer Aurora instances. Now that we have the proxy endpoints setup, we can create binds for the reader and the writer proxy endpoints.</p><pre>AURORA_WRITER_BIND = &#39;aurora_writer_bind&#39;<br>AURORA_READER_BIND = &#39;aurora_reader_bind&#39;<br>AURORA_WRITER_PROXY_BIND = &#39;aurora_writer_proxy_bind&#39;<br>AURORA_READER_PROXY_BIND = &#39;aurora_reader_proxy_bind&#39;<br><br>SQLALCHEMY_BINDS = {<br>   AURORA_WRITER_BIND: &#39;postgresql://{username}:{password}@{host}:{port}/{db}&#39;.format(<br>    username=&#39;scott&#39;,<br>    password=&#39;tiger&#39;,<br>    host=&#39;aurora.writer.com&#39;,<br>    port=5432,<br>    db=&#39;mydb&#39;<br>  ),<br>  AURORA_READER_BIND: &#39;postgresql://{username}:{password}@{host}:{port}/{db}&#39;.format(<br>    username=&#39;scott&#39;,<br>    password=&#39;tiger&#39;,<br>    host=&#39;aurora.reader.com&#39;,<br>    port=5432,<br>    db=&#39;mydb&#39;),<br>   AURORA_WRITER_PROXY_BIND: &#39;postgresql://{username}:{password}@{host}:{port}/{db}&#39;.format(<br>    username=&#39;scott&#39;,<br>    password=&#39;tiger&#39;,<br>    host=&#39;aurora.proxy.writer.com&#39;,<br>    port=5432,<br>    db=&#39;mydb&#39;<br>  ),<br> AURORA_READER_PROXY_BIND: &#39;postgresql://{username}:{password}@{host}:{port}/{db}&#39;.format(<br>    username=&#39;scott&#39;,<br>    password=&#39;tiger&#39;,<br>    host=&#39;aurora.proxy.reader.com&#39;,<br>    port=5432,<br>    db=&#39;mydb&#39;<br>  ),<br>}<br><br>SQLALCHEMY_DATABASE_URI = SQLALCHEMY_BINDS[AURORA_WRITER_PROXY_BIND] # Change default bind to writer proxy</pre><p>Now we can switch to using the proxy binds for our read and write APIs.</p><pre>def my_write_api():<br>  db.session.set_bind(AURORA_WRITER_PROXY_BIND)<br>   // API Logic<br><br>def my_read_api():<br>  db.session.set_bind(AURORA_READER_PROXY_BIND)<br>   // API Logic</pre><h3>Gotchas with RDSProxy</h3><p>The main limitation of using an external proxy with transaction pooling is that Postgres’ session-based features will not work correctly. This means any kind of “<strong>SET XXXX</strong>” will not work. Also, historically, prepared statements have also been a problem. However, AWS introduced some enhancements to RDSProxy to <a href="https://aws.amazon.com/blogs/database/amazon-rds-proxy-multiplexing-support-for-postgresql-extended-query-protocol/">support prepared statements in late 2023</a> and we never encountered any issues. We encountered several issues when rolling out RDSProxy in our test/prod environment:</p><h4>Statement timeout</h4><p>When connecting with the database using the proxy, we received the following error:</p><pre>sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at &quot;aurora.reader.proxy.com&quot; (10.0.146.4), port 5432 failed: FATAL: Feature not supported: RDS Proxy currently doesn&#39;t support command-line options.</pre><p>The reason for this was the following configuration in our settings files:</p><pre>SQLALCHEMY_ENGINE_OPTIONS = {&#39;connect_args&#39;: {&#39;options&#39;: &#39;-c statement_timeout={timeout}&#39;.format(<br>timeout=str_env(&#39;POSTGRES_STATEMENT_TIMEOUT&#39;)<br>)}}</pre><p>To overcome this issue, we decided to set the statement_timeout at the user level and removed the SQLALCHEMY_ENGINE_OPTIONS:</p><pre>ALTER ROLE scott SET statement_timeout = &#39;5s&#39;;</pre><h4>Session pinning</h4><p>This is probably the most important issue to be mindful of when moving to RDSProxy. Normally, RDSProxy optimizes database connections by multiplexing multiple client requests over fewer actual database connections. However, some database session states require a persistent connection to a specific backend instance, forcing RDSProxy to “pin” that session. The pinning of sessions reduces how many connections are available for reuse. To get the most benefit from connection pooling, it is worth minimizing behaviors that trigger session pinning.</p><h4>Session pinning: How to catch them?</h4><p>Conveniently, RDSProxy charts all occurrences of session pinning in the monitoring tab of the RDSProxy called “<strong>DatabaseConnectionsCurrentlySessionPinned</strong>.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hAhd-IIrw35HAjt2" /><figcaption>Session pinning metrics from AWS cloudwatch</figcaption></figure><p>In addition, it logs the cause of the session pinning as well. These logs can be found in CloudWatch at a <strong>WARN</strong> level and include the text, “<strong>The client session was pinned to the database connection . . .</strong>”.</p><p>We did encounter some rare queries which are session pinned, for which we never found the source of this session pinning where the SQL query exceeded the 16384 byte limit:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*veaec0n2gkimPSD5" /><figcaption>Example session pinning Cloudwatch log</figcaption></figure><p>RDSProxy does not log these SQL statements even with <strong>Enhanced Logging</strong> enabled. We attempted to find this query on the database side. Unfortunately, we could never find this query in pg_stat_statements. Probably because pg_stat_statements stores a parameterized version of the query that could be much shorter than the actual one. Especially, if it is a query with an IN clause looking for a lot of values. The only other alternative is logging every statement in the database to try and find them. But, given how rare the session pinning incidents are, we have not attempted to do that as of yet.</p><p>Apart from this instance, we went ahead and addressed each of the following session pinning issues:</p><h4>Advisory lock_timeout</h4><p>We had several flows in our application using <a href="https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS">advisory locks</a> with varying timeouts.</p><pre>db.session.execute(&#39;SET lock_timeout=3000&#39;);</pre><p>This changes the state of the session and requires session pinning. One option was to set the lock_timeout at the user level. However, because each flow had a different timeout requirement, we opted to use SET LOCAL which is transaction-scoped and does not create session pinning.</p><pre>db.session.execute(&#39;SET LOCAL lock_timeout=3000&#39;);</pre><h4>Sequence numbers</h4><p>We had some legacy flows relying on sequence number generation in our write APIs. We had to refactor our code to ensure that we rely on the auto-incremented value generated on writes to the DB and not generate the sequence using<strong> </strong>nextval().</p><p>There are many other reasons why session pinning occurs. A full list is available in the <a href="https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-pinning.html">official AWS documentation</a></p><h4>Query latency</h4><p>Due to an extra network hop added by the proxy, there will be some latency introduced. For our application, most of the APIs did not see any significant increase in latency. There was one API which was latency sensitive and we chose to directly connect with the reader instead of using the proxy. We will evaluate this API in future so see if we can change the query patterns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*w5QIduu0IibVSoTg" /><figcaption>Sample query for which the p95 latency increased due to the proxy rollout</figcaption></figure><h3>RDSProxy Live!</h3><p>As soon as we switched the writer to RDSProxy, we immediately saw the number of connections to it dropping by <strong>~56%.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*zxt5Yk2Tf7h3Lddf" /><figcaption>Drop in writer connections as soon as we rolled out</figcaption></figure><p>We can also clearly see that the <strong>writer connections were below 800, compared to not using proxy where the connection fluctuated between 900 and 1400 over a period of 7 hours</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3UTnydXI0-xBS447" /><figcaption>Writer connections fluctuation with and without proxy</figcaption></figure><p>We also see a slight improvement in CPU performance for the writer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*RLKW7dcFxo07evxq" /><figcaption>Writer CPU % with and without proxy</figcaption></figure><p>Aurora supports multiple readers (up to 15) and each reader on an average dropped connections by <strong>~16%. </strong>We can also see how the proxy is able to smooth out the number of connections compared to not using the proxy where the connections fluctuate. We also saw significant improvement in CPU consumption for each reader.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*aI_Z2DBKOA2IKXye" /><figcaption>Reader connections fluctuation with and without proxy</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*114aX7-XxC47z8HI" /><figcaption>Reader CPU % with and without proxy</figcaption></figure><p>AWS provides <a href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy.monitoring.html">metrics</a> on RDSProxy for the number of client connections, DB connections (combined from all readers + writer), connections borrowed, etc. In live traffic, we saw the total client connections to the proxy and how they translated to the DB connections to readers and the writer.</p><p>There were some write queries generating session pinning and after fixing them,<strong> we saw the connection to the writer dropping further by 33% (from 750 to 500!)</strong>. We can also see how the proxy is able to handle auto scaling of the application well when there is no session pinning keeping a flat connection count of 500.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6M87V5OM_WFyArpo" /><figcaption>Writer connections after fixing session pinning with RDSProxy</figcaption></figure><h4><strong>How efficient was RDSProxy in managing the connections?</strong></h4><p>The efficiency of proxy is calculated using the multiplexing ratio which is <strong>ClientConnections/DatabaseConnections</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fERPcHJ8LFWg4i1e" /><figcaption>Connections to RDSProxy for both reads and writes</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iMs1QQShDV2tyVX7" /><figcaption>Total connections to the database using RDSProxy</figcaption></figure><p>From the CloudWatch graphs above, we can see that the proxy had a multiplexing ratio of<strong> ~2.4 (3.9k/1.65k)</strong></p><p><strong>Note: The CloudWatch statistics include both the traffic to the reader and the writer when counting ClientConnections and the DatabaseConnections.</strong></p><p>Here’s a comprehensive summary of the usage of proxy and the connection improvements we saw with the readers and writer during peak hours with ~40 pods:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KsktustDiySDLW4LgweGzg.png" /></figure><h3>RDSProxy scale testing</h3><p>To understand the benefits of the proxy, we conducted some scale testing for the writer and reader DB. For the scope of this blog post, we will restrict the discussion to only the writer since the writer cannot be horizontally scaled out unlike the readers. In this test, we did not increase the application traffic, but rather increased the number of application pods connecting to the writer database to better understand how efficiently proxy manages connection. We divided our test with session pinning and without session pinning to understand the connection multiplexing efficiency.</p><h4>With session pinning</h4><p>We used a different user for queries which we knew caused session pinning so that we could have a breakdown in pg_stat_activity by username. We scaled out to ~200 pods which created ~1700 connections to the DB. We discovered that <strong>out of the 1700 writer connections, the session pinned connections were ~1100</strong>. This demonstrates how critical it is to fix session pinning for efficient connection management. The total DB connections including the readers was ~2900.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ZpZ38cHoYrIwASWM" /><figcaption>Connections to the readers and the writer after scaling out to 200 pods</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*v8Q1tl77vuCLmP17" /><figcaption>Connections to RDSProxy for both reads and writes</figcaption></figure><p>The multiplexing ratio here was <strong>~2.24</strong> (6500/2900).</p><h4>Post session pinning fixes</h4><p>After refactoring our code to fix session pinning, we observed the following metric in CloudWatch:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*13gt_cwstx_4nxFA" /><figcaption>Connections to RDSProxy for both reads and writes with 250 pods after fixing session pinning</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*tTnAA-wrLG1G1YbH" /><figcaption>Total connections to the database using RDSProxy</figcaption></figure><p>As you can see, we were able to achieve a multiplexing ratio of <strong>~4.4 </strong>(7.1k/1.6k) and that too with 250 pods!</p><h4><strong>So how does our writer perform now after eliminating session pinning?</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Va96Ff_MFKHH-vPm" /></figure><p>If you compare the results from session pinning, we reached 1.5k connections to the writer with 200 pods. <strong>After eliminating session pinning, our connections were flat at ~500 connections!</strong> <strong>Contrast this with what we had before RDSProxy: 1400 connections occurring with just 40 pods</strong>. This demonstrates how important it is to eliminate or minimize session pinning as much as possible to efficiently multiplex the connections to the DB and scaling of the application.</p><p>To summarize our findings:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8jE9iy3KZrJkMKXYLBF08Q.png" /></figure><h3>Closing notes &amp; next steps</h3><p>RDSProxy has proven beneficial so far for us to ensure our application can scale gracefully without throttling the Aurora Postgres DB. The key to ensure efficient multiplexing is minimizing session pinning as much as possible. The AWS monitoring definitely needs some improvement to help catch the remaining session pinned queries. While query latency caused by the extra network hop did not cause significant issues for us, it is vital to evaluate RDSProxy if your application APIs are latency sensitive. As a next step, given the proxy’s efficiency, we will evaluate our clusters to see if we can downsize our readers/writers to lower instance size and possibly also scale down the number of readers to reduce our cost.</p><p><em>Lyft is hiring! If you’re passionate about solving these kind of distributed system problems, visit </em><a href="https://www.lyft.com/careers"><em>Lyft Careers</em></a><em> to see our openings.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=200db7f562d7" width="1" height="1" alt=""><hr><p><a href="https://eng.lyft.com/beyond-query-optimization-aurora-postgres-connection-pooling-with-sqlalchemy-rdsproxy-200db7f562d7">Beyond Query Optimization: Aurora Postgres Connection Pooling with SQLAlchemy &amp; RDSProxy</a> was originally published in <a href="https://eng.lyft.com/">Lyft Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres Aurora DB major version upgrade with minimal downtime]]></title>
            <link>https://eng.lyft.com/postgres-aurora-db-major-version-upgrade-with-minimal-downtime-4e26178f07a0?source=rss-ca7aad95f241------2</link>
            <guid isPermaLink="false">https://medium.com/p/4e26178f07a0</guid>
            <category><![CDATA[aurora]]></category>
            <category><![CDATA[infrastructure]]></category>
            <category><![CDATA[downtime-reduction]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Jay Patel]]></dc:creator>
            <pubDate>Mon, 11 Mar 2024 19:58:31 GMT</pubDate>
            <atom:updated>2024-03-11T19:58:31.141Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*xMhYlG_8Rb9JDop6Bl9IqQ.jpeg" /><figcaption>Photo by <a href="https://commons.wikimedia.org/wiki/User:Arctic_light">Frank Olsen</a> UNDER <a href="https://creativecommons.org/licenses/by-sa/3.0/deed.en">CC BY-SA 3.0 DEED</a></figcaption></figure><h3>Introduction</h3><p>Our payment platform team had the unique challenge to upgrade our Aurora Postgres DB from v10 to v13. This DB was responsible for storing transactions within Lyft and contains ~400 tables (with partitions) and ~30TB of data. <strong>Upgrading the database </strong><a href="https://wiki.postgresql.org/wiki/In-place_upgrade"><strong>in-place</strong></a><strong> would have resulted in ~30 mins of downtime</strong>. Such significant downtime is untenable — it would cause cascading failures across multiple downstream services, requiring a large amount of engineering effort to remediate. Through native replication between the two Postgres major versions and a blue/green deploy, we were able to reduce the downtime to <strong>~7 mins</strong>!</p><h3>Considered Upgrade Approaches</h3><h3>In-place upgrade</h3><p>This is the simplest method to upgrade the PG database. It involves upgrading the DB directly using the <a href="https://www.postgresql.org/docs/current/pgupgrade.html">pg_upgrade</a> command. However, when an in-place upgrade is triggered, there is downtime due to the upgrade occurring at the storage layer, not the compute layer. This results in the storage layer, and therefore effectively all write/read operations, becoming unavailable during the upgrade. Our analysis revealed that this process, and the resulting downtime, would have lasted ~30 minutes.</p><h3>DMS</h3><p>AWS provides the <a href="https://aws.amazon.com/blogs/database/achieving-minimum-downtime-for-major-version-upgrades-in-amazon-aurora-for-postgresql-using-aws-dms/">Data Migration Service</a>, which allows logical replication between a source and target Postgres DB. This allows for instantaneously cutting traffic over to the upgraded DB, allowing us to minimize downtime. Unfortunately, when cutting over to the newly-upgraded DB, the traffic would fully load the new database, causing replication lag to remain elevated for the following days/weeks.</p><h3>Native Logical Replication</h3><p>To overcome the issue of full load using Amazon’s DMS — we can clone the existing DB, upgrade it to the desired version, and then setup replication between the source and the target DB. This was the least disruptive method in our situation, and the approach that we decided to employ. <strong>Our experimentation in a testing environment gave us enough confidence to move ahead with this approach</strong>.</p><h3>Upgrade using Logical Replication</h3><p>To understand logical replication, let’s familiarize ourselves with the following terms:</p><ol><li><strong>Publisher</strong> — the source DB publishing the data for replication</li><li><strong>Subscriber</strong> — the target DB subscribing to the source DB for replication data</li><li><strong>Replication slot</strong> — the basic idea of a replication slot is to let the publisher know about the transactions committed by the subscriber so far. This allows the publisher to know if the WAL up to a certain point should be preserved or flushed. A replication slot can have one or more tables added to it. If a table/partition has large number of insert, update, deletes happening, it is best to create a dedicated replication slot to avoid lag between the source and target DB</li></ol><p>Let’s talk about the upgrade steps now. We divide the upgrade into two phases:</p><ol><li>Setup replication (Phase 1)</li><li>Cutover traffic to the upgraded DB (Phase 2)</li></ol><h3>Replication Setup (Phase 1)</h3><h3>Part 1: Setup publisher (PG10)</h3><ol><li>Create publications</li></ol><p>Let’s create publications for table1, table2, and other tables. We are creating a separate publication for table1 &amp; table2 with heavy writes.</p><pre>CREATE PUBLICATION pub_all_table;<br>CREATE PUBLICATION pub_table1;<br>CREATE PUBLICATION pub_table2;</pre><p><em>SQL queries to create the 3 publications we’ll use for the replication.</em></p><p>2. Add tables to publication</p><pre>ALTER publication pub_table1 ADD TABLE table1;<br>ALTER publication pub_table2 ADD TABLE table1;<br>ALTER publication pub_all_tables ADD TABLE table3,table4,table5;</pre><p><em>SQL queries to add corresponding tables to each of the 3 publications.</em></p><p>3. Create replication slots¹</p><pre>SELECT pg_create_logical_replication_slot(‘pub_all_table’, ‘pgoutput’);<br>SELECT pg_create_logical_replication_slot(‘pub_table1’, ‘pgoutput’);<br>SELECT pg_create_logical_replication_slot(‘pub_table2’, ‘pgoutput’);</pre><p><em>SQL queries to create logical replication slots for each publication.</em></p><h3>Part 2: Setup subscriber (PG13)</h3><ol><li>Clone the PG10 database cluster . Aurora Cloning² uses copy on write and thus is really fast. Our 30 TB data was cloned within 20 minutes or so.</li><li>Capture the <a href="https://www.postgresql.org/docs/current/datatype-pg-lsn.html#:~:text=This%20type%20is%20a%20representation,for%20example%2C%2016%2FB374D848%20.">LSN</a> in the cloned DB. Think of the LSN as a global commit number which acts as the initial checkpoint from which we want the replication to begin.</li></ol><pre>SELECT * FROM aurora_volume_logical_start_lsn();<br> <br>aurora_volume_logical_start_lsn<br> — — — — — — — — — — — — — — — — -<br> 29B/A04B33A8</pre><p>3. The replication slots &amp; publication tables in the new subscriber DB are relics of cloning the PG10 DB, so we can safely drop them from the table.</p><pre>SELECT pg_drop_replication_slot(‘pub_all_table’);<br>SELECT pg_drop_replication_slot(&#39;pub_table1&#39;);<br>SELECT pg_drop_replication_slot(&#39;pub_table2&#39;);</pre><pre>DROP PUBLICATION pub_all_table;<br>DROP PUBLICATION pub_table1;<br>DROP PUBLICATION pub_table2;</pre><p>4. Upgrade our PG10 clone to PG13. This upgrade takes ~30 mins to complete, during which we can run the following command in the source DB (PG10) to find the replication lag between our two DBs.</p><pre>SELECT now() AS CURRENT_TIME,<br> slot_name,<br> active,<br> active_pid,<br> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS diff_size,<br> pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS diff_bytes<br>FROM pg_replication_slots<br>WHERE slot_type = ‘logical’;<br><br><br>current_time | slot_name | active | active_pid | diff_size | diff_bytes <br> — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — + — — — — + — — — — — — + — — — — — -+ — — — — — — <br> 2023–03–27 04:33:46.974475+00 | pub_all_table | f | | 66 MB | 69123352<br> 2023–03–27 04:33:46.974475+00 | pub_table1 | f | | 66 MB | 69106928<br> 2023–03–27 04:33:46.974475+00 | pub_table2 | f | | 66 MB | 69084400<br> 2023–03–27 04:33:46.974475+00 | pub_table3 | f | | 66 MB | 69074600<br> 2023–03–27 04:33:46.974475+00 | pub_table4 | f | | 66 MB | 69064856<br> 2023–03–27 04:33:46.974475+00 | pub_table5 | f | | 66 MB | 69053368<br> 2023–03–27 04:33:46.974475+00 | pub_table6 | f | | 66 MB | 69035888</pre><p><em>At this time, we see that the size of the lag between our databases is 66MB.</em></p><pre>current_time | slot_name | active | active_pid | diff_size | diff_bytes <br> — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — + — — — — + — — — — — — + — — — — — -+ — — — — — — <br> 2023–03–27 04:43:45.983791+00 | pub_all_table | f | | 200 MB | 209291552<br> 2023–03–27 04:43:45.983791+00 | pub_table1 | f | | 200 MB | 209275128<br> 2023–03–27 04:43:45.983791+00 | pub_table2 | f | | 200 MB | 209252600<br> 2023–03–27 04:43:45.983791+00 | pub_table3 | f | | 200 MB | 209242800<br> 2023–03–27 04:43:45.983791+00 | pub_table4 | f | | 200 MB | 209233056<br> 2023–03–27 04:43:45.983791+00 | pub_table5 | f | | 200 MB | 209221568<br> 2023–03–27 04:43:45.983791+00 | pub_table6 | f | | 200 MB | 209204088</pre><p><em>Just 10 minutes later, the replication lag has increased 3x, from 66MB to 200MB!</em></p><p>5. Create a subscription in PG13<br><strong>Note: Create but do not enable the subscription yet!</strong></p><pre>CREATE SUBSCRIPTION sub_all_table <br>CONNECTION ‘host=mydb-cluster.cluster-c123.us-east-1.rds.amazonaws.com dbname=db_name port=5432 user=user password=xxx’ <br>PUBLICATION pub_all_table WITH ( copy_data = false, <br>create_slot = false, enabled = false, connect = true, <br>slot_name = ‘pub_all_table’ ); <br><br>CREATE SUBSCRIPTION sub_table1 CONNECTION <br>&#39;host=mydb-cluster.cluster-c123.us-east-1.rds.amazonaws.com dbname=db_name port=5432 user=user password=xxx&#39; <br>PUBLICATION pub_table1 WITH ( copy_data = false, <br>create_slot = false, enabled = false, connect = true, <br>slot_name = &#39;pub_table1&#39; );<br><br>CREATE SUBSCRIPTION sub_table2 CONNECTION <br>‘host=mydb-cluster.cluster-c123.us-east-1.rds.amazonaws.com dbname=db_name port=5432 user=user password=xxx’ <br>PUBLICATION pub_table2 WITH ( copy_data = false, create_slot = false, <br>enabled = false, connect = true, slot_name = ‘pub_table2’ );</pre><p><em>We run the above SQL to create a connection between our PG13 DB and the PG10 DB, allowing us to eventually enable the subscription and begin to reduce replication lag.</em></p><p>6. Capture Replication Origin³</p><pre>SELECT * FROM pg_replication_origin;<br><br>roident | roname<br>— — — — -+ — — — — — — -<br>1 | pg_53563397<br>2 | pg_53563398<br>3 | pg_53563399<br>4 | pg_53563400<br>5 | pg_53563401<br>6 | pg_53563402<br>7 | pg_53563403</pre><p><em>We capture the replication origin of the new DB, which will be used to start the replication process from the appropriate checkpoint for each slot.</em></p><p>7. Advance Replication Origin to the LSN number captured earlier.</p><p>This is a crucial step to ensure we start CDC from a known point for each slot</p><pre>SELECT pg_replication_origin_advance(pg_53563397,’29B/A04B33A8&#39;);<br>SELECT pg_replication_origin_advance(pg_53563398,’29B/A04B33A8&#39;);<br>SELECT pg_replication_origin_advance(pg_53563399,’29B/A04B33A8&#39;);<br>SELECT pg_replication_origin_advance(pg_53563400,’29B/A04B33A8&#39;);<br>SELECT pg_replication_origin_advance(pg_53563401,’29B/A04B33A8&#39;);<br>SELECT pg_replication_origin_advance(pg_53563402,’29B/A04B33A8&#39;);<br>SELECT pg_replication_origin_advance(pg_53563403,’29B/A04B33A8&#39;);</pre><p><em>Use Postgres’ pg_replication_origin_advance feature to advance to the LSN captured earlier.</em></p><p>Re-checking the replication lag between the two databases, it has continued to grow, since we have not yet enabled subscription.</p><pre>current_time | slot_name | active | active_pid | diff_size | diff_bytes <br> — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — + — — — — + — — — — — — + — — — — — -+ — — — — — — <br> 2023–03–27 06:07:23.448674+00 | pub_all_table | f | | 1177 MB | 1234508464<br> 2023–03–27 06:07:23.448674+00 | pub_table1 | f | | 1177 MB | 1234492040<br> 2023–03–27 06:07:23.448674+00 | pub_table2 | f | | 1177 MB | 1234469512<br> 2023–03–27 06:07:23.448674+00 | pub_table3 | f | | 1177 MB | 1234459712<br> 2023–03–27 06:07:23.448674+00 | pub_table4 | f | | 1177 MB | 1234449968<br> 2023–03–27 06:07:23.448674+00 | pub_table5 | f | | 1177 MB | 1234438480<br> 2023–03–27 06:07:23.448674+00 | pub_table6 | f | | 1177 MB | 1234421000</pre><p><em>In ~85 minutes, the replication lag has shot up to 1177MB</em></p><p>8. Finally, with the advanced replication origin, we can enable subscription in PG13, which should start to reduce the replication lag between the databases.</p><pre>ALTER SUBSCRIPTION sub_all_table ENABLE;<br>ALTER SUBSCRIPTION sub_table1 ENABLE;<br>ALTER SUBSCRIPTION sub_table2 ENABLE;<br>-- Enable other subscriptions as well<br><br><br>current_time | slot_name | active | active_pid | diff_size | diff_bytes <br> — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — + — — — — + — — — — — <br> 2023–03–27 06:17:19.748714+00 | pub_all_table | t | 11757 | 168 MB | 176012552<br> 2023–03–27 06:17:19.748714+00 | pub_table1 | t | 10825 | 643 MB | 673892912<br> 2023–03–27 06:17:19.748714+00 | pub_table2 | t | 12181 | 992 MB | 1040602856<br> 2023–03–27 06:17:19.748714+00 | pub_table3 | t | 17747 | 1099 MB | 1152246216<br> 2023–03–27 06:17:19.748714+00 | pub_table4 | t | 16015 | 344 MB | 361209240<br> 2023–03–27 06:17:19.748714+00 | pub_table5 | t | 17878 | 1205 MB | 1263605248<br> 2023–03–27 06:17:19.748714+00 | pub_table6 | t | 17961 | 1205 MB | 1263605248</pre><p><em>The replication lag now is decreasing. Note that the replication lag of a heavy writes table will be larger than the others. But it is catching up! Also note that the active_pid field is now true which means that there are processes assigned to carry out the replication. Pretty cool!</em></p><pre>current_time | slot_name | active | active_pid | diff_size | diff_bytes <br> — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — + — — — — + — — — — — — + — — — — — -+ — — — — — — <br> 2023–03–27 06:25:08.704248+00 | pub_all_table | t | 11757 | 37 kB | 38144<br> 2023–03–27 06:25:08.704248+00 | pub_table1 | t | 10825 | 50 kB | 51576<br> 2023–03–27 06:25:08.704248+00 | pub_table2 | t | 12181 | 697 MB | 731124584<br> 2023–03–27 06:25:08.704248+00 | pub_table3 | t | 18329 | 73 kB | 75200<br> 2023–03–27 06:25:08.704248+00 | pub_table4 | t | 16015 | 47 kB | 47760<br> 2023–03–27 06:25:08.704248+00 | pub_table5 | t | 17878 | 37 kB | 38144<br> 2023–03–27 06:25:08.704248+00 | pub_table6 | t | 17961 | 73 kB | 75200</pre><p><em>Within ~20 mins of enabling subscription in step 8, our replication lag is down to a few kBs now.</em></p><p><strong>Success!</strong> There will always be some form of replication lag due to network latency, but our subscription process has successfully minimized it!</p><p>9. We can now run <a href="https://www.postgresql.org/docs/current/sql-vacuum.html">VACUUM ANALYZE</a> on the parent tables to update statistics on our target DB for optimal query performance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/639/1*ymTzRmtPhjwoxauTmlZr6A.jpeg" /><figcaption>PG10-&gt;PG13 Replication Setup</figcaption></figure><h3>Bring traffic to our upgraded DB (Phase 2)</h3><p>Our goal is now to bring this replication lag to 0 and cut the traffic over to the upgraded DB.</p><h3>Reason to halt traffic</h3><p>Because of the ingress write traffic to our database, there will always be some amount of replication lag between the source and target DBs. If we cutover to the new database while there is replication lag, we’ll lose the lagging data. The only way to ensure 0 replication lag between the two DBs, in turn ensuring a lossless migration, is to halt ingress traffic for a (short) period of time.</p><p><strong>Note</strong>: In our case, only 1 service was connecting to our transactions DB and all downstream services used CRUD APIs through that service to communicate with the DB. If you have multiple services connecting to the DB, you will have to halt traffic to all those services.</p><h3>Approach to halt traffic and avoid writes</h3><p>To avoid writes, we made our PG10 DB read only. Our internal infra uses k8s as the service orchestrator and envoy as a proxy. To prevent any traffic from flowing to our DB, we could consider scaling our service pods down to 0 or intentionally fail our <a href="https://www.envoyproxy.io/docs/envoy/latest/intro/arch_overview/upstream/health_checking">healthcheck</a>. However, this would add to our downtime during cutover, as it would take some time for our service to scale back up and be ready to serve traffic. Instead, we chose to use an <strong>envoy </strong><a href="https://www.envoyproxy.io/docs/envoy/latest/intro/arch_overview/upstream/circuit_breaking"><strong>circuitbreaker</strong></a><strong>, </strong>which returns an HTTP 503 code immediately to the downstream caller. This combination of circuit breaker + read only source DB ensures there are no new writes happening and the replication lag between the source and target DB quickly goes to 0.</p><h3>Switching traffic to the target DB</h3><p>A simple approach to switching traffic is to update a runtime config value for the connection string, changing it from the old DB to the new DB. However, this would require deploying a runtime config change and also redeploying the application to use the new connection string. To overcome this issue, we opted instead for <a href="https://aws.amazon.com/route53/">AWS Route53</a>. Our service would use route53 connection URL to initially connect with the PG10 DB and then during cutover, we would update the route53 entry to point to the upgraded DB URL. Route53 guarantees that DNS entries will be updated within <strong>60</strong> seconds, allowing us to ensure that the application connects to the upgraded DB with minimal delay.</p><h3>Cutover steps</h3><ol><li>Double-check the replication lag. It should be no more than a few kBs.</li><li><strong>Downtime starts</strong> — Trigger circuit breaker to halt all traffic to application <br><strong>Why: To ensure no data loss during cutover.</strong> <strong><br></strong>We wait for 1 min or so for all requests to finish processing.<br><strong>How to verify: </strong>SSH into a downstream pod and ensure that a 503 is returned for any GET requests.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/639/1*HnEuQSTR4Fmd3fuhl858hQ.jpeg" /><figcaption>Circuit breaker enabled</figcaption></figure><p>3. Put PG10 DB in read-only mode<br><strong>Why: </strong>Ensure that even if requests somehow makes it to the application(s), there is no data written.<br><strong>How to verify:</strong> Write a script to write a dummy entry into a dummy table. The write will ideally fail loudly, with an error conveying that write transactions are disabled. <br>ALTER DATABASE my_db SET default_transaction_read_only = true;</p><p>4. Terminate all connections to source PG10 DB<br><strong>Why: </strong>Ensures no connections to PG10 DB from application(s).</p><p>5. Check replication lag<br><strong>Why:</strong> If replication is 0, it means both PG10 and PG13 DB are in sync.</p><pre>SELECT now() AS CURRENT_TIME,<br> slot_name,<br> active,<br> active_pid,<br> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS diff_size,<br> pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS diff_bytes<br>FROM pg_replication_slots<br>WHERE slot_type = ‘logical’;<br><br>current_time | slot_name | active | active_pid | diff_size | diff_bytes <br> — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — + — — — — + — — —<br> 2023–04–03 08:37:46.169934+00 | pub_all_table | t | 58088 | 0 bytes | 0<br> 2023–04–03 08:37:46.169934+00 | pub_table1 | t | 57556 | 0 bytes | 0<br> 2023–04–03 08:37:46.169934+00 | pub_table2 | t | 57691 | 0 bytes | 0<br> 2023–04–03 08:37:46.169934+00 | pub_table3 | t | 59805 | 0 bytes | 0<br> 2023–04–03 08:37:46.169934+00 | pub_table5 | t | 59025 | 0 bytes | 0<br> 2023–04–03 08:37:46.169934+00 | pub_table6 | t | 59376 | 0 bytes | 0<br> 2023–04–03 08:37:46.169934+00 | pub_table7 | t | 59530 | 0 bytes | 0</pre><p><em>Example SQL query to ensure that there is no replication lag between the databases. The diff_bytes is 0 now!</em></p><p>6. We now need to reset sequences in, which we accomplished with the following script: <a href="https://wiki.postgresql.org/wiki/Fixing_Sequences">https://wiki.postgresql.org/wiki/Fixing_Sequences</a><br>This ensures that the sequence starts from the <strong>last entry</strong> of the individual tables.<br><strong>Why: </strong>The target DB (PG13), which was a clone of the source DB(PG10), has all of its sequence number(s) lagging since the cloning was completed. We need to reset the sequence numbers so that new writes do not cause any sequence number collision.</p><p>7. Update route53, changing the DB connection string to refer to the PG13 DB.</p><p>8. Verify DNS update of both reader &amp; writer of route53 URL<br>nslookup my-route53-writer-url.net <br>This will take a few seconds to update from PG10 to PG13 connection string.</p><p>9. SSH into an application pod and run a script which writes to a dummy table. <br><strong>Why:</strong> If the write succeeds, you know that the PG13 DB successfully processed the write (PG10 would error out, given that it is in read-only mode).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*H9rdnTK1Hn9E78a5FnCMSA.jpeg" /><figcaption><em>Route53 now points to our PG13 (upgraded) database.</em></figcaption></figure><p>10. <strong>Downtime over — </strong>Turn the circuit breaker off, allowing ingress traffic to the application<strong>. </strong>You should now see regular traffic flowing and writes happening to PG13. The downtime in our case was just <strong>~7 mins</strong> which was a big win considering the in-place upgrade would have taken <strong>~30 mins</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/699/1*N47QiZVRfqQXQLnM4rmQ-g.jpeg" /><figcaption>Circuit breaker disabled. Traffic flowing into the service again</figcaption></figure><p>This is what our traffic dashboard looked like during the downtime:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TnWN8gXfVc19oejq" /></figure><p><strong>SUCCESS!</strong></p><p>The only traffic to the pod was a health check, which is why you can see the single green line despite no traffic to our application. <strong>Please note that we also had setup a rollback replication from PG13 -&gt; PG10 in case of any issues with PG13, however, it is not discussed here in the interest of simplicity.</strong></p><h3>Closing Notes</h3><p>Our model of blue/green deployment for Aurora upgrade was created and executed in April 2023. As of October 2023, AWS now <a href="https://aws.amazon.com/about-aws/whats-new/2023/10/amazon-rds-blue-green-deployments-aurora-rds-postgresql/">supports</a> blue/green deployment for Aurora Postgres. This should definitely help bring down the downtime further given that all of the steps for replication, cutting the traffic over to the upgraded DB, and the rollback setup would be handled within their platform. This blog would be of immense help to understand what happens under the hood with AWS blue/green deployment!</p><h3>Acknowledgements</h3><p>Special thanks to <a href="https://www.linkedin.com/in/shaileshrangani/">Shailesh Rangani</a> and <a href="https://www.linkedin.com/in/suyog-pagare-%E2%98%81%EF%B8%8F-5a226918/">Suyog Pagare</a> whose Postgres expertise made this upgrade with minimal downtime possible.</p><p><em>If you’re interested in solving problems like this one, please take a look at our </em><a href="https://www.lyft.com/careers"><em>careers page</em></a>!</p><h3>Footnotes</h3><p>¹ <a href="https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS">PostgreSQL: Documentation: 16: 49.2. Logical Decoding Concepts</a><br>² <a href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Clone.html">Cloning a volume for an Amazon Aurora DB cluster</a><br>³ <a href="https://www.postgresql.org/docs/current/replication-origins.html">PostgreSQL: Documentation: 16: Chapter 50. Replication Progress Tracking</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4e26178f07a0" width="1" height="1" alt=""><hr><p><a href="https://eng.lyft.com/postgres-aurora-db-major-version-upgrade-with-minimal-downtime-4e26178f07a0">Postgres Aurora DB major version upgrade with minimal downtime</a> was originally published in <a href="https://eng.lyft.com/">Lyft Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>