<?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 Evgeny Kharitonov on Medium]]></title>
        <description><![CDATA[Stories by Evgeny Kharitonov on Medium]]></description>
        <link>https://medium.com/@evgeny_26981?source=rss-7795017fd57------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Bd1LVWIZCAhKESaC_9qTEQ.jpeg</url>
            <title>Stories by Evgeny Kharitonov on Medium</title>
            <link>https://medium.com/@evgeny_26981?source=rss-7795017fd57------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 03:24:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@evgeny_26981/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[Make the MCP the only door — and every cluster touch becomes visible]]></title>
            <link>https://medium.com/tailor-tech/make-the-mcp-the-only-door-and-every-cluster-touch-becomes-visible-dd7b4ba0b80d?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/dd7b4ba0b80d</guid>
            <category><![CDATA[claude-code]]></category>
            <category><![CDATA[observability]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[mcp-server]]></category>
            <category><![CDATA[security]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Tue, 04 Aug 2026 12:04:10 GMT</pubDate>
            <atom:updated>2026-08-04T12:04:10.124Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Part 2 of 2 — enforcing the gateway, and the observability that falls out of it.</em></p><p>Part 1 built a scoped gateway: a per-cluster <a href="https://github.com/containers/kubernetes-mcp-server">Kubernetes MCP server</a> sitting behind Cloudflare Access and Okta, read-only by default, write access only in dev, sessions that expire on their own. On paper, that’s a real guardrail — an agent talking to that MCP server can only do what the gateway lets it do.</p><p>The problem is the phrase “talking to.” Nothing stops an agent from <em>not</em> talking to it. If the same shell that runs the MCP client can also run kubectl or the cloud CLI directly, and there are usable credentials sitting in that shell&#39;s environment, the agent has a second, unscoped door standing right next to the scoped one. It doesn&#39;t matter how carefully you designed the gateway&#39;s RBAC if the agent can just step around it.</p><p>So this post is about two things: closing that second door so the gateway is the <em>only</em> path in — and the payoff that falls out once it is, which is that every cluster touch suddenly becomes something you can see.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*66kvYoh8uQLgg7Jg_hKmvA.jpeg" /></figure><h3>The MCP has to be the path of least resistance</h3><p>Scoped access only means something if the MCP is the path the agent actually takes — and left to itself, it isn’t always. An agent improvising its way through a task, or nudged by a prompt injection that suggests a shortcut, will use whatever tool gets the job done, including a raw CLI call. Telling it not to is a convention, and conventions don’t survive contact with an agent under pressure to finish.</p><p>The fix isn’t more policy documentation. It’s making the raw command fail.</p><h3>The mechanism: a pre-tool-use hook</h3><p>Coding agent runtimes commonly expose a hook that runs before any shell command executes, with the power to allow, ask, or deny it. That’s the natural interception point: before the agent’s shell command runs, inspect it, and if the command being executed is kubectl or the cloud CLI, deny it and tell the agent to use the MCP tool instead. The agent gets a clear redirect, not just a wall — &quot;use the cluster MCP tool for this,&quot; which it can act on immediately.</p><p>The naive way to decide whether a command is a cluster command is to look for the tool’s name in the text — and it’s wrong in both directions at once. It’s too eager: brew install kubectl, command -v kubectl, and a grep for the word all mention the tool without running it, and a guardrail that blocks those gets muted in its first week. It&#39;s also too lax: zsh -c &quot;kubectl get pods&quot; never presents the command at the top level at all — and neither does eval, ssh host &#39;…&#39;, a pipe into a shell, or an inline python3 -c script.</p><p>So we ended up writing a small purpose-built checker rather than relying on a pattern: something that understands just enough shell structure to work out what is <em>actually</em> being executed, including when it’s wrapped a layer or two deep. Less pattern-matching, more parsing — which turns out to be the shape of this problem generally, the moment your input is a shell string rather than a tool call.</p><p>It’s best-effort, and we treat it that way. A shell is expressive enough that no checker is ever complete, which is exactly why the real boundary lives elsewhere: in the gateway’s RBAC and in which credentials an agent session holds at all. The hook’s job is narrower — make the MCP the path of least resistance and the raw path fail. Alongside it, the agents’ own instructions point at the MCP for anything cluster-shaped. In practice that combination works well.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tOFA_xkeWA7sgNqHSfW7LA.png" /></figure><h3>Elevated operations stay human-only</h3><p>Some operations aren’t things an agent should be able to do at all — assuming an elevated cloud role, decrypting a secret parameter. The hook blocks the agent from these by design, full stop: there’s no MCP tool for them, and a human runs them, in their own shell, under their own identity.</p><p>This is a deliberate line, not an oversight: agents don’t get elevated cloud power, ever, regardless of how the request is phrased or how legitimate the task sounds. The MCP’s scoped RBAC from Part 1 defines everything an agent <em>can</em> reach; this is the explicit statement of what it categorically cannot.</p><h3>The dividend: a door you control is a door you can measure</h3><p>Closing the raw path did something we didn’t fully appreciate until after it shipped: it turned the gateway into the single point every cluster interaction has to pass through — and a chokepoint you control is a chokepoint you can <em>measure</em>. Once every interaction is forced through one authenticated gateway, cluster access stops being invisible and becomes legible: you can log it, attribute it, and put it on a dashboard.</p><p>Human cluster access was already on a controlled path: just-in-time credentials, issued per person, expiring on their own. What that path doesn’t give you is a picture of <em>agent</em> behaviour. An agent working inside a developer’s shell inherits that developer’s credentials — so whatever it does arrives with no separate identity, no scope of its own, and no record that distinguishes it from the human. And because each CLI call goes straight from a laptop to the API server, there’s no aggregate view either: no way to ask which clusters agents are hitting, which operations they actually reach for, or whether one has started looping. Routing agents through their own gateway is what turns that from guesswork into a dashboard.</p><p>We didn’t set out to build an audit system. We built a chokepoint for security reasons, and the chokepoint turned out to double as a sensor.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m5dLvwXs18M3D4ULH7M_Pw.png" /></figure><h3>Two signal sources, two different questions</h3><p>Two independent layers of the same design each answer a different question, and together they cover the ground raw kubectl never did.</p><p><strong>The front door: identity, via Cloudflare Access.</strong> Every connection to the MCP is an authenticated, per-user OIDC session — true by construction, because the block made it the only way in. Cloudflare Access logs each of those sessions natively: who authenticated, which cluster’s MCP endpoint they hit, and when. That gives the agent side its own record — <em>which identity opened a session against which cluster, and when</em> — tied to a real Okta identity instead of riding a developer’s credentials, and it lives in Cloudflare’s own Access logs, no export required.</p><p><strong>The door itself: usage, via MCP server metrics.</strong> The MCP server exposes Prometheus-style metrics, and Datadog’s OpenMetrics autodiscovery scrapes them per cluster, each cluster landing in its own metric namespace. That gives us request volume, a breakdown by which tool was actually called (list pods vs. exec vs. describe — the operations agents reach for in practice, not the ones we assumed they would), latency, and error rate. Rolled up across every cluster, it’s one “Kubernetes MCP fleet usage” dashboard instead of N clusters we’d otherwise check one at a time.</p><h3>What this actually buys you</h3><p>Concretely, questions that used to require guesswork now have answers sitting in a dashboard:</p><ul><li><strong>Which clusters are agents actually hitting, and to do what?</strong> Not which clusters they’re <em>authorized</em> to hit — which ones see real traffic, and which tools dominate it.</li><li><strong>Is an agent misbehaving?</strong> A request-volume spike on one cluster, or one tool suddenly dominating call volume, is a visible signal — the kind of thing that used to stay invisible until someone noticed a symptom downstream.</li><li><strong>Which operations do agents actually reach for?</strong> Not the policy on paper, but what they really do, cluster by cluster.</li><li><strong>A clean answer for an access review.</strong> “Here’s every agent session against production this quarter, by identity, and what it called” — the agent side of the house documented to the same standard as the human one.</li></ul><p>None of this comes out of a CLI-based path, however well governed: there’s no gateway in the middle to instrument.</p><h3>It closes the loop on the blast-radius model</h3><p>Part 1’s whole argument was about shrinking blast radius: production is read-only, sessions expire in minutes, and the scope of what any single session can do is deliberately small. Add this layer on top and the picture sharpens further — an agent touching production isn’t just constrained, it’s <strong>rare, short, attributable, and countable</strong>. The security posture and the observability posture reinforce each other: the smaller and more logged the blast radius, the less any single touch matters, and the easier it is to notice the one that does.</p><h3>What’s next: a full trace per tool call</h3><p>The two signals we have today answer different questions at different resolutions. Cloudflare Access tells us <em>who</em> opened a session against <em>which</em> cluster. The MCP server’s metrics tell us <em>what kind</em> of work happened there, in aggregate — call volume, which tools, latency, errors. What neither gives us is the middle: a per-call record tying one specific operation to the identity that asked for it.</p><p>The path to that is already in the building. We run an internal capability layer called Loom — one platform service hosting connectors to our enterprise systems behind the same Cloudflare Access edge, rather than a microservice per integration. We wrote about the thinking behind it in <a href="https://medium.com/tailor-tech/dont-build-the-integration-build-the-capability-bcae956a172d">Don’t Build the Integration, Build the Capability</a>. The relevant property here is that Loom already sits <em>in the request path</em>: authenticated requests pass through it on the way to the backing system.</p><p>Anything in the request path can be instrumented. Proxying the cluster MCPs through Loom would turn each tool call into a traced event — this identity, this cluster, this operation, this outcome — instead of a counter that went up. That’s the difference between “somebody authenticated to production this afternoon and there were forty MCP calls” and “this person listed pods in that namespace at 14:03.”</p><p>It isn’t built yet. But it’s the natural next step, and it follows from the same idea the whole series rests on: put a door where you can see it, and everything downstream gets easier — enforcement first, and then the trace.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dd7b4ba0b80d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/make-the-mcp-the-only-door-and-every-cluster-touch-becomes-visible-dd7b4ba0b80d">Make the MCP the only door — and every cluster touch becomes visible</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Letting AI agents into our Kubernetes clusters-without handing over the keys]]></title>
            <link>https://medium.com/tailor-tech/letting-ai-agents-into-our-kubernetes-clusters-without-handing-over-the-keys-ea9cb7033c6e?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/ea9cb7033c6e</guid>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[mcp-server]]></category>
            <category><![CDATA[cloudflare-zero-trust]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[security]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Thu, 16 Jul 2026 07:06:01 GMT</pubDate>
            <atom:updated>2026-07-16T07:06:01.509Z</atom:updated>
            <content:encoded><![CDATA[<p>Hand an autonomous agent an infrastructure incident to investigate, and most of what it needs it can already get without touching a cluster: logs, metrics, deployment status, error rates, and traces all live in your observability stack. Our agents reach that through a Datadog MCP, and they reach the cloud control plane through an AWS MCP. There’s no official AWS MCP, so we built a thin one ourselves — a read-only server backed by a dedicated IAM role scoped to exactly what the agents need, nothing more. For a large share of investigations, that combination is genuinely enough. We deliberately do <em>not</em> want agents leaning on raw cluster access for the things observability already answers better.</p><p>But some trails lead where observability doesn’t follow — a DNS resolution failure inside the cluster, a network policy silently dropping traffic, a CNI or service-mesh quirk, the live state of a resource nothing happens to be scraping. That’s when an agent needs to look at the cluster itself. So the interesting combination isn’t “give the agent a cluster” — it’s giving the agent AWS, Datadog, <em>and</em> the cluster, each through its own MCP, so it can line up what the cloud and the dashboards are saying against what the cluster is actually doing. An agent that can query all three is a genuinely strong first responder on an infra issue; one that’s blind to the cluster keeps handing the hard part back to a human.</p><p>The catch is <em>how</em> you give an agent that cluster access. The lazy answer is to hand it a kubeconfig — the same one on your laptop, with the same standing permissions, sitting in a file an agent (or a bug in the agent’s tool-use) can read at any time, valid until someone remembers to rotate it. That’s broad, long-lived, and unaudited: exactly the three properties you don’t want anywhere near something that writes its own commands.</p><p>The right answer isn’t “no access.” It’s <strong>access scaled to blast radius</strong> — how much damage a mistake in that specific environment can actually do. This post is the shape of that model: a gateway in front of every cluster, read-only everywhere by default, write access carved out in exactly one place on purpose. Part 2 covers how that gateway is actually enforced as the <em>only</em> door in. Part 3 covers what you get once every touch through it is a structured event: an audit trail for agent-driven infrastructure work that didn’t exist before.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*isTPPl0ZMWwcBKpi7lhFew.jpeg" /></figure><h3>The gateway: agents talk to an MCP, never to the API server</h3><p>The mechanism that makes any of this possible is the <strong>Model Context Protocol (MCP)</strong> — a standard way to expose tools and data to an AI agent as a discrete, named set of callable actions, instead of a general-purpose shell. An agent that has “an MCP tool called get_pod_logs&quot; can be scoped, logged, and rate-limited in a way that an agent with &quot;a shell and a kubeconfig&quot; cannot.</p><p>The design puts a <strong>Kubernetes MCP server</strong> — an <a href="https://github.com/containers/kubernetes-mcp-server">open-source project</a> built specifically to expose cluster operations (get, list, logs, describe, and optionally exec) as MCP tools — in front of <strong>each</strong> cluster individually. One instance per cluster, not one shared instance for the fleet. Agents talk to that MCP; they never see the cluster’s own API server endpoint or a credential that would let them reach it directly.</p><p>That’s the load-bearing structural decision: the MCP is not a convenience wrapper around kubectl, it&#39;s the entire boundary. Everything downstream — identity, RBAC, session length — is a policy applied at that one chokepoint, per cluster.</p><h3>Identity and time, not network reachability</h3><p>Each per-cluster MCP is published behind <strong>Cloudflare Access</strong> (Zero Trust), using <strong>Okta</strong> as the identity provider through managed OAuth. Practically, that means every connection to an MCP is an authenticated, per-user OIDC session — not a shared secret that, once leaked, works for anyone who has it until someone notices and rotates it.</p><p>This is the principle worth naming explicitly, because it’s easy to build the opposite by default: <strong>the control plane here is identity and time, not network reachability.</strong> It’s tempting to reach for network controls — this cluster’s API server is only reachable from this VPN, this bastion, this CI runner — because they’re familiar and easy to reason about topologically. But network reachability answers “where are you connecting from,” not “who are you and for how long.” A laptop on the right VPN with a stale kubeconfig is a bigger risk than a short-lived, per-user, RBAC-scoped session issued five minutes ago — even when the session is read-only. Identity-and-time as the primary gate, with network position as a secondary layer at most, is the harder default to build but the one that actually matches the threat: it’s not usually attackers on the wrong network, it’s standing credentials nobody remembers exist.</p><p>Concretely, each MCP instance runs under its own dedicated Kubernetes <strong>ServiceAccount</strong>, and that ServiceAccount’s <strong>RBAC</strong> is the actual enforcement of what an agent connected through it can do. One rule holds everywhere, no exceptions: a set of sensitive resource types is denied outright on every cluster- <strong>secrets among them</strong> - regardless of environment or session length.</p><h3>Blast radius sets the policy, not convenience</h3><p>With the chokepoint and the identity model in place, the interesting design question is: what should the RBAC on each cluster’s ServiceAccount actually allow? The answer is scaled directly to what a mistake in that cluster costs:</p><ul><li><strong>dev</strong> — read <strong>and write</strong>, but still bounded: create, update, delete, and exec, scoped to a single namespace and subject to the same denied-resource rules as every other cluster. This is the deliberate <strong>acceleration zone</strong>. <br>An agent here can restart a deployment, apply a fix, patch a config, and verify the result in the same loop, without a human relaying every command. That’s the entire point of giving an agent cluster access in the first place, and dev is where a wrong guess costs a kubectl rollout undo, not an incident.</li><li><strong>staging, production, ops, and data clusters</strong> — <strong>read-only</strong>: get, list, logs, top, describe. No writes, anywhere, no matter how confident the agent is. The blast radius of a mistake here is real users or real data, so the answer to “can the agent fix this itself” is no — it can <em>diagnose</em>, and a human (or a more constrained path — see Part 2) acts on the diagnosis.</li></ul><p>Session lifetime follows the same logic and is tuned per cluster: dev sessions run on the order of a day, since the whole value of the acceleration zone is not re-authenticating every few minutes. An internal ops cluster sits around an hour — enough for a focused debugging session, short enough to bound exposure. Production sessions are deliberately short — on the order of ten minutes — because even read access to production should not be a thing you forget you’re holding. Different clusters are also gated to different identity groups in Okta, so “who can even request a session here” is itself scoped before RBAC ever comes into play.</p><p>None of these numbers are load-bearing on their own — they’re design choices, tunable as the system matures. What’s load-bearing is the shape: <strong>the acceleration zone is exactly one environment wide, and every other environment defaults to read-only with a short leash.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xi7ZhannVWv6pWuKkCIEAw.png" /></figure><h3>Two identity planes, one deliberately narrower than the other</h3><p>There are two separate ways to reach a cluster, and keeping them separate is itself a design decision. Humans who still need raw kubectl — for the operations that genuinely require it — authenticate through a cloud IAM assume-role flow gated by TOTP: a real, if familiar, credential-issuance path with its own standing risk. Agents authenticate through the Cloudflare Access → Okta OAuth flow into the MCP, and that path never distributes a cluster credential of any kind to anything the agent can read or exfiltrate. An agent session and a human kubectl session don&#39;t share a mechanism, a credential format, or a blast radius — which means hardening one doesn&#39;t quietly weaken the other, and a compromise of the agent&#39;s tooling can&#39;t be laundered into raw cluster access.</p><h3>What’s still open</h3><p>Two honest gaps, both intentional rather than overlooked. Read-only outside dev means some real operational tasks — restarting a stuck pod in staging, clearing a bad cache entry in an ops cluster — currently have no path through the MCP at all. That’s not an oversight; it’s next in this series: Part 2 is about making the MCP the <em>only</em> door, including how the narrow, audited paths for the operations that do need to write outside dev get built without reopening the blast radius this whole model exists to contain.</p><p>The other gap: per-user attribution end-to-end — knowing precisely which identity, on which agent run, issued which specific call — is still maturing. Today you know who opened the session; tracing an individual tool call back through an agent’s own reasoning chain is a harder problem, and it’s the subject of Part 3, where every cluster touch becomes a structured, queryable event.</p><p>Access scoped to blast radius, gated by identity and time rather than network position, is the foundation everything else in this series builds on. It’s also, on its own, a stronger baseline than most humans get: a ten-minute, RBAC-scoped, per-user session to production beats a kubeconfig that’s been sitting in ~/.kube/config since a laptop was issued. Part 2 picks up from here — how that MCP boundary gets enforced as the <em>only</em> way in, for humans and agents alike.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ea9cb7033c6e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/letting-ai-agents-into-our-kubernetes-clusters-without-handing-over-the-keys-ea9cb7033c6e">Letting AI agents into our Kubernetes clusters-without handing over the keys</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Don’t Build the Integration, Build the Capability]]></title>
            <link>https://medium.com/tailor-tech/dont-build-the-integration-build-the-capability-bcae956a172d?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/bcae956a172d</guid>
            <category><![CDATA[mcp-server]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[security]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[zero-trust]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Sun, 12 Jul 2026 07:47:27 GMT</pubDate>
            <atom:updated>2026-07-12T07:47:27.887Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A platform team’s job with AI isn’t to wire every agent into every system. It’s to make any team’s secure, agent-ready connection a same-day job. Here’s the design work that gets you there.</em></p><p>Every department wants agents. Marketing wants one wired into their campaign tools so it can draft and schedule sends without leaving the conversation. IT wants one connected to their device-management system so fleet questions answer themselves. Support wants theirs sitting on top of the ticketing platform. Data wants the warehouse accessible as naturally as a chat. And everybody wants to search the company knowledge base without switching tools.</p><p>That’s a lot of connectors — and if you build them one at a time, you’re in trouble before you’ve shipped the third one. You’ve got a scatter of half-maintained services, no coherent identity model, and a platform team that’s bottlenecked every time someone files a new “we want an agent” request. The problem isn’t any single integration. The problem is that one-at-a-time is the wrong unit of work.</p><p>Here at Tailor Brands, we ran into this early. So instead of building integrations, we built a capability — an internal platform we call <strong>Loom</strong>, designed so that any team can have a new secure, agent-ready connector in roughly a day. This post is about the design decisions that made that possible.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9QqriSVTNCh2WXnFRojOFQ.png" /></figure><h3>The trap: one integration at a time</h3><p>The mechanics of connecting an agent to a single system are, by now, a solved problem — there’s a standard protocol for it (MCP), and the tutorials all walk the same happy path. The catch is the words <em>single system</em>: you stand up a connector for the marketing platform, another for the ticketing system, another for device management. When you do that at company scale you end up with something like:</p><ul><li>A new microservice per connector, each one owned (or forgotten) differently.</li><li>Auth solved independently on each one — some use API keys in the environment, some roll their own token check, none of them share a policy.</li><li>Observability bolted on differently if at all.</li><li>The platform team as the person who does this work every time a new request comes in.</li></ul><p><strong>The goal isn’t to build the integrations. It’s to build the capability to produce integrations quickly and safely.</strong> That reframe changes the design completely.</p><h3>Design decision 1 — one platform, many connectors</h3><p>The first decision: instead of one service per connector, Loom hosts all of them inside a single platform service.</p><p>Here’s why this works. When you actually sit down to build a connector, the unique work per connector is surprisingly small: talk to the upstream API, shape the results in a way that’s useful to an LLM, expose the right operations. That part varies by connector. But everything around it — identity verification, request routing, graceful shutdown, structured logging, health endpoints — is identical across all of them. Built once in a shared chassis, maintained once.</p><p><strong>The unique work per connector is small. The scaffolding is large. Build the scaffolding once.</strong></p><p>The trade-off is obvious and worth naming honestly: connectors deploy together. If you want to ship an update to the marketing connector, you redeploy the platform, which also redeploys the ticketing and device-management ones. For an internal platform — where you control the pipeline and the workloads — this is actually fine. One deployment pipeline, one reliability surface, one place to find logs. For a product you sell to external customers where each connector might have different SLAs, you’d think twice. For us, it’s strictly a feature.</p><p>Today Loom runs roughly seven connectors across departments: Marketing, IT, Support, Data, an org-wide knowledge base backed by vector search, a shared file store, and a connector for our internal deployment system — all built and hardened over a couple of months. That it didn’t take a big team is the whole point of this post: most of each connector is scaffolding we only had to write once.</p><h3>Design decision 2 — solve identity once, at the edge</h3><p>The second decision is where to enforce authentication — and the answer is: not inside the connectors.</p><p>Every Loom connector sits behind a zero-trust access layer. Traffic arrives at the edge — a Cloudflare Access layer in front of the platform — which verifies the request against our company SSO (Okta) before it ever reaches the application. The edge authenticates; the application trusts. When a request makes it through, a signed identity assertion is forwarded downstream. The connector decodes it to know who’s calling; it doesn’t re-verify, because the only way a request reaches it is through the edge.</p><blockquote><em>“A connector should assume it’s already behind a locked door — don’t build a second lock inside an already-locked room.”</em></blockquote><p>The consequences of this choice are significant:</p><ul><li><strong>Access policy becomes a per-connector dial, not per-connector code.</strong> Who can reach the device-management connector versus the marketing connector is a configuration entry in the edge layer, not a logic branch in application code.</li><li><strong>Security review has one address.</strong> When you want to audit how agents are authenticated against your systems, there’s one place to look.</li><li><strong>The OAuth machinery lives at the perimeter.</strong> Agent clients need to discover connectors and register against them dynamically — the protocol defines an OAuth discovery and dynamic client registration flow per resource. That flow belongs at the edge — it has to know about auth — not scattered inside six different application handlers.</li></ul><p>That last point is exactly why the next decision matters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UMMej4FXvf4aW6zTZPpfaw.png" /></figure><h3>Design decision 3 — a front door per connector</h3><p>We started Loom with path-based routing under a single host: one hostname, paths like /marketing, /support, /device-management. It seemed simpler. It wasn&#39;t.</p><p>The problem is that access control, OAuth discovery, and request routing all want to operate at the level of a connector, not at the level of a path suffix. Cloudflare Access policies are scoped to hosts. OAuth discovery endpoints live at /.well-known under the resource root — and &quot;the resource&quot; has to be a host, not a path prefix, or the client registration flows break. The routing layer wants to match by host.</p><p>When every layer independently wants host-level boundaries, path-based routing is fighting the grain of the stack. <strong>We switched to a dedicated subdomain per connector</strong> — marketing-mcp.example.internal, support-mcp.example.internal, and so on. It looks like more pieces, but it&#39;s actually simpler: one clean addressing scheme that the edge, the OAuth flows, and the routing layer all agree on. That alignment keeps everything else simple.</p><h3>Design decision 4 — design for scale-out from day one</h3><p>Agents generate bursty, concurrent load. A single conversation can spawn multiple parallel tool calls; a shared deployment tool or knowledge base sees that load multiplied across every employee using it. The only scalable answer is to run many interchangeable replicas behind a load balancer.</p><p>That only works if each request is self-contained. <strong>Statelessness is the constraint — but a narrower one than it first sounds.</strong> The rule isn’t <em>no state</em>; it’s <em>no state in the process</em>. If a connector holds session context in memory, or caches user data on one pod, then every request has to land on that same pod and you’ve given up horizontal scale. Shared state is fine, as long as it lives somewhere every replica can reach: auth arrives in the forwarded assertion, call context rides in the request, and anything that genuinely needs to persist goes to a backing store. One of our connectors does exactly this — it keeps per-user credentials for a task-management tool in Postgres, which keeps the pods interchangeable and, as a bonus, opens the door to richer stateful features later. The pods stay disposable; the state just doesn’t live inside them.</p><p>It’s less elegant in the small. There are moments when stateful shortcuts feel natural — holding onto a cursor, caching an auth token per user. We push back on all of them. <strong>Statelessness is less elegant in the small; far more robust in the large.</strong> An extra round-trip or a lookup in a fast shared store is almost always the right call over process-local state you have to maintain.</p><h3>The payoff — make the next connector cheap</h3><p>Once the pattern stabilized — shared chassis, edge auth, subdomain boundary, stateless design — adding a new connector became mechanical. Describe what system you’re connecting, what operations it exposes, and who should have access. Stand up the connector code (which is genuinely small once the scaffolding exists). Add an access policy entry at the edge. Add a routing entry for the new subdomain. That’s it.</p><p>Mechanical work should be automated. Once the pattern repeated often enough to be confident it was stable, we wrapped the entire rollout in a single command: describe the connector and it scaffolds the connector code, wires the access policy, wires the routing entry, and opens everything for review as a single coherent change. What used to be a multi-repo, multi-step process scattered across a few different systems is now a guided conversation.</p><p>The effect on how the platform team relates to the rest of the company is real. The answer to “can we get an agent connected to X?” went from “let’s scope a project” to “let’s do it this week.” <strong>The platform team becomes the on-ramp, not the gate.</strong> That’s the outcome the design was aimed at from the beginning.</p><p>There’s a second-order effect that’s easy to miss: when adding a connector is cheap, connectors become <strong>disposable</strong>. Where a solid official or community connector already exists, we just use it. Where one doesn’t — or where it exists but doesn’t match how our teams actually work — we build our own, tuned to exactly what we need. And because the investment in any single connector is small, we’re never precious about it: one that stops earning its keep gets thrown away and replaced, not maintained forever out of sunk cost. Being able to treat integrations as disposable — use, build, swap, or delete on demand — turns out to be a surprisingly powerful place to stand.</p><p>The automation didn’t come first, though — and that honesty matters. We built it only after the pattern had repeated enough times that we were confident it was stable and worth encoding. Automating an uncertain pattern just makes it faster to make the same mistake in more places. Get the pattern right first; automate second.</p><h3>What we’d carry forward</h3><p>If you’re building something similar, here’s what the design history argues for:</p><ol><li><strong>Build the capability, not the integrations.</strong> One shared chassis, maintained once.</li><li><strong>Push security to the edge.</strong> Auth solved in one layer, surfaced as policy, not code.</li><li><strong>Pick a boundary the whole stack agrees on.</strong> Subdomain-per-connector aligns routing, access control, and OAuth discovery — fighting that alignment is expensive.</li><li><strong>Design for many replicas early.</strong> Statelessness is a constraint, not a nice-to-have; retrofit is much harder than design.</li><li><strong>Automate the rollout only after the pattern is stable.</strong> Premature automation locks in early mistakes.</li></ol><p><em>Small tip from my perspective:</em> the departments asking for connectors don’t think about your routing layer or your trust model. They care that it showed up the same week they asked, and that they can trust it with their data. The platform abstractions exist to make that reliability and speed possible — not to be visible.</p><p>Agents are only as valuable as the systems you safely connect them to. An agent that can reason beautifully but can’t touch your CRM, your device management layer, or your company knowledge is a clever toy. A platform team’s job isn’t to build every flow — it’s to make every flow possible.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bcae956a172d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/dont-build-the-integration-build-the-capability-bcae956a172d">Don’t Build the Integration, Build the Capability</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Remote Development Environment Supercharged with MCP servers]]></title>
            <link>https://medium.com/tailor-tech/remote-development-environment-supercharged-with-mcp-servers-c4b959cb321c?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/c4b959cb321c</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[mcp-server]]></category>
            <category><![CDATA[dx]]></category>
            <category><![CDATA[development-environment]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Thu, 14 Aug 2025 08:33:38 GMT</pubDate>
            <atom:updated>2025-08-14T08:33:38.282Z</atom:updated>
            <content:encoded><![CDATA[<p>Developer environments have been a headache since the dawn of software. Keeping them in sync with production, getting fast feedback, and dodging the dreaded “works on my machine” problem has never been easy. And with today’s complex stacks — microservices, data pipelines, service meshes, multiple databases — the gap only gets wider, and the cost of setup and upkeep keeps climbing. Like many teams, we’ve tried many approaches to make it less painful.</p><p>In 2020, we transitioned from a local development environment using docker-compose to a remote development environment.</p><p>docker-compose has its limits when it comes to a microservices architecture — as the number of microservices grew, it forced vertical scaling that didn’t make sense. The developer experience (DX) wasn’t ideal — we tracked how much of our developers’ time was wasted fixing local environment issues, dealing with database migration problems, updating local repositories, etc. — it was a LOT.</p><p>We (the platform team) tried to simplify and abstract away docker-compose commands and enhance the developer experience by building a Golang CLI tool (conjure). That helped, but it didn’t solve the core issues, which grew over time (more microservices, more teammates) and pushed us to find alternatives.</p><p>We looked for a development environment that would handle most of our team’s pain points and would be scalable — after some research, and with no ready-to-use product or framework available, we launched an internal project to create a development environment that would use the infra tech stack we already had at the time.</p><p>For the planned environment, we needed AWS EKS, Argo CD, and GitHub Actions, which we were already using for our staging and production environments.</p><p>Recently, we’ve further enhanced our infrastructure by integrating MCP (Model Context Protocol) servers into the development lifecycle for our full-stack and data teams.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jNpEUdC-rHFTOAyt6vQ0qQ.jpeg" /></figure><h3>Remote Development Environment</h3><p>As mentioned, we’ve had this development environment for more than five years. During this time, we’ve updated and improved it — it has evolved alongside our production stack — but the core design is still the same. I’ll briefly explain how it’s built and used so we have better context later when I describe the tools introduced with the new MCP servers.</p><p>The environment is a remote-only setup that mirrors our production architecture as closely as possible.</p><p>Each developer gets their own personal Kubernetes namespace in AWS EKS, ensuring complete isolation and preventing conflicts. A simple Slack message is all it takes to spin up a personal namespace.</p><p>This remote-first approach extends to all operations: all tests and critical operations are executed remotely, leveraging environment variables and secrets configured exclusively within the remote pods. This design choice ensures our development work is always performed in the proper context, significantly reducing “it works on my machine” issues and ensuring a smooth transition to staging and production.</p><p>During the day, we have dozens of releases to production. Our CI/CD releases new versions to all environments, so all development namespaces receive updates continuously, using Argo CD (with auto-synced Argo applications). That includes database migration processes, which run as Kubernetes Jobs as part of each Argo CD application’s sync process.</p><p>One of the important factors in good DX is the feedback loop — a fast and informative feedback loop. When a developer creates something and can see the results immediately — the logs, the UI, and any other part of the system affected by the change — it’s really nice and speeds up the development process.</p><p>For that, we’re using automatic file synchronization powered by <a href="https://www.devspace.sh/docs/cli/devspace_sync">Devspace CLI</a>, which creates a seamless bidirectional flow between local files and the remote container.</p><p>To optimize resource usage and costs, our environments include an “auto-shutdown” mechanism that scales ephemeral services down to zero replicas in the evenings.</p><p>Despite the benefits of DevSpace CLI for file synchronization, developers still face one challenge: they have to shell into the synced container to run tests and review logs.</p><p>To reduce this cognitive load, we used conjure to abstract away the complexity of numerous under-the-hood actions — like applying deployment patches, watching logs, shelling into remote containers, and issuing DevSpace CLI commands directly — so developers can focus on their core tasks.</p><p>Lately, like everyone else, we started using agents in our IDE (Cursor). With its agentic mode and the options it opens up for us, we figured we could push DX even further by creating MCP servers that help developers debug issues, let the agent write tests and execute them remotely for a full feedback loop, and add a few more handy tools.</p><h3>Integrated MCP Servers for Cursor IDE Agentic Workflows</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mChO7mc_ObcRs6ka-PzscA.png" /></figure><p>Working with AI agents is amazing — at least that’s the first impression you get when you try them. Over time, you discover the importance of good context and prompts, and their current limitations (for now) — but that’s a bigger topic we might cover later. For now, as a platform team, we want to optimize our teams’ internal workflows, DX, velocity, and quality as much as we can, and we really love quick big wins (these MCPs were developed in a few days).</p><p>We implemented three MCP servers that serve our full-stack and data teams:<br>1. Dev<br>2. Postgres<br>3. Redshift</p><h4>Dev MCP</h4><p>The Dev MCP is for the full-stack teams. We developed it to address known pain points and optimize development workflows, with the following capabilities:</p><ul><li><em>Log analysis —</em>a developer can ask the AI agent to scan the remote container’s logs to help debug issues and provide insights</li><li><em>ArgoCD operations —</em>allows AI agents to perform operations on the developer’s Argo CD applications (e.g., sync the application, analyze resource events, etc.).</li><li><em>Health check — l</em>ets an AI agent check a service’s health or the status of the whole dev environment. It reviews deployment statuses, analyzes logs if needed, and reports the results. If the issue is fixable with other tools, it will try to fix it as well.</li><li><em>Service detection —</em>verifies that the service we’re working on is up and running and that the remote service name aligns with the repo name.</li><li><em>exec —</em>allows the AI agent to execute commands in the remote container (e.g., run tests, lint checks, etc.)</li></ul><p>The full-stack teams also crafted Cursor rules for some common daily workflows, like prompts for testing different packages. They can trigger those rules, and the AI agent will implement tests according to the standards we set, execute them in the remote container (a pod), and fix issues on its own — with the Dev MCP at it’s disposal we get a nice combination!</p><p>We rolled out these rules across all our repos (we use a multi-repo architecture), organized by language, with options to override or add custom rules via Terraform. It’s now self-service for the teams: they can update or add prompts/rules via GitHub PRs, and everyone benefits from each other’s work and investment.</p><p>The Dev MCP also takes care of small, commonly spotted issues in development environments, reducing the time the platform team needs to handle them.</p><h4>Postgres MCP</h4><p>This MCP is also for the full-stack teams — the name is pretty explicit, but I’ll add a few details anyway.<br>We implemented tools to help developers explore and more quickly understand the entities in each database. For exploration, we added tools like show_schemas, list_tables, describe_table, and list_indexes.<br>Queries are executed using the execute_query tool, with built-in default query limits. We also added a tool to try to optimize SQL queries by analyzing them: explain_query. These tools allow the AI agent in the IDE to check results, help with schema/database exploration, prepare mock data to test migrations, etc.</p><p>With both of these MCP servers, the AI agents in our IDEs can not only assist with coding but also access the dev ecosystem to debug issues, simulate and implement data migrations, run tests, and much more. In the end, this helps developers skip some of the grunt work, focus on the interesting parts of coding, and save a LOT of time.</p><h4>Redshift MCP</h4><p>Redshift MCP was built for the data team.<br>Similar to the Postgres MCP, we added tools for schema exploration and query execution.<br>We created a dedicated role in our development Amazon Redshift Serverless with read permissions on all schemas, including those <a href="https://docs.aws.amazon.com/redshift/latest/dg/datashare-overview.html">shared from other Redshift cluster</a>s. <br>That means data engineers and analysts now have an AI agent that can safely explore data across development and production.</p><p>We also created a dedicated schema for this role, with write permissions, so it can execute queries and write temporary data for further analysis or review.</p><p>The data team is also crafting internal prompts — using internal context — to produce ready-to-use explorations and analyses that leverage the Redshift MCP and its access to all our data directly from the analyst’s IDE, which can significantly speed up workflows that once took a LOT of time.</p><h3>Current states and further steps</h3><p>Current LLMs already perform reasonably well, and by giving them custom tools that can access our internal data and improve context, we’re seeing better results. They help automate the development cycle: <em>implementing some code</em> -&gt; <em>test it</em> -&gt; <em>in case it fails it can now analyze remote container logs, check the state of the data developer’s database etc …</em> -&gt; <em>identify the issue and fix it</em>. <br>This action cycle exists without MCP servers, but it’s not nearly as rich or powerful — MCP makes a big difference.</p><p>We rolled out the MCP servers to the teams a few months ago. Since then, the teams have improved the tools’ functionality and instructions, tuned them to their needs, and are very happy with the results and artifacts they produce.</p><p>These are very interesting times, and as a platform team, we need to stay on it and utilize these new tools and capabilities to improve the teams’ DX, velocity, and quality.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c4b959cb321c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/remote-development-environment-supercharged-with-mcp-servers-c4b959cb321c">Remote Development Environment Supercharged with MCP servers</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using Database Read Replicas In Distributed Systems]]></title>
            <link>https://medium.com/tailor-tech/using-database-read-replicas-in-distributed-systems-d80eaf6bbf8a?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/d80eaf6bbf8a</guid>
            <category><![CDATA[rds]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[istio]]></category>
            <category><![CDATA[redis]]></category>
            <category><![CDATA[golang]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Wed, 02 Oct 2024 06:34:22 GMT</pubDate>
            <atom:updated>2025-08-13T10:52:15.187Z</atom:updated>
            <content:encoded><![CDATA[<p>When scaling web applications a common approach is to add read replicas for database access. While this is a good approach it comes with different caveats for different use cases.</p><p>Here at Tailor Brands we’ve added read replicas long ago for specific cases but wanted to create a more robust solution that can be used in a much wider range of application and use cases without requiring additional cognitive load of planning.</p><p>One of the main concerns when using read replicas comes from the inherent nature and implementation where, there is a replication lag between the primary instance and its read replica(s). This lag can cause bugs and data issues in cases where a system expects data consistency across all nodes at all times. As such if it’s not the case, due to the replication lag, a read replica might return stale data.</p><p>There isn’t a single correct way to handle the replication lag in a distributed system since the approach chosen depends deeply on the use case and architecture at hand.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RXVYDYuc0RkZhz_PQfL8EQ.png" /></figure><h3>Architecture and Constrains</h3><p>At Tailor Brands, we’re using AWS EKS service to deploy our microservices and we’re using <a href="https://istio.io/">Istio</a>, as our service mesh.<br>For the east-west (internal) traffic, we’re using gRPC and for the north-south (external) traffic, we’re using HTTPS (Rest).<br>We’re also using PubSub messages and workers for asynchronous workloads.</p><p>As our main relational Database, we’re using AWS Aurora RDS cluster with <a href="https://www.pgpool.net/mediawiki/index.php/Main_Page">PGpool-II</a> (also on EKS) for connection pooling and query load balancing.<br>The average lag between the primary instance and the read replica is about 20ms, which is quite low thanks to <a href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.html">Aurora architecture</a>. As you’d expect, it changes constantly, it usually corresponds to the load on the primary instance and also dependent on AWS infrastructure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NTgxIlQFDmCb8w-cGZeOjg.jpeg" /></figure><h3>Replication Lag Use Cases</h3><p>Before diving in to the solutions, let’s cover the use cases we need to handle, so we’ll have a clearer view of what we’re trying to solve.</p><p>First, let’s cover the HTTPS (Rest) API calls use cases -<br>We’re quite strict on staying “Restful” here at Tailor and we’re consistent in our HTTP Verb usage.</p><p>So it follow that for mutation verbs, such as POST, PUT, PATCH — we should exclusively access the primary instance since it’s a “write” operation. it’s pretty easy to achieve this with our setup, we can just wrap all these calls in transactions and PGpool-II will use the primary instance for all queries within the transaction. It’ll use the primary for read queries (select) during the transaction as well, this is important for read-after-write cases which sometimes occur. Another added bonus here is that we make sure all our mutating operations are wrapped in transactions.<br>While this can cause other issues (due to locks for example) this isn’t something we’re going into in this writeup.</p><p>For a read verb, GET — we probably want to use the read replica (almost) always. We might think this should just be always since even for “read-after-write” cases we’re not likely to get stale data from the read replica, since usually the network time for the response and another request would be far greater than 20ms. But — this is still theoretically possible and since “unlikely” is not something we can rely on, this case need to be covered.<br>Another concern here is that the data might get updated in the background by a PubSub call or something else and in that case we’ll again have a race condition for getting the updated data, tho in that case we don’t have any guarantee on background process timing so we can ignore it.</p><p>For gRPC calls, it’s pretty much the same as for the HTTP calls in term of handling the calls and the communications with the Database. The only different is that we don’t know off hand if a call is mutating or not. But either way it won’t matter as we mentioned before, we’ll need to handle both cases anyway.</p><p>For the asynchronous workloads (workers/PubSub) — in these cases we’re more likely to get stale data — we’re keeping our queues as small as possible and the worker handlers trigger almost immediately after a new task is added to a queue.<br>Our workers are using Redis and the roundtrip to Redis is very short (about 5ms-10ms), usually less then 20ms.<br>In case we’re updating some records and enqueuing some job that depends on that updated data, the job will usually start the handling in our system in under 10ms and it will get the old record, which might cause some very nasty data bugs and bad user experience. This sort of issue can also be quite hard to debug, especially if you don’t have the same lag latency in Dev (or you might not even be using read replicas).</p><h3>Solutions</h3><p>We needed a solution that meet’s the following requirements:<br>* Minimum performance impact on our application.<br>* Minimum changes to our services (code changes).<br>* Minimum cognitive load on developers.<br>* Minimum time for implementation, 4 weeks max.</p><p>The first option that we ruled out was to define lag SLAs and to increase the cognitive load on the developers by making them handle the lag on application side (such as enqueue asynchronous jobs with some sort of delay).<br>We’re always trying to reduce the cognitive load on developers and to add this constraint would be the complete opposite of what we’re trying to do. Besides, it’s an error prune solution that is too open to human error and will probably add unnecessary latency to our flows.</p><p>We thought about creating a cache layer that will be the middleman between our services and the DB, so all the writes and the reads will use it. This option probably meet the requirements of performance impact, but the implementation estimations of this solution was above 4 weeks of work due to the complexity of this solution at such a critical point of our system.<br>While this is a common approach — we felt that creating a single point of failure such as this was too big of a risk for the amount of time we wanted to invest in it.</p><p>Another option that we thought about was to use some sort of distributed locking system, but locking the updated resources until the replication is done will for sure impact the performance of the application and it’s a complex implementation as well with a lot of edge cases to cover.</p><p>One more option was to make the writes synchronous in the DB side, so the writer instance will wait until the change applied on the read replica and only then to response. We could do it by changing default <a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">parameters</a>:</p><pre>synchronous_commit=remote_apply # the default value is &quot;on&quot;</pre><p>But that will for sure impact significantly the latencies on our microservices since most mutations don’t need to wait for the change to apply to the replicas.</p><p>After reviewing some more ideas and solutions without any findings, we decided to try and design a solution that will meet our requirements.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zpB6Em7-xcnOHY_gcxg1fg.png" /></figure><h3>Lagless</h3><p>We designed and implemented a new microservice (using Golang), <em>lagless</em> , that will act as an extra side hop before each query is sent to PGpool-II. It will indicate if the query need to be forced to be executed in the primary instance or not, if not, we let PGpool-II decide on it’s own where the query should go.</p><p>We’re using gRPC for our internal communication — this is the <em>proto</em> file representing the requests sent to <em>lagless</em>:</p><pre>syntax = &quot;proto3&quot;;<br>package lagless.internal_api.v1;<br><br>message ShouldForcePrimaryRequest {<br> string database_name = 1;<br> string query = 2;<br>}<br><br>message ShouldForcePrimaryResponse {<br> bool force_primary = 1;<br>}<br><br>service InternalQueryParserService {<br> rpc ShouldForcePrimary(ShouldForcePrimaryRequest) returns (ShouldForcePrimaryResponse);<br>}</pre><ul><li><strong>ShouldForcePrimaryRequest</strong> — represent the request, we’re passing the Database name and the SQL query that’s about to be sent to PGpool-II and executed in our DB.</li><li><strong>ShouldForcePrimaryResponse</strong> — represent the response, it’s a boolean — force the query to use the primary instance or not.</li><li><strong>ShouldForcePrimary</strong> — the function that we’re exposing for the clients.</li></ul><p><em>lagless</em> gets the SQL query that is about to be executed, parses it to get the table(s) name(s) and determines if it’s a read or write operation. After we parsed the query and we got the details we need, it checks if recently<strong>*</strong> there was a <strong>write</strong> operation to these tables and in case there was a <strong>write</strong> operation and the current query is a <strong>read</strong> operation, it will return <em>true</em> to the client.<br>The response — <em>true</em> means that we need to force the query to use the primary instance, otherwise we’ll let <em>PGpool-II</em> decide (using its built-in load-balancer).</p><p>Each <em>lagless</em> pod is using in-memory cache and storing the following data:</p><ul><li>Database name with the key: <br> <em>db_name</em> — to be able identify that the pod is serving the queries of this DB, it will be used to overcome “cold start” that we’ll elaborate about later on. <br>The TTL of these keys is 15 minutes, to avoid False positive cold start identifications.</li><li>We’re storing data about the tables, the key is:<br> <em>table_name</em> — if exists, it means that there was a write operation to this table recently and we should return `true` in case the query in question is a <strong>read</strong> operation, if it’s a <strong>write</strong> operation, we reset the TTL for this key.<br>The TTL for the <em>table_name</em> keys — we’re polling from AWS Cloudwatch the max lag replica value, and multiplying it X<em>5</em> as a safety margin.<br>The polling process running in a separate go routine in each <em>lagless</em> pod and polling the value every 5 seconds (100ms is the default values we set in case of failure to poll the metric) .<br>This way, our TTLs are derived from the recent lag that was recorded in our RDS cluster and we’re reducing the chance of hitting the case where the lag duration was a bit higher and we haven’t forced the query to primary when needed.<br>We’re also storing the query parsing result to avoid the expensive parse operation for each request, we’ll elaborate about it in the next sections.</li></ul><p>When a pod is going down, we are losing all the in-memory data of this pod and all the queries of all the services that it was serving untill now will move to a new pod — <strong>cold start</strong>.</p><p><strong>cold start</strong> — when <em>lagless</em> pod gets a request with an SQL query and Database name that doesn’t exist in it’s in-memory cache, it usually means (except in case of a brand new microservice, or when with extremely low request rate) that the handling of the SQL queries for this Database was moved from another <em>lagless</em> pod and we don’t have the latest <strong>write</strong> operations recorded in our cache — so we’ll add this key (<em>db_name</em>) to the in-memory cache (in warm-up state) and for the next 2 seconds it will return <em>true (</em>which means “force to primary”<em>) </em>for every SQL query that related to this DB, during this time we will populate the cache of the parsed queries (which is stored in our Redis). <br>After 2 seconds, the DB state is updated to <em>active</em> and we will use the in-memory cache to decide if to enforce primary instance usage as usual.</p><p>As mentioned above, we’re storing the query parsing results in each pod in in-memory caching, but this data can be reused in all <em>lagless</em> pods and changes over time, when new queries are introduced in our app. <br>To avoid populating the in-memory cache with expensive parsing operations each time a new pod starts, we’re using Redis as a centralised cache-aside.</p><p>When a <em>lagless </em>pod gets a request with a Database name that doesn’t exist in its in-memory cache, during the cold start period, it GETs all the parsed queries that relates to this Database and populates the in-memory cache.<br>When <em>lagless</em> gets a request with an SQL query that isn’t cached, it parses the query, returns the response to the client and in a separate go routine hashes the query and stores the value in both caches.<br>We need to store the table names that this query is referring to and a boolean that indicates if it’s a mutation operation or not.<br>For example:</p><pre>INSERT INTO example_table (name)<br>VALUES (&#39;Sample Name&#39;);</pre><p>That will produce the following record in both caches:</p><pre>Key=&lt;hash of the query&gt;<br>Value={&quot;is_mutation&quot;: true,&quot;tables&quot;: [&quot;example_table&quot;]}</pre><p>This way, each <em>lagless</em> pod has an in-memory cache of all the queries for the Databases its serving and for each SQL query that comes in, it normalizes the query, hashes it and checks if it is already in cache and uses the cached data instead of parsing it.</p><p>Now, you probably wonder — how can we use in-memory cache in Kubernetes?! How does that fit with the common load balancing mechanism?<br>If we have a microservice deployment, with 5 pods for example and 3 <em>lagless</em> pods, the “normal” way that kubernetes load balances the requests is across all the <em>lagless</em> pods and each of them will have a different (and wrong) state of the SQL operations for this microservice in cache, right?<br>That’s where Istio comes in for rescue.</p><h3>Load balance requests to Lagless Using Istio</h3><p>Let’s assume we have a microservice <em>foo</em> that is using <em>foo-db</em> as its Database, this microservice has a Deployment of (minimum) 3 pods and another separate deployment <em>foo-worker</em> for the worker pods with (minimum) 3 pods.<br>We need all foo’s and foo-worker’s pods using the same <em>lagless</em> pod to know if they need to force the SQL queries to the primary instance, otherwise they might not get the correct status of the latest writes.<br>We used Istio’s DestinationRule resource in order to achieve this:</p><pre>apiVersion: networking.istio.io/v1alpha3<br>kind: DestinationRule<br>metadata:<br>  name: lagless<br>  namespace: default<br>spec:<br>  host: lagless<br>  trafficPolicy:<br>    loadBalancer:<br>      consistentHash:<br>        httpHeaderName: x-database-name</pre><p>We used the <em>loadBalancer</em> capabilities using <em>consistentHash</em> by headers to load balance the requests to <em>lagless</em> .<br>In our clients, we’re sending the requests with the following header:</p><pre>x-database-name: &lt;database name&gt;</pre><p>So, if we go back to our example of <em>foo</em> microservice, all the pods of <em>foo</em> and <em>foo-worker</em> deployments, are sending the requests to <em>lagless</em> with the following header:</p><pre>x-database-name: foo-db</pre><p>This way, all the requests are using the same <em>lagless</em> pod.</p><h3>Clients for ruby and Golang</h3><p>One of our requirements was to minimize the code changes in our microservices — we create’ed a private Golang package and a ruby Gem that were added to our services and initialized at the startup of every microservice using environment variables.</p><p>PGpool-II, support’s hints (comments that are added to the SQL query) — the relevant hint that we’re using is:</p><pre>/*NO LOAD BALANCE*/</pre><p>Using it will mark the SQL query to be executed on the primary instance.</p><h4>Golang Client</h4><p>In Golang services we’re using Gorm for DB access so modifying it’s behavior was fairly simple.<br>To achieve our goal of checking every SQL query in <em>lagless</em> before sending it to PGpool-II we needed to implement a custom Postgres driver and a driver connection:<br>The driver wrapper:</p><pre>type DriverWrapper struct {<br> underlyingDriver driver.Driver<br>}<br><br>func NewDriverWrapper(underlyingDriver driver.Driver) *DriverWrapper {<br> return &amp;DriverWrapper{underlyingDriver: underlyingDriver}<br>}<br><br>func (d *DriverWrapper) Open(name string) (driver.Conn, error) {<br> conn, err := d.underlyingDriver.Open(name)<br> if err != nil {<br>  return nil, err<br> }<br> return &amp;ConnWrapper{underlyingConn: conn}, nil<br>}</pre><p><em>DriverWrapper</em> — is a type that wraps the <em>database/sql/driver</em> and implements its interface (<em>Open</em> method).</p><p>We also needed to wrap the sql Connection type — this is where we’re implementing our functionality of communicating with <em>lagless</em> and decide if we need to force the query to use the primary instance.<br>The conn wrapper:</p><pre>type ConnWrapper struct {<br> underlyingConn driver.Conn<br>}<br><br>func (c *ConnWrapper) Prepare(query string) (driver.Stmt, error) {<br> return c.underlyingConn.Prepare(query)<br>}<br><br>func (c *ConnWrapper) Close() error {<br> return c.underlyingConn.Close()<br>}<br>.... // implemeting all the relevant interfaces of driver.Conn<br><br><br>func (c *ConnWrapper) PrepareContext(ctx context.Context, query string) (driver.Stmt, error) {<br> ...<br> // check if forcing by checking with lagless<br> ...<br>}<br><br>func (c *ConnWrapper) ExecContext(ctx context.Context, query string, argsV []driver.NamedValue) (driver.Result, error) {<br> ...<br> // check if forcing by checking with lagless<br> ...<br>}<br><br>func (c *ConnWrapper) QueryContext(ctx context.Context, query string, argsV []driver.NamedValue) (driver.Rows, error) {<br> ...<br> // check if forcing by checking with lagless<br> ...<br>}</pre><p>We created a wrapper for the type <em>driver.Conn</em> and implemented all the relevant interfaces of <em>driver.Conn</em>. We’re actually using the original functionality for all the methods.<br>We added to <em>PrepareContext</em> , <em>ExecContext</em> and <em>QueryContext</em> methods the needed functionality that will allow us to check if this SQL query needs to be forced to use the primary instance.<br>In these methods we’re calling <em>lagless</em> and waiting for a response (true/false — force or not).<br>To reduce affect on users, we set by default <em>5ms</em> timeout for these calls, in case of a timeout, it will force the query to the primary instance.</p><p>As a side note — we’ll mention here that lagless takes around 1/10 of 1ms to process the requests, it is the network and other infrastructure that might timeout (including istio, cilium, grpc processing…).</p><p>The call to <em>lagless</em> looks something like that:</p><pre>...<br>metadataPair      metadata.MD<br>metadataPair = metadata.Pairs(&quot;x-database-name&quot;, databaseName)<br>laglessCtx = metadata.NewOutgoingContext(laglessCtx, metadataPair)<br>res, err := protos.LaglessClient.ShouldForcePrimary(laglessCtx, &amp;lagless.ShouldForcePrimaryRequest{<br> Query:        query,<br> DatabaseName: databaseName})<br>if err != nil {<br>...</pre><p>The <em>metadataPair</em> is how we’re passing headers in gRPC calls (<em>google.golang.org/grpc/metadata</em>), we need this header so all the calls that check the SQL query from the same database will use the same <em>lagless</em> pod as explained above.</p><p>All our Golang microservices are using a private package to connect to the Database, we developed this package to avoid repetitive boilerplate code in each service, all the monitoring functionality and settings, the creation of connection strings etc.. are the same across all services.<br>This also made it easier for us to add this functionality to all services using the private package and bump the version in all Go microservices.<br>This package initiates without any additional code, just using the environment variables.</p><h4>Ruby Client</h4><p>For the ruby clients we used a simple Rails feature (7.1+) and implemented something similar in the following way:</p><pre>...<br>PRIMARY_HINT = &quot;/*NO LOAD BALANCE*/&quot;<br>...<br>@metadata = { &#39;x-database-name&#39;: config.db_name }<br>...<br><br>ActiveRecord.query_transformers &lt;&lt; -&gt;(sql, context = nil) { transform_query(sql, context) }<br><br>def transform_query(sql, _context)<br><br>req = Lagless::InternalApi::V1::ShouldForcePrimaryRequest.new(<br>          database_name: db_name, query: sql<br>        )<br><br>begin<br>  should_force = Timeout.timeout(Hinter.config.timeout) do<br> @client.should_force_primary(req, metadata: @metadata)&amp;.force_primary<br>  end<br><br><br> should_force ? force_primary(sql) : sql<br> rescue StandardError =&gt; err<br>   ...<br>   force_primary(sql)<br> end<br><br> def force_primary(sql)<br>  ...<br>  &quot;#{PRIMARY_HINT} #{sql}&quot;<br>end</pre><ul><li><a href="http://twitter.com/metadata"><em>@metadata</em></a> — similar to the golang client, we need to add the header to the gRPC calls.</li><li><em>ActiveRecord.query_transformers</em> — Using <em>ActiveRecord</em> we are able to invoke a lambda for each SQL query that’s about to be executed (<em>transform_query</em> ).</li><li><em>transform_query</em> — this method implements the call to <em>lagless</em> and inject’s the hint to the SQL query if the response is true, or if there’s any error (e.g. timeout of <em>5ms</em>).</li></ul><p>We added this Gem to all our Ruby on Rails microservices.</p><h3>Monitoring</h3><p>This kind of critical component requires very good observability and monitoring in place.<br>For <em>lagless</em> we set monitors as we’re doing for all microservices — server errors, latency, request rate anomalies.<br>The monitoring values for this service were also pretty easy to set —</p><ul><li>We’re not expecting any server errors (alert in case &gt; 0% ).</li><li><em>lagless</em> handles the calls in about <em>100μs</em> — so we want to be notified if it was above <em>350μs</em> for 5 minutes or more.</li><li>Anomalies in request rates, we’re using the built-in Datadog anomalies detectors.</li></ul><p>We also need to monitor the clients and since this is a centralised(ish) component, where all the services that are using a Database are using it, we wanted an overall client monitor.<br>We set to notify us if we’re hitting <em>&gt; 2%</em> of client errors (e.g. <em>timeouts</em>).</p><h3>Conclusions</h3><p>We deployed it to production a few months ago and wanted to share some info and lessons -</p><p>The setup of <em>lagless</em> is very stable, it handles the vast majority of calls in about <em>80μs–100μs</em>, the bottleneck is the networking which takes about <em>2ms–3ms</em>, so it means we added at least <em>~3ms</em> of latency to each HTTP/gRPC endpoint that is using a Database for handling the request.<br>We saw some exceptions in latency, for very big queries using more than 10 tables, the normalizations and hashing of these queries take longer — about <em>200μs</em>, but it’s not a common use-case.<br>The clients timeouts across all of our production workloads is usually below <em>0.7%</em>, which is reasonable.</p><p>Each new microservice that’s created is using <em>lagless</em> — this setup is across all our environments, including dev, which we try to keep as similar to production as possible.</p><p>This solution has met our requirements and is working pretty well, the tradeoffs were reasonable for our use case, we did impact a bit the performance of our application, but we monitored each and every microservice that started to use <em>lagless</em> and saw an affect of no more than <em>5ms</em> in most cases which is not critical for most of our use cases when compared to the ability of horizontal scaling of our read replicas over time without any changes to our applications.</p><h3>The road ahead</h3><p>Some ideas for further optimizations we’ve had and have yet to explore:</p><ul><li>Cache per customer ID so accessing customer specific content will only be affected by that specific customer. Can be achieved by query parsing or by another header. This is a common occurence in other read replica management solutions implementations we’ve seen.</li><li>Integreate lagless functionality alongside PGpool-II processing maybe using something like <a href="https://github.com/postgresml/pgcat">PGCat</a> with it’s plugin architecture. Doing this would completely remove the need for a side request and maintain all the benefits mentioned here while remove this latency addition we’ve incurred. it would also rely on single parsing operation across the query lifecycle.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d80eaf6bbf8a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/using-database-read-replicas-in-distributed-systems-d80eaf6bbf8a">Using Database Read Replicas In Distributed Systems</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automate Database Creation for Microservices]]></title>
            <link>https://medium.com/tailor-tech/automate-database-creation-for-microservices-d1c117b593f3?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/d1c117b593f3</guid>
            <category><![CDATA[1password]]></category>
            <category><![CDATA[gitops]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[kubernetes]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Sat, 18 May 2024 13:19:14 GMT</pubDate>
            <atom:updated>2025-08-13T10:53:09.733Z</atom:updated>
            <content:encoded><![CDATA[<p>Creating a new Microservice usually involves a lot of components like monitors, infrastructure (e.g S3 buckets, IAM roles, etc.) and Databases.<br>We follow <a href="https://microservices.io/patterns/data/database-per-service.html">best practices</a> and create a new Database per new microservice if it requires one.</p><p>Creation of a new Database is a process in which we’re creating a new application role for the new Microservice itself with limited permissions, usually for CRUD operation on the records of the tables, we also create a role for migrations, which has an extended set of permissions compared to the application role, since it needs to be able to perform DDL queries in that Database.<br>We also need to update the permissions of the role used by our data team which is read-only.</p><p>Recently we added the ability for our engineers to completely self serve themselves in creating a Database for a new Microservice they are building. All while keeping with all the security and architecture best practices we’ve learned along the way.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*h30vgSwyvBh87aSzTAzOrA.png" /></figure><h3>Why?</h3><p>We mentioned in our <a href="https://medium.com/tailor-tech/effective-real-time-inter-service-communication-in-a-micro-service-architecture-24eb8e801a8a">previous articles </a>our architecture.<br>tl;dr — We’re working with AWS EKS for all our environments.</p><p>When an engineer creates a new Microservice, if it is stateful it will most likely require it’s own Database that we need to setup in all environments, from development all the way to production.<br>When a new Database is created, it’s not only the database itself, there are a few users and different roles for permissions that we need to create. This is all in order to maintain security and architecture best practices, but we’ll save that for another article.</p><p>We had some sort of automation for the DB creation, but the engineer still needed to manually create the passwords for all the new roles and add them to 1Password (where we’re managing vaults for the Databases per environment), add these secrets to AWS SSM (parameter-store) which we use in our Kubernetes clusters by way of <a href="https://github.com/external-secrets/external-secrets">External-Secrets</a> to create the Secrets resources in the clusters.</p><p>We realized that the process of creating the secrets of the roles is a time drain on it’s own and beyond being less secure (prone to human error) it is also confusing and complicated. We wanted to create a better Developer eXperience while getting closer to our standards and reducing friction.</p><h3>How?</h3><p>When it comes to our infrastructure we are big believers in Gitops, sometimes with IaC tools such as Terraform and sometimes using in-cluster approaches, using ArgoCD.</p><p>In this instance we decided to stay close to the service definition which uses Helm and ArgoCD as these are the tools most of the engineers are most familiar with.<br>We created a dedicated Kubernetes Job, using helm chart and in each change that the engineer does (writing the new service name in that case) — ArgoCD will trigger an AutoSync operation that will fire the job.</p><p>The Job is using a tiny Docker image that we’ve built specifically for this use case, all it contains are the relevant drivers and a small Golang binary.<br>The binary holds all the logic required to create the Database, users and everything else detailed below.<br>We’re big Golang users here at Tailor and we not only build backend microservices using the language but also use it as a system/scripting language such as in this case.</p><p>All the engineer needs to do is open a PR with a change to the <em>values.yaml</em> of the helm chart and the only value that is changing is the <em>new_service_name</em> field which is enough for the binary to create all the resources we need.</p><p>The binary essentially does the following steps: <br>1. <em>1Password</em> Items — creates a new ‘Login’ items in the correct vault, by environment, with the role names as the username and a passwords that generated using 1Password with strong requirements.<br>2. <em>AWS SSM</em> parameter-store — adding new items to parameter stores (secured parameters) that represents the roles and the passwords.<br>3. Connecting to the correct AWS Aurora RDS instance by environment and executes the queries to create the new database, roles and granting the relevant permissions to the roles.<br>4. We’re using <a href="https://www.pgpool.net/mediawiki/index.php/Main_Page">Pgpool</a> in our environments, this setup requires some updates to its secrets as well, so it covers this step as well.</p><p>All this done in a few seconds and the effort of creating a new database in every environment that the developer needs decreased by a lot to a one liner change and PR.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AogVYxEJknDwHLd0QN4bZQ.png" /><figcaption>Created by DALL·E</figcaption></figure><h3>1Password Integration</h3><p>Before this automation, we used 1Password as a separate source of secrets that wasn’t connected to our environments, we liked that all our vaults are in a “separate” place from our workloads in our environment and that’s why, historically, we were using AWS SSM as our secrets source for the External-Secrets.<br>We decided to reconsider this decision, which was made 6–7 years ago, since then a lot changed and we decided to integrate 1Password into our system and as first step to do it for the DB creation automation.</p><p>We needed to deploy <a href="https://developer.1password.com/docs/connect/">1Password Connect</a> server into our clusters to be able to make the API calls to 1Password.<br>We used their <a href="https://github.com/1Password/connect-helm-charts/tree/main/charts/connect">helm chart</a> for the setup, the Docs were very clear and it was a smooth and very quick process overall, we encountered three small issues during the setup which I’ll share in case you’re setting it up to save you some time:</p><ol><li>The default type of the Service is a <em>NodePort</em><strong>(!)</strong>, which might be a security concern by opening it to the public internet (<a href="https://github.com/1Password/connect-helm-charts/issues/194">Github issue</a>).<br>the helm chart support overriding the value using (<em>connect.serviceType</em>) — we’re using is internally in our cluster, so we set it as ClusterIP.</li><li>The <em>credentials.json</em> file that you’re getting need to be double encoded in base64. It depends how you’re installing the 1Password Connect into your environment, but the bottom line is that the value that you’re adding to the secret need to be base64 encoded.</li><li>We had firewalls rules on 1Password settings, we needed to allow access from our relevant IP’s.</li></ol><h3>Conclusions</h3><p>We are considering to move all our secret management to 1Password, including the Kubernetes secrets creation and syncing, it’s not optimal to have two sources of secrets right now, the setup was easy and we can achieve reasonable separation of concerns using the vaults and tokens for each environment, we’ll post a new article of this migration if we’ll decide to do it.</p><p>This optimization, which wasn’t a big effort for our Platform team in term of time, not only saves some time to the engineer creating a new Microservice and prevents human errors and security risks, it also improves the DX for the team, which is a very important aspect in Platform engineering.</p><p>We think that DX and self-service are both important key’s to improving team velocity and satisfaction.<br>We achieve further impact by avoiding these “stuck”/”annoying” moments when working on infrastructure/setup areas of the platform for the engineer that just want’s to get on with the service or feature they are working on and get to the business impact ASAP.</p><p>Sometimes it’s hard to prioritize these kind of tasks, because we’re not creating dozens of microservices every single day, it’s easy to just stay with what’s <em>okay</em> even if it’s not <strong>great</strong>. We need to remember that not only the time spent on it accumulates but the frustration the engineers experience does as well. Eventually we’ll reach a point where they avoid creating services and build lesser products just to avoid frustration.</p><p>Small TIP from my perspective — try to listen to the engineers, identify the pain points that relate to the platform they are working on and find the quick wins. These quick wins, over time, accumulate to a much bigger impact for the teams and business.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d1c117b593f3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/automate-database-creation-for-microservices-d1c117b593f3">Automate Database Creation for Microservices</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Throttling Asynchronous Tasks In a Distributed System]]></title>
            <link>https://medium.com/tailor-tech/throttling-asynchronous-tasks-in-a-distributed-system-e25c263a99ff?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/e25c263a99ff</guid>
            <category><![CDATA[golang]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[asynchronous]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Thu, 28 Dec 2023 08:53:43 GMT</pubDate>
            <atom:updated>2025-08-13T10:53:51.029Z</atom:updated>
            <content:encoded><![CDATA[<p>How to throttle asynchronous jobs in a distributed system, without too much complexity and global locking mechanisms?</p><p>Recently we had a new constraint in our product — we needed to throttle asynchronous tasks by type so it won’t execute more than X tasks at the same time.<br>In this post we’ll use Golang, but it can be implemented in any programming language.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*liK-iba0Klm5CPpY_Irerw.png" /></figure><h3>Our Stack + Asynq</h3><p>We use a Microservices architecture deployed over Kubernetes (AWS EKS) and as such — each service that we run in our platform runs in multiple instances. This creates great resilience and scalability but also comes with some concurrency concerns spanning multiple instances (or pods).<br>The same worker pod is deployed using multiple replicas and tasks are pulled from the queue and executed using go routines.</p><p>For our Golang services, we’re using the great package <a href="https://github.com/hibiken/asynq">Asynq</a> to manage our asynchronous tasks.<br>We have a big variety of asynchronous tasks and we utilize this package nicely using AWS ElastiCache Redis cluster.</p><h3>The Challenge</h3><p>Asynq package supports concurrency setting, but it is implemented as a per process (pod in our case) throttler, it uses channels under the hood (<a href="https://github.com/hibiken/asynq/blob/v0.24.1/processor.go#L111">code link</a>) and we needed a global solution, one that will span across all pods that consume these tasks.</p><p>To get the answer if a given task should be throttled by task type we needed to have one source of truth across our distributed and multi-threaded (go routines) environment — there are serval ways to achieve this.<br>We can use a global lock, that is used by all the replicas, or we can execute the check in Redis server side, which is a thread safe and executed in one place which eliminates all the complexity and the potential bugs of maintaining a global lock key.</p><p>Since we already have Redis as part of our infrastructure and we use it for Asynq itself — using it also as a locking mechanism made sense as there was no new infrastructure overhead.</p><h3>Throttling Tasks</h3><p>In Asynq package, we can add Middlewares to the worker server.<br>So we implemented a small middleware that checks if a task should be throttled.</p><p>We chose an approach that is the least error prone and most efficient that we could find— executing lua script in Redis server which will manage the throttle functionality and will return to the client if the task should be throttled or not.</p><p>Using this approach we also guarantee that the execution of the check is globally safe.</p><p>Let’s go through the steps needed in order to implement thus mechanism.</p><h4>Lua script</h4><p>First, we need a lua script that will be executed on the Redis server.<br>We were inspired by the great script we saw in <a href="https://github.com/ixti/sidekiq-throttled/blob/v1.1.0/lib/sidekiq/throttled/strategy/concurrency.lua">sidekiq-throttled</a> gem:</p><pre>local key = KEYS[1]<br>local jid = ARGV[1]<br>local lmt = tonumber(ARGV[2])<br>local ttl = tonumber(ARGV[3])<br>local now = tonumber(ARGV[4])<br><br>redis.call(&quot;ZREMRANGEBYSCORE&quot;, key, &quot;-inf&quot;, &quot;(&quot; .. now)<br><br>if lmt &lt;= redis.call(&quot;ZCARD&quot;, key) and not redis.call(&quot;ZSCORE&quot;, key, jid) then<br>  return 1<br>end<br><br>redis.call(&quot;ZADD&quot;, key, now + ttl, jid)<br>redis.call(&quot;EXPIRE&quot;, key, ttl)<br><br>return 0</pre><p>This isn’t exactly what we used but it’s a great example on how it can be done — for each key (<em>local key = KEYS[1]</em>), which represents a task type, we have a set of task IDs (<em>local jid = ARGV[1]</em>), the script checks the length of the set and if it reached the limit (<em>local lmt = tonumber(ARGV[2])</em>) it will return <em>1 (throttled)</em>.</p><p>If it’s still under the limit — add the job id to the list and return <em>0 (not throttled)</em>.</p><p>In order to access the script from our golang side we define the Redis script using Redis golang package: github.com/redis/go-redis/v9:</p><pre>var throttleScript = redis.NewScript(`<br>local key = KEYS[1]<br>local jid = ARGV[1]<br>local lmt = tonumber(ARGV[2])<br>local ttl = tonumber(ARGV[3])<br>local now = tonumber(ARGV[4])<br><br>redis.call(&quot;ZREMRANGEBYSCORE&quot;, key, &quot;-inf&quot;, &quot;(&quot; .. now)<br><br>if lmt &lt;= redis.call(&quot;ZCARD&quot;, key) and not redis.call(&quot;ZSCORE&quot;, key, jid) then<br>  return 1<br>end<br><br>redis.call(&quot;ZADD&quot;, key, now + ttl, jid)<br>redis.call(&quot;EXPIRE&quot;, key, ttl)<br><br>return 0<br>`)</pre><p>Next we define a <em>throttle</em> function that we can use in our worker middleware:</p><pre>func throttle(ctx context.Context, key, taskID string, concurrency int64) (bool, error) {<br> keys := []string{key}<br><br> res, err := throttleScript.Run(ctx,<br>  RedisClient,<br>  keys,<br>  taskID,<br>  concurrency,<br>  config.throttleKeyTTL,<br>  time.Now().Unix()).Result()<br> if err != nil {<br>  return false, fmt.Errorf(&quot;failed to run Lua script: %w&quot;, err)<br> }<br><br> return res == 1, nil<br>}</pre><h4>Done Tasks</h4><p>To mark the task as done, we will define a function that removed it from the list.</p><p>We don’t need a custom lua in this case since it’s a single Redis command.</p><p><em>taskDone</em>:</p><pre>func taskDone(ctx context.Context, key string, taskID string) error {<br> _, err := RedisClient.ZRem(ctx, key, taskID).Result()<br> if err != nil {<br>  return fmt.Errorf(&quot;failed to delete key: %s, error: %w&quot;, key, err)<br> }<br> return nil<br>}</pre><h4>Asynq Middleware</h4><p>Now, let’s define the Asynq middleware function that will handle the incoming tasks:</p><pre>var throttleError = errors.New(&quot;throttled&quot;)<br><br>func throttleMiddleware(h asynq.Handler) asynq.Handler {<br> return asynq.HandlerFunc(func(ctx context.Context, task *asynq.Task) error {<br>  if _, exists := config.tasksConcurrencyConfig[task.Type()]; !exists { // check if throttle limit is configured for the task<br>   return h.ProcessTask(ctx, task) // no throttle configured - continue to handler<br>  }<br><br>  taskID, ok := asynq.GetTaskID(ctx)<br>  if !ok {<br>   return errors.New(&quot;throttleMiddleware: failed to get task ID from context&quot;)<br>  }<br><br>  key := fmt.Sprintf(&quot;throttle:%s&quot;, task.Type()) // redis key to throttle by<br><br>  throttled, err := throttle(ctx, key, taskID, config.tasksConcurrencyConfig[task.Type()].Concurrency)<br>  if err != nil {<br>   return err<br>  }<br><br>  if throttled {<br>   return throttleError<br>  }<br><br>  defer func() {<br>   if err := taskDone(ctx, key, taskID); err != nil {<br>    config.reportError(ctx, fmt.Errorf(&quot;throttleMiddleware: failed to remove done task key from redis set: %s, error: %w&quot;, task.Type(), err))<br>   }<br>  }()<br><br>  return h.ProcessTask(ctx, task)<br> })<br>}</pre><p>Let’s go over the middleware function:</p><pre>if _, exists := config.tasksConcurrencyConfig[task.Type()]</pre><p>checking if there is a configured throttle limit for the incoming task by task type, in case it’s not configured, it will continue to the handler/next middleware.</p><pre>throttled, err := throttle(ctx, key, taskID, config.tasksConcurrencyConfig[task.Type()].Concurrency)</pre><p>calling the throttle function that we mentioned before, with the key (task type), task ID (element in the set), the configured limit of concurrent tasks.</p><pre>defer func()</pre><p>we want to mark this task as done, which means to remove the task ID from the set in Redis by the task type. It’s important to call it as defer function so no task will get stuck there after the return h.ProcessTask(ctx, task) and will affect our concurrency until the TTL will expire and clean it up on it’s own.</p><p>When setting up the worker server, we need to add the middleware:</p><pre>mux := asynq.NewServeMux()<br>mux.Use(throttleMiddleware)</pre><h4>No Retries Count</h4><p>One last thing that is important to know is that when we are returning an error from the middleware, it goes to the retry mechanism of Asynq. <br>But in our case, it wasn’t a failure, we just wanted it to be executed a bit later when there are less tasks that are in progress than the defined limit and we want to avoid counting it as a retry.<br>To achieve this we can define an <em>IsFailure</em> function in the configuration of the worker server:</p><pre>asynq.Config{<br> IsFailure = func(err error) bool {<br>  return !errors.Is(err, throttleError)<br> }<br>}</pre><p><em>throttleError</em> — this is a static error we’re returning in the middleware in case that we got the answer from Redis server that the task is throttled.<br>In that case, Asynq won’t count it as failure and won’t change the count of the retries.</p><p>Any other errors from the task will behave normally.</p><h3>Conclusions</h3><p>As mentioned, this is not the only possible solution, but the simplicity of the implementation, together without the need for new infrastructure made it an easy choice for us.</p><p>It’s pretty simple and can be used not only with Golang and Asynq package, the idea can be implemented using any programming language and the lua script is a nice and elegant approach as well.</p><p>Feel free to follow the implementation or try your own.</p><p>As for giving back — we will be working on a PR for asynq to add this functionality back to the package down the line. If you feel like you want to beat us to it, go ahead!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e25c263a99ff" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/throttling-asynchronous-tasks-in-a-distributed-system-e25c263a99ff">Throttling Asynchronous Tasks In a Distributed System</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Redshift scale up with Datashare]]></title>
            <link>https://medium.com/tailor-tech/redshift-scale-up-with-datashare-440c0e04e704?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/440c0e04e704</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[scaleup]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[data-architecture]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Tue, 19 Sep 2023 07:19:47 GMT</pubDate>
            <atom:updated>2025-08-19T08:24:23.958Z</atom:updated>
            <content:encoded><![CDATA[<h3>Redshift Scaleup with Datashare</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y-72zGWloBnLeNCgvucBqA.jpeg" /></figure><p>Here at Tailor Brands, we’ve been long time users of AWS Redshift for our Data-Warehouse (DWH). Recently we started to utilizing <a href="https://docs.aws.amazon.com/redshift/latest/dg/datashare-overview.html">datashare</a> feature, which enables read access to live data across Amazon Redshift clusters.</p><p>In the following post we’ll cover some of the benefits of such a transition and see how to migrate from the older node types, <em>DC2</em>, to <em>RA3</em> (which support datashare). We’ll also review the creation of data producer(s) and consumer(s).</p><h3>Why we needed this change</h3><p>Before the migration, we had one cluster with <em>DC2</em> nodes. <br>This cluster served us well for the last few years, but recently we started to encounter performance and storage issues.</p><p>All our processes were using this cluster, the data producers (e.g <a href="https://airflow.apache.org/">Airflow</a> DAGs), data consumers (e.g Tableau dashboards, dev environments etc ..).<br>We lacked proper concern separation, which meant that any process can affect our data producer processes or our production analytic processes, even a bug or a bad query in one of the dev environments could potentially impact our production processes, such as by causing CPU throttling on the nodes.</p><p>Another issue we had is the storage limitation, <em>DC2</em> nodes come with limited storage and our total storage of the cluster was only a few Terabytes. <br>We’re monitoring our cluster using Datadog, and although we have in place procedures and DAGs for cleanups of dev and temp tables, we had too many alerts about the state of the storage in the cluster. It created an ongoing challenge that demanded time and effort from our Data Engineers.</p><p>We realized we have long outgrown our setup and it was time to evolve.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*dXnQ3FPTWP03z-zBMGu1Xw.png" /></figure><h3>Migration to RA3 nodes</h3><p>We decided it was time to move on to the new node type — <em>RA3</em> that supports datashare, which will help us redesign the usage of producers (of data) and consumers.<br>We also get a lot more storage per node — minimum 32 TB per node.</p><p>The first step was to change the node types from <em>DC2</em> to <em>RA3</em> using <a href="https://docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-operations.html">elastic resize</a> .<br>Before we ran it on our live nodes we tried to run this on a snapshot based cluster to make sure that it works as expected. We also wanted to get as accurate as possible estimation of run time for the change. Since during the time it runs no new data can be written to the DWH. <br>It’s important to share these details with the different stakeholders using the DWH or it’s dervied products.<br>We turned off all the processes that write to the DWH and initiated the resize, it took 15 minutes (in read-only mode) for the whole process, which replaced the underlying node types and migrated all the data — not bad at all!</p><h3>Creating a new datashare</h3><p>Now that our cluster is using the <em>RA3</em> nodes, we can utilize the datashare feature.<br>We wanted to share our production schemas to our data consumers processes which will be able to use production data without affecting the compute power of the processes that produce the data.</p><p>To <a href="https://docs.aws.amazon.com/redshift/latest/dg/within-account.html">create a new datashare</a>, we’re logging in to the producer cluster and executing:</p><pre>CREATE DATASHARE salesshare;</pre><p>Example of how to add the schemas and tables to the created datashare:</p><pre>ALTER DATASHARE salesshare ADD SCHEMA PUBLIC; <br>ALTER DATASHARE salesshare ADD TABLE public.tickit_sales_redshift; --add specific table<br>ALTER DATASHARE salesshare ADD ALL TABLES IN SCHEMA PUBLIC; --add all tables in public schema</pre><p>You can verify that the datashare exists by executing:</p><pre>SHOW DATASHARES LIKE &#39;sales%&#39;;</pre><h3>Creating a new consumer(s)</h3><p>Now that we have a datashare(s) we can create a new Redshift cluster with <em>RA3</em> nodes or Serverless — as a data consumer.</p><p>After creating the consumer cluster, we’re logging into the <strong>producer </strong>cluster again and granting a permission for this new cluster to use the datashare:</p><pre>GRANT USAGE ON DATASHARE salesshare TO NAMESPACE &#39;&lt;consumer namespace&gt;&#39;;</pre><p>Now we can login into the <strong>consumer</strong> cluster and create the shared Database which will have access to the datashare:</p><pre>CREATE DATABASE sales_db FROM DATASHARE salesshare OF NAMESPACE &#39;&lt;producer cluster namespace&gt;&#39;;</pre><p>Now that we have the `sales_db` DB, we can create external schema(s) and grant permissions of usage to the roles we need:</p><pre>CREATE EXTERNAL SCHEMA sales_schema FROM REDSHIFT DATABASE &#39;sales_db&#39; SCHEMA &#39;public&#39;;<br>GRANT USAGE ON DATABASE sales_db TO bob;<br>GRANT USAGE ON SCHEMA sales_schema TO GROUP analyst_group;</pre><p>After we completed the steps above, we can use the compute power of the new cluster and access the data from the producer cluster using external schemas.</p><h3>Redesigning our Data Warehouse</h3><p>We decided to separate all the data producer processes from the consumers by creating one producer cluster and a couple of consumer clusters and to get to 70%-80% CPU utilization of nodes in the clusters.</p><p>The cluster with the new nodes (after the resize) was used as our producer cluster.<br>We created new clusters for the consumer processes and moved those workloads to use them.<br>We now had full separation of concerns with our consumer clusters accessing all of our produced data without affecting the producer cluster at all.</p><p>We decided to use Serverless Redshift for our dev environments, since it’s a <a href="https://aws.amazon.com/blogs/aws/introducing-amazon-redshift-serverless-run-analytics-at-any-scale-without-having-to-manage-infrastructure/">classic use case</a> when the cluster isn’t in use in the weekends, nights and holidays.</p><blockquote>Amazon Redshift Serverless is ideal when it is difficult to predict compute needs such as variable workloads, periodic workloads with idle time, and steady-state workloads with spikes. <br>This approach is also a good fit for ad-hoc analytics needs that need to get started quickly and for test and development environments.</blockquote><p>For the rest of the consumers we’re using Redshift clusters with <em>RA3</em> nodes.</p><h3>Conclusions</h3><p>We completed the migration and the redesign of our Data Warehouse successfully by reducing the total compute power (less nodes) we’re using, removing the storage issue we had completely and separate the concerns to the point where no consumer workload can affect any data producer workloads.</p><p>The new architecture allows us to separate consumers further into dedicated clusters as the need arises.</p><p>The total costs is higher than the architecture before the change, since the new <em>RA3</em> nodes are more expensive (~X2 for the same compute power) but with all the tradeoffs and the benefits we get from the new architecture, we think it’s worth the price.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=440c0e04e704" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/redshift-scale-up-with-datashare-440c0e04e704">Redshift scale up with Datashare</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How we’re solving a latency issue on a 3 Billion Records Postgres Table]]></title>
            <link>https://medium.com/tailor-tech/how-were-solving-a-latency-issue-on-a-3-billion-records-postgres-table-6edfc5f553f0?source=rss-7795017fd57------2</link>
            <guid isPermaLink="false">https://medium.com/p/6edfc5f553f0</guid>
            <category><![CDATA[sre]]></category>
            <category><![CDATA[reliability-engineering]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[scale]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Evgeny Kharitonov]]></dc:creator>
            <pubDate>Mon, 07 Aug 2023 09:45:39 GMT</pubDate>
            <atom:updated>2025-08-13T12:09:00.548Z</atom:updated>
            <content:encoded><![CDATA[<p>No one likes getting paged in the middle of the night, even less when the alert shows “Database latency”, that’s never a good sign.</p><p>Slowdowns of SQL queries on an unpartitioned table with more than 3 Billion records which happened “sporadically” a few times with an interval of a few months — That’s the issue we faced lately at <a href="https://www.tailorbrands.com/">Tailor Brands</a> and this post is about how we handled it and some interesting conclusions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*riJAGZ4cnWfCJgNUlcJbYA.jpeg" /></figure><h3>‘Brands’ Table</h3><p>Before diving into the issue, A few words about brands table and the tech stack.<br>We’re using AWS RDS Aurora PostgreSQL clusters and in one of our clusters we’ve got a Database with the table: brands which reached lately 3 Billion records. <br>Our users can create multiple brands and for historical reasons, the table isn’t partitioned and every day there are about 800k INSERT/ UPDATE operations on that table.</p><p>As you can imagine, having so many update operations made vacuum operations more frequent and more resource-intensive as well.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*lv_bQzf7lYh_n_gZETa4Aw.png" /></figure><h3>The Sporadic Issue</h3><p>Queries latency on the table brandsis suddenly increasing from ~5ms to ~300ms and the CPU utilization of our RDS cluster increased by ~20%. <br>We say it’s “Sporadic” because it wasn’t a one-time incident, it happened a couple of times in the last few months and our assumptions regarding the root cause turned out to be inaccurate.<br>So after the third time it happened, we dove in.</p><p>You might be asking yourself at this point why didn’t we dive head first into research the first time it happened — that would be a fair question.</p><p>The answer is that when we prioritize our work and do cost/value calculations, one-off incidents get a low-value rank since we don’t immediately assume they’ll repeat.<br>We also take into consideration the amount of work required to resolve an issue, even if not at the root level cause when it occurs. In this case, we managed to resolve the immediate symptom quite quickly without a lot of work.</p><p>Another aspect of it was that we <em>thought</em> we knew why it was happening, we were close but not 100% there.</p><h3>(Lack of) Detection</h3><p>We’re using Datadog to monitor all our microservices and infrastructure metrics. <br>Each microservice and infrastructure component is covered with pre-defined monitors (using Terraform) with static thresholds or anomaly detections that will notify us via Slack, and Pagerduty if it’s a critical issue.</p><p>Since the issue affected a single table, the combined latency for the one service accessing it wasn’t as affected as needed to trigger an alert, as such it took a while until we noticed the increase in one of our dashboards.<br>That meant that the effects were longer lasting than needed.</p><p>This sort of thing drove us to improve our monitoring and for our main endpoints, we set up specific monitors separate from the entire service running them.</p><h3>Investigation</h3><p>After the issue was detected, we started to investigate the issue as a SR issue due to its impact on the user experience.</p><p>We quickly observed that most HTTP request latencies to the relevant microservice had spiked from ~100ms to 1–3 seconds.<br>Further investigation with Datadog’s flame graphs revealed that the cause of the sudden latency is the sudden surge in SQL query latency and since most requests perform several SQL queries onbrands table it significantly slowed down all the requests for this critical core service.</p><p>We started to look for a root cause, looked first at our RDS Aurora cluster (PostgreSQL) metrics (although it is monitored by Datadog as well) to verify that the cluster’s instances are healthy and there is nothing unusual in the metrics or any events.<br>Finding no clues there, we looked at the Slack releases channels for any recent releases linked to the service (We work in continuous deployment and release to production many times a day) and saw nothing there as well. <br>We continued to investigate the issue and checked if the issue was coming from the same pod (Our microservices running on AWS EKS), or from the same node (We’re using <a href="https://github.com/aws/karpenter">Karpenter</a> for node selection and auto-scaling), or if there aren’t enough resources allocated to the pods (memory/CPU), looked at the logs of the service.<br>However, all these checks came up empty.</p><p>As you might want to note — checking the pod/service behavior shouldn’t be helpful since changes there shouldn’t affect DB performance as long as the queries stay the same. But that’s not always the case, let&#39;s say when we add many queries for example.<br>We always try and cover a wide breadth of areas when we check for root causes once we eliminate the immediate suspects.</p><p>You never know where you’ll find your next clue!</p><h3>ANALYZE and DB stats</h3><p>After clearing all the above potential root causes, we began to suspect that the issue might be related to the table itself since we knew that the table is huge and that it’s updated very frequently.</p><p>We decided to check when the last <a href="https://www.postgresql.org/docs/current/sql-vacuum.html">VACUUM</a> and <a href="https://www.postgresql.org/docs/current/sql-analyze.html">ANALYZE</a> operations were executed on the table brands.</p><blockquote><em>ANALYZE</em> collects statistics about the contents of tables in the database, and stores the results in the <a href="https://www.postgresql.org/docs/current/catalog-pg-statistic.html"><em>pg_statistic</em></a> system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.</blockquote><p>We executed the following query:</p><pre>SELECT <br>    last_vacuum,<br>    last_autovacuum,<br>    last_analyze,<br>    last_autoanalyze <br>FROM <br>    pg_stat_user_tables <br>WHERE <br>    relname = &#39;brands&#39;;</pre><p>We noticed that the lastVACUUM was a few days ago but ANALYZE wasn’t executed for <strong>5 months</strong>, which means that the stats about the table brands weren’t updated for a very long time and during that time we had about 120 Million operations of INSERT/UPDATE on that table.</p><p>The table is a valid candidate for auto-analyze operations if:</p><pre><em>Total number of Inserts/Deletes/Updates</em> = <em>TotalRecords</em>*<strong>autovacuum_analyze_scale_factor</strong> + <strong>autovacuum_analyze_threshold</strong> </pre><p><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html">Default values</a> are:</p><pre>autovacuum_analyze_scale_factor: 0.1<br>autovacuum_analyze_threshold: 50</pre><p>So it means in our case that it will analyze the table after ~300 million write operations on brands table and we had “only” about 120M.</p><p>After the revelation about the state of ANALYZE we executed manually:</p><pre>ANALYZE brands;</pre><p>And it instantly restored latency to normal levels.</p><h3>Same Issue but a lot worse</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*NsVnV4BHT0H1pCaI" /><figcaption>Photo by <a href="https://unsplash.com/@armedshutter?utm_source=medium&amp;utm_medium=referral">Ayo Ogunseinde</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>We decided to update the values so the AUTO_ANALYZE will be executed every 5M write operations (~once a week):</p><pre>ALTER TABLE brands<br>  SET (<br>    autovacuum_analyze_scale_factor = 0,<br>    autovacuum_analyze_threshold = 5000000<br>);</pre><p>After it was triggered the first time after the update, we saw the same symptoms, which was surprising to us, and to mitigate the issue quickly we executed manually the ANALYZE brands again.</p><p>But, this time instead of solving the issue, the CPU utilization of RDS spiked to 100% and we saw a very big queue of queries piling up, we canceled the ANALYZE query but it didn’t help.</p><p>We needed to scale down the microservice completely and then we were able to execute ANALYZE brands again successfully, scaling up the service and it worked — WEIRD 😵!</p><h3>Investigation (again)</h3><p>We hypothesize that because by default ANALYZE using 300 × <a href="https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET"><em>default_statistics_target</em></a> (which is <em>300 × 100 = 30000</em> records by default) to build up the stats which are then used by the PostgreSQL query planner to help it make more informed decisions when choosing the best execution plan for SQL queries, that means we’re using 0.00001% of the records. It sometimes ends up with stats that don’t reflect the table data correctly and if we’ll continue to execute it once a week it will be like a game of roulette — sometimes the stats are good and sometimes they’re not.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*FvZOiDEIJE6pY5ax" /><figcaption>Photo by <a href="https://unsplash.com/@freewalkingtoursalzburg?utm_source=medium&amp;utm_medium=referral">Free Walking Tour Salzburg</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Mitigation plan</h3><p>First off, we reverted the changes to the default values since we suspect that the first time it happened it was because of outdated stats (~5 months old) so it will give us more time than one week until the potential next incident.</p><p>We also added a new dedicated Datadog monitor that will alert us in case of increased latency of SQL queries on brands table.<br>Additionally, we updated our Terraform monitor module with a new monitor that will alert us in case of unusual SQL query latency, so we won’t rely only on the overall latency of HTTP requests of each microservice.</p><h3>What’s next</h3><p>The microservice that is using brands table does not use the read replicas of the RDS cluster, so we’re planning to utilize the replica for this service as well using <a href="https://github.com/pgpool/pgpool2">pgpool2</a> which will do the query parsing and targeting for us.</p><p>We’re planning to increase the default value of <a href="https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET"><em>default_statistics_target</em></a>, so the sample size will be bigger to try and avoid the incorrect statistics, and if changing the value will help, we’ll then change the auto-analyze back to more frequent executions.</p><p>You can expect an update once we know more.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6edfc5f553f0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/tailor-tech/how-were-solving-a-latency-issue-on-a-3-billion-records-postgres-table-6edfc5f553f0">How we’re solving a latency issue on a 3 Billion Records Postgres Table</a> was originally published in <a href="https://medium.com/tailor-tech">Tailor Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>