DEV Community: scubaDEV

Your Hand-Rolled Two-Phase Commit Between Two Databases Isn't Atomic

scubaDEV — Mon, 15 Jun 2026 15:37:46 +0000

I had a write that spanned two physically separate databases. A rename that had to propagate across several tables in one database and a couple of tables in another, and the two had to stay consistent. No distributed transaction coordinator was available to me. So I did the obvious thing: opened a transaction on each, did the work, and committed them one after the other inside a try/catch with rollbacks on both sides. It felt safe. It compiled. It passed tests.

Then I drew the failure on a whiteboard, and the safety evaporated.

The window that ruins everything

Here's the structure, simplified:

await using var txA = await dbA.Database.BeginTransactionAsync();
await using var txB = await dbB.Database.BeginTransactionAsync();

await DoWorkOnA(dbA);
await DoWorkOnB(dbB);

await txA.CommitAsync();   // <-- succeeds
await txB.CommitAsync();   // <-- what if this throws?

Two transactions do not make one atomic operation. CommitAsync is a point of no return, and there are two of them. Between the first commit returning and the second one starting, there is a window. If txB fails in that window — the connection drops, the process is killed, the database hiccups — then A is permanently committed and B never happens. Your rollback in the catch block is useless: you can't roll back txA, it's already durable. The two databases now disagree, and nothing in your code will heal that on its own.

This is the dual-write problem, and it's not a bug you can fix by being more careful with try/catch. The atomicity you want simply isn't available from two independent commits. Ordering them, nesting them, wrapping them — none of it closes the window, because the window is inherent to having two commit points.

Why "it's never failed" isn't reassurance

The seductive thing about this pattern is that the window is small, so in practice it almost never triggers. You can run it for a year and never see an inconsistency. That's exactly what makes it dangerous: it trains you to trust it, and then it fails during the one incident — a deploy, a failover, an OOM kill — when you're least able to notice a quietly desynced record. Rare and catastrophic beats frequent and visible, from the bug's point of view.

What actually addresses it

None of these are as convenient as two commits in a row. That's the point — the convenience was the illusion.

Make one database the source of truth. Write atomically to a single primary, and treat the second database as a projection you update asynchronously and idempotently afterward. The primary is always correct; the secondary catches up. You trade instant consistency for the ability to actually guarantee the important write.

Use the outbox pattern. In the same transaction as your primary write, insert a row describing the change into an "outbox" table. A separate process reads the outbox and applies the change to the second database, retrying until it succeeds. Because the outbox row is committed atomically with the real work, you can never lose the intent — you can only delay it. This is the standard answer for a reason.

Detect and reconcile. If you truly can't restructure, at least stop pretending the write is atomic. Make the second write idempotent and retryable, and run a reconciliation pass that finds and repairs divergence. It's a patch over the gap, not a closing of it — but an honest patch beats a false guarantee.

The mindset shift

The real fix isn't a code change, it's dropping a belief: that committing two transactions back-to-back is "basically" atomic. It isn't, and no arrangement of them will be. The moment a write spans two systems, you've left the world of database transactions and entered distributed systems, where the tools are outboxes, sagas, idempotency, and reconciliation — not a second CommitAsync and some hope. Recognizing which world you're in is most of the battle. The pseudo-2PC is sometimes good enough; just never mistake "good enough" for "atomic."

Everything That Breaks When You Generate PDFs with Headless Chrome in a Container

scubaDEV — Mon, 15 Jun 2026 15:34:31 +0000

Server-side PDF generation with headless Chrome is the kind of feature that demos perfectly and then ambushes you in production. The "how to render a PDF with a headless browser" tutorials all stop exactly where the real problems start: the container, the fonts, the hangs, the bill. This is the operational minefield, one mine at a time. Each of these cost me real debugging hours.

1. The distro's Chromium package is a trap

On a Debian/Ubuntu container image, installing the chromium package often gives you a snap stub that simply won't launch in a headless container. The error is unhelpful and the fix is non-obvious: install actual Google Chrome from Google's official apt repository, at image build time, and point your driver at it explicitly:

# install Chrome from Google's repo at BUILD time, never at runtime
RUN wget -q -O - https://dl.google.com/linux/linux_signing_key.pub | apt-key add - \
 && echo "deb [arch=amd64] https://dl.google.com/linux/chrome/deb/ stable main" \
    > /etc/apt/sources.list.d/google-chrome.list \
 && apt-get update && apt-get install -y google-chrome-stable
