<?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 Igor Shevelenkov on Medium]]></title>
        <description><![CDATA[Stories by Igor Shevelenkov on Medium]]></description>
        <link>https://medium.com/@igorshevelenkov?source=rss-6dab93607880------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*MhQ-DXDnWsnnF1cha_h2CA.jpeg</url>
            <title>Stories by Igor Shevelenkov on Medium</title>
            <link>https://medium.com/@igorshevelenkov?source=rss-6dab93607880------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 10:49:34 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@igorshevelenkov/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[When the aggregator gives up]]></title>
            <link>https://medium.com/miki-dev/when-the-aggregator-gives-up-64b36c94308a?source=rss-6dab93607880------2</link>
            <guid isPermaLink="false">https://medium.com/p/64b36c94308a</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[the-open-network]]></category>
            <category><![CDATA[cryptocurrency]]></category>
            <dc:creator><![CDATA[Igor Shevelenkov]]></dc:creator>
            <pubDate>Wed, 20 May 2026 13:47:35 GMT</pubDate>
            <atom:updated>2026-05-20T13:47:35.005Z</atom:updated>
            <content:encoded><![CDATA[<h4><strong>Building real-time analytics on TON — and the one metric that broke our pattern</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dZisV66aOP2wB1PoYgdrow.png" /></figure><p><strong>Some context first</strong></p><p>We were building the backend for a TON prediction market. Users bet on outcomes, liquidity providers move in and out, markets resolve, people claim their winnings. Normal enough.</p><p>The analytics layer had to keep up with all of it in real time. What’s a user’s current P&amp;L? What’s the TVL on this market? How many trades in the last 24 hours? And — the one that ended up causing the most headaches — what’s a user’s current win streak?</p><p>In the early days we just queried the raw event log. A chain of UNION ALLs, some aggregations, ship it. Fine at 10k events. At 1M it was embarrassing. We had to rethink.</p><p><strong>The idea: precompute everything, at write time</strong></p><p>The approach that worked: every time an on-chain event comes in, we open a Postgres transaction, write the raw event row, and update the aggregate tables right there in the same transaction. The cursor only advances after commit. This means the read models literally cannot drift from the event log — if the transaction fails, nothing moves.</p><p>No background jobs. No eventual consistency. No waking up at 2am because the hourly rebuild is running slow.</p><p>The core of it is what we started calling a <strong>signed-delta upsert</strong>. A trade comes in and we do something like:</p><pre>INSERT INTO user_market_positions (user, market, yes_amount, no_amount)<br>VALUES ($1, $2, $3, $4)<br>ON CONFLICT (user, market) DO UPDATE<br>SET yes_amount = user_market_positions.yes_amount + EXCLUDED.yes_amount,<br>no_amount = user_market_positions.no_amount + EXCLUDED.no_amount;</pre><p>Bought yes-shares? `yes_amount` is positive. Sold them? Negative. One statement handles both. No read-modify-write, no race conditions.</p><p>This worked beautifully for almost everything. Volume, TVL, win counts, position sizes — all of them decompose into a delta. Reads go from O(events) to O(1). The aggregate tables are tiny and fast.</p><p>Small bonus trick: if you need to know whether the upsert just inserted a new row or updated an existing one, you can check `xmax`:</p><pre>INSERT INTO … ON CONFLICT DO UPDATE …<br>RETURNING (xmax = 0) AS was_inserted;</pre><p>`xmax = 0` means no previous version of the row existed — it was a fresh insert. It’s a Postgres internal, but it’s stable, and it saves you an extra round-trip when you need to branch on insert vs. update.</p><p><strong>The one metric that didn’t cooperate</strong></p><p>Here’s the thing about win streaks: they’re not a sum.</p><p>A streak is about the <em>order</em> that bets resolved. If a user had W, W, L — their current streak is 0. But if a late resolution flips that to W, L, W — it’s suddenly 1. The same three outcomes, different sequence, different answer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Eq1-UORyKdkkc5YvV2iZtQ.png" /></figure><p>There’s no delta you can emit at trade time that means “streak is now 3.” The market hasn’t resolved yet. You don’t know.</p><p>We spent a while trying to force it anyway. Could we store partial streak state and patch it on each resolution? Could we decompose it into a counter plus a break flag? We kept finding edge cases — concurrent resolutions, users holding positions across multiple markets — where it fell apart. Every approach either required replaying history or quietly pretended that order didn’t matter.</p><p>At some point we just wrote it down: <strong>streaks are not incrementally aggregatable</strong>. And once we said it out loud, it was obvious what to do.</p><p><strong>The fix: just replay it</strong></p><p>When a market resolves, we pull the resolved positions for every user who held it, in chronological order. We replay the wins and losses in application code — a simple loop — and write back `(current_streak, best_streak, total_wins, total_losses)` in one batch update:</p><pre>UPDATE user_stats AS s<br>SET<br>current_streak = v.current_streak,<br>best_streak = GREATEST(s.best_streak, v.best_streak),<br>total_wins = v.total_wins,<br>total_losses = v.total_losses<br>FROM (VALUES<br>($1::uuid, $2::int, $3::int, $4::int, $5::int),<br>…<br>) AS v(user_id, current_streak, best_streak, total_wins, total_losses)<br>WHERE s.user_id = v.user_id;</pre><p>The scope is bounded — we’re only touching users who held <em>*this*</em> market, usually dozens of people. It runs once when the market resolves, not on every API request. It’s not clever, but it’s correct and it’s fast enough.</p><p>This is the only place in the whole system where we recompute from scratch instead of accumulating. We treat it as a deliberate exception, not a design failure.</p><p><strong>What we took away from this</strong></p><p>Incremental aggregation is genuinely powerful for most of what you’d put in an analytics backend. If a metric can be expressed as a delta — a number you add or subtract — it fits the pattern cleanly and you get fast reads essentially for free.</p><p>But some things can’t be expressed that way. Anything that depends on ordering, or on global state that doesn’t exist yet at write time, is going to fight you. The more you try to bend it into the incremental model, the messier the code gets.</p><p>The better instinct is to notice early when something doesn’t fit, accept it, and reach for the simpler approach — even if the simpler approach is “replay a bit of history once in a while.”</p><p>The goal isn’t a system that uses one pattern everywhere. It’s a system that does the right thing for each problem.</p><p><strong>That’s a wrap</strong></p><p>If you’re building analytics on top of an event-driven or on-chain system, the signed-delta pattern is worth reaching for early — it’ll handle most of what you need cleanly. Just stay honest about the cases where it doesn’t fit.</p><p>Building something on TON or dealing with a backend that’s gotten harder to reason about than it should be? Feel free to <a href="https://miki.digital/">reach out or book a call</a>.</p><p><strong>References</strong></p><p>- <a href="https://www.postgresql.org/docs/current/sql-insert.html">[PostgreSQL: INSERT … ON CONFLICT]</a></p><p>- [<a href="https://www.postgresql.org/docs/current/ddl-system-columns.html">PostgreSQL system columns (xmin, xmax)</a>]</p><p>- [<a href="https://martinfowler.com/eaaDev/EventSourcing.html">Event Sourcing pattern — Martin Fowler</a>]</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64b36c94308a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/miki-dev/when-the-aggregator-gives-up-64b36c94308a">When the aggregator gives up</a> was originally published in <a href="https://medium.com/miki-dev">MiKi Blockchain</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>