<?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 Jatin Jain Saraf on Medium]]></title>
        <description><![CDATA[Stories by Jatin Jain Saraf on Medium]]></description>
        <link>https://medium.com/@jatinjainsaraf?source=rss-5de1872d828------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*X_LsKR8U3DCn0US2eUv0Tw.jpeg</url>
            <title>Stories by Jatin Jain Saraf on Medium</title>
            <link>https://medium.com/@jatinjainsaraf?source=rss-5de1872d828------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 16:55:28 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jatinjainsaraf/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[Bloom Filter, Reservoir Sampling, HyperLogLog: Three Probabilistic Answers to Three Different…]]></title>
            <link>https://medium.com/@jatinjainsaraf/bloom-filter-reservoir-sampling-hyperloglog-three-probabilistic-answers-to-three-different-24d0748c2b2f?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/24d0748c2b2f</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[backend-development]]></category>
            <category><![CDATA[backend]]></category>
            <category><![CDATA[redis]]></category>
            <category><![CDATA[systems-thinking]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Tue, 27 Oct 2026 17:15:27 GMT</pubDate>
            <atom:updated>2026-10-27T17:31:58.072Z</atom:updated>
            <content:encoded><![CDATA[<h3>Bloom Filter, Reservoir Sampling, HyperLogLog: Three Probabilistic Answers to Three Different Questions</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*d4NYNMq7jCRaJd3R" /></figure><h3>Same family, different jobs</h3><p>Bloom filters, reservoir sampling and HyperLogLog tend to get filed in the same mental drawer. All three are probabilistic. All three give up something to save memory. All three let you process a stream without keeping everything you’ve seen. If you’ve only met them in a “data structures you should know” listicle, they blur together into one category: clever hashing tricks that are approximately right.</p><p>But they aren’t interchangeable, and the difference isn’t one of accuracy or sophistication. <strong>Each one answers a different question.</strong> Pick the wrong one and you don’t get a slightly worse answer. You get a confident answer to a question you didn’t ask.</p><p>Here’s the setup for the whole post. SupraScan indexes a continuous stream of blockchain transactions. Every transaction has a sender wallet. Millions of transactions arrive, wallets come and go, and every analytics feature competes with the indexer itself for memory. Against that one stream, we keep asking three questions:</p><ul><li><strong>“Have we seen wallet X before?”</strong> That’s a Bloom filter.</li><li><strong>“Give me 1,000 random transactions from today’s stream.”</strong> That’s reservoir sampling.</li><li><strong>“How many distinct wallets transacted today?”</strong> That’s HyperLogLog.</li></ul><p>Three questions, three structures, three different definitions of a useful answer. The rest of this post is about why you can’t swap them, plus a fourth question that none of them answers.</p><h3>Three questions, three structures</h3><pre>Have I seen this wallet?<br>  Structure:      Bloom filter<br>  What it stores: A bit array<br>  Error behavior: False positives, never false negatives<br>  Memory:         Set at creation from expected items and FP target<br>  Mergeable:      Yes, bitwise OR, if size and hash functions match<br>  Deletable:      Not in the ordinary form<br><br>Give me k random transactions<br>  Structure:      Reservoir sample<br>  What it stores: The k sampled items themselves<br>  Error behavior: Ordinary sampling variation<br>  Memory:         O(k) items<br>  Mergeable:      Only with stream lengths and a weighted merge<br>  Deletable:      Removing an item breaks uniformity<br><br>How many distinct wallets?<br>  Structure:      HyperLogLog<br>  What it stores: A small array of registers<br>  Error behavior: Estimate with about 0.81% standard error (Redis)<br>  Memory:         Fixed, 12 KB in Redis<br>  Mergeable:      Yes, register-wise max, if precision matches<br>  Deletable:      No per-wallet deletion<br><br>How often does wallet X transact?<br>  Structure:      Count-Min sketch<br>  What it stores: A grid of counters<br>  Error behavior: Overestimates only, from collisions<br>  Memory:         Fixed by width and depth<br>  Mergeable:      Yes, cell-wise sum, if dimensions and hashes match<br>  Deletable:      Not safely in the standard form</pre><p>The last line of each entry surprises people the most. None of these structures lets you “undo” a wallet cheaply, because none of them remembers which wallet set which bits.</p><h3>Bloom filter: “Have we seen this wallet?”</h3><p>A Bloom filter is a bit array of size m and k hash functions. To add a wallet, hash it k times and set those k bits. To check a wallet, hash it the same way and look at those k bits. If any of them is 0, the wallet was never added. If all of them are 1, it <em>might</em> have been added, or other wallets might have set those bits between them.</p><p>That gives the one-sided error every Bloom filter guarantee rests on:</p><ul><li><strong>“No” means no.</strong> No false negatives, as long as you never clear bits.</li><li><strong>“Yes” means maybe.</strong> False positives happen at a rate you choose up front.</li></ul><p>The analogy I use is a bouncer with a fuzzy guest list. He never turns away someone who’s on the list. Occasionally he waves in a stranger who looks enough like a guest.</p><p>Here’s a small implementation, written to show the mechanics rather than to replace a library. It derives all k positions from one SHA-256 digest using double hashing (h1 + i * h2), a well-known shortcut that performs close to k independent hash functions in practice without being literally equivalent to them. The same helper drives the Count-Min sketch later in the post:</p><pre>import { createHash } from &#39;node:crypto&#39;;<br><br>// Runs once per constructor; trial division over odd numbers is plenty here<br>function nextPrime(n) {<br>  if (n &lt;= 2) return 2;<br>  for (n += n % 2 === 0 ? 1 : 0; ; n += 2) {<br>    let prime = true;<br>    for (let d = 3; d * d &lt;= n &amp;&amp; prime; d += 2) prime = n % d !== 0;<br>    if (prime) return n;<br>  }<br>}<br><br>// Two 32-bit hashes from one digest. size must be prime: then any step in<br>// [1, size - 1] is coprime to it, so the first `size` positions h1 + i * h2<br>// are all distinct and an item&#39;s k positions never land on the same slot.<br>// 32-bit hashes can&#39;t address more than 2^32 slots, hence the upper limit.<br>function twoHashes(item, size) {<br>  if (size &lt; 2 || size &gt; 2 ** 32) throw new RangeError(&#39;size must be a prime in [2, 2^32]&#39;);<br>  const digest = createHash(&#39;sha256&#39;).update(item).digest();<br>  const h1 = digest.readUInt32BE(0) % size;<br>  const h2 = 1 + (digest.readUInt32BE(4) % (size - 1));<br>  return [h1, h2];<br>}<br><br>class BloomFilter {<br>  constructor(expectedItems, falsePositiveRate) {<br>    if (!Number.isInteger(expectedItems) || expectedItems &lt;= 0) {<br>      throw new RangeError(&#39;expectedItems must be a positive integer&#39;);<br>    }<br>    if (!(falsePositiveRate &gt; 0 &amp;&amp; falsePositiveRate &lt; 1)) {<br>      throw new RangeError(&#39;falsePositiveRate must be between 0 and 1&#39;);<br>    }<br>    // Optimal sizing for a target false-positive rate, rounded up to a prime<br>    this.m = nextPrime(Math.ceil(-(expectedItems * Math.log(falsePositiveRate)) / Math.LN2 ** 2));<br>    this.k = Math.max(1, Math.round((this.m / expectedItems) * Math.LN2));<br>    this.bits = new Uint8Array(Math.ceil(this.m / 8));<br>  }<br><br>  #positions(item) {<br>    const [h1, h2] = twoHashes(item, this.m);<br>    return Array.from({ length: this.k }, (_, i) =&gt; (h1 + i * h2) % this.m);<br>  }<br><br>  add(item) {<br>    for (const p of this.#positions(item)) this.bits[p &gt;&gt; 3] |= 1 &lt;&lt; (p &amp; 7);<br>  }<br><br>  mightContain(item) {<br>    return this.#positions(item).every((p) =&gt; this.bits[p &gt;&gt; 3] &amp; (1 &lt;&lt; (p &amp; 7)));<br>  }<br>}</pre><p>Sized for 1 million wallets at a 1% false-positive rate, this picks m = 9,585,061 bits (the optimal 9,585,059, rounded up to the next prime, which is about 1.2 MB of bit array) and k = 7. I loaded it with 1 million synthetic wallet IDs, checked every one of them, then probed it with five separate sets of 1 million IDs that were guaranteed never to have been inserted (a different prefix):</p><pre>false negatives:                 0 of 1,000,000<br>false positive rate, 5 probes:   1.007%  1.003%  1.020%  1.017%  0.984%<br>bit array:                       1.2 MB (ceil(m / 8) bytes, excluding object overhead)</pre><p>The false-positive rate is empirical, so it wobbles a little from probe set to probe set, but it sits on the 1% it was sized for.</p><p>Compare that with storing the addresses themselves. A Supra or Aptos address is 32 bytes, so 1 million of them is 32 MB of raw address bytes alone. A real set adds hash-table overhead on top, and JavaScript strings of hex addresses cost several times more again. The filter costs about 9.6 bits per wallet no matter how long the addresses are.</p><h3>Where it earns its keep</h3><p><strong>Skipping disk reads in LSM-trees.</strong> Cassandra, RocksDB and friends keep a Bloom filter per SSTable. A point lookup checks the filter first and skips every SSTable that definitely doesn’t contain the key. This is why LSM reads aren’t as bad as “check every file on disk” sounds (the <a href="https://academy.jatinjainsaraf.com/system-design-in-depth/storage-engines-btrees-vs-lsm-trees">B-Trees vs LSM-Trees module</a> walks through the read path, and the <a href="https://insight.jatinjainsaraf.com/why-postgres-and-cassandra-made-opposite-bets-on-storage-engines">Postgres vs Cassandra storage engines post</a> covers why the two databases chose differently).</p><p><strong>Guarding against cache penetration.</strong> If attackers or buggy clients request wallets that don’t exist, every request misses the cache and hits the database. A filter of known wallets lets you reject those cheaply. Be precise about what this buys you: it only works if “in the filter” means exactly what the lookup needs (“this wallet exists in our index”), and if the filter is updated whenever a new wallet is indexed. A filter that lags the index will reject real, brand-new wallets. That’s not a false negative of the filter. It’s a false negative of your synchronization. (Penetration’s two siblings, stampede and avalanche, need different fixes; the <a href="https://academy.jatinjainsaraf.com/redis-in-depth/cache-stampede-avalanche-penetration">cache stampede, avalanche and penetration module</a> covers all three.)</p><h3>What it can’t do</h3><p>You can’t delete. Clearing a wallet’s bits would also clear bits that other wallets rely on, and those wallets would start returning “definitely not present”, which breaks the one guarantee the filter has. Counting Bloom filters (small counters instead of bits) and cuckoo filters support deletion, at the cost of more memory per item and their own failure modes.</p><p>You also can’t list what’s inside or ask how many times a wallet appeared. The filter only knows “these bits are on”.</p><h3>Reservoir sampling: “Give me 1,000 random transactions”</h3><p>Different problem. You’re debugging a fee-calculation issue and want a random sample of today’s transactions to inspect by hand. You don’t know how many transactions today will have. You can’t store all of them and sample at midnight, because that defeats the point.</p><p>The obvious shortcuts are both wrong:</p><ul><li><strong>Keep the first 1,000.</strong> That’s a sample of the first few seconds after midnight, not of the day.</li><li><strong>Keep every Nth transaction.</strong> You don’t know N until the day ends, and any periodic pattern in the stream (a bot firing every 1,000 blocks) aliases straight into your sample.</li></ul><p>Reservoir sampling (Algorithm R) keeps a uniform random sample of size k from a stream of unknown length, in a single pass, with O(k) memory:</p><ol><li>Put the first k items into the reservoir.</li><li>For the n-th item (n &gt; k), pick a random integer j in [0, n).</li><li>If j &lt; k, replace reservoir slot j with the new item. Otherwise drop it.</li></ol><pre>function reservoirSample(stream, k) {<br>  if (!Number.isInteger(k) || k &lt; 0) {<br>    throw new RangeError(&#39;k must be a non-negative integer&#39;);<br>  }<br>  const reservoir = [];<br>  if (k === 0) return reservoir;<br>  let n = 0;<br>  for (const item of stream) {<br>    n++;<br>    if (reservoir.length &lt; k) {<br>      reservoir.push(item);<br>      continue;<br>    }<br>    const j = Math.floor(Math.random() * n);<br>    if (j &lt; k) reservoir[j] = item;<br>  }<br>  return reservoir;<br>}</pre><h3>Why every transaction gets a fair chance</h3><p>Take item number n in a stream that ends up N items long. Three steps:</p><ol><li><strong>It gets in</strong> with probability k/n (that&#39;s j &lt; k when j is uniform over n values).</li><li><strong>At each later step </strong><strong>i, it gets evicted</strong> only if the new item gets in (probability k/i) <em>and</em> lands on its particular slot (probability 1/k). That&#39;s (k/i) × (1/k) = 1/i, so it survives step i with probability (i-1)/i.</li><li><strong>Surviving every step</strong> from n+1 to N multiplies those odds, and the product telescopes:</li></ol><pre>P(survive)  = (n / (n+1)) × ((n+1) / (n+2)) × ... × ((N-1) / N)  =  n / N<br> <br>P(in final sample)  = P(gets in) × P(survive)  =  (k / n) × (n / N)  =  k / N</pre><p>Every item, early or late, ends up with the same k/N chance. You can check this empirically: sample 3 items from a stream of 10, repeat 100,000 times, and count how often each item shows up. You expect about 30,000 each:</p><pre>item:    0      1      2      3      4      5      6      7      8      9<br>count:   30034  30025  29961  29947  30130  29787  29798  30079  30081  30158</pre><p>The analogy: a raffle where tickets keep arriving all night, and at any moment the drum holds a fair draw of everything that arrived so far.</p><h3>The caveats that matter in production</h3><p><strong>Uniform is not the same as representative.</strong> Each transaction has an equal chance, so a chain or a transaction type that makes up 0.1% of traffic will contribute about one item to a 1,000-item sample, often zero. If you need coverage per chain, per hour or per transaction type, keep a separate reservoir per group (stratified sampling).</p><p><strong>Merging needs the stream lengths.</strong> If two indexer workers each keep a reservoir of 1,000, concatenating them doesn’t give a uniform sample of the combined stream when one worker saw 10 million transactions and the other saw 100,000. A correct merge draws from each reservoir in proportion to its stream length, so each worker has to report n along with its sample.</p><p><strong>The randomness is part of the correctness.</strong> Math.random() is fine for debugging samples. If a sample feeds anything adversarial (audits, rewards, anything a user could game by timing their transactions), use a proper source of randomness.</p><h3>HyperLogLog: “How many distinct wallets?”</h3><p>I’ve covered this one in depth elsewhere: the <a href="https://academy.jatinjainsaraf.com/redis-in-depth/hyperloglog-bitmaps-geospatial">HyperLogLog, Bitmaps, and Geospatial module</a> of the Redis course explains the internals, the <a href="https://insight.jatinjainsaraf.com/redis-hyperloglog-counting-millions-of-unique-users-with-just-12kb-of-memory">Redis HyperLogLog introduction</a> covers PFADD, PFCOUNT and PFMERGE, and the <a href="https://insight.jatinjainsaraf.com/case-study/exact-analytics-in-12kb-hardening-a-wallet-uniqueness-system">wallet-uniqueness case study</a> covers what went wrong running it under concurrent production writes. So here&#39;s only what this comparison needs.</p><p>HyperLogLog hashes each wallet and looks at the run of leading zeros in the hash. A long run is rare, so seeing one is evidence that many distinct values went by. Splitting hashes across many registers and averaging their evidence turns a noisy signal into a stable estimate. Redis uses 16,384 registers in 12 KB, with a standard error of about 0.81%.</p><p>Two precise points worth stating:</p><ul><li><strong>It’s an estimate.</strong> 0.81% is a standard error, a statistical property of the estimator, not a promise that every count lands within 0.81% of the truth. Most counts land well inside that. Some land outside it.</li><li><strong>Duplicates are free.</strong> A wallet that sends 50,000 transactions sets the same register the same way every time. That’s exactly the property sampling lacks, as the next section shows.</li></ul><p>What HyperLogLog can’t do: tell you whether wallet X is in the set, list the wallets, or tell you how often any wallet appeared. It only knows “about this many distinct things went past”.</p><h3>Why you can’t substitute one for another</h3><p>So far, each structure has answered its own question. The interesting part starts when you try to reuse an answer you already have for a different purpose, which is exactly where the “they’re all basically the same” intuition breaks. Each structure throws away the information the others depend on.</p><h3>A sample can’t count distinct wallets</h3><p>This is the substitution people actually try: “we already keep a 1,000-transaction sample, just count the distinct wallets in it and scale up.”</p><p>Here are two synthetic streams of 1 million transactions each, both shuffled:</p><ul><li><strong>Stream A:</strong> 500 bot wallets send 900,000 transactions between them, and 100,000 real users send 1 transaction each. True distinct wallets: <strong>100,500</strong>.</li><li><strong>Stream B:</strong> 20,000 wallets send exactly 50 transactions each. True distinct wallets: <strong>20,000</strong>.</li></ul><p>I ran the reservoirSample function above on each with k = 1000, then repeated the whole thing 100 times, reshuffling both streams every time, so the result doesn&#39;t hinge on one lucky run:</p><pre>stream   true distinct   distinct in sample (100 runs)    naive estimate (median x1000)<br>                         min    median   max<br>A        100,500         494    518      544              518,000<br>B        20,000          964    976      983              976,000<br> <br>Stream A&#39;s sample showed fewer distinct wallets than B&#39;s in 100 of 100 runs.</pre><p>The sample doesn’t just get the numbers wrong. <strong>It ranks the streams backwards, every time.</strong> Stream A has five times as many wallets, but its sample shows about half as many distinct ones, because roughly 90% of the draws land on the same 500 bots. The 100,000 one-time users, who are most of the answer, show up about 100 times in the sample and look like a rounding error.</p><p>Scaling the sample up doesn’t fix this, because the sample can’t tell a long tail of one-time wallets from no tail at all. Very different streams can produce very similar samples. Charikar, Chaudhuri, Motwani and Narasayya made this precise for one specific setting in “Towards Estimation Error Guarantees for Distinct Values” (PODS 2000): when an estimator sees only a uniform random sample of r rows from a table of n rows, then for any such estimator there exist tables where its ratio error is large, on the order of sqrt(n/r), with non-trivial probability. That&#39;s a statement about worst-case inputs in that sampling model, not a claim that every sample-based estimate is bad on every distribution. But blockchain traffic, with a few very loud wallets and a long tail of quiet ones, looks a lot like the hard cases. HyperLogLog sits outside that model entirely: it sees every item, it just doesn&#39;t keep them.</p><p>And yet that same sample is good at its own job. Questions about transactions are what it’s built for. Across the 100 runs, the share of sampled transactions that came from bots ranged from 87.5% to 92.2%, with a median of 90%, against a true value of exactly 90%. That’s ordinary sampling variation around the right answer. <strong>The sample is good for questions about transactions and bad for questions about distinct wallets.</strong> That’s the whole lesson in one sentence.</p><h3>HyperLogLog can’t answer “have we seen X?”</h3><p>It keeps one small number per register: the longest run of leading zeros seen so far. Wallet X’s hash may have set a register, or a different wallet may have set it higher already. There’s nothing left to check X against. A cardinality sketch answers questions about the whole set and nothing about any single member.</p><h3>A Bloom filter can count, until it can’t</h3><p>This is the interesting near-miss. If you know m and k, the fraction of bits that are set tells you roughly how many distinct items went in. With X bits set:</p><pre>n ≈ -(m / k) × ln(1 - X / m)</pre><p>On the 1-million-wallet filter above, 4,966,293 of the 9,585,061 bits are set (51.8%), and the formula gives <strong>999,691</strong>. That is an estimate under the usual Bloom-filter model (well-spread hashes, no deletions), not an exact count. Duplicates are free here too, since re-adding a wallet sets bits that are already on. So why not just use it?</p><p>Because it only works while the filter is sized for the count. A Bloom filter’s memory grows linearly with the number of items you plan to hold, about 9.6 bits per wallet at 1%. As the filter fills up, X / m approaches 1, the logarithm blows up, and the estimate falls apart. HyperLogLog stays at 12 KB whether you count a thousand wallets or a billion. The Bloom filter can answer the counting question, but at a price that grows with the answer, and only up to the capacity you guessed in advance.</p><h3>A Bloom filter can’t give you a sample</h3><p>It doesn’t store transactions, only bits derived from them. There’s nothing to inspect.</p><h3>The fourth question: “How often does this wallet transact?”</h3><p>None of the three answers this. A Bloom filter says “seen”. HyperLogLog says “how many distinct”. A reservoir says “here are some transactions”. None of them says “wallet X sent 1,831 transactions today”.</p><p>The exact answer is a counter per wallet, a hash map or a Redis INCR key each (the <a href="https://academy.jatinjainsaraf.com/redis-in-depth/atomic-counters-rate-limiters">atomic counters and rate limiters module</a> covers that pattern), which is just as big as storing every wallet. The probabilistic answer is a <strong>Count-Min sketch</strong>: d rows of w counters, one hash function per row. To add a wallet, increment one counter in each row. To estimate a wallet&#39;s count, read its counter in each row and take the minimum.</p><p>Like the Bloom filter above, this is an illustration of the algorithm, reusing the same twoHashes helper, not a production sketch library:</p><pre>class CountMinSketch {<br>  constructor(epsilon, delta) {<br>    if (!(epsilon &gt; 0 &amp;&amp; epsilon &lt; 1) || !(delta &gt; 0 &amp;&amp; delta &lt; 1)) {<br>      throw new RangeError(&#39;epsilon and delta must be between 0 and 1&#39;);<br>    }<br>    this.width = nextPrime(Math.ceil(Math.E / epsilon));<br>    this.depth = Math.ceil(Math.log(1 / delta));<br>    // Float64 counters stay exact up to 2^53, far past where Uint32 would wrap<br>    this.table = Array.from({ length: this.depth }, () =&gt; new Float64Array(this.width));<br>  }<br> <br>  #columns(item) {<br>    const [h1, h2] = twoHashes(item, this.width);<br>    return Array.from({ length: this.depth }, (_, row) =&gt; (h1 + row * h2) % this.width);<br>  }<br> <br>  add(item, count = 1) {<br>    this.#columns(item).forEach((col, row) =&gt; { this.table[row][col] += count; });<br>  }<br> <br>  estimate(item) {<br>    return Math.min(...this.#columns(item).map((col, row) =&gt; this.table[row][col]));<br>  }<br>}</pre><p>Every counter a wallet touches also holds counts from other wallets that collided there, so each row overestimates. Taking the minimum picks the row with the least collision noise. In the standard insertion-only form, the estimate never underestimates. Two claims are worth keeping apart here:</p><ul><li><strong>The theoretical guarantee.</strong> This is the standard additive-error bound for the usual construction: width e / epsilon, depth ln(1 / delta), and each row&#39;s hash drawn independently from a pairwise-independent family. Under those conditions, any single query overestimates by more than epsilon × N with probability at most delta, where N is the total number of transactions.</li><li><strong>What this code does.</strong> It derives every row’s column from one SHA-256 digest, which is a practical construction, not a proof that those independence assumptions hold. What it can show is how this implementation behaves on a real workload.</li></ul><p>With epsilon = 0.001 and delta = 0.01, that&#39;s 5 rows of 2,719 counters, about 109 KB with 8-byte counters. On Stream A from earlier (N = 1 million, so the bound is 1,000):</p><pre>wallet                   true count   estimate<br>bot7                     1,800        1,831<br>user42                   1            35<br>never-seen wallet        0            28<br> <br>all 100,000 one-time users:  mean overcount 31,  worst 1,837<br>estimates above the 1,000 bound:  22 of 100,000  (0.022%)</pre><p>The worst case is worth a look. Some estimates overshot the bound, and that doesn’t contradict the theory: the guarantee caps the probability for each query, it doesn’t promise that every query in a batch of 100,000 stays inside. Empirically, this implementation on this workload exceeded the bound on 22 of 100,000 queries, a rate comfortably below delta. That&#39;s evidence the construction behaves well here, not a proof that it meets the formal guarantee.</p><p>That table shows exactly what it’s good for. In this run, the typical overestimate was around 30 transactions. That’s negligible for a wallet with thousands of transactions and overwhelming for a wallet that appeared once. <strong>Count-Min is for finding the loud wallets, not for auditing the quiet ones.</strong></p><p>In practice that means flagging wallets whose estimated volume crosses a threshold, then checking them exactly. If you want the top 100 wallets rather than everyone over a threshold, pair the sketch with a size-100 min-heap of the current leaders, the same “k largest” pattern from the <a href="https://academy.jatinjainsaraf.com/data-structures-and-algorithms-in-depth/heap-applications">heap applications module</a>. A high count isn’t evidence of abuse. Exchanges, bridges and market makers are loud by design. The sketch tells you where to look, not what you’ll find.</p><p>If you’re on Redis, the RedisBloom module (bundled with Redis Stack, and part of Redis 8) ships Bloom filters, cuckoo filters, Count-Min sketches and Top-K as native commands, so you rarely need to build these yourself.</p><h3>When the question involves two sets</h3><p>Distinct counting has a follow-up problem. Sooner or later someone asks a question about two sets:</p><ul><li>How many wallets were active on <strong>both</strong> mainnet and testnet?</li><li>How many wallets transacted this week but <strong>not</strong> last week?</li></ul><p>HyperLogLog handles unions natively: merge the two sketches and count. For intersections, the usual workaround is inclusion-exclusion, |A ∩ B| = |A| + |B| - |A ∪ B|. The identity is exact. The trouble is that you&#39;re feeding it three estimates and subtracting large numbers to get a small one.</p><p>Suppose each chain has 5 million wallets and only 10,000 are on both. The union is 9,990,000. At 0.81% standard error, the union estimate alone typically misses by tens of thousands of wallets (0.81% of 9.99 million is about 81,000), and |A| and |B| each carry their own error of around 40,000. Those errors are partly correlated, since the union sketch is built from the same registers, so you can&#39;t simply add them up. But whichever way they combine, they&#39;re measured in tens of thousands, and the answer you want is 10,000.</p><p>Rather than argue about the exact error formula, I simulated it. I didn’t run Redis itself. This is a Redis-parameterized HLL: 16,384 registers, a 64-bit hash split into a 14-bit register index and a 50-bit rank, register-wise max for the union, and the classic HLL estimator with the small-range correction (Redis uses a newer estimator, which mainly differs at small counts, far below these). The setup: 5 million wallets per chain, an overlap of exactly 10,000, and 30 runs with different hash seeds. In these 30 runs, the union estimates stayed within about 0.8% of the true count. The intersection estimates did not:</p><pre>true intersection:              10,000<br>estimates (30 runs):            -128,271 to 125,076<br>negative:                       13 of 30<br>more than double the truth:     13 of 30<br>within 50% of the truth:         4 of 30</pre><p>Thirty runs illustrate the problem rather than map its full error distribution, but the mechanism doesn’t depend on the simulation: healthy sketches, a correct identity, and an answer that is mostly noise. The subtraction takes errors that are small relative to 10 million and makes them enormous relative to 10,000.</p><p><strong>Theta sketches</strong>, also in Apache DataSketches, are built for this. They keep a sample of hash values, not register maxima, which lets them do union, intersection and difference directly. (HyperLogLog can only reach A-not-B through the same inclusion-exclusion arithmetic, |A ∪ B| - |B|, with the same error problem.) They cost more memory than HyperLogLog for the same accuracy, and intersections of very small overlaps are still hard for any sketch. But when the product question is &quot;how many wallets did X and Y&quot;, it&#39;s the right family to start from. DataSketches also offers HLL with a choice of 4-, 6- or 8-bit registers, trading memory for update speed, which matters once you keep thousands of sketches (one per time bucket, per chain).</p><h3>The decision cheat sheet</h3><p>Start from the question, not the data structure:</p><pre>Is this item definitely new?<br>  Use:        Bloom filter<br>  Remember:   &quot;No&quot; is certain, &quot;yes&quot; is maybe; no deletes<br><br>Give me a fair sample to look at<br>  Use:        Reservoir sample<br>  Remember:   Uniform isn&#39;t stratified; merges need stream lengths<br><br>How many distinct items?<br>  Use:        HyperLogLog<br>  Remember:   It&#39;s an estimate; fixed memory at any scale<br><br>How often does this item appear?<br>  Use:        Count-Min sketch<br>  Remember:   Overestimates; great for heavy hitters only<br><br>How many items in A and B, or A but not B?<br>  Use:        Theta sketch<br>  Remember:   HLL inclusion-exclusion breaks on small overlaps<br><br>Exactly how many, for billing or compliance?<br>  Use:        None of these<br>  Remember:   Count exactly and pay for it</pre><p>That last entry matters. All of these structures are good because they’re comfortable being slightly wrong. When being slightly wrong costs money or breaks an audit, the right data structure is a database table and an honest COUNT(DISTINCT).</p><p>So yes, these three are close. They’re close the way a thermometer, a barometer and a rain gauge are close: same weather station, same sky, three instruments that each measure exactly one thing. Before you pick one, write down the question you’re asking.</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/bloom-filter-reservoir-sampling-hyperloglog-three-questions"><em>https://insight.jatinjainsaraf.com</em></a><em> on October 27, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=24d0748c2b2f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Your Node.js App Never Imports zlib. It Might Still Be Vulnerable to a Brotli RCE]]></title>
            <link>https://medium.com/@jatinjainsaraf/your-node-js-app-never-imports-zlib-it-might-still-be-vulnerable-to-a-brotli-rce-96b29a1434e8?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/96b29a1434e8</guid>
            <category><![CDATA[backend]]></category>
            <category><![CDATA[nodejs]]></category>
            <category><![CDATA[backend-development]]></category>
            <category><![CDATA[security]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Thu, 22 Oct 2026 17:39:30 GMT</pubDate>
            <atom:updated>2026-10-22T17:48:58.651Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*QzPrIohdBsfU3a9f" /></figure><p>On October 21, 2026, Node.js shipped security releases for all three supported lines. The headline fix is <a href="https://nodejs.org/en/blog/vulnerability/october-2026-security-releases">CVE-2026–111497</a>, a High-severity out-of-bounds write in Node’s Brotli decoder that, according to the advisory, permits remote code execution.</p><p>The natural first reaction is “we don’t decompress anything, so this isn’t ours.” I had that reaction too. A search of my own site’s application code found no zlib usage at all. Then I traced what Node&#39;s built-in fetch() does with a compressed response, and found the Brotli decoder sitting on a path my code uses every day.</p><p>This post walks through that investigation: the source code, a small reproduction you can run yourself, why some features deserve more urgency than others, and the checklist I’d run on any Node service right now.</p><p><strong>The facts, up front:</strong></p><ul><li><strong>Component:</strong> the Brotli decoder in Node.js (zlib)</li><li><strong>CVE:</strong> <a href="https://nodejs.org/en/blog/vulnerability/october-2026-security-releases">CVE-2026–111497</a>, rated High in the Node.js advisory</li><li><strong>Fixed in:</strong> v22.23.4, v24.22.1, v26.12.1</li><li><strong>What to do:</strong> upgrade to the patched release on your line, rebuild, redeploy, and verify the version production actually runs</li></ul><p>It landed in a loud news cycle that also included two PHP remote code execution vulnerabilities, a Railway security disclosure about customer environment variables, and the compromise of eight eMudhra intermediate certificate keys. The Node.js release received less attention, but JavaScript teams should treat it as urgent runtime maintenance.</p><p>The release covers 10 CVEs: 4 rated High, 3 Medium and 3 Low. All the High and Medium issues affect 24.x and 26.x, and all but one affect 22.x as well. It also updates bundled dependencies, including Undici (the library behind Node’s built-in fetch()), c-ares, OpenSSL on 22.x and 24.x, and the bundled CA certificates.</p><p>Node 20 and Node 25 are end-of-life. The advisory is explicit that end-of-life versions are always affected when a security release ships, and they get no fixes. If anything you run is still on one of them, that’s the first thing to change.</p><p>Brotli is a compression format. Servers use it to shrink HTTP responses, and Node ships a native Brotli decoder (inside zlib) to expand them again.</p><p>A decompressor works like a moving crew unpacking boxes into a room. The compressed data says, in effect, “this box holds this much, put it here.” The crew trusts the instructions and keeps unpacking. An out-of-bounds write is what happens when a carefully crafted box convinces the crew to keep unpacking past the wall of the room, straight into the neighbour’s flat. In a process, the neighbour’s flat is other memory: data structures, pointers, things the program relies on. Overwrite the right bytes and you can steer what the program does next. That’s the road from “decompression bug” to “remote code execution.”</p><p>The advisory credits the OpenAI Codex Security team (Khai Tran and Sylvie Mayer) with reporting it, and Matteo Collina and Antoine du Hamel with the fix. (The first version of the advisory credited Matteo Collina as the reporter; the Node.js team <a href="https://github.com/nodejs/nodejs.org/commit/d216a65d">updated the credits</a> on October 22, so you may still see the older wording in cached copies.) The <a href="https://nodejs.org/en/blog/release/v24.22.1">v24.22.1 release notes</a> identify the fix as a cherry-pick from the upstream Brotli library, attributed to Evgenii Kliuchnikov. That identifies the patch as coming from the Brotli dependency Node bundles, rather than as a change to Node’s own JavaScript. The advisory doesn’t describe the exact input that triggers the bug, so I won’t guess at exploit details. What matters for us is a simpler question: does untrusted Brotli data ever reach that decoder in my process?</p><p>Here’s the search I ran across my site’s source tree (excluding the course content folder, whose lessons mention zlib in prose):</p><pre>grep -rnE &quot;Brotli|zlib&quot; src --exclude-dir=content</pre><p>No results. My application code never imports zlib and never decompresses anything by hand. (That covers my code, not my dependencies, which is exactly the point of what follows.)</p><p>But the site calls fetch() constantly: the LinkedIn API, the YouTube API, the Instagram Graph API, Google&#39;s font CSS. And fetch() decompresses responses for you. That&#39;s the whole point of it: you call res.json() and get JSON, not compressed bytes.</p><p>So I checked how. Node v24.22.1 bundles Undici 7.30.1. In <a href="https://github.com/nodejs/undici/blob/v7.30.1/lib/web/fetch/index.js#L2256-L2260">that version’s fetch implementation</a>, the response’s Content-Encoding header picks the decoder, and for br it creates a native Brotli decoder:</p><pre>} else if (coding === &#39;br&#39;) {<br>  decoders.push(zlib.createBrotliDecompress({<br>    flush: zlib.constants.BROTLI_OPERATION_FLUSH,<br>    finishFlush: zlib.constants.BROTLI_OPERATION_FLUSH<br>  }))<br>}</pre><p>Reading source is one thing, so I also tested it. A tiny local server returns a Brotli-compressed body over plain HTTP, and Node’s built-in fetch() reads it:</p><pre>import http from &#39;node:http&#39;;<br>import zlib from &#39;node:zlib&#39;;<br> <br>const body = zlib.brotliCompressSync(<br>  Buffer.from(&#39;{&quot;hello&quot;:&quot;decoded by Node\&#39;s Brotli decoder&quot;}&#39;)<br>);<br> <br>let seenAE;<br>const srv = http.createServer((req, res) =&gt; {<br>  seenAE = req.headers[&#39;accept-encoding&#39;];<br>  res.writeHead(200, {<br>    &#39;content-type&#39;: &#39;application/json&#39;,<br>    &#39;content-encoding&#39;: &#39;br&#39;,<br>  });<br>  res.end(body);<br>}).listen(0, async () =&gt; {<br>  const r = await fetch(`http://127.0.0.1:${srv.address().port}/`);<br>  console.log(&#39;client sent accept-encoding:&#39;, seenAE);<br>  console.log(&#39;json():&#39;, await r.json());<br>  srv.close();<br>});</pre><p>Output on Node v24.22.1 (bundled Undici 7.30.1):</p><pre>client sent accept-encoding: gzip, deflate<br>json(): { hello: &quot;decoded by Node&#39;s Brotli decoder&quot; }</pre><p>Look at the first line. Over plain HTTP, fetch() didn&#39;t advertise Brotli; it asked for gzip, deflate. The server sent Brotli anyway, and fetch() decoded it. In the Node.js fetch() implementation I tested, the response&#39;s Content-Encoding decides which decoder runs, not the request&#39;s Accept-Encoding. (Over HTTPS, this version does advertise br, so a normal server will happily use it.)</p><p>Two things this experiment does not show. It isn’t an exploit: it proves the decoding path exists, not that the vulnerability can be triggered through it. And it ran on the patched v24.22.1, so it says nothing about the patched runtime being vulnerable. What it does establish is that, in the Node.js v24.22.1 fetch() implementation I tested, a Brotli-encoded response reaches the native decoder even when the request did not advertise Brotli support.</p><p>That changes the threat model. Don’t treat Accept-Encoding as a security boundary. With the built-in fetch() I tested, whoever controls the response controls whether your process runs the Brotli decoder. If you use a different HTTP client or runtime, verify its behavior rather than assuming it matches.</p><p>This is the production question, and the answer decides how urgent this is for you.</p><p><strong>Calls to big, well-known APIs.</strong> LinkedIn, Google, Stripe and the like are unlikely to send you malicious Brotli. The risk here is low, but not zero: a compromised upstream, a hijacked domain or a proxy in the middle all change who’s really answering.</p><p><strong>Calls to URLs your users give you.</strong> This is where it gets serious. Think of webhook URL testers, link preview and Open Graph extractors, document importers, URL-to-PDF services, image proxies and RSS readers. In any feature where a user types a URL and your server fetches it, an attacker may control the response that reaches the decompressor. On an affected runtime, that’s a potentially dangerous path and the first place to patch. The exact impact depends on the vulnerable code path and your environment, so don’t assume every URL-fetching feature is automatically exploitable, but don’t assume it’s safe either.</p><p><strong>Redirects.</strong> A request to a host you trust can redirect to one you don’t. fetch() follows redirects by default, and the response that finally gets decoded may come from a server you never meant to talk to.</p><p><strong>Incoming request bodies.</strong> If any middleware in your stack decompresses request bodies based on the client’s Content-Encoding header, then every client on the internet can choose to send you Brotli. Check what your body parser does with compressed uploads; don&#39;t assume it ignores them.</p><p>A useful analogy for all four: your code is the person who signed for the parcel, but fetch() is the assistant who opens it. You never touched the box, and it still got opened in your office.</p><p>One more point, because patching tends to feel like the end of the job: upgrading Node fixes this bug, not the risk of fetching user-supplied URLs. Those features still need normal server-side request forgery (SSRF) protection: restrict which destinations are allowed, validate the resolved IP address (not just the hostname), and re-check the destination on every redirect. The next section shows why the “validate the resolved address” part matters.</p><p>Three more issues are rated High, and two of them are about exactly the kind of checks SSRF protection relies on:</p><ul><li><strong>CVE-2026–105483: fullwidth-digit IP addresses.</strong> TLS certificate hostname checks can be bypassed when an IP address is written with fullwidth digits (Unicode characters that look like 1, 2, 3 but aren&#39;t ASCII).</li><li><strong>CVE-2026–106069: loosely resolved IP addresses.</strong> A separate bypass, where a host that loosely resolves to an IP address passes the certificate hostname check. Hostname verification is the part of TLS that proves you’re talking to the server you meant to. Service-to-service calls are where teams trust TLS the most and look at it the least.</li><li><strong>CVE-2026–105482: </strong><strong>dns.resolve truncates names containing a NUL character.</strong> When a name is truncated at a NUL byte, the string your code checked and the name that actually gets resolved can differ. Not every allowlist is vulnerable, but any check-then-resolve flow that handles untrusted hostnames is worth a look.</li></ul><p>One Medium-rated fix matters to anyone who builds startup snapshots or single executable applications (SEAs): CVE-2026–106074. The entropy caches behind crypto.randomUUID() and crypto.randomInt() were being saved into the snapshot, so every process started from it repeats the same &quot;random&quot; values. The fix is in the new Node binary, but a snapshot you already built with an affected version still contains the old cached values. If you ship snapshots or SEAs, rebuild them with the patched Node. If you don&#39;t know what a startup snapshot is, you almost certainly don&#39;t use one.</p><p>The eMudhra incident from the same news cycle is easy to file under “browser problem.” Eight eMudhra intermediate CA keys were so weak they could be recovered from public certificates, which in principle lets someone issue certificates that impersonate websites. Chrome, Firefox, Apple and Microsoft moved to block them within days.</p><p>Browsers have their own trust stores and update them quickly. Non-browser clients are a different story: API clients, build pipelines, containers and server runtimes each trust whatever certificate list they were configured with. By default, Node verifies HTTPS connections against a CA list bundled into the binary (you can opt into the operating system’s store with --use-system-ca), and this release updates that bundled list in response to the eMudhra compromise.</p><p>That doesn’t mean every outdated Node app is about to have its traffic intercepted. An attacker would still need a usable certificate and a position on the network. But it does mean the browser fix didn’t fix your servers. The runtime’s trust configuration is part of its security surface, and an old binary carries an old list.</p><p>The PHP flaws disclosed the same day show how quickly a public security fix attracts attention. <a href="https://x.com/eastdakota/status/2113079352682570051">Cloudflare CEO Matthew Prince reported</a> that Cloudflare’s network saw thousands of exploit attempts in the 10 hours before the announcement (details had leaked early), tens of thousands in the 45 minutes after it, hundreds of thousands within two hours, and millions by the time he posted. A published patch also tells attackers where the bug was.</p><p>That’s the case for treating runtime security releases as urgent maintenance rather than routine dependency housekeeping. To be clear, it isn’t evidence that CVE-2026–111497 is being exploited; I haven’t found a reliable report that it is. The broader threat environment justifies the urgency on its own.</p><p><strong>1. Check what you run locally.</strong></p><pre>node -v</pre><p>You want v22.23.4, v24.22.1, v26.12.1, or later on the same line. A local upgrade is the start, not the fix.</p><p><strong>2. Check what production actually runs.</strong> This is the step people skip. Your package.json saying &quot;node&quot;: &quot;24.x&quot; is a range, not a version. On a managed platform, the provider picks the patch release. In a container, the base image does. Log the version at startup and read it from a real deployment:</p><pre>console.info({ nodeVersion: process.version, nodeEnv: process.env.NODE_ENV });</pre><p>Use logs, deployment metadata or authenticated internal tooling for this. Don’t add a public endpoint that announces your runtime version to anyone who asks.</p><p><strong>3. Upgrade, rebuild and redeploy.</strong> Upgrade to the patched release on your supported line, rebuild the deployment artifact, redeploy it, then repeat step 2 against the new deployment.</p><p><strong>4. Make sure the image actually changed.</strong> What matters is that the final image contains a patched Node, not that you ran a build. If you pin an exact version ( node:24.21.0-alpine), bump the pin deliberately. If you use a moving tag like node:24, pull the updated base image and rebuild. Either way, check the result:</p><pre>docker run --rm your-image:tag node -v</pre><p><strong>5. Check your serverless platform’s runtime policy.</strong> Providers handle runtime patches differently: some roll them out automatically, some only on the next deploy. Find out which yours does, and redeploy if it’s required.</p><p><strong>6. List every place you fetch a URL a user controls.</strong> Webhook testers, link previews and Open Graph extractors, importers, URL-to-PDF services, image proxies. Patch the services behind them first, and confirm their redirect handling and destination validation while you’re there.</p><p><strong>7. Rebuild snapshots and SEAs</strong> if you use them, after upgrading Node.</p><p><strong>8. Get off Node 20 and Node 25.</strong> They are end-of-life and will not receive these fixes.</p><p>The chain is short: no zlib import, then the built-in fetch(), then Undici&#39;s decompression, then the native Brotli decoder. Security boundaries can sit below the layer where you normally look for them, and you can&#39;t remove this one from your own code.</p><p>So the action is one you can take today: find out which Node version production is actually running, not which version your config file hopes for, and if it’s older than the fixed release on its line, upgrade, rebuild and redeploy.</p><p><strong>Can Node.js </strong><strong>fetch() expose an application to Brotli decoder vulnerabilities?</strong> It can reach the decoder. In Node v24.22.1 (Undici 7.30.1), fetch() passed a response marked Content-Encoding: br to the native Brotli decoder even though the request didn&#39;t advertise Brotli, so a server that controls the response controls whether that decoder runs. My test shows that path exists; it doesn&#39;t show that CVE-2026-111497 can be triggered through it. The advisory is the source for the vulnerability and its impact, and upgrading is the fix either way.</p><p><strong>Does removing </strong><strong>zlib imports eliminate the risk?</strong> No. The decoder is used by the runtime on your behalf, through fetch() and anything else that decompresses data. The fix is upgrading the Node runtime itself.</p><p><strong>How do I verify the Node.js version running in production?</strong> Log process.version at startup and read it from a live deployment&#39;s logs, or check the runtime inside your built image with docker run --rm your-image:tag node -v. Don&#39;t trust the engines field or your local node -v.</p><p><em>Last checked against the Node.js advisory on October 22, 2026. If the advisory changes, this post will be updated and the change noted here.</em></p><ul><li><a href="https://nodejs.org/en/blog/vulnerability/october-2026-security-releases">Node.js: October 21, 2026 Security Releases</a>: full CVE list, severities, credits and fixed versions</li><li><a href="https://nodejs.org/en/blog/release/v24.22.1">Node.js v24.22.1 release notes</a>: the release used for the experiment above</li><li><a href="https://github.com/nodejs/undici/blob/v7.30.1/lib/web/fetch/index.js#L2256-L2260">Undici v7.30.1, </a><a href="https://github.com/nodejs/undici/blob/v7.30.1/lib/web/fetch/index.js#L2256-L2260">lib/web/fetch/index.js</a>: the response decoding code quoted in this post</li><li><a href="https://x.com/eastdakota/status/2113079352682570051">Matthew Prince on X: #php2shell stats</a>: the original post with Cloudflare’s cumulative exploit-attempt figures</li><li><a href="https://officechai.com/stories/cloudflare-ceo-says-php-exploit-attempts-went-from-thousands-to-millions-within-hours-of-disclosure/">OfficeChai: Cloudflare CEO on PHP exploit attempts</a>: Matthew Prince’s figures on attack traffic after the PHP disclosure</li><li><a href="https://thehackernews.com/2026/10/eight-emudhra-ca-keys-compromised.html">The Hacker News: Eight eMudhra CA Keys Compromised</a>: background on the certificate incident</li><li><a href="https://blog.railway.com/p/security-disclosure-wednesday-october-21st">Railway: Security Disclosure, October 21</a>: the Railway incident mentioned in the introduction</li></ul><p>The Node.js In-Depth modules on <a href="https://academy.jatinjainsaraf.com/nodejs-in-depth/native-streams-off-heap">Native Streams and Off-Heap Buffer Storage</a> (where zlib streams live), <a href="https://academy.jatinjainsaraf.com/nodejs-in-depth/config-security-rate-limiting">Configuration, Security Hardening, and Rate Limiting</a>, <a href="https://academy.jatinjainsaraf.com/nodejs-in-depth/npm-ecosystem">npm and the Node.js Ecosystem</a> and <a href="https://academy.jatinjainsaraf.com/nodejs-in-depth/docker-containerization-production">Dockerizing Node.js Applications for Production</a> cover the pieces this post touched in more depth.</p><p>And if this feels familiar, it should. In December 2025, <a href="https://insight.jatinjainsaraf.com/react-js-next-js-security">the React Server Components remote code execution vulnerability</a> taught the same lesson from a different layer: application security depends on the platform and framework underneath you, not only the code your team writes.</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/nodejs-october-2026-brotli-rce-cve-2026-111497"><em>https://insight.jatinjainsaraf.com</em></a><em> on October 22, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=96b29a1434e8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[You Might Not Need a Queue Service: Postgres SKIP LOCKED as a Job Queue]]></title>
            <link>https://medium.com/@jatinjainsaraf/you-might-not-need-a-queue-service-postgres-skip-locked-as-a-job-queue-f4744325afe4?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/f4744325afe4</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[design-systems]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Thu, 22 Oct 2026 17:00:50 GMT</pubDate>
            <atom:updated>2026-10-22T17:57:39.860Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*UWaEVYwq4NFGDNH2" /></figure><h3>The Problem Every Indexer Hits Eventually</h3><p>A blockchain indexer starts life as one process reading one block at a time. (The full architecture of the one I work on is in the case study <a href="https://insight.jatinjainsaraf.com/case-study/indexing-a-blockchain-in-real-time-at-scale">Indexing a Blockchain in Real Time, at Scale</a>.) Then a backfill comes along: two million historical blocks to catch up on. One worker would take days, so you start ten.</p><p>Now the ten workers need to split the work. Worker 3 takes blocks 0 to 9,999, worker 7 takes 10,000 to 19,999, and no two workers should be indexing the same range at the same time. When a worker crashes halfway through its range, someone else has to pick that range up.</p><p>The reflex is to reach for a queue: Redis with BullMQ, SQS, RabbitMQ. That’s one more service to deploy, monitor, secure and pay for, and one more place for state to drift out of sync with your database.</p><p>Before you add any of that, look at the database you already run. PostgreSQL has had a feature built for exactly this since version 9.5: FOR UPDATE SKIP LOCKED. With one table and one query, ten workers can claim different jobs concurrently without waiting on each other, and leases let another worker retry a job when its owner crashes.</p><p>This post covers how it works, the traps that hurt in production (stale workers, long transactions and table bloat), how to watch the queue, and the point where you really should move to a dedicated queue.</p><h3>The Jobs Table</h3><p>Start with the table. Each row is one unit of work, here a range of blocks to index.</p><pre>CREATE TABLE jobs (<br>  id            bigserial PRIMARY KEY,<br>  kind          text        NOT NULL,<br>  payload       jsonb       NOT NULL,<br>  status        text        NOT NULL DEFAULT &#39;pending&#39;<br>                CHECK (status IN (&#39;pending&#39;, &#39;running&#39;, &#39;done&#39;, &#39;failed&#39;)),<br>  run_at        timestamptz NOT NULL DEFAULT now(),<br>  attempts      int         NOT NULL DEFAULT 0,<br>  locked_by     text,<br>  claim_token   uuid,<br>  locked_until  timestamptz,<br>  created_at    timestamptz NOT NULL DEFAULT now()<br>);<br> <br>INSERT INTO jobs (kind, payload)<br>SELECT &#39;index_blocks&#39;,<br>       jsonb_build_object(&#39;from&#39;, g, &#39;to&#39;, g + 9999)<br>FROM generate_series(0, 1999999, 10000) AS g;</pre><p>That’s 200 jobs. Now ten workers need to take them one at a time, safely. (claim_token will matter later, when we deal with workers that come back from the dead.)</p><h3>Three Ways to Get It Wrong</h3><h4>Attempt 1: Read, then update</h4><pre>SELECT id FROM jobs WHERE status = &#39;pending&#39; ORDER BY run_at, id LIMIT 1;<br>-- application code holds the id<br>UPDATE jobs SET status = &#39;running&#39; WHERE id = $1;</pre><p>Two workers run the SELECT at the same moment, both see job 42, and both update it. Nothing in the second statement checks that the job is still pending, so both workers index the same blocks.</p><p><strong>In production:</strong> duplicate rows, unique constraint violations in the indexed tables, and a backfill that does more work than it needs to. You might add AND status = &#39;pending&#39; to the update and check the affected row count, which closes the race, but now the loser has to retry, and under load most workers spend their time losing.</p><h4>Attempt 2: Lock the row with FOR UPDATE</h4><pre>BEGIN;<br>SELECT id FROM jobs<br>WHERE status = &#39;pending&#39;<br>ORDER BY run_at, id<br>LIMIT 1<br>FOR UPDATE;<br>-- ... mark it running, commit ...</pre><p>This one is correct: only one transaction can hold the row lock. (How row locks and advisory locks work underneath is covered in the <a href="https://academy.jatinjainsaraf.com/postgresql-in-depth/locking-internals">Locking Internals</a> module of PostgreSQL In-Depth.) The problem is what the other nine workers do. They all run the same query on the same data, so they all reach the same first row and wait for its lock.</p><p>When the lock is released, PostgreSQL rechecks the row against the query’s conditions. Its status is now running, so the waiting worker can&#39;t have it and moves on to the next pending row. But the other eight waiters do the same thing at the same moment, so they pile onto the second row together, and the line forms again.</p><p><strong>In production:</strong> you scale from one worker to ten and throughput barely moves. With this query pattern, adding workers mostly adds lock contention, not throughput. Workers spend their time waiting in a line for rows someone else is about to take, instead of claiming independent jobs.</p><h4>Attempt 3: Advisory locks</h4><p>pg_try_advisory_lock(id) can work, but you manage the lock yourself, session-level locks survive a connection being returned to the pool, and a lock call in the WHERE clause can lock rows the LIMIT later throws away. It&#39;s a sharp tool for a problem that already has a built-in answer.</p><h3>SKIP LOCKED: Walk Past the Plates Someone Is Holding</h3><p>Picture a buffet. With plain FOR UPDATE, everyone queues behind the first person and waits for them to finish choosing. With SKIP LOCKED, if someone already has their hand on a plate, you take the next one. Nobody waits and nobody grabs the same plate.</p><p>That’s exactly what the clause does. When the query hits a row that another transaction has locked, it doesn’t wait. It skips the row and keeps scanning until it has found enough rows it <em>can</em> lock.</p><p>Here’s the claim query. It picks a job, locks it, marks it running and returns it, all in one statement:</p><pre>WITH next AS (<br>  SELECT id<br>  FROM jobs<br>  WHERE status = &#39;pending&#39;<br>    AND run_at &lt;= now()<br>  ORDER BY run_at, id<br>  LIMIT 1<br>  FOR UPDATE SKIP LOCKED<br>)<br>UPDATE jobs j<br>SET status       = &#39;running&#39;,<br>    attempts     = j.attempts + 1,<br>    locked_by    = $1,<br>    claim_token  = gen_random_uuid(),<br>    locked_until = now() + interval &#39;5 minutes&#39;<br>FROM next<br>WHERE j.id = next.id<br>RETURNING j.id, j.kind, j.payload, j.claim_token;</pre><p>Run it from ten workers at once and each gets a different job. Raise the LIMIT to claim a batch of ten in one round trip. id in the ORDER BY is a tie-breaker, so jobs scheduled for the same instant come out in a predictable order.</p><p>One thing to know: SKIP LOCKED deliberately gives you an inconsistent view of the table, because it pretends locked rows don&#39;t exist. That&#39;s exactly right for a queue and exactly wrong for almost anything else, such as reports, balances or counts. Keep it on queue tables. The <a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE">PostgreSQL docs on the locking clause</a> say the same.</p><h3>The First Trap: Holding the Transaction Open</h3><p>The tempting way to run a job looks like this:</p><pre>BEGIN<br>  claim job with SKIP LOCKED<br>  index 10,000 blocks          &lt;- minutes<br>  mark job done<br>COMMIT</pre><p>It feels elegant. If the worker crashes, the transaction rolls back and the row lock is released, so the job becomes claimable again with no extra code.</p><p>It’s also one of the most expensive patterns you can run against PostgreSQL:</p><ul><li><strong>Every worker pins a database connection for the whole job.</strong> Ten workers means ten connections sitting idle in a transaction for minutes. With a small pool, the rest of your application starts waiting for connections.</li><li><strong>Long transactions hold back vacuum.</strong> <a href="https://academy.jatinjainsaraf.com/postgresql-in-depth/mvcc">MVCC</a> is time travel for rows: old row versions stay around as long as any running transaction might still need to see them. This transaction has already written something (the claim), which gave it a transaction ID. Until it ends, vacuum can’t remove row versions that became dead after that ID was assigned, because Postgres has to treat the transaction as one that might still need them. A read-only transaction holds vacuum back the same way while it keeps an old snapshot, for example under REPEATABLE READ. The effect reaches every table in the database, not just the jobs table. Your hottest tables bloat while the job runs.</li><li><strong>Idle-in-transaction timeouts will kill it.</strong> If you’ve set idle_in_transaction_session_timeout (and you should have), long jobs get killed partway through.</li></ul><p>The fix is to <strong>claim and commit straight away</strong>, then run the job outside any transaction. The claim query above does exactly that: it’s a single auto-committed statement. The row is no longer locked, but status = &#39;running&#39; keeps other workers away from it.</p><h3>Leases Replace Locks</h3><p>Once the job runs outside a transaction, a crashed worker no longer releases anything automatically. That’s what locked_until is for. It&#39;s a lease: the worker owns the job until that time, and then the job is up for grabs again.</p><h4>Why a worker ID isn’t enough</h4><p>Picture this sequence:</p><ol><li>Worker A claims job 42.</li><li>Worker A stalls in a long garbage-collection pause, or loses its network for a minute.</li><li>Its lease expires, and the job goes back to pending.</li><li>Worker A’s process restarts with the same worker ID and claims job 42 again (or worker B claims it).</li><li>The old, stalled execution of worker A wakes up and marks the job done.</li></ol><p>If ownership checks only compare locked_by, step 5 succeeds whenever the new claim carries the same worker ID, and a stale execution completes a claim that belongs to someone else. That&#39;s why the claim query generates a fresh claim_token for <strong>every claim</strong>. The worker ID says who is working on the job. The token says which claim this is.</p><p>A long job extends its lease as it goes (a heartbeat), and only the current claim can do that:</p><pre>UPDATE jobs<br>SET locked_until = now() + interval &#39;5 minutes&#39;<br>WHERE id = $1<br>  AND claim_token = $2<br>  AND status = &#39;running&#39;;</pre><p>Completion checks the same token:</p><pre>UPDATE jobs<br>SET status       = &#39;done&#39;,<br>    locked_by    = NULL,<br>    claim_token  = NULL,<br>    locked_until = NULL<br>WHERE id = $1<br>  AND claim_token = $2<br>  AND status = &#39;running&#39;<br>RETURNING id;</pre><p>If no row comes back, the claim was lost: another worker now owns the job, or the reaper took it back. The worker must stop processing and must not treat the job as completed. That doesn’t undo side effects it has already performed, which is why those need to be idempotent (more on that below). If a heartbeat returns no row, the worker should stop working on the job right away.</p><p>A reaper, which can be a cron job or any worker on a timer, takes back expired leases. It clears the token, so no stale execution can match it afterwards. It also pushes the retry into the future with exponential backoff and some jitter, so a job that fails because a downstream service is down doesn’t get retried in lockstep with every other failing job (the <a href="https://academy.jatinjainsaraf.com/system-design-in-depth/timeouts-retries-backoff">Timeouts, Retries, and Backoff</a> module explains why jitter matters):</p><pre>UPDATE jobs<br>SET status       = CASE WHEN attempts &gt;= 5 THEN &#39;failed&#39; ELSE &#39;pending&#39; END,<br>    run_at       = now() + make_interval(<br>                     secs =&gt; least(<br>                       3600.0,<br>                       30.0 * power(2.0, least(attempts, 20)) * (0.5 + random())<br>                     )<br>                   ),<br>    locked_by    = NULL,<br>    claim_token  = NULL,<br>    locked_until = NULL<br>WHERE status = &#39;running&#39;<br>  AND locked_until &lt; now();</pre><p>The cap applies after the jitter, so no retry waits longer than an hour. Because the claim query increments attempts when a job is claimed, the first failed attempt waits 30 to 90 seconds, the second 60 to 180, and so on until the one-hour cap. Use the same backoff when a worker catches an error and gives the job back on purpose. If many leases can expire at once (say, a whole worker fleet restarts), reap in bounded batches with the same SKIP LOCKED pattern instead of one big update, so recovery doesn&#39;t arrive as a burst of WAL and dead tuples.</p><p>Note what actually takes a job away from a worker here. An expired lease only makes the job eligible for recovery. The reaper is what revokes the claim, by clearing its token. So a worker that finishes just after its lease expires, before the reaper has run, can still complete the job, and that’s fine: nobody else has claimed it yet. Once the reaper clears the token, the old claim can never match again.</p><h4>At-least-once, not exactly-once</h4><p><strong>A job can still run more than once.</strong> A worker can finish the work and crash just before marking it done. The lease expires and another worker runs it again. That’s not a bug in this design. Every queue that redelivers work behaves this way, including SQS and BullMQ. The queue gives you at-least-once processing, not exactly-once.</p><p>The claim token protects the queue row. It can’t stop a stale worker from sending an email or calling a webhook after it has lost its lease. Checking the token right before the side effect makes the window smaller but doesn’t close it. Closing it completely needs the receiving system to reject stale writers, which is what fencing tokens do (see <a href="https://academy.jatinjainsaraf.com/system-design-in-depth/distributed-locks-and-coordination">Distributed Locks and Coordination</a>). Idempotency has to cover the job’s real side effects: for the indexer, that means writing indexed rows with INSERT ... ON CONFLICT DO NOTHING or an upsert, so indexing the same block range twice gives the same result. For external calls, use idempotency keys or deduplication on the receiving side. I covered the pattern in <a href="https://insight.jatinjainsaraf.com/idempotency-keys-how-to-make-retries-safe-in-distributed-systems">Idempotency Keys: How to Make Retries Safe in Distributed Systems</a>.</p><h3>The Second Trap: The Queue Table Bloats Itself</h3><p>A queue table is the worst-case workload for MVCC. Every job is inserted, updated to running, updated again (sometimes several times, for heartbeats) and then marked done or deleted. In PostgreSQL an UPDATE doesn&#39;t change a row in place. It writes a new version and leaves the old one behind as a dead tuple for vacuum to clean up.</p><p>So a table that only ever holds a few hundred pending jobs can contain hundreds of thousands of dead row versions. The production symptom is subtle: <strong>the claim query slowly gets slower</strong>, even though the queue is short. Each claim has to step over dead index entries and dead heap tuples to find the live pending rows at the front of the queue.</p><p>Three things keep it under control.</p><h4>1. A partial index on exactly what the claim query reads</h4><pre>CREATE INDEX jobs_claimable_idx<br>  ON jobs (run_at, id)<br>  WHERE status = &#39;pending&#39;;</pre><p>It matches the claim query’s filter and its ORDER BY run_at, id, so the claim reads the front of the index directly. Because it only covers pending jobs, it&#39;s far smaller than an index over the whole table.</p><p>It is not automatically tiny, though. When a job moves from pending to running, its old index entry doesn’t vanish. It stays in the index as a dead entry until vacuum removes it. (Why B-tree entries outlive the rows they point to is covered in <a href="https://insight.jatinjainsaraf.com/btree-reads-inserts-updates-deletes-blockchain-indexer">How a B-Tree Really Reads, Inserts, Updates and Deletes</a>.) Under heavy churn the index fills up with entries for jobs that were claimed long ago, even when only a handful are pending right now. The partial index makes the claim efficient. It doesn’t replace vacuum.</p><h4>2. Keep finished jobs out of the hot table</h4><p>Don’t let done rows build up in the table you claim from. Either delete them when they finish, or move them to a history table on a schedule. If you need to keep history for weeks, partition the history table by time and drop old partitions. Dropping a partition removes its data as a catalog and file operation, without creating a dead tuple per row, while DELETE of a million rows creates a million dead tuples for vacuum to process. It isn&#39;t free: a plain detach or drop takes an ACCESS EXCLUSIVE lock on the parent table. That mode conflicts with everything, even plain reads, so while the request waits for running queries to finish, every new query on that table queues behind it. If that matters, use ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY first. It takes a weaker lock, but it can&#39;t run inside a transaction block or when the table has a default partition, and if it&#39;s interrupted you have to finish it with DETACH PARTITION ... FINALIZE. The <a href="https://academy.jatinjainsaraf.com/postgresql-in-depth/advanced-partitioning">Advanced Partitioning</a> module covers partition maintenance with pg_partman, and <a href="https://insight.jatinjainsaraf.com/case-study/scaling-a-blockchain-database-from-a-single-partition-to-4-6tb">Scaling a Blockchain Database From a Single Partition to 4.6 TB</a> shows the same idea on a real indexer.</p><h4>3. Tell autovacuum this table is special</h4><p>Autovacuum’s default trigger is a fraction of the table’s estimated size, tuned for ordinary tables. A queue table has a small live set and a high churn rate, so give it a fixed trigger instead:</p><pre>ALTER TABLE jobs SET (<br>  autovacuum_vacuum_scale_factor = 0,<br>  autovacuum_vacuum_threshold    = 1000<br>);</pre><p>That means: vacuum after roughly every 1,000 dead tuples, however big the table is. The 1,000 is a starting point, not a recommendation for every system. Pick it from the table’s update rate, how long a vacuum takes, and how much bloat you can tolerate.</p><p>Leave vacuum throttling (autovacuum_vacuum_cost_delay) at its default to begin with. Turning it off lets vacuum run flat out, and on a database that&#39;s already busy with indexing traffic, that I/O competes with your real queries. If vacuum can&#39;t keep up, look at how long it runs, the cost limits, your spare I/O and the competing workload before you touch throttling. The <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html">routine vacuuming docs</a> cover these settings in depth, the <a href="https://academy.jatinjainsaraf.com/postgresql-in-depth/autovacuum">Autovacuum</a> module walks through the settings people most often get wrong, and I wrote about what autovacuum actually does in <a href="https://insight.jatinjainsaraf.com/postgres-wal-checkpoints-wait-events-vacuum-analyze-explained">Every Postgres Write Triggers Five Background Processes</a>.</p><p>And remember the first trap: none of this works if a long-running transaction is holding back vacuum. Bloat and long transactions are the same problem seen from two sides.</p><h3>Watching the Queue in Production</h3><p>A Postgres-backed queue needs watching at two levels.</p><p><strong>Queue health:</strong></p><ul><li>Pending and running job counts.</li><li>Age of the oldest pending job (the number your users actually feel).</li><li>Job duration, retry rate and failure rate.</li><li>Expired leases per hour, which tells you about crashing or stalling workers.</li><li>Claim query latency.</li></ul><p><strong>Database health:</strong></p><ul><li>Dead tuple trend and autovacuum frequency for the jobs table, from pg_stat_user_tables. n_dead_tup is an estimate, and last_autovacuum alone doesn&#39;t prove vacuum is keeping up, so watch the trend alongside claim latency.</li><li>Connection usage.</li><li>WAL volume and replication lag. Queue churn is all writes, and on an indexer that can push replicas behind (see <a href="https://insight.jatinjainsaraf.com/taming-postgresql-replication-lag-in-real-time-blockchain-indexers">Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers</a>).</li><li>Long-running transactions and sessions stuck idle in transaction, from pg_stat_activity.</li></ul><p>A growing queue doesn’t automatically mean Postgres is the bottleneck. Jobs can back up because an external API is slow or because you don’t have enough workers. The age of the oldest job and the job duration, read together, tell you which.</p><h3>Stop Polling: LISTEN and NOTIFY</h3><p>Idle workers need to find out when new work arrives. Running the claim query every 100 milliseconds from ten workers is a lot of wasted queries on an empty queue. Running it every 10 seconds adds up to 10 seconds of latency.</p><p>PostgreSQL has a built-in wake-up signal. Workers listen on a channel:</p><pre>LISTEN jobs_ready;</pre><p>Whatever enqueues work sends a notification in the same transaction:</p><pre>BEGIN;<br>INSERT INTO jobs (kind, payload) VALUES (&#39;index_blocks&#39;, &#39;{&quot;from&quot;: 2000000, &quot;to&quot;: 2009999}&#39;);<br>NOTIFY jobs_ready;<br>COMMIT;</pre><p>Notifications are delivered only when the transaction commits, so a worker never wakes up for a job it can’t see yet. Treat the notification as a hint to run the claim query, not as the job itself. The job lives in the table. The notification is just a tap on the shoulder.</p><p>Three things to know before you rely on it:</p><ul><li><strong>Notifications aren’t a durable delivery mechanism.</strong> A session that isn’t connected and listening when the notification is sent never gets it. A worker restarting during a deploy will miss some. Keep a slow poll, every 30 seconds or so, as a safety net.</li><li><strong>LISTEN needs a dedicated session.</strong> It doesn’t work through PgBouncer in transaction pooling mode, because your next query may run on a different server connection. Give each worker one direct connection for listening. More on why in <a href="https://insight.jatinjainsaraf.com/connection-pooling-in-the-serverless-era-five-failure-modes">Connection Pooling in the Serverless Era: Five Failure Modes</a>.</li><li><strong>NOTIFY serializes part of the commit.</strong> A transaction that has sent a notification takes a global lock near the end of its commit, so transactions that call NOTIFY queue up behind each other at that step. Commits that don&#39;t notify aren&#39;t affected by this particular bottleneck. At low rates you&#39;ll never notice. If you enqueue thousands of jobs per second, send one notification per batch, not one per row.</li></ul><p>See the docs for <a href="https://www.postgresql.org/docs/current/sql-listen.html">LISTEN</a> and <a href="https://www.postgresql.org/docs/current/sql-notify.html">NOTIFY</a> for the exact delivery rules.</p><h3>The Feature No Queue Service Can Give You</h3><p>There’s one advantage that’s easy to miss: <strong>you can enqueue a job in the same transaction as the business data that caused it.</strong></p><pre>BEGIN;<br>INSERT INTO blocks (height, hash, ...) VALUES (...);<br>INSERT INTO jobs (kind, payload) VALUES (&#39;index_events&#39;, &#39;{&quot;height&quot;: 2000123}&#39;);<br>COMMIT;</pre><p>If the transaction commits, both rows become visible together. If it rolls back, neither is written. There’s no window where the block exists without its job.</p><p>With Redis or SQS you’d write to the database and then publish to the queue, and if the process dies between the two, you either lose the job or queue work for data that was never committed. That’s the dual-write problem. A transactional outbox solves it by recording the event in the same transaction as the business data and publishing it to another system afterwards. An outbox and a jobs table are built the same way, a row written atomically with your data, but they differ in purpose: an outbox holds events waiting to be delivered somewhere else, while a jobs table holds work your own workers execute. I covered the outbox in detail in <a href="https://insight.jatinjainsaraf.com/the-dual-write-problem-outbox-sagas-and-2pc">The Dual-Write Problem</a>.</p><h3>When You Really Do Need a Queue Service</h3><p>Postgres as a queue isn’t free and it isn’t unlimited. Move to something dedicated when:</p><ul><li><strong>Queue traffic starts hurting application traffic.</strong> Every claim, heartbeat, retry and status change is a write on your primary. Each one produces WAL and dead tuples, and each one competes with your application for CPU, disk, connections and vacuum. There’s no universal jobs-per-second number where Postgres stops being a good queue: a job that’s claimed once and completed once costs far less than one that heartbeats every 30 seconds and retries three times. Benchmark with your real payloads, batch sizes and heartbeat interval, and watch queue latency, application query latency and replication lag together.</li><li><strong>You need fan-out.</strong> One event consumed independently by five different services is pub/sub. Kafka, Redis Streams or SNS-to-SQS fit that shape. A jobs table doesn’t.</li><li><strong>You need a replayable log.</strong> If consumers have to re-read last Tuesday’s events, you want a retained log like Kafka, not a table you’re constantly deleting from.</li><li><strong>You need strict ordering.</strong> SKIP LOCKED hands out jobs roughly in order, but job 2 can finish before job 1. If order per key matters (all events for one account, in sequence), you have to design for it, and a partitioned log does that more naturally.</li><li><strong>The work crosses service boundaries.</strong> If the producer and consumer don’t share a database, a shared jobs table becomes a coupling you’ll regret.</li></ul><p>For an indexer backfill, background emails, webhook deliveries, report generation, or most of the jobs a typical backend runs, none of these apply.</p><p>If you do outgrow it, the System Design In-Depth modules on <a href="https://academy.jatinjainsaraf.com/system-design-in-depth/message-queues">Message Queues</a>, <a href="https://academy.jatinjainsaraf.com/system-design-in-depth/event-streaming-kafka">Event Streaming with Kafka</a> and <a href="https://academy.jatinjainsaraf.com/system-design-in-depth/background-jobs-and-scheduling">Background Jobs and Scheduling</a> cover what to move to, and <a href="https://academy.jatinjainsaraf.com/redis-in-depth/bullmq-internals">BullMQ Internals</a> shows how the most common Redis queue does the same claim-and-lease dance with Redis data structures.</p><h3>The Checklist</h3><ul><li>Claim jobs with FOR UPDATE SKIP LOCKED, ordered by run_at, id.</li><li>Commit straight after claiming. Never hold a transaction open for the whole job.</li><li>Use leases with a fresh claim token per claim, and check the token on every heartbeat and completion.</li><li>Retry with backoff and jitter, and cap the attempts.</li><li>Make the job’s side effects idempotent. The queue is at-least-once.</li><li>Index the claim query with a matching partial index.</li><li>Keep finished jobs out of the hot table and tune autovacuum from observed churn.</li><li>Watch the oldest pending job’s age, expired leases and dead tuples.</li><li>Keep a polling fallback when you use LISTEN and NOTIFY.</li><li>Benchmark before you move to a dedicated queue.</li></ul><h3>The Takeaway</h3><p>A queue is a table where many readers each need to take a different row. PostgreSQL ships with the exact primitive for that. FOR UPDATE SKIP LOCKED lets parallel workers claim different jobs without waiting on each other. Leases with claim tokens take care of crashed and stalled workers. A partial index plus vacuum settings tuned to the table keep the claim fast. And transactional enqueue gives you a guarantee that an external queue can&#39;t.</p><p>Start with the database you already run. Add a queue service when you can name the limit you hit, not before.</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/postgres-skip-locked-job-queue"><em>https://insight.jatinjainsaraf.com</em></a><em> on October 22, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f4744325afe4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Dual-Write Problem: Why “Update the DB, Then Publish an Event” Is Broken]]></title>
            <link>https://medium.com/@jatinjainsaraf/the-dual-write-problem-why-update-the-db-then-publish-an-event-is-broken-7ec5ca89fdfb?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/7ec5ca89fdfb</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[systems-thinking]]></category>
            <category><![CDATA[design-systems]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Sat, 29 Aug 2026 03:29:04 GMT</pubDate>
            <atom:updated>2026-08-29T03:37:15.746Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*rLbJQVrxMsQdZdRk" /></figure><p>An order gets saved to Postgres. The next line of code publishes an “order created” event to a message broker, so the payments service can charge the customer, shipping can start prepping a label, and notifications can send a confirmation email. That’s the whole design. It works in every test, in staging, and in production, for months.</p><p>Then one day the process gets killed between those two lines. Deploy rolled a pod mid-request. The broker connection timed out for four seconds during a network blip. A GC pause pushed the request past a load balancer timeout and the client retried against a different instance while the first one was still finishing up. The order is sitting in the database, fully committed, real, billable. Nobody downstream ever hears about it. No exception was thrown. No alert fired. The system looks healthy. A customer just placed an order that will never ship.</p><h3>Why You Can’t Just Wrap It in a Transaction</h3><p>The instinct is to reach for a transaction: begin, insert the order, publish the event, commit. That doesn’t work, and it’s worth being precise about why, because the reason is architectural, not a missing try/catch.</p><p>A database transaction gives you atomicity over one resource: the database. Postgres can guarantee the order row either exists or doesn’t, with nothing in between visible to other readers. A message broker (Kafka, RabbitMQ, SQS) is a <em>separate system</em> with its own commit protocol, its own durability guarantees, and no shared transaction coordinator with your database. There’s no operation that atomically says “commit this Postgres row and this Kafka message together, or neither.” You are making two independent network calls to two independent systems, and between them there is always a window where one has succeeded and the other hasn’t.</p><p>Move the event publish before the database commit and you get the opposite failure: the event goes out, downstream services start acting on an order that doesn’t exist yet, and then the database write fails (constraint violation, connection drop, whatever) and now payments has charged a card for an order that was never actually created. Order doesn’t matter. Two independent writes to two independent systems, with no atomicity across the boundary, will eventually diverge no matter which one goes first. This is the dual-write problem, and it’s not a bug in your code, it’s a gap in what a database transaction can promise you.</p><p><strong>Why this matters more than it looks like it should:</strong> this isn’t a rare edge case you can accept as background noise. Every deploy is a process restart. Every autoscaling event is new pods coming up while old ones drain mid-request. Every network blip between your service and your broker is a window for this to happen. At low traffic it might occur once a month and get written off as “weird, must’ve been a fluke.” At real scale, with thousands of writes a minute, this gap fires constantly, and it fires exactly during the conditions you’re least equipped to notice it: deploys and incidents, when your attention is already somewhere else.</p><h3>Two-Phase Commit: The Theoretically Correct Answer Nobody Uses</h3><p>The textbook fix for “atomically commit across two systems” is Two-Phase Commit (2PC). A coordinator asks every participant (the database, the broker) to prepare, meaning “lock this resource and confirm you <em>can</em> commit, but don’t commit yet.” Once every participant says yes, the coordinator tells everyone to commit for real. If any participant says no, the coordinator tells everyone to roll back.</p><p>It’s a real protocol with a real correctness proof, and it’s almost never what production systems actually use for this problem, for reasons that show up the moment you operate it instead of just reading about it:</p><ul><li><strong>It’s blocking.</strong> Once a participant says “yes, I can commit,” it has to hold that lock until the coordinator’s final decision arrives. If the coordinator crashes after collecting votes but before sending the commit decision, every participant is stuck holding locks indefinitely, waiting for a coordinator that might not come back.</li><li><strong>Most message brokers don’t implement the participant side of the protocol at all.</strong> Kafka has no native 2PC participant role. You’d be building and maintaining that coordination layer yourself, on top of a system that was never designed to expose it.</li><li><strong>It doesn’t survive network partitions gracefully.</strong> The entire protocol assumes the coordinator can eventually reach every participant. A partition during the decision phase leaves things in exactly the indeterminate, locked state the protocol was supposed to prevent.</li></ul><p>2PC is the right answer to a narrower question: coordinating multiple <em>databases</em> that all speak a compatible protocol, in a controlled environment where you own the failure modes. It’s the wrong tool for “coordinate my database with my message broker,” which is the shape of the dual-write problem almost everyone actually hits.</p><h3>The Outbox Pattern: Make the Second Write Boring</h3><p>The pattern that actually gets used starts from a reframe: stop trying to make two different systems commit atomically, and instead make the <em>second write</em> something the <em>same</em> database transaction can own.</p><p>Instead of publishing to the broker directly, you write the event as a row in an outbox table, in the exact same transaction as the order insert:</p><pre>BEGIN;<br>INSERT INTO orders (id, customer_id, total_cents, status)<br>  VALUES (&#39;ord_123&#39;, &#39;cust_456&#39;, 4999, &#39;created&#39;);<br>INSERT INTO outbox (id, aggregate_id, event_type, payload, created_at)<br>  VALUES (gen_random_uuid(), &#39;ord_123&#39;, &#39;order.created&#39;, &#39;{&quot;order_id&quot;:&quot;ord_123&quot;,&quot;total_cents&quot;:4999}&#39;, now());<br>COMMIT;</pre><p>Now atomicity is trivial, because both writes are ordinary rows in the database you already have transactional guarantees for. Either both rows exist or neither does. There is no window where the order exists but its event doesn’t, because “the event exists” now just means “a row in the same table transaction says so.”</p><p>A separate relay process, running continuously, polls the outbox table for unpublished rows (or reads Postgres’s write-ahead log directly via logical replication, which is how tools like Debezium do it without polling), publishes each one to the real message broker, and marks it published. If the relay crashes, it resumes from the last unpublished row on restart. If the broker is briefly unavailable, the events just sit in the table until it recovers. The order write itself never blocks on the broker being up at all.</p><p>The trade-off is honest, not hidden: downstream consumers now see events after a short delay (however often the relay polls, typically sub-second to a few seconds), and they can receive the same event more than once if the relay publishes successfully but crashes before marking the row as sent. That second part is why every consumer of these events has to be idempotent, not because it’s good hygiene in the abstract, but because at-least-once delivery is the actual guarantee the outbox gives you, and “exactly once” isn’t achievable without it.</p><h3>Sagas: The Same Problem, Stretched Across Multiple Services</h3><p>The outbox pattern solves atomicity for one write plus one event. A saga is what you reach for when the operation itself spans multiple services, each with its own local database, and there’s no way to wrap the whole thing in a transaction even in principle: reserve inventory, charge the payment, create the shipment. Three services, three databases, one logical operation.</p><p>A saga runs this as a sequence of local transactions, each one committing on its own, with a <strong>compensating action</strong> defined for each step in case a later step fails:</p><ol><li>Inventory service reserves the item. Commits locally.</li><li>Payment service charges the card. Commits locally.</li><li>Shipping service creates the shipment. If this fails…</li><li>…run the compensations in reverse: refund the payment, release the inventory reservation.</li></ol><p>Nothing here is atomic in the ACID sense. There’s a real window, between steps 1 and 3, where inventory is reserved and no shipment exists yet. A saga doesn’t hide that window, it makes it explicit and time-bounded, and gives you a defined path back to a consistent state if the last step doesn’t complete. That’s a fundamentally different consistency model than a database transaction (eventual, with defined compensations, instead of immediate and atomic), and pretending otherwise is where sagas go wrong in practice: teams build the happy path, skip writing the compensating actions because “that won’t really happen,” and then an incident forces someone to write the refund logic live, under pressure, for a case they never tested.</p><p>Each step publishing its “I’m done” or “I failed” signal is itself a dual-write problem at a smaller scale, which is why saga implementations lean on the outbox pattern internally for each step, rather than being a separate mechanism from it. The two patterns compose: outbox solves atomic local write-plus-event, sagas solve the multi-step orchestration built on top of that primitive.</p><h3>What This Actually Demonstrates</h3><p>None of this is about picking the “advanced” pattern to look sophisticated. It’s about recognizing that “write to the database, then tell everyone else” is not one operation, it’s two, and the honest response to that is either to make the second write ride inside the first one’s transaction (Outbox) or to make the multi-step version of that gap explicit and recoverable (Sagas) — not to assume the gap won’t matter until an incident proves otherwise.</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/the-dual-write-problem-outbox-sagas-and-2pc"><em>https://insight.jatinjainsaraf.com</em></a><em> on August 29, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7ec5ca89fdfb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Postgres and Cassandra Made Opposite Bets on Storage Engines]]></title>
            <link>https://medium.com/@jatinjainsaraf/why-postgres-and-cassandra-made-opposite-bets-on-storage-engines-8c80c0fc2a21?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c80c0fc2a21</guid>
            <category><![CDATA[backend-development]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[backend]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Sat, 15 Aug 2026 09:55:21 GMT</pubDate>
            <atom:updated>2026-08-15T10:00:27.543Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-m4bRLGa11bZfUZ2" /></figure><p>A write-heavy ingestion table starts slow on Postgres. Not immediately, but a few weeks in: inserts that used to take a millisecond now take ten, autovacuum is perpetually behind, and EXPLAIN ANALYZE shows time going into index maintenance nobody remembers configuring. The instinct is to blame the schema, or the hardware, or &quot;Postgres doesn&#39;t scale.&quot; None of those are quite right. The real answer is a decision Postgres made in the 1990s, long before this table existed: it keeps its indexes as B-Trees, and every one of them has to stay sorted, on every write.</p><h3>Every Write Has to Land Somewhere</h3><p>Strip a database down to its storage engine and the job is always the same: take a write, put it on disk in a shape that makes future reads fast, and don’t lose it. There are two dominant answers to how to do that, and most databases you’ve heard of lean on one.</p><p><strong>B-Trees</strong> keep some part of the data sorted on disk, in place, at all times. MySQL’s InnoDB and SQLite go all the way: the table itself is a B-Tree, keyed by its primary key or rowid, so the table and its main index are the same structure (a “clustered index”). Postgres is more layered: the table (the heap) is an unordered file with no sort order of its own, rows just go wherever the free space map says there’s room, but every index on that table, including the primary key, is a separate B-Tree that has to stay sorted and has to be updated on every write that touches an indexed column.</p><p><strong>LSM-Trees</strong>, log-structured merge trees (Cassandra, RocksDB, LevelDB, and CockroachDB’s Pebble storage engine), take the opposite approach everywhere: a write never touches its final sorted position immediately. It gets appended to whatever’s currently open, and getting everything back into sorted order is a job for later, done in the background, in bulk.</p><p>That fork, sorted-in-place versus sorted-later, explains almost every practical difference in how these two families of databases behave under load.</p><h3>B-Trees: Pay at Write Time, Save at Read Time</h3><p>Think of each B-Tree index as a filing cabinet that’s always perfectly alphabetized. Every entry goes directly into its correct folder, in its correct position, the moment it arrives. Finding anything later is fast and predictable, you walk straight to the folder. But filing it correctly in the first place means locating the right spot, possibly shifting other entries out of the way, and writing to a specific place on disk rather than just the next free spot.</p><p>Here’s what a Postgres insert actually does. The row itself is appended to the heap, close to a sequential write, wherever the free space map finds room. But every index on that table, the primary key, any unique constraint, any column you’ve indexed for lookups, is a B-Tree, and each one needs a new entry written to its correct leaf page, wherever that page happens to sit on disk. Two indexes on a table means one heap append plus two separate random writes, every single insert.</p><p>Updates are the more expensive case, and this is where MVCC changes the arithmetic. Postgres never overwrites a row in place: an UPDATE marks the old row version dead and writes an entirely new version elsewhere in the heap. That means an UPDATE costs everything an INSERT costs, a new heap entry, plus a new entry in every index, plus it leaves a dead tuple behind that autovacuum eventually has to clean up.</p><p><strong>Why this shows up in production:</strong> early in a table’s life, its hot pages and index pages fit comfortably in shared_buffers, so those &quot;random&quot; writes are really just writes to RAM, flushed to disk lazily and cheaply. Once the table and its indexes outgrow memory, every insert or update has a real chance of touching an index page that isn&#39;t cached, turning a cheap write into a disk seek, on every indexed column, on every write. Add autovacuum needing to revisit the dead tuples MVCC leaves scattered across heap pages, and you get the exact symptom this post opened with: a table that used to be fast getting steadily slower with no code change, just growth, and just more indexes to maintain per write.</p><h3>LSM-Trees: Write First, Sort Later</h3><p>An LSM-Tree handles the same problem by refusing to do that work up front. Picture an inbox tray instead of a filing cabinet: every new entry just gets dropped on top of the pile. Filing is instant, because there’s no filing, you’re not finding anything, you’re just adding to a stack. Periodically, someone takes the whole pile, sorts it properly, and merges it into the already-sorted sections below it.</p><p>Mechanically: writes go into an in-memory structure (a memtable) and are also logged to a commit log for durability, both sequential operations. When the memtable fills up, it’s flushed to disk as an immutable sorted file (an SSTable). Over time you accumulate many of these sorted files, and a background process called <strong>compaction</strong> merges them together, discarding values that have been overwritten or deleted along the way.</p><p>Deletes are handled the same indirect way updates are avoided: a delete doesn’t remove anything immediately, it writes a <strong>tombstone</strong>, a marker saying “this key is deleted as of this timestamp.” The actual old data only disappears once compaction physically rewrites the SSTables that contained it.</p><p><strong>Why this shows up in production:</strong> writes are cheap and consistently fast, because they’re always sequential appends, regardless of how big the dataset gets or where the “right place” for the data would eventually be. That’s why Cassandra-style databases are the default reach for write-heavy workloads: time-series ingestion, event logs, metrics pipelines. The cost is deferred, not eliminated. A read might have to check the memtable and several SSTables before it can be sure it has the latest version of a key, that’s <strong>read amplification</strong>, and it’s the mirror image of the index-maintenance cost a B-Tree pays on every write. Compaction itself is a real, ongoing background cost too: it consumes disk I/O and CPU that has to be budgeted for, and if compaction ever falls behind sustained write load, both read latency and disk usage climb, in a way that looks a lot like Postgres autovacuum falling behind, just triggered by the opposite kind of pressure.</p><h3>“Just Switch to Cassandra” Isn’t the Fix It Sounds Like</h3><p>It’s tempting to read the above and conclude LSM-Trees are strictly better for anything write-heavy. They’re not, they’re differently shaped, with their own failure modes:</p><p>Migrating a workload from Postgres to Cassandra to solve a write-scaling problem, without checking whether the workload also needs the guarantees Postgres was giving up in the trade, is how you end up rebuilding transactional consistency by hand in application code, which is a worse position than the one you started in.</p><h3>When Each Model Actually Wins</h3><p>The honest framework isn’t “which database is better,” it’s “which cost am I willing to pay”:</p><p>Postgres isn’t losing to Cassandra when a table gets slow under heavy writes. It’s honoring the trade it made on day one: keep every index sorted and correct on every write, so reads stay cheap and consistent forever. Whether that’s still the right trade for a specific table, and how many indexes it really needs, is a workload question, not a verdict on the database.</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/why-postgres-and-cassandra-made-opposite-bets-on-storage-engines"><em>https://insight.jatinjainsaraf.com</em></a><em> on August 15, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c80c0fc2a21" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PostgreSQL 19 vs. 18: What Actually Changed, Feature by Feature]]></title>
            <link>https://medium.com/@jatinjainsaraf/postgresql-19-vs-18-what-actually-changed-feature-by-feature-bae7bb794a0d?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/bae7bb794a0d</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Mon, 27 Jul 2026 15:24:05 GMT</pubDate>
            <atom:updated>2026-07-27T15:52:38.255Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*X_E6YTvsAl0QQXbQ" /></figure><p>Every major PostgreSQL release gets the same LinkedIn headline treatment: “this changes everything.” Most of the time it doesn’t, it’s a dozen genuine improvements wrapped in the language of a paradigm shift. PostgreSQL 19 is a real release with real substance, but the honest way to evaluate it isn’t the headline feature, it’s asking what specifically didn’t work in PG18 that now does, and what the actual cost of adopting it is.</p><h3>The Shape of the Two Releases</h3><p>PostgreSQL 18 was primarily an <strong>I/O and observability</strong> release: the Asynchronous I/O subsystem rewired how the engine reads from disk, pg_stat_io got a near-total overhaul, and B-Tree skip scans fixed a decade-old multicolumn-index limitation. PostgreSQL 19 is primarily a <strong>DDL/DML ergonomics and operational-maintenance</strong> release: the headline feature (SQL/PGQ) is a new query language surface, but the features that will actually change your on-call life are REPACK CONCURRENTLY, native partition reshaping, and parallel autovacuum.</p><p>That distinction matters because it tells you where to look for value. If PG18 already fixed your I/O-bound scan performance, PG19 isn’t going to double it again, it’s going to fix the maintenance operations that PG18 left untouched.</p><h3>1. SQL/PGQ: Property Graph Queries</h3><p><strong>What it is:</strong> PostgreSQL 19 adds SQL/PGQ, the SQL:2023 standard for querying graph-shaped data. You define a <strong>property graph</strong> as a read-only view over existing relational tables (a node table, an edge table), then query it with MATCH and Neo4j-style arrow syntax: MATCH (a:Employee)-[:MANAGES]-&gt;(b:Employee). Internally, PostgreSQL rewrites the arrow syntax into ordinary joins against your existing tables before the planner ever runs, so it inherits your existing indexes and your existing row-level security automatically.</p><p><strong>Comparison (PG18 vs. PG19):</strong> In PG18, and every version before it, modeling a graph relationship (a dependency tree, an org chart, an authorization graph, “who reports to whom”) meant writing a recursive CTE, WITH RECURSIVE, walking a self-referencing table one level at a time. Recursive CTEs work, but they&#39;re procedural: you write the recursion, you write the termination condition, and the planner frequently struggles to estimate the cost of a deep or unbounded traversal, which is exactly the kind of query that &quot;chokes the planner&quot; that gets complained about on social media. There was no declarative way to say &quot;find all paths matching this pattern&quot; - you had to hand-build the loop yourself in SQL.</p><p><strong>Pros:</strong></p><ul><li>No secondary graph database, no sync pipeline. If your graph queries are shallow-to-medium depth over data that’s already relational, you get graph-shaped querying without standing up Neo4j and building an ETL job to keep it current.</li><li>Declarative pattern matching gives the planner a clearer picture of intent than a hand-written recursive CTE, which can lead to better plans for the same logical query.</li><li>Inherits your existing security model for free, since a property graph is “just a view.”</li></ul><p><strong>Cons:</strong></p><ul><li>It’s still running joins under the hood. You do <strong>not</strong> get index-free adjacency, the property that makes a native graph database like Neo4j fast for deep, multi-hop traversal (5+ hops over millions of edges). If your actual workload is that kind of traversal, SQL/PGQ won’t rescue you from needing a dedicated graph store.</li><li>New syntax surface means new things to learn and new things the planner can misjudge; it’s not yet battle-tested the way recursive CTEs are, having existed for two decades.</li><li>The realistic audience for this feature is “developers who currently reach for a recursive CTE for shallow hierarchical queries,” not “teams running production graph analytics at Neo4j scale.” Know which one you are before switching.</li></ul><h3>2. REPACK: Unifying and Unlocking VACUUM FULL / CLUSTER</h3><p><strong>What it is:</strong> PostgreSQL 19 introduces a single REPACK command that replaces both VACUUM FULL (rewrite the table to reclaim bloat) and CLUSTER (rewrite the table in index order). The critical addition is a CONCURRENTLY option - REPACK ... CONCURRENTLY - that rebuilds the table <strong>without</strong> taking the ACCESS EXCLUSIVE lock that made both of the old commands unusable on a live, high-traffic table.</p><p><strong>Comparison (PG18 vs. PG19):</strong> In PG18, if a table had accumulated enough bloat (dead row versions from updates/deletes that VACUUM alone couldn&#39;t reclaim) that you needed to physically shrink it, your only in-core option was VACUUM FULL, which locks the table exclusively for the entire rewrite. On a table serving live traffic, that&#39;s not a maintenance task, it&#39;s a scheduled outage. The workaround was the third-party pg_repack extension, which achieves a similar result without full locking by building a shadow copy and swapping it in, but that means installing and trusting an external extension for something this fundamental.</p><p><strong>Pros:</strong></p><ul><li>One command name instead of two (VACUUM FULL and CLUSTER remain for backward compatibility, but REPACK is now the unified entry point).</li><li>Removes a dependency on a third-party extension for a core maintenance operation.</li></ul><p><strong>Cons:</strong></p><ul><li>CONCURRENTLY avoiding the exclusive lock doesn&#39;t mean it&#39;s free, it still consumes I/O and CPU rewriting the table, and the new max_repack_replication_slots variable exists because concurrent repack has its own resource considerations to tune.</li><li>If your team already has pg_repack (the extension) working reliably, migrating to core REPACK CONCURRENTLY is a &quot;nice to have simplify,&quot; not an urgent fix, don&#39;t rip out something that already works without testing the native replacement first.</li></ul><h3>3. Native Partition Merge/Split</h3><p><strong>What it is:</strong> ALTER TABLE ... MERGE PARTITIONS and ALTER TABLE ... SPLIT PARTITIONS, letting you reshape an existing partitioned table&#39;s partition boundaries natively.</p><p><strong>Comparison (PG18 vs. PG19):</strong> PG18 invested heavily in partition <em>query performance</em>, more efficient planning over many partitions, better partitionwise joins, reduced memory use for partition pruning, but did nothing for partition <em>maintenance</em>. If a monthly partition grew too large and you wanted to split it into two, or several small partitions had accumulated and you wanted to merge them, there was no native command. You did it by hand: create new partition(s), migrate the relevant rows with INSERT ... SELECT, detach and drop the old partition, all while carefully managing locks and application downtime windows.</p><p><strong>Pros:</strong></p><ul><li>Turns a multi-step, error-prone, DBA-scripted operation into a single DDL statement.</li><li>Makes it realistic to actually right-size partitions over time as data volume and access patterns change, rather than living with whatever partition scheme you picked at design time.</li><li>Complements PG18’s partition query-planning work: PG18 made partitioned tables fast to <em>query</em>, PG19 makes them practical to <em>maintain</em>.</li></ul><p><strong>Cons:</strong></p><ul><li>Any partition reshape on a large table still means physically moving rows, this is not a free metadata-only operation, plan for I/O and lock impact accordingly.</li><li>Doesn’t retroactively fix a bad initial partitioning key choice, it makes <em>boundary</em> changes easier, not a full re-partitioning strategy change.</li></ul><h3>4. GROUP BY ALL</h3><p><strong>What it is:</strong> New SELECT syntax, GROUP BY ALL, which automatically groups by every column in the SELECT list that isn&#39;t an aggregate or window function, no manual enumeration required.</p><p><strong>Comparison (PG18 vs. PG19):</strong> PG18 addressed a related but distinct problem at the planner level: it learned to <em>ignore</em> GROUP BY columns that were functionally dependent on other grouped columns (for example, if you group by a table&#39;s unique primary key, other same-table columns don&#39;t logically need to be listed, and PG18&#39;s planner recognized this and dropped them from the actual grouping operation for efficiency). That&#39;s an internal cost optimization, it didn&#39;t change what you had to <em>type</em>. In PG18 you still hand-wrote every column in the GROUP BY clause, no matter how wide the SELECT list.</p><p><strong>Pros:</strong></p><ul><li>Removes the tedious, error-prone task of keeping a long GROUP BY list in sync with the SELECT list, adding a column to one and forgetting the other is a classic source of &quot;column must appear in GROUP BY&quot; errors.</li><li>Particularly valuable for wide analytical queries with 10+ grouping dimensions.</li></ul><p><strong>Cons:</strong></p><ul><li>Implicit grouping means it’s slightly easier to accidentally group by more (or fewer) columns than you intended if the SELECT list changes later, explicit lists are more self-documenting for complex queries. Use with intention, not as a default habit for every query.</li></ul><h3>5. FOR PORTION OF: Temporal UPDATE/DELETE</h3><p><strong>What it is:</strong> New clause for UPDATE and DELETE, FOR PORTION OF &lt;period&gt; FROM &lt;start&gt; TO &lt;end&gt;, that lets you modify or delete just a sub-range of a temporal (range-based) row, automatically splitting the surrounding range as needed.</p><p><strong>Comparison (PG18 vs. PG19):</strong> PG18 introduced the <em>constraint</em> half of temporal tables: WITHOUT OVERLAPS for PRIMARY KEY/ UNIQUE and PERIOD for foreign keys, letting you enforce that time ranges in a table never overlap. But it gave you no corresponding <em>operation</em> half. If you had a row valid from Jan-Dec and needed to change just the March-June portion, you had to manually delete the original row and insert two (or three) new rows representing the split ranges yourself, exactly the kind of fiddly, off-by-one-prone logic a database feature should be doing for you.</p><p><strong>Pros:</strong></p><ul><li>Closes the gap PG18 left half-finished: PG18 gave you guardrails (non-overlapping constraints), PG19 gives you the verbs (safely editing a slice of history without hand-rolling the split).</li><li>Meaningful for any system tracking effective-dated data, pricing history, contract terms, HR assignment periods, insurance coverage windows.</li></ul><p><strong>Cons:</strong></p><ul><li>Temporal tables remain a niche feature relative to PostgreSQL’s overall audience, most applications don’t model data this way, and adopting WITHOUT OVERLAPS + FOR PORTION OF is a genuine schema-design commitment, not a drop-in swap.</li></ul><h3>6. INSERT ... ON CONFLICT DO SELECT ... RETURNING</h3><p><strong>What it is:</strong> Extends ON CONFLICT beyond DO NOTHING/ DO UPDATE with a new DO SELECT ... RETURNING option, returning (and optionally locking, via FOR UPDATE/ FOR SHARE) the row that caused the conflict, without modifying it.</p><p><strong>Comparison (PG18 vs. PG19):</strong> PG18 made real progress on returning row state, adding OLD/ NEW alias support to RETURNING across INSERT/ UPDATE/ DELETE/ MERGE, so you could see before-and-after values in a single statement. But ON CONFLICT itself was unchanged: still only DO NOTHING (silently skip, tell you nothing about the existing row) or DO UPDATE (you must actually modify something to get a RETURNING result). The common &quot;get-or-create&quot; pattern, insert a row if it doesn&#39;t exist, otherwise just give me the existing one, had no clean native expression; teams worked around it with a no-op DO UPDATE SET col = col just to trigger a RETURNING clause.</p><p><strong>Pros:</strong></p><ul><li>Directly solves get-or-create without a no-op write, which matters for both clarity and for avoiding unnecessary row versions from a fake update (fewer wasted row versions means less bloat, which loops back to why REPACK CONCURRENTLY matters less often).</li><li>Optional row locking (FOR UPDATE/FOR SHARE) on the conflicting row means you can safely read-then-act on it within the same statement, useful for concurrent upsert-adjacent logic.</li></ul><p><strong>Cons:</strong></p><ul><li>Another ON CONFLICT branch to learn and to get right in mixed application code that already juggles DO NOTHING and DO UPDATE logic; worth auditing existing upsert helper functions to see where this actually simplifies things versus where it&#39;s unnecessary.</li></ul><h3>7. TOAST Default Compression: pglz → lz4</h3><p><strong>What it is:</strong> The default compression algorithm for TOASTed (large, out-of-line) values changes from pglz to lz4.</p><p><strong>Comparison (PG18 vs. PG19):</strong> lz4 TOAST compression already existed as an option in PG18 (and earlier), you could set default_toast_compression = lz4 or specify it per-column. But almost nobody did, because defaults are what most schemas actually run with. PG18 shipped with pglz as the out-of-the-box default; PG19 flips that default.</p><p><strong>Pros:</strong></p><ul><li>A genuinely free win for anyone storing large JSONB, text, or bytea values who never manually tuned this setting, lz4 compresses and decompresses meaningfully faster than pglz at a comparable ratio.</li><li>Zero migration effort for new databases; the new default just applies.</li></ul><p><strong>Cons:</strong></p><ul><li>Existing tables don’t retroactively recompress, this only affects newly TOASTed values going forward (or values rewritten via REPACK/VACUUM FULL), so an existing large database won&#39;t see the benefit until data is naturally rewritten or explicitly reprocessed.</li></ul><h3>8. Parallel Autovacuum Workers</h3><p><strong>What it is:</strong> A single autovacuum job on one table can now use multiple parallel workers, controlled globally by autovacuum_max_parallel_workers and per-table by the autovacuum_parallel_workers storage parameter. PG19 also adds a scoring system ( autovacuum_vacuum_score_weight, autovacuum_freeze_score_weight, and related variables) to decide which tables get processed first, replacing a simpler threshold-only heuristic.</p><p><strong>Comparison (PG18 vs. PG19):</strong> PG18 improved autovacuum’s <em>behavior</em> significantly: “eager freezing” let normal vacuums freeze all-visible pages proactively (reducing the cost of a later full freeze), autovacuum_worker_slots let you raise the effective worker cap at runtime without a restart, and autovacuum_vacuum_max_threshold let you set a fixed dead-tuple trigger point instead of relying purely on percentages. What PG18 didn&#39;t change: <strong>each individual vacuum job on a given table still ran as one single worker process</strong>. On a genuinely huge, high-churn table, that one worker was the throughput ceiling, no matter how many total autovacuum worker slots your cluster had available.</p><p><strong>Pros:</strong></p><ul><li>Directly attacks the specific case that bites teams running very large, high-write tables: a vacuum job that used to take hours on one worker can now split the work across several, on the same table, at the same time.</li><li>The new scoring system for processing order is a real improvement over pure threshold checks, better prioritizing which of many candidate tables actually needs attention most urgently.</li><li>Complements, rather than duplicates, PG18’s improvements, PG18 made each vacuum pass smarter and more proactive; PG19 makes the biggest passes faster by parallelizing them.</li></ul><p><strong>Cons:</strong></p><ul><li>More parallel workers means more concurrent I/O and CPU contention during vacuum; on a system already tight on resources, this needs the same careful tuning any parallelism feature does, it’s not automatically a net win without headroom to spend.</li><li>Per-table autovacuum_parallel_workers is one more tuning knob DBAs now need to understand and set deliberately for their biggest tables, rather than relying entirely on cluster-wide defaults.</li></ul><h3>9. Logical Replication: Native Sequence Synchronization</h3><p><strong>What it is:</strong> Sequences can now be included in logical replication. CREATE/ ALTER PUBLICATION ... ALL SEQUENCES publishes all sequences, and ALTER SUBSCRIPTION ... REFRESH SEQUENCES syncs sequence <em>values</em> (not just existence) on the subscriber to match the publisher. pg_get_sequence_data() lets you inspect sync state directly.</p><p><strong>Comparison (PG18 vs. PG19):</strong> PG18 made solid logical replication improvements, generated column values could finally be replicated, and the default streaming mode for new subscriptions switched from off to parallel for better apply performance. But sequences were entirely untouched by logical replication in PG18 and every prior version: a subscriber&#39;s sequences had no relationship to the publisher&#39;s. If you promoted a logically-replicated subscriber to primary during a failover, its sequences would still be wherever they last were on that subscriber, not caught up to the publisher&#39;s actual nextval() position, unless you manually reset them yourself before cutting traffic over.</p><p><strong>Pros:</strong></p><ul><li>Removes a genuinely dangerous, well-known logical-replication gap: without this, a poorly-timed failover onto a logically-replicated subscriber can hand out primary-key values that collide with rows the old primary already committed, a duplicate-ID bug hiding specifically in your disaster-recovery path, the worst possible place for a bug to hide since it only shows up when you’re already in an incident.</li><li>pg_get_sequence_data() gives visibility into sync state that simply didn&#39;t exist before, useful for confirming a subscriber is actually safe to promote.</li></ul><p><strong>Cons:</strong></p><ul><li>This is a correctness fix for a previously-silent gap, not a performance feature, teams need to actively adopt ALL SEQUENCES and REFRESH SEQUENCES in their subscription setup; it isn&#39;t retroactively applied to existing subscriptions without action.</li></ul><h3>Which of These Actually Change How You Operate Postgres</h3><p>Ranking by realistic production impact, not headline appeal:</p><ol><li><strong>REPACK CONCURRENTLY</strong> - removes a hard operational constraint (mandatory downtime for bloat reclaim) that has existed since the beginning of Postgres.</li><li><strong>Parallel autovacuum</strong> — directly extends throughput on the exact tables where autovacuum has historically fallen behind.</li><li><strong>Logical replication sequence sync</strong> — closes a real correctness gap sitting specifically in failover paths.</li><li><strong>Partition merge/split</strong> — makes partition schemes maintainable as data grows, rather than fixed at design time.</li><li><strong>SQL/PGQ</strong> — genuinely useful for a specific class of query (shallow hierarchical/relationship modeling), not a universal graph-database replacement, know which camp you’re in before treating it as a headline reason to upgrade.</li><li>Everything else (GROUP BY ALL, FOR PORTION OF, ON CONFLICT DO SELECT, TOAST lz4 default) - real, welcome ergonomics and default-quality improvements, but incremental rather than architectural.</li></ol><h3>Where This Fits</h3><p>If you’re running the <a href="https://academy.jatinjainsaraf.com/postgresql-in-depth">PostgreSQL In-Depth course</a>, the maintenance-and-bloat modules built around pg_repack and manual VACUUM FULL tradeoffs are the direct prerequisite for understanding why REPACK CONCURRENTLY matters as much as it does, and the partitioning phase is the natural place to slot in the new merge/split DDL. For the deep architectural picture PG19 builds on top of (process model, MVCC, the planner, WAL), see <a href="https://insight.jatinjainsaraf.com/the-postgresql-elephant-in-the-room-a-deep-dive-into-the-architecture-that-powers-giants">&quot;The PostgreSQL Elephant in the Room.&quot;</a></p><p>PostgreSQL 19 isn’t a rewrite of what Postgres is, it’s the same server process, managing files intelligently, that <a href="https://insight.jatinjainsaraf.com/what-the-postgresql-server-is-actually-doing">“What the PostgreSQL Server Is Actually Doing”</a> describes, just with more of the maintenance and ergonomic rough edges sanded down. That’s not a smaller story than “Postgres killed the recursive CTE”, it’s a more honest one.</p><p>#PostgreSQL #Database #Backend #SoftwareEngineering #DatabaseInternals #SQL #DevOps</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/postgresql-19-vs-18-what-actually-changed-feature-by-feature"><em>https://insight.jatinjainsaraf.com</em></a><em> on July 27, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bae7bb794a0d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How the LRU Cache Actually Works and Why Redis, Browsers, and Postgres All Approximate It]]></title>
            <link>https://medium.com/@jatinjainsaraf/how-the-lru-cache-actually-works-and-why-redis-browsers-and-postgres-all-approximate-it-d5e281f93a2a?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/d5e281f93a2a</guid>
            <category><![CDATA[cache]]></category>
            <category><![CDATA[lru-cache]]></category>
            <category><![CDATA[systems-thinking]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Tue, 21 Jul 2026 19:19:48 GMT</pubDate>
            <atom:updated>2026-07-21T19:21:26.738Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6vQl1IJ_iDXq06Ic" /></figure><p>You’ve felt this before: you restart a service, or open your laptop after the weekend, and everything is sluggish for the first few minutes. Every page load feels like it’s dragging. Then, without you doing anything, it speeds back up. Nothing changed in your code. What changed is that the cache went cold, and it just warmed back up.</p><p>Almost every system that makes this speed-up possible Redis, your browser, your database, even a function that “remembers” its last few results is solving the exact same problem underneath: it has limited room, so when that room runs out, it has to decide what to throw away first. The answer nearly all of them land on is the same one: throw away whatever hasn’t been touched in the longest time. That policy has a name Least Recently Used, or LRU and the way it’s actually built is one of the more satisfying “two weak pieces make one strong piece” stories in software.</p><h3>The problem neither obvious answer solves</h3><p>Say you want to build this yourself: a fixed-size cache that instantly tells you if something is stored, and instantly evicts the “stalest” entry the moment you’re full.</p><p>Your first instinct might be a plain lookup table a dictionary, a hash map, whatever your language calls it. That gives you instant answers to “is this here?” But a lookup table has no concept of time. It doesn’t know that key A was touched ten seconds ago and key B was touched ten minutes ago. To find the stalest entry, you’d have to check everything slow, and it gets slower as the cache grows.</p><p>So your second instinct might be a simple ordered list keep every entry in a line, most recently used at the front. That solves the ordering problem. But now finding a specific entry means walking the line from the front until you happen to find it. And worse, once you find it, moving it to the front means pulling it out of the middle of the line which, in a plain list, means shifting everything around it.</p><p>Neither piece alone works. One gives you instant lookup with no memory of time. The other gives you a sense of time with no instant lookup.</p><h3>The fix: don’t choose, combine</h3><p>The actual solution used almost everywhere is to stop treating “look something up” and “track its age” as the same job, and instead let one structure handle each, wired together.</p><p>Picture a line of people, ordered by who was served most recently front of the line is “just here,” back of the line is “hasn’t been seen in ages.” That’s your time-ordering structure, and because it’s a <em>doubly linked</em> line (each person knows who’s directly in front of and behind them), pulling anyone out of the middle and moving them to the front is instant you’re just relinking a few neighbors, not shuffling the whole line.</p><p>Now add a cashier holding a notebook that maps every person’s name directly to their exact spot in that line. You don’t scan the line to find someone you check the notebook, and it points you straight at them. That notebook is your lookup table, except its entries don’t hold the person’s information directly they hold a pointer to where that person is standing.</p><p>Put those two together and something interesting happens: a lookup stops being “search the line” and becomes “check the notebook, then relink two spots in the line.” Both steps are instant, regardless of how many people are in line. The lookup table gives you the address; the line gives you a cheap way to reorder once you’re there. Neither piece is doing the other’s job they’re doing their own job, wired to the same underlying entries.</p><p>Eviction is the same trick pointed at the back of the line instead of the front: whoever is standing at the very back is, by definition, the stalest entry so when the cache is full, that’s who gets dropped, and the notebook forgets their name too.</p><h3>Why real systems don’t build this exactly</h3><p>Here’s the part worth sitting with: at genuinely large scale, most real caching systems <em>don’t</em> implement pure LRU, because keeping a perfectly ordered “line” updated on literally every single read becomes its own overhead once you’re handling millions of operations a second.</p><p>Redis’s LRU eviction, for instance, doesn’t maintain one global perfectly-ordered line at all it samples a handful of random keys and evicts whichever of <em>those</em> looks stalest, repeated as needed. It’s an approximation, and a good one, because it gets 90% of the benefit of true LRU for a fraction of the bookkeeping cost.</p><p>Your browser’s HTTP cache and CDN edge caches lean on the same core idea evict whatever’s gone longest untouched once storage fills up to decide what to drop when they hit their limits, which is exactly why re-visiting a page you loaded five minutes ago feels instant, but a page from three weeks ago fetches fresh.</p><p>Databases play the same game one layer down. A database’s in-memory buffer pool the pages of the table currently held in RAM instead of read from disk often uses something called clock-sweep instead of textbook LRU: cheaper to maintain, same underlying goal of keeping “hot” pages in memory and letting cold ones get evicted first.</p><p>And plenty of ordinary application code hits this without ever calling it LRU by name “cache the last 500 computed results, and once that limit is hit, drop whatever hasn’t been asked for in a while” is the identical problem, just at a size where the textbook version runs perfectly well with no approximation needed.</p><h3>The actual takeaway</h3><p>The mechanism is almost deceptively small once you see it: pair a lookup table with an ordered line, and let the table’s entries point directly into the line instead of duplicating what’s in it. That’s the whole trick. What’s genuinely interesting is how far that one idea travels from a toy interview problem, to the reason your database feels fast after warming up, to the reason revisiting a webpage is instant and revisiting an old one isn’t. You’ve probably relied on a system built this way today without ever knowing it had a name.</p><p><em>Originally published at </em><a href="https://jatinjainsaraf.com/blog/how-the-lru-cache-actually-works-and-why-redis-browsers-and-postgres-all-approximate-it"><em>https://insight.jatinjainsaraf.com</em></a><em> on July 21, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d5e281f93a2a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Redis HyperLogLog: Counting Millions of Unique Users with Just 12KB of Memory]]></title>
            <link>https://medium.com/@jatinjainsaraf/redis-hyperloglog-counting-millions-of-unique-users-with-just-12kb-of-memory-bdcee2621fb7?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/bdcee2621fb7</guid>
            <category><![CDATA[redis]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Wed, 15 Jul 2026 16:24:51 GMT</pubDate>
            <atom:updated>2026-07-15T16:24:51.371Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*AKYMKk6RyQG6q8Zw" /></figure><p>When building at scale, answering a seemingly simple question like <em>“How many unique users visited our site today?”</em> can become a massive engineering challenge.</p><p>If you use a traditional Set data structure to store every unique ID, tracking millions of users will rapidly consume Gigabytes of RAM. This is where <strong>Redis HyperLogLog</strong> comes to the rescue.</p><h3>What is HyperLogLog?</h3><p>HyperLogLog (HLL) is a probabilistic data structure used to estimate the cardinality of a set (the number of unique elements). Instead of storing the actual elements, it hashes them and observes the patterns of the binary representation of the hash.</p><p>The magic of Redis’ implementation of HyperLogLog is that it can estimate the cardinality of millions of unique items while <strong>always using a maximum of 12 KB of memory</strong> and maintaining a standard error of just 0.81%.</p><h3>How it works (The Math made simple)</h3><p>Imagine you are flipping a coin. If you flip it and get “Heads” 5 times in a row, you’d intuitively know you’ve probably been flipping that coin for a while.</p><p>HLL works on a similar principle using hashing:</p><ol><li>Every item added to the HLL is hashed into a large binary string (e.g., 101100101...).</li><li>The algorithm looks for the <strong>longest sequence of leading zeros</strong> in these binary strings.</li><li>If the longest sequence of leading zeros across all hashes is N, the estimated number of unique elements is roughly 2^N.</li></ol><p>To reduce variance and make the estimate incredibly accurate, Redis divides the data into 16,384 internal registers and averages the results using harmonic means. This brings the memory footprint to exactly 16384 × 6 bits = 12 KB.</p><h3>Practical Usage in Redis</h3><p>Using HLL in Redis is delightfully simple. It provides three main commands:</p><h3>1. PFADD</h3><p>Adds elements to the HyperLogLog.</p><pre>PFADD website_visitors:2026-06-06 &quot;user_102&quot; &quot;user_883&quot; &quot;user_911&quot;<br># Returns 1 if the internal register was altered</pre><h3>2. PFCOUNT</h3><p>Returns the approximated cardinality.</p><pre>PFCOUNT website_visitors:2026-06-06<br># Returns: 3</pre><h3>3. PFMERGE</h3><p>Merges multiple HyperLogLogs into a single one. This is perfect for rolling up daily metrics into weekly or monthly metrics!</p><pre>PFMERGE visitors:this_week visitors:monday visitors:tuesday visitors:wednesday</pre><h3>When to use HyperLogLog</h3><p>HyperLogLog is not a silver bullet. You should use it when:</p><ul><li>You need to count massive amounts of unique items (IP addresses, user IDs, search queries).</li><li>You care about memory efficiency more than absolute 100% precision.</li><li>You do <strong>not</strong> need to retrieve the actual items back from the data structure.</li></ul><p>If you need to list the users, or if absolute precision is required for billing purposes, stick to a standard Redis Set or an SQL database. But for analytical dashboards and scale, HyperLogLog is an absolute superpower.</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/redis-hyperloglog-counting-millions-of-unique-users-with-just-12kb-of-memory"><em>https://insight.jatinjainsaraf.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bdcee2621fb7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why a Single ALTER TABLE Can Take Down Your Whole Database]]></title>
            <link>https://medium.com/@jatinjainsaraf/why-a-single-alter-table-can-take-down-your-whole-database-d39934386af8?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/d39934386af8</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[dbms]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[nosql]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Wed, 15 Jul 2026 16:06:34 GMT</pubDate>
            <atom:updated>2026-07-15T16:21:01.995Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0JXtPaaAofNNBOwC" /></figure><p>A team runs a routine migration: ALTER TABLE transactions ADD COLUMN region TEXT. Nothing dramatic on paper. Twelve minutes later the connection pool is full, every request from every user is failing, and on-call is paged. The migration itself was never the problem. A lock queue was.</p><h3>A Lock Isn’t a Wall, It’s a Queue Ticket</h3><p>The word “lock” makes people picture a barrier: something is locked, everything else waits outside. That’s not quite how PostgreSQL locking works, and the difference is exactly what causes outages like this one.</p><p>A better picture is a queue ticket. Every statement that touches a table takes a ticket. Some tickets are compatible with each other, holders can be served side by side, no problem. Other tickets are not compatible, and a ticket holder has to wait for the incompatible one ahead of it to finish before it can be served.</p><p>Two plain SELECT statements are always compatible. They take the weakest kind of ticket there is, and a hundred of them can run at once without ever noticing each other. The trouble starts when someone joins the line holding the strongest possible ticket.</p><h3>The Strongest Ticket Blocks Everyone, Even the Compatible Ones</h3><p>PostgreSQL has a handful of table-level lock strengths, but only one really matters for this story. It’s called ACCESS EXCLUSIVE, and it&#39;s acquired by ALTER TABLE, DROP TABLE, and TRUNCATE. It conflicts with every other lock in the system, including a plain read.</p><p>Here’s the part that catches people off guard: PostgreSQL mostly serves lock requests in the order they arrive. So if an ALTER TABLE shows up and has to wait for one long-running SELECT to finish, it doesn&#39;t just wait quietly off to the side. It gets in line. And every SELECT that shows up after it also has to get in line, behind the ALTER TABLE, even though those new SELECT statements would have been perfectly happy running alongside the original one.</p><p>One slow reader plus one waiting migration is enough to freeze every future read on that table, until the slow reader finally lets go.</p><h3>How Twelve Minutes Becomes an Outage</h3><p>This is close to how it actually plays out in production. An analytics query kicks off against a busy table and, because nobody set a timeout on it, keeps running for twelve minutes. A few minutes in, someone ships a routine schema migration on that same table. The migration asks for its ACCESS EXCLUSIVE ticket and starts waiting on the analytics query.</p><p>From that moment, every new request the application sends to that table queues up behind the migration. Within half a minute, hundreds of connections are stuck waiting on a lock, not on any actual work. The connection pool, which was never designed to hold hundreds of idle-but-blocked connections, fills up completely. New requests can’t even get a connection to wait with. The site goes down, and the root cause line in the postmortem is almost funny in how small it is: one long read, one migration with no timeout.</p><h3>The Fix Is Two Settings, Not a Rewrite</h3><p>The defense here isn’t clever code, it’s two timeouts that should be set before any schema change ever touches a live table.</p><p>The first tells the migration itself to give up quickly if it can’t get its lock in a few seconds, rather than parking itself at the front of a queue that keeps growing. The second is a ceiling on how long any query is allowed to run at all, so a stray analytics query can never hold a lock for twelve minutes in the first place. Neither setting is exotic. Both are usually just missing.</p><p>Set correctly, the same migration either succeeds in under a second because nothing was in its way, or it fails immediately and cleanly so you can retry it a minute later. What you never want is the third option: waiting, silently, gathering a crowd behind it.</p><h3>Row Locks Are a Separate, Smaller Story</h3><p>Everything above is about locking the table’s structure. There’s a second, unrelated locking system for locking individual rows of data, and it solves a different problem: two transactions trying to change the same row at the same time.</p><p>The classic example is a balance check before a debit. Read the balance, confirm there’s enough money, then subtract the amount. Without a row lock, two concurrent withdrawals can both read the same starting balance and both proceed, and the account ends up wrong. Locking that one row for the duration of the transaction closes the gap.</p><p>There’s also a neat variant built for queues: instead of locking a row and making every other worker wait for it, a worker can ask to skip any row that’s already locked and grab the next free one instead. That’s the difference between ten background workers piling up behind each other and ten workers each picking up a different job at the same instant.</p><h3>Deadlocks: When Two Correct Transactions Still Collide</h3><p>Occasionally two transactions each hold a lock the other one needs. Transaction A has locked account 1 and wants account 2. Transaction B has locked account 2 and wants account 1. Neither can proceed, and neither will ever let go voluntarily. PostgreSQL notices this after about a second, picks one of the two transactions, and kills it so the other can continue.</p><p>The fix isn’t a database setting, it’s a coding discipline: always acquire locks in the same order, everywhere in the codebase. If every transfer function locks the lower account ID first regardless of direction, the two transactions above stop being able to collide at all. It’s the same trick as everyone in a crowded hallway agreeing to keep to the right.</p><h3>Locks With No Data Attached</h3><p>PostgreSQL also offers a kind of lock that has nothing to do with any table or row: an application-defined lock, identified by whatever number you choose. It’s mainly used for one thing, making sure a background job runs on only one server at a time, even when there are several application instances that could all try to start it. Grab the lock, run the job, and if the process crashes mid-job, the lock releases itself automatically instead of leaving things stuck. It’s a lighter, more reliable substitute for the Redis-based mutex a lot of teams reach for by default.</p><h3>The Takeaway</h3><p>A lock isn’t something you fight, it’s a ticket you’re standing in line with, and most locking outages don’t start with a bad lock. They start with someone who forgot to set a timeout and ended up holding the line for everyone behind them.</p><h3>Where This Fits in the Full Course</h3><p>This is Module 13 of the <a href="https://academy.jatinjainsaraf.com/postgresql-in-depth">PostgreSQL In-Depth course</a>, from the Architect phase covering the internals senior engineers eventually have to learn the hard way. If this scenario feels familiar, the earlier modules on <a href="https://academy.jatinjainsaraf.com/postgresql-in-depth/mvcc">MVCC</a> and transaction internals build the concurrency model this one depends on.</p><p>#PostgreSQL #Database #Backend #SQL #ProductionIncidents #SoftwareEngineering</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/why-a-single-alter-table-can-take-down-your-whole-database"><em>https://insight.jatinjainsaraf.com</em></a><em> on July 15, 2026.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d39934386af8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Your Blockchain Indexer Is Fine — Until It Isn’t]]></title>
            <link>https://medium.com/@jatinjainsaraf/your-blockchain-indexer-is-fine-until-it-isnt-d175efe8e3e8?source=rss-5de1872d828------2</link>
            <guid isPermaLink="false">https://medium.com/p/d175efe8e3e8</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[blockchainindexer]]></category>
            <category><![CDATA[blockchain]]></category>
            <dc:creator><![CDATA[Jatin Jain Saraf]]></dc:creator>
            <pubDate>Tue, 14 Jul 2026 15:22:19 GMT</pubDate>
            <atom:updated>2026-07-14T15:22:19.873Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hhmqa-X-LPWo32c8" /></figure><p>What nobody tells you about hitting 20–30 TB of indexed blockchain data, and why “just optimize it later” is the most expensive sentence in…</p><h3>Your Blockchain Indexer Is Fine — Until It Isn’t</h3><p>The first year of building a blockchain indexer is deceptively smooth.</p><p>Blocks arrive. Transactions get parsed. Wallets accumulate state. Your PostgreSQL instance hums along. Queries return in milliseconds. Storage is cheap. The team ships features.</p><p>Then year two happens. Then year three.</p><p>Somewhere between 5 TB and 10 TB, your infrastructure bill starts a quiet conversation with your CTO. By 20 TB, that conversation gets loud. By 30 TB — which is entirely reachable for a high-throughput chain after three years of production indexing — you are no longer talking about features. You are talking about survival: how do you change anything in a live system of this size without taking it down, corrupting data, or spending six months on a migration that should have taken three weeks?</p><p>This is that article. Not the “here’s how to build an indexer” tutorial. The one about what happens <em>after</em> — when the data is already there, it’s already the wrong shape, and the chain doesn’t stop producing blocks while you figure it out.</p><h3>What Makes Move Different at the Data Layer</h3><p>Before talking about scale, it’s worth understanding why Move-based blockchains (Aptos, Sui, Supra, and others) create a fundamentally different indexing problem than EVM chains.</p><p><strong>EVM state is shallow.</strong> An Ethereum indexer primarily deals with: blocks, transactions, logs, and account balances. The schema is wide but not deep. Relationships are relatively flat.</p><p><strong>Move state is deep and typed.</strong> Move organises on-chain state as <em>typed resources</em> attached to addresses. A single wallet doesn’t just have a balance — it can own dozens of distinct resource types simultaneously: coin stores, object collections, automation registrations, governance positions, staking records. Each resource type has its own structure. Each needs to be indexed, tracked, and queryable.</p><p><strong>Move has a first-class object model.</strong> In chains like Sui and Supra, everything is an addressable object with an owner, a version, a type path, and a parent-child relationship to other objects. Your indexer isn’t just recording what happened — it’s maintaining a live, queryable snapshot of an entire object graph that mutates with every block.</p><p><strong>Events are strongly typed.</strong> Move’s event system emits structured, typed payloads — not raw byte arrays like EVM logs. This is powerful for developers querying the indexer, but it means your event storage needs to handle variable schemas across dozens of event types, each with different fields, all arriving in the same stream.</p><p>The consequence of all this: a production Move indexer has <strong>far more tables, far more relationships, and far more cross-references</strong> than an EVM equivalent. A realistic production schema has 35–50 tables. Most of them are interconnected. Most of them grow with every block.</p><p>That scale of interconnection is exactly what makes optimisation so painful later.</p><h3>A Note Before We Go Further</h3><p>The patterns described in this article were not born from carelessness — they were the sensible choices given what was known at the time. The purpose of laying them out here is not to assign blame, but to share the experience of running into them at scale, so engineers building similar systems today can recognize them early and make the tradeoffs consciously rather than discover the cost years later.</p><h3>The Compounding Problem: Why Early Decisions Are Permanent</h3><p>Here is the core tension of blockchain indexer design: <strong>you make your schema decisions when you have zero data, but you live with those decisions when you have 30 TB of it.</strong></p><p>Every structural inefficiency you introduce on day one doesn’t just cost you N bytes per row. It costs you N bytes multiplied by every row ever written, across every table that inherited the pattern, indexed twice over because PostgreSQL also stores your mistakes in its B-tree indexes.</p><p>Let’s make this concrete.</p><h3>The Repeated String Problem</h3><p>Imagine you store a short identifier — say, the name of the network environment (“mainnet”, “testnet”) — as a VARCHAR column in every table. Seems harmless. It&#39;s just a word.</p><p>Now imagine that string is present in 40 tables. In every row of every one of those tables. At 10 billion total rows distributed across those tables — entirely realistic after three years on a fast chain — that one string, repeated, costs you <strong>tens to hundreds of gigabytes</strong> depending on its length.</p><p>But here’s where it gets worse: you can’t just ALTER TABLE your way out of this at 30 TB. An ALTER TABLE that changes a column type on a 2-billion-row table doesn&#39;t complete in minutes. It locks the table. It rewrites the heap. It takes hours or days. And you have 40 tables to fix. And the chain is still producing blocks.</p><p>The “right” solution — replacing the repeated string with a 2-byte integer foreign key — would have cost 30 minutes on day one. At year three, it costs you a multi-month migration project with significant operational risk.</p><h3>The Fat Primary Key Problem</h3><p>A transaction hash is typically 66 characters. A timestamp is 8 bytes. If your transaction table’s primary key is a composite of these plus an environment name, you’ve created a <strong>~90-byte primary key</strong>.</p><p>That primary key gets copied — physically, on disk — into every foreign key column of every child table. A transaction-heavy Move indexer has 10 or more child tables per transaction: events, state changes, coin transfers, fungible asset movements, sender records, fee payer records, and payload details. Each one stores that full 90-byte reference on every row.</p><p>At 10 billion transactions with 8 child rows each on average:</p><p>90 bytes × 10 child tables × 8 rows × 10B transactions<br> = ~72 TB of foreign key storage alone</p><p>And PostgreSQL builds a B-tree index on every foreign key. That doubles it.</p><p>A 4-byte or 8-byte surrogate integer primary key — decided on day one — reduces this by a factor of 10 to 20. But retrofitting surrogate keys onto live tables with hundreds of billions of rows, active foreign key constraints, and applications reading those columns in real time is one of the hardest classes of database migrations that exists.</p><h3>The Wrong Type Problem</h3><p>Move’s RPC layer returns numeric values as strings because they can exceed JavaScript’s safe integer range. blockHeight: &quot;18000000&quot;. sequenceNumber: &quot;4294967298&quot;. version: &quot;999999999&quot;.</p><p>Indexer developers, working fast, copy this directly to the database: blockheight VARCHAR(256). version VARCHAR(10). sequencenumber VARCHAR(255).</p><p>It works. For a while.</p><p>Then someone tries to write WHERE blockheight BETWEEN 15000000 AND 16000000. Postgres does lexicographic comparison on VARCHAR. The query returns wrong results. A bug is filed. A cast is added. The cast prevents index use. The query slows down. An index is added on CAST(blockheight AS BIGINT). Now you have a functional index instead of a simple column index, which is harder to maintain and slower to build.</p><p>At 2 billion blocks, changing VARCHAR(256) to BIGINT requires a full table rewrite. At 30 TB, that&#39;s a weekend project, not a 10-minute fix.</p><h3>JSONB: The Tool That Becomes the Problem</h3><p>JSONB is genuinely useful for a Move indexer. Move resources have variable schemas. Event payloads differ by type. Storing semi-structured data in a relational database without JSONB means either a hundred narrow tables or an unmaintainable EAV design.</p><p>But JSONB has a cost that surprises people who haven’t run it at scale.</p><p><strong>Storage overhead is fixed regardless of content.</strong> A JSONB column storing {} still has ~20-30 bytes of binary representation overhead. When you have millions of rows with small or empty JSON blobs, that overhead dominates.</p><p><strong>Updates are expensive.</strong> PostgreSQL stores JSONB as an immutable value. When you update a JSONB column — even to change a single key — the database writes a completely new version of the entire document and marks the old one dead. For tables that update JSONB columns frequently (like wallet state, which changes on every relevant transaction), this creates significant table bloat over time. The dead tuples accumulate. VACUUM can’t always keep up. The table grows even if the actual data size doesn’t.</p><p><strong>GIN indexes on JSONB are large and slow to build.</strong> A GIN index on a full JSONB column is many times larger than a B-tree index on a scalar column. On a table with billions of rows, building or rebuilding a GIN index is a multi-hour operation that consumes significant I/O.</p><p>The failure mode at scale is not that JSONB stops working. It’s that it gradually degrades — updates get slower, VACUUM runs longer, storage grows faster than the data warrants — and diagnosing the root cause in a 30 TB database with dozens of tables requires significant forensic work.</p><h3>The Migration Problem Is the Real Problem</h3><p>Everything described above has a known correct solution. Surrogate keys. Proper numeric types. Selective JSONB use. Hash storage as binary instead of hex strings. These are not novel ideas.</p><p>The reason they matter — the reason this article exists — is not the storage cost in isolation. It’s what that storage cost means when you try to fix it three years into production.</p><p><strong>You cannot take a blockchain indexer offline to migrate it.</strong> The chain does not pause. Blocks arrive every second. If your indexer falls behind, you create a gap in your data that is either expensive to backfill or permanently lost, depending on your node’s pruning settings.</p><p><strong>Schema migrations at this scale require orchestration, not just SQL.</strong> You cannot ALTER TABLE indexer_transaction ADD COLUMN transaction_id BIGSERIAL on a 3-billion-row table and move on with your day. You need to: add the column with no constraint, backfill in batches during low-traffic windows, add the constraint as NOT VALID, validate it in a separate step, update all application code to write to both old and new columns during the transition, cut over reads, then drop the old column. Each step has rollback implications. Each step must be tested against a full-size copy of production data.</p><p><strong>Foreign key changes require cascade planning.</strong> If you want to replace a composite FK with a surrogate integer FK, you need to: add the surrogate column to the parent, backfill it, add it to all child tables, backfill it there, drop the old FK constraints, add new ones, drop the old columns. For 10 child tables, each with billions of rows, this is months of careful work — not because any individual step is hard, but because the sequence must be correct and live traffic must be handled at every stage.</p><p><strong>Indexes cannot be rebuilt without significant I/O impact.</strong> Replacing a large text-based index with a smaller integer-based index on a table that’s being actively written requires CREATE INDEX CONCURRENTLY - which takes the load without locking, but takes proportionally longer and puts sustained read pressure on the disk. On a busy 30 TB database, a single large index rebuild can take 12-24 hours.</p><p>This is the real complexity of running a production Move indexer at scale. Not any single technical problem, but the compounding interaction of many small early decisions that each seemed reasonable at the time — and the enormous operational cost of unwinding them under load, without downtime, while the chain keeps running.</p><h3>What Good Design Buys You</h3><p>The flip side of all this is worth stating clearly.</p><p>A team that makes the right schema decisions at the start — surrogate integer keys, correct numeric types, binary hash storage, selective JSONB use, properly normalised repeated strings — doesn’t just save storage. They buy themselves <em>optionality</em>.</p><p>At 30 TB with a well-designed schema, adding a new index takes hours, not days. Migrating a table to a new structure is a weekend project, not a quarter-long initiative. Backfilling new columns is a batch job, not a rewrite. Query optimisation is about access patterns, not fighting the type system.</p><p>The teams that build Move indexers correctly from the start can spend year three shipping new features and supporting developers building on their data. The teams that don’t spend year three fighting their own database.</p><p>That’s the real cost of getting it wrong. Not the terabytes. The lost time.</p><h3>Closing Thought</h3><p>If you are designing a Move blockchain indexer today — or evaluating one — the questions worth asking are not just “does it index all transaction types?” or “what’s the query latency?” They are:</p><ul><li>What does the schema look like at 100× current data volume?</li><li>How long does it take to add a column to your largest table?</li><li>Can you change a primary key structure without taking the system down?</li><li>How do you handle schema migrations under continuous write load?</li></ul><p>The answers reveal a lot about how much technical debt is already priced into the system — and how much of your future engineering capacity it will consume.</p><p>Blockchain data is permanent and ever-growing. Your schema decisions are not quite permanent — but at 30 TB, they’re close enough that the difference barely matters.</p><p>The Move ecosystem is still early, and the tooling for large-scale indexer operations is still maturing. If you are building in this space and want to compare notes, the comments are open.</p><p>By Jatin Jain Saraf on May 5, 2026.</p><p><em>Originally published at </em><a href="https://insight.jatinjainsaraf.com/your-blockchain-indexer-is-fine-until-it-isnt"><em>https://insight.jatinjainsaraf.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d175efe8e3e8" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>