ENV CHROME_PATH=/usr/bin/google-chrome

Doing this at runtime instead means your first request after every cold start pays a download, or fails offline. Bake the browser into the image.

2. Fonts aren't ready when you think they are

This one produced wrong output, not a crash, which is worse. If you measure content height to decide page scaling before the fonts have loaded, you measure against fallback metrics and your scaling is off by 10–15%. The page looks subtly wrong and you blame your CSS.

The fix is to wait for the browser to tell you fonts are ready before you measure anything:

await document.fonts.ready;   // only now is it safe to measure layout

3. A hung Chrome must never hold a slot forever

Headless Chrome can hang. When it does, the danger isn't the one stuck render — it's that the stuck render holds a concurrency slot indefinitely and slowly starves everything behind it. You need two guards: a hard per-render timeout, and a bounded pool so a bad render can't take the whole service down.

private static readonly SemaphoreSlim _renderPool = new(2, 2);   // max concurrent
private const int RenderTimeoutMs = 60_000;                      // hard ceiling

await _renderPool.WaitAsync();
try
{
    using var cts = new CancellationTokenSource(RenderTimeoutMs);
    await RenderAsync(html, cts.Token);   // killed if it overruns
}
finally { _renderPool.Release(); }

4. The same binary path won't exist everywhere

Chrome lives at one path in your container, another on a developer's laptop, and maybe nowhere on a fresh CI box. Hardcoding the path makes the code run in exactly one environment. A three-tier resolution chain lets one codebase run everywhere: honor an explicit environment variable first, auto-detect a known host install second, and fall back to downloading a managed browser only as a last resort.

5. The cost nobody budgets for

Here's the one that didn't show up in any tutorial: moving rendering from the client's browser to your server means you now run a full browser per render. In our case it roughly tripled the CPU and memory the service needed. The feature was free when the client's machine paid for it. Server-side, it's a line item. Size your pods accordingly before you ship, not after the autoscaler panics.

The shape of the lesson

None of these are about Chrome's API. They're about the gap between "renders a PDF on my machine" and "renders PDFs reliably for everyone." Bake the browser into the image, wait for fonts before measuring, never let a hung render hold a slot, resolve the binary by environment, and budget for the compute you just moved onto your own servers. The happy-path code is the easy 20%. This is the other 80%.

Dynamic Column Updates in EF Core Without Hand-Rolling SQL Injection

scubaDEV — Mon, 15 Jun 2026 15:32:27 +0000

Sometimes you genuinely need the set of columns to update to be data, not code. An operator maps configuration fields to database columns, and you want to honor that mapping without redeploying every time it changes. The naive solution — build an UPDATE string from those column names — is also one of the easiest ways to hand-write a SQL injection vulnerability. This is how to get the flexibility without the hole.

We'll build it up in three layers: make it work, make it safe, then count the cost.

Layer 1: The dynamic update, the wrong way

The tempting version concatenates column names into SQL:

// DO NOT do this.
var sql = $"UPDATE products SET {columnName} = {value} WHERE id = {id}";

If columnName comes from configuration that an operator can edit, you've just made your schema writable by whoever controls that config. A value of name = 'x'; DROP TABLE products; -- is now your problem. Even "trusted" config is an injection surface the moment it flows into a SQL string.

Layer 2: The same feature with EF.Property

EF Core's ExecuteUpdateAsync lets you set a property by name without ever building SQL yourself. EF.Property<T> takes the property name as a string, and EF parameterizes the value and validates the property against the model:

await db.Products
    .Where(p => p.Id == id)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => EF.Property<float?>(p, columnName), value));

This is already a different security posture: the value is a parameter, not interpolated text, and EF will throw rather than emit SQL if columnName isn't a real mapped property. But "EF will throw" is a runtime backstop, not a policy. We want to reject bad names before they reach the database, fail closed, and control exactly which columns are writable.

Layer 3: Reflection as a whitelist

The guard is to validate every incoming column name against the entity's actual properties, using reflection, and to keep an explicit blacklist of fields that must never be touched dynamically:

private static readonly HashSet<string> Forbidden =
    new(StringComparer.Ordinal) { "Id", "CustomerId", "CreatedAt" };

private static readonly HashSet<string> Allowed =
    typeof(Product)
        .GetProperties(BindingFlags.Public | BindingFlags.Instance)
        .Select(p => p.Name)
        .Where(name => !Forbidden.Contains(name))
        .ToHashSet(StringComparer.Ordinal);

public async Task ApplyAsync(int id, IReadOnlyDictionary<string, float?> updates)
{
    foreach (var key in updates.Keys)
        if (!Allowed.Contains(key))
            throw new InvalidOperationException($"Column '{key}' is not updatable.");

    await using var tx = await db.Database.BeginTransactionAsync();
    var query = db.Products.Where(p => p.Id == id);

    foreach (var (name, value) in updates)
        await query.ExecuteUpdateAsync(s =>
            s.SetProperty(p => EF.Property<float?>(p, name), value));

    await tx.CommitAsync();
}

The important properties of this design:

Whitelist, not blacklist, as the primary control. The set of allowed names is derived from the type itself, so it can't drift out of sync with the schema. The blacklist only subtracts the sensitive few.
Fail closed. An unknown column raises before any database call, and the transaction means a bad item rolls everything back rather than half-applying.
No SQL is ever constructed from input. The column name only ever indexes into a validated set and is handed to EF as a model property reference.

The cost, stated honestly

This isn't free. You pay reflection and a dictionary lookup per field, and one ExecuteUpdateAsync per column rather than one combined statement. For a handful of configurable fields on a record, that's nothing. For a hot path updating dozens of columns across millions of rows, you'd cache the allowed set (as above, it's static) and consider batching. Measure before you optimize, but know the trade is there.

The principle to carry off

Dynamic behavior driven by config is fine. Dynamic SQL text built from config is the danger. The fix isn't to ban the feature — it's to make sure every externally-influenced identifier is validated against a server-side whitelist that the user can't expand, and to let the ORM parameterize values so you never assemble a query string by hand. Reflection over your own type is the cleanest source for that whitelist, because the schema is the source of truth.

SELECT MAX()+1 Is a Race Condition Waiting to Happen

scubaDEV — Mon, 15 Jun 2026 15:23:47 +0000

This is a story in three acts about one of the most innocent-looking patterns in backend code: generating the next number in a sequence. It passed every test. It ran fine for months. Then a unique-index violation showed up in production logs, and the fix I reached for first didn't actually fix it.

Act 1: The code that looks correct

The requirement was a per-customer progressive number — ORD-1, ORD-2, and so on. The implementation read like plain English:

var existing = await db.Orders
    .Where(o => o.CustomerId == customerId)
    .ToListAsync();

var next = existing
    .Select(o => ParseProgressive(o.Code))
    .DefaultIfEmpty(0)
    .Max() + 1;

db.Orders.Add(new Order { Code = $"ORD-{next}", CustomerId = customerId });
await db.SaveChangesAsync();

Read the current max, add one, insert. There is nothing wrong with this code if only one thing ever runs it at a time. The problem is that assumption is almost never true, and nothing in the code makes it true.

Act 2: Why it breaks, and the fix that doesn't

This is a TOCTOU bug — time-of-check to time-of-use. Two requests for the same customer arrive nearly together:

Request A reads max = 7. Computes next = 8.
Request B reads max = 7 before A has inserted. Also computes next = 8.
Both insert ORD-8. The unique index rejects the second. Production error.

My first instinct was a lock around the method — make the read-and-write atomic in the application. And in a single-process test, that "works." Here's the trap: the moment you run more than one instance of the service (and you will — that's what horizontal scaling is), an in-process lock protects nothing. Instance 1 and instance 2 each hold their own lock and happily compute the same number. The in-memory fix doesn't close the race; it just shrinks the window enough to pass your tests and lie to you.

The real lesson arrived here: uniqueness has to be enforced by the one thing both instances share — the database. Not by application code, no matter how clever the locking.

Act 3: The fixes that actually hold

There are several, and the right one depends on whether the number has to be gapless.

If gaps are acceptable (you just need unique and roughly increasing), use the database's own sequence or identity. It's atomic by construction, across any number of instances:

CREATE SEQUENCE order_seq;
-- each insert pulls nextval('order_seq'), no read-then-write

If the number must be per-customer and gapless, you can't lean on a global sequence. Then you serialize at the row level. A Postgres advisory lock keyed on the customer turns concurrent inserts for the same customer into a queue, while leaving different customers fully parallel:

SELECT pg_advisory_xact_lock(hashtext($1));  -- $1 = customer id
-- now the read-max-and-insert is safe within this transaction

Or let the database be the referee with insert-and-retry. Keep the unique index, attempt the insert, and on a conflict recompute and try again. The constraint you were treating as an error becomes your concurrency control:

INSERT INTO orders (...) VALUES (...)
ON CONFLICT (customer_id, code) DO NOTHING;

The thing I'd tell past me

The bug wasn't the arithmetic. It was believing that "read, then write" is a single step. It's two, and anything can happen in between. When correctness depends on two operations being indivisible, the only component that can guarantee that across a scaled-out service is the shared database — through a sequence, a lock, or a constraint you actually let do its job. Application-side locking on a multi-instance service is a comfort blanket, not a fix.

The Hidden Math of Mid-Cycle Subscription Upgrades

scubaDEV — Mon, 15 Jun 2026 15:21:51 +0000

Here's a question that sounds trivial and isn't. A customer is on Basic, 3 seats. On day 12 of a 30-day billing cycle they switch to Pro, 2 seats. How much do you charge them today, and what do they renew at next month?

If your first instinct was "new price times new quantity," you've already shipped the bug. That's the renewal amount, not what they owe now. The amount due today is a proration, and getting it wrong means either robbing the customer or robbing yourself — quietly, on every mid-cycle change, forever.

I wrote this logic for a real billing integration, covered it in comments, and I'm glad I did, because six months later I couldn't reconstruct it from memory. Here's the part no payment SDK gives you.

What proration actually means

The customer already paid for the full cycle up front. When they change plans mid-cycle, two things are true:

They have unused credit on the old plan for the days remaining.
They owe the cost of the new plan for those same remaining days.

What you charge today is the difference between those two. Not the new full price — just the delta for the slice of the cycle they haven't used yet.

So for our example, with 18 of 30 days remaining (60% of the cycle left):

Unused Basic credit ≈ 60% of (Basic price × 3 seats)
New Pro cost ≈ 60% of (Pro price × 2 seats)
Charge today = new cost − unused credit

The next renewal then bills the full Pro × 2 at the normal cycle boundary. Two completely different numbers, and conflating them is the classic mistake.

The four cases, one of which "should never happen"

Once seats enter the picture, a single change can be any of four shapes, and they don't behave the same:

Upgrade + more seats — charge the prorated plan delta on existing seats and the prorated cost of the new seats.
Upgrade + fewer seats — charge the plan delta only on the seats that survive; the removed seats generate credit, not charge.
Downgrade — typically costs 0 today. You don't refund mid-cycle cash; the lower price takes effect next renewal.
First purchase — no proration at all, just a clean charge.

In my code one branch is commented "this should never happen given the UI… but it's supported if needed." That comment is doing real work. Defensive billing code that handles the impossible case is cheaper than a support ticket from the one customer who found a way to trigger it.

The trap that actually bit me: dates

The subtle bug wasn't the money math. It was making the new cycle start exactly where the old one ended.

You take the provider's NextBillingTime, and if you carry the time-of-day along with it, the new cycle and the old one drift apart by hours — enough to double-bill a fraction of a day or leave a gap. The fix was to collapse to a date (drop the time component entirely) so the boundary lines up cleanly. In .NET that's exactly what DateOnly is for. A whole class of off-by-a-few-hours billing weirdness disappears the moment you stop carrying the clock around.

The design call: compute, log, never surprise the user

The last decision was philosophical. Proration is the kind of calculation that, if it's ever wrong, is wrong in a way the customer notices on their card statement. So I didn't expose intermediate failures to the user at all. Every branch produces a computed result object — the amount, the case it took, the dates it used — and logs the inputs aggressively. If a charge ever looks off, I can replay exactly which path ran and why, instead of guessing from a stack trace.

That's the real lesson, more than the arithmetic: for money math, observability is part of the feature. The calculation that you can't reconstruct after the fact is the one that costs you a chargeback.

Takeaway

Mid-cycle subscription changes aren't "new price × quantity." They're a difference of two prorated amounts, branching into four cases, anchored to a date boundary you have to normalize by hand. Write it once, comment the impossible branch, log the inputs, and let DateOnly save you from the clock. Then never touch it again if you can help it.

Role-Based Access Control in Blazor WebAssembly with Azure AD

scubaDEV — Mon, 15 Jun 2026 14:45:46 +0000

Blazor WebAssembly runs entirely in the browser. That single fact shapes everything about how you implement authorization, because nothing the client decides can be trusted. A user can open dev tools, edit memory, and flip any boolean you use to hide a button.

So role-based access control in a WASM app is really two separate jobs:

Cosmetic — show users only the parts of the UI they're allowed to use, so the app feels coherent.
Enforced — make sure the API rejects anything a user shouldn't be able to do, regardless of what the client sends.

This post covers both, driven from Azure AD app roles.

Step 1: Define app roles in Azure AD

In the Azure portal, open your app registration → App roles → create a role. The important field is the Value — that's the string that lands in the token. For example, a role with value BasicUser.

Then assign users to that role under Enterprise applications → your app → Users and groups. Azure AD will now include the role in the roles claim of the access token issued to that user.

Step 2: Map the role claim in the client

Blazor WASM doesn't automatically know that the roles claim should map to .NET role checks. You tell it during authentication setup:

builder.Services.AddMsalAuthentication(options =>
{
    builder.Configuration.Bind("AzureAd", options.ProviderOptions.Authentication);
    options.ProviderOptions.DefaultAccessTokenScopes.Add("api://your-api-id/access_as_user");

    // Make the "roles" claim drive IsInRole / [Authorize(Roles = ...)]
    options.UserOptions.RoleClaim = "roles";
});

With that mapping in place, the standard authorization primitives start working off your Azure AD roles.

Step 3: Conditional UI with AuthorizeView

For showing and hiding pieces of UI, AuthorizeView is the cleanest tool:



        Run report


        You don't have access to this feature.

This is the cosmetic layer. It's genuinely useful — it stops users from being confused by controls they can't use — but on its own it secures nothing.

Step 4: Restrict navigation

A common pattern is to hide whole sections of the nav menu. You can check roles imperatively by injecting the authentication state:

@inject AuthenticationStateProvider AuthState

@if (_isBasicUser)
{
    Reports
}

@code {
    private bool _isBasicUser;

    protected override async Task OnInitializedAsync()
    {
        var state = await AuthState.GetAuthenticationStateAsync();
        _isBasicUser = state.User.IsInRole("BasicUser");
    }
}

You can also protect the routed pages themselves with an attribute, so that even a user who types the URL directly gets bounced to the "not authorized" view:

@page "/reports"
@attribute [Authorize(Roles = "BasicUser")]

Again — useful, but still client-side. A determined user can bypass all of it.

Step 5: The part that actually matters — enforce on the server

Every endpoint behind the UI must independently check the role. The browser-side checks are a convenience; the API is the boundary that counts.

[ApiController]
[Route("api/reports")]
public class ReportsController : ControllerBase
{
    [HttpPost("run")]
    [Authorize(Roles = "BasicUser")]
    public async Task RunReport()
    {
        // Only reachable by a token that actually carries the role.
        // ...
        return Ok();
    }
}

Because the same Azure AD token carries the same roles claim to the API, the server validates the role from a source the client can't forge. If someone strips the client-side checks and calls the endpoint directly, the [Authorize] attribute rejects them.

The mental model to take away

Think of it as defense in two layers with very different jobs:

The Blazor WASM layer makes the app pleasant and coherent — users see what's relevant to them.
The API layer makes the app secure — it assumes the client is hostile and validates every role on its own.

If you only do the client side, you have a UI that looks locked down and an API that's wide open. If you only do the server side, you have a secure app with a confusing UI full of buttons that error out. You want both, and it's worth being explicit about which layer you're working on at any given moment — because they're easy to conflate, and conflating them is exactly how WASM apps end up insecure.

From Permanent Tokens to a Server-Authoritative JWT Model in .NET

scubaDEV — Mon, 15 Jun 2026 14:41:41 +0000

A lot of integrations start the same way: you get an access token from a third-party API, you store it somewhere, and you keep using it. It works on day one. The trouble shows up later — the token can't be revoked without a redeploy, every instance of your app trusts it blindly, and when several requests try to refresh it at the same time you get a small storm of duplicate refresh calls.

This post walks through replacing that pattern with a server-authoritative model: the server is the single source of truth for token state, tokens are short-lived, and revocation is a database write instead of a deployment.

The problem with "permanent" tokens

A permanent or very long-lived token has three properties you don't want in production:

It can't be revoked cheaply. If it leaks, your only real lever is rotating it manually and pushing config everywhere.
Trust is implicit. Any holder of the token is authorized, and the application has no internal record of whether that token should still be valid.
Refreshes race. Under load, multiple callers notice the token is expired at the same instant and all trigger a refresh.

The fix for all three is to stop treating the token as a static secret and start treating it as state your server owns.

The shape of the solution

The model has three moving parts:

A database table that holds the current token, its expiry, and a validity flag.
A token manager that is the only component allowed to read, refresh, or invalidate the token.
Per-tenant concurrency control so that only one refresh happens at a time, even with many concurrent callers.

Here's a minimal version of the persisted state:

public class AccessTokenRecord
{
    public int CompanyId { get; set; }
    public string AccessToken { get; set; } = string.Empty;
    public DateTimeOffset ExpiresAt { get; set; }
    public bool IsValid { get; set; }
}

The token manager

The manager centralizes everything. Nothing else in the codebase talks to the token table directly — that single rule is what makes the model "authoritative."

public class TokenManager
{
    private readonly AppDbContext _db;
    private readonly IExternalAuthClient _auth;

    // One semaphore per company so refreshes don't stampede,
    // but different companies don't block each other.
    private static readonly ConcurrentDictionary _locks = new();

    public TokenManager(AppDbContext db, IExternalAuthClient auth)
    {
        _db = db;
        _auth = auth;
    }

    public async Task GetValidTokenAsync(int companyId, CancellationToken ct = default)
    {
        var record = await _db.AccessTokens
            .FirstOrDefaultAsync(t => t.CompanyId == companyId, ct);

        if (record is { IsValid: true } && record.ExpiresAt > DateTimeOffset.UtcNow.AddMinutes(1))
            return record.AccessToken;

        return await RefreshAsync(companyId, ct);
    }

    private async Task RefreshAsync(int companyId, CancellationToken ct)
    {
        var gate = _locks.GetOrAdd(companyId, _ => new SemaphoreSlim(1, 1));
        await gate.WaitAsync(ct);
        try
        {
            // Re-check inside the lock: another caller may have just refreshed.
            var record = await _db.AccessTokens
                .FirstOrDefaultAsync(t => t.CompanyId == companyId, ct);

            if (record is { IsValid: true } && record.ExpiresAt > DateTimeOffset.UtcNow.AddMinutes(1))
                return record.AccessToken;

            var fresh = await _auth.RequestTokenAsync(companyId, ct);

            record ??= new AccessTokenRecord { CompanyId = companyId };
            record.AccessToken = fresh.Token;
            record.ExpiresAt = fresh.ExpiresAt;
            record.IsValid = true;

            _db.AccessTokens.Update(record);
            await _db.SaveChangesAsync(ct);

            return record.AccessToken;
        }
        finally
        {
            gate.Release();
        }
    }

    public async Task InvalidateAsync(int companyId, CancellationToken ct = default)
    {
        var record = await _db.AccessTokens
            .FirstOrDefaultAsync(t => t.CompanyId == companyId, ct);

        if (record is null) return;

        record.IsValid = false;
        await _db.SaveChangesAsync(ct);
    }
}

Two details are doing the heavy lifting here.

The double-check inside the lock. The first caller to find an expired token enters the semaphore and refreshes. Every other caller that was waiting then re-reads the record after acquiring the lock, finds it's already fresh, and returns immediately. You pay for exactly one refresh, not one per waiting request.

One semaphore per tenant. A single global lock would serialize refreshes across all companies, turning an unrelated tenant's slow auth call into everyone's problem. Keying the semaphore by companyId isolates them.

Why short-lived beats permanent

Once token state lives in the database, expiry stops being scary. A token that lives 30 minutes and refreshes automatically is strictly safer than one that lives forever, because the blast radius of a leak is now measured in minutes. And revocation becomes trivial: flipping IsValid to false means the next call refreshes, no redeploy required.

A note on invalidation triggers

InvalidateAsync is worth wiring to real events rather than calling it ad hoc. Good triggers include the external API returning a 401, an admin disconnecting an integration, or a credential rotation. The pattern is always the same: mark the record invalid, let the next GetValidTokenAsync transparently fetch a new one.

Wrapping up

The shift here is conceptual more than technical. You stop thinking of the token as a secret you hold and start thinking of it as state your server is responsible for. Once you make that move, short lifetimes, clean revocation, and safe concurrency all fall out of the same small design — a database record, one manager, and a per-tenant lock.

Postgres or ClickHouse? Row vs Column Storage, and When Each Wins

scubaDEV — Mon, 15 Jun 2026 14:40:08 +0000

PostgreSQL and ClickHouse both speak SQL, both call themselves databases, and both will happily store your data. That surface similarity gets teams into trouble, because under the hood they're built for opposite jobs. Picking the wrong one doesn't show up on day one — it shows up six months later when a query that should take 50ms takes 40 seconds.

The single distinction that explains almost everything is how rows are laid out on disk.

Row storage vs column storage

PostgreSQL is row-oriented. All the fields of one record sit next to each other on disk:

(1, 'error',   'billing', '2026-06-15', 'timeout')
(2, 'info',    'auth',    '2026-06-15', 'login ok')
(3, 'warning', 'billing', '2026-06-15', 'retry')

ClickHouse is column-oriented. Each field is stored as its own contiguous run:

id:        1, 2, 3
level:     error, info, warning
service:   billing, auth, billing
timestamp: 2026-06-15, 2026-06-15, 2026-06-15
message:   timeout, login ok, retry

That's the whole thing. Everything else — performance, compression, what each is good and bad at — falls out of this one choice.

Why this makes them good at opposite jobs

Fetching one whole record is a row store's home turf. SELECT * FROM users WHERE id = 42 touches a single contiguous location and pulls the entire row in one read. Updating that user touches the same one place. This is OLTP — many small, targeted reads and writes, with transactions and consistency. Postgres is built for it.

Scanning a few columns across millions of rows is the column store's home turf. SELECT service, count(*) FROM logs GROUP BY service only needs the service column — so ClickHouse reads only that column off disk and ignores the rest. A row store has to read every full row just to look at one field. This is OLAP — aggregations and scans over huge datasets. ClickHouse is built for it.

Columnar layout also compresses far better, because values in one column are similar to each other (lots of repeated service names, timestamps in a tight range). Better compression means less data read from disk, which compounds the speed advantage. Add vectorized execution — processing whole column blocks at once — and analytical queries that crawl on Postgres fly on ClickHouse.

The trade-offs, stated plainly

	PostgreSQL (row)	ClickHouse (column)
Best at	Transactions, point lookups, updates	Aggregations, scans over big data
Single-row read/write	Fast	Slow / awkward
Updates & deletes	Cheap, native	Heavy "mutations", avoid
Inserts	Row-at-a-time is fine	Wants large batches
Transactions / ACID	Full support	Not its job
Aggregations on millions of rows	Slow	Extremely fast
Typical use	App backend, orders, users	Analytics, logs, metrics, events

The ClickHouse weaknesses are the mirror image of its strengths. Updating or deleting a single row is genuinely painful — the column layout that makes scans fast makes targeted edits expensive. And it wants inserts in big batches, not one row per request. If your workload is "update this order's status," ClickHouse is the wrong tool. If it's "show me revenue per region for the last year," Postgres will struggle.

They're complements, not competitors

The mistake I see most is treating this as a versus. In practice the two sit side by side: Postgres runs the transactional core of the app — the data users create, edit, and rely on being consistent — while ClickHouse holds the high-volume, append-only, analytical data: logs, events, metrics, anything you query in aggregate and never update.

The heuristic

Forget the feature lists and ask one question about your dominant query:

Few rows, many columns (fetch or change a specific record) → row store, use Postgres.
Many rows, few columns (aggregate across a huge table) → column store, use ClickHouse.

Most teams don't need to choose. They need Postgres for the app and, eventually, something columnar for the analytics — and the failure mode is forcing one engine to do the other's job because adding a second database felt like too much. The two layouts exist precisely because no single one is good at both.