# Just use Postgres. Seriously. > Cache, fan-out, job coordination, leader election, idempotency: running all of it on the one PostgreSQL you already operate, and why SKIP LOCKED is the one pattern deliberately avoided. - Author: Simon Wimmesberger - Published: 2026-07-17 - Canonical: https://blog.wimmesberger.dev/posts/just-use-postgres/ - Tags: postgresql, architecture, elarion, dotnet --- Every architecture review has the same reflex arc. Caching comes up, and someone says Redis. Events come up, and someone says a broker. Scheduled jobs on multiple instances? Quartz, with its lock tables. Leader election? Something with ZooKeeper, surely. Twenty minutes in, a CRUD application running on four nodes has six stateful systems in its diagram. Each one brings a connection string, a backup story, a security review, and its own way of being down at 3 a.m. **At the small-to-mid tier of one to ten nodes, the PostgreSQL you already run can do all of those jobs.** Often better, because Postgres sits *inside your transactions*, and none of the dedicated systems do. [Elarion](https://github.com/swimmesberger/Elarion), my open-source .NET application framework, commits to this fully: cache, fan-out, outbox, scheduler coordination, leader leases, idempotency, blobs. One database. Its positioning states the tier plainly: > Elarion's defaults target **small-to-mid deployments: ~1–10 nodes, vertical-first, on the one PostgreSQL the application already runs**. This post is the tour, with the actual SQL. But first, a small vocabulary, because the whole tour is really just three tricks. ## Three shapes instead of six systems Almost every "infrastructure" job below boils down to one question: **several nodes want to do something. Who's allowed?** Postgres can referee that question in three ways: 1. **The fence.** Everyone tries to `INSERT` the same row; a unique constraint lets exactly one insert succeed. Winning the insert *is* the permission. No locks, no waiting. Losers just get "0 rows." 2. **The lease column.** A row carries `lock_id` and `locked_until_utc`. Claiming means stamping your id into it with a conditional `UPDATE`. If you crash, you release nothing; the claim simply *expires*, and someone else stamps over it. 3. **The one-row lease.** A single row per role, saying who owns it and until when. Renew it while you're alive; anyone may take it over once it expires. The row *is* the leader election. If shapes 2 and 3 look like the same trick to you, well spotted: they are. Both are leases, meaning ownership that expires instead of being released. The difference is the grain. Shape 2 leases *many existing work rows*, stamped in batches and finalized once; shape 3 leases *one identity*, in a row that exists only to be owned and is renewed on a heartbeat for as long as its holder lives. Same physics, different lifecycles, and the code for the two looks different enough that I keep them apart. Fence, lease column, one-row lease. That's the toolbox. Notice what's *not* in it, because that's where the tour starts. ## SKIP LOCKED: the famous pattern we don't use Every "Postgres as a queue" article leads with this one, so let's give it its due. The classic job queue is a single table and one magic clause: ```sql DELETE FROM jobs WHERE id IN ( SELECT id FROM jobs ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 10 ) RETURNING *; ``` `FOR UPDATE` locks the rows you're claiming. `SKIP LOCKED` tells competing workers to *skip past* locked rows instead of waiting in line behind them. Claim, work, and delete in one transaction, and you have a broker-free job queue. It's a genuinely great pattern. Elarion uses it in exactly zero places. Not out of ignorance, but because of a constraint the single-table queue can't survive: **fan-out**. A `SKIP LOCKED` queue works because "claimed" and "done" are properties of *the row*. That's fine while every job has exactly one consumer. But an application *event* like "an order was placed" has many consumers: send the invoice, update analytics, call a webhook. Now "done" isn't one flag anymore. The invoice sender being done says nothing about the webhook. One table can't carry that; the design has to split. Elarion's ADR compresses this into a parenthetical: > With exactly one consumer, claiming + working + marking in one transaction does work — that is a single-table job queue, `FOR UPDATE SKIP LOCKED`. Fan-out pub/sub is what forces the two-ledger split. We'll meet the two ledgers in a moment. First, the simplest shape in action. ## Scheduled jobs: whoever inserts the row wins Run one app on five nodes and every node's scheduler wakes up at 02:00 wanting to run the nightly import. You need exactly one to proceed. Quartz answers this with database locks that serialize *the schedulers*. Elarion instead uses **the fence**: before executing, every node tries to insert a claim row for this exact occurrence of this exact job: ```sql title="the claim (shape #1, the fence)" INSERT INTO elarion_scheduler_claims (job_name, occurrence_utc, claimed_at_utc) VALUES ($1, $2, $3) ON CONFLICT (job_name, occurrence_utc) DO NOTHING; ``` The primary key is `(job_name, occurrence_utc)`, so only one insert can succeed. That node runs the job. Everyone else gets zero rows back, notes "claimed elsewhere," and moves on. No leader, no scheduler service, no lock table with its own lifecycle. Ten nodes claiming a once-per-second job costs ten tiny inserts per second; the database doesn't notice. One wrinkle, included because it's instructive. Cron jobs share a wall-clock instant across nodes ("02:00:00"), so the fence works as-is. Fixed-rate jobs don't: each node computes its own next due time, so two nodes would insert *different* timestamps, and both fences would "win." For that case the claim first takes a **transactional advisory lock**, which is a lock on a *name* rather than a row, held only until commit: ```sql SELECT pg_advisory_xact_lock(hashtextextended($1, 0)); ``` It's keyed per job, so unrelated jobs never wait on each other. And it's one of only three advisory-lock sites in the whole framework. Worth remembering: **advisory locks are the exception tool, not the workhorse.** ## The cache is an UNLOGGED table The objection to Postgres-as-cache is real: every write normally goes through the write-ahead log (WAL), the journal that makes data crash-safe and feeds replication. A cache doesn't need any of that, and paying for it hurts. Postgres has had the answer since 9.1: an `UNLOGGED` table skips the WAL entirely. Writes get cheap, replicas carry nothing, and the price is that the table is wiped after a crash. For a cache, that price is *precisely nothing*: > The worst case is a cold repopulate, never data loss. In Elarion it's a one-flag default on top of Microsoft's `Caching.Postgres` package: ```csharp title="Elarion.Caching.PostgreSql (excerpt)" {6} services.AddDistributedPostgresCache(options => { // UseWAL = false makes the table UNLOGGED — cache writes skip the // write-ahead log and the table is truncated on crash: fine for a cache. options.TableName = "elarion_cache"; options.CreateIfNotExists = true; options.UseWAL = false; configure(options); }); ``` Is Redis faster per operation? Yes. It's also *"a second datastore to provision, secure, back up, monitor, and keep available."* Behind an in-memory first-level cache, per-operation latency of the second level is rarely what you're actually buying. (There's even a test asserting the table really is `UNLOGGED`. If you claim it, enforce it.) ## LISTEN/NOTIFY: pub/sub with commit semantics Postgres has a built-in publish/subscribe mechanism, and it's criminally underused. Any connection can `LISTEN` on a named channel; `NOTIFY` delivers a small payload to every current listener. Elarion uses it to tell all nodes "this setting changed" and to route live events to whichever node holds a browser's SSE connection. *"No broker, no Redis."* The killer feature is one nobody's Redis setup has: **a NOTIFY fired inside a transaction is delivered only if that transaction commits.** Fire it on the same connection as your writes, and your "cache invalid!" signal can never race ahead of the data change it announces, and never survives a rollback of it either: ```csharp title="transactional notify: announced only on commit" await dbContext.Database.ExecuteSqlRawAsync( "SELECT pg_notify({0}, {1})", [options.ChannelName, payload], ct); ``` Two production realities the happy-path demos skip: - **Missed notifications are gone.** Postgres doesn't queue for listeners that weren't connected. So after any reconnect, assume you missed everything: re-read the state you care about. A spurious re-read is cheap; a missed invalidation is a support ticket. - **Dead connections look idle.** A listener blocked on a half-open connection (NAT timeout, failover without a goodbye) would wait forever. The fix is mundane: if nothing arrives for a while, run `SELECT 1`. A dead connection throws; reconnect and re-read. One hard limit to design around: payloads cap at about 8 KB. Send *ids and hints*, never documents. Elarion's settings payload says *which key changed*, and everyone re-reads the value through the store. ## The outbox and its inbox The problem the outbox solves, in one sentence: you commit an order to the database, then try to publish "order placed", and the publish fails, leaving the order saved but the event lost forever. The fix is to make the event part of the same transaction. Write it into an `outbox` table alongside the business change; both commit or neither does. A background worker then delivers pending events. Two details in Elarion's version are worth stealing. **Claiming work is shape #2, the lease column.** The delivery worker doesn't lock rows. It picks candidate ids, then *stamps* them with its id and an expiry, using an update that only touches rows nobody else holds: ```csharp title="claiming a delivery batch (shape #2, the lease column)" .Where(m => candidateIds.Contains(m.Id) && m.ProcessedOnUtc == null && (m.LockedUntilUtc == null || m.LockedUntilUtc < now)) .ExecuteUpdateAsync(s => s .SetProperty(m => m.LockId, lockId) .SetProperty(m => m.LockedUntilUtc, leaseUntil), ct) ``` If the worker crashes mid-batch, no cleanup is needed: its stamps expire, and another worker stamps over them. Every "mark delivered" is guarded by the same `lockId`, so a worker whose lease ran out can't finish someone else's claim. And the claim query runs against a **partial index**, an index with a `WHERE processed_on_utc IS NULL` filter, so it only ever contains the pending tail, never the ever-growing archive. **The inbox is the second ledger.** Delivery is at-least-once. Retries mean a consumer *will* eventually see the same event twice, so each consumer keeps its own record of what it has processed. Put plainly: the outbox is the sender's checklist ("did I durably record this?"), and each consumer keeps its own checklist ("did *I* handle this?"). And because of fan-out, the consumer's checklist must be keyed by *(consumer, message)*, not by message alone. This is exactly the picture from the diagram above: | | Outbox row | Inbox row | |---|---|---| | One per | **event** | **(event, consumer)** | | Written by | publisher's transaction | the **consumer's own** transaction | | Says | "this event is durably recorded" | "**this consumer** processed this message" | The inbox row commits atomically with the consumer's own writes, so "I processed it" and "its effects exist" can never disagree. And mechanically? The inbox is shape #1 again: an `INSERT … ON CONFLICT DO NOTHING` into the idempotency table. The fence rides again. That was the densest stretch of this post, so step through the whole dance instead of rereading it. Every click advances one event, and you can crash the worker at any moment it holds the lease. Try it twice: crash before consumer A's delivery and after it, and watch how the second run needs A's checklist to shrug off the duplicate: ## Leader election is one row Now the shape that surprises people. Some things should run on exactly one node at a time; Elarion homes certain actors this way. The textbook answer involves consensus protocols, quorums, ZooKeeper. The one-to-ten-node answer is **shape #3, a lease**: temporary ownership that must be renewed and can be taken over once it lapses. The entire membership protocol is one table row: ```csharp title="acquire, renew, and steal in one upsert (shape #3)" return $"INSERT INTO {table} AS lease ({roleCol}, {ownerCol}, {addressCol}, {expiresCol}) " + "VALUES ({0}, {1}, CAST({2} AS character varying), {3}) " + $"ON CONFLICT ({roleCol}) DO UPDATE SET {ownerCol} = EXCLUDED.{ownerCol}, " + $"{addressCol} = EXCLUDED.{addressCol}, {expiresCol} = EXCLUDED.{expiresCol} " + $"WHERE lease.{ownerCol} = EXCLUDED.{ownerCol} OR lease.{expiresCol} <= {{4}}"; ``` Read the `WHERE` clause slowly; it's the whole algorithm. Update the row **if it's already mine** (renew) **or if it has expired** (take over). One affected row means "I hold it." Every node just runs this on a heartbeat. *"The row is the whole membership protocol."* Words only get this so far. Try it: crash the holder and watch what happens; then restart it and stop a holder gracefully instead: Three details turn "clever" into "safe," and the simulation shows all of them: - **Fail closed.** When you crash the holder, its row stays owned until expiry. During that gap *nobody* acts: single-homed work pauses, and pending events queue in the outbox. Nothing is lost; it's late. That's the honest cost, bounded by the lease duration (30 s by default; the demo compresses it). - **Graceful shutdown is instant.** Release just sets the expiry to *now*, and the next heartbeat from any node takes over immediately. - **One clock.** Expiry timestamps are written and compared using the *application's* clock, passed as parameters; the database clock is never consulted. And each node locally considers its own hold ended a safety margin *before* the stored expiry, so the old holder always stops acting before a new one can legitimately start. Two holders is the failure mode this design refuses to have. The docs add the guardrail that keeps it honest: *"A role lease is not a distributed-lock API. Do not create one per tenant, record, or work item."* A handful of stable roles, not a lock service. ## The supporting cast, briefly The same shapes keep reappearing. **Idempotency keys** (the guarantee that a request sent twice doesn't *happen* twice) are the fence on `(operation, scope, owner, key)`: two nodes processing the same request contend on the same row, one insert wins, the other replays the stored result. A scoped `lock_timeout` even turns "blocked behind an in-flight duplicate" into a fast HTTP 409 instead of a hung request. **Bulk ingestion** uses `COPY … FROM STDIN (FORMAT BINARY)`, roughly ten times faster than multi-row inserts once you're past ~10k rows. Even **blobs** ride along in a `bytea` column, for the tier where that's perfectly fine. ## Where this stops Honesty section. Every default above is sized for that 1–10-node tier, and each sits behind a small interface. Past the tier, you swap the implementation (Redis behind the cache, a broker behind the event fan-out) without touching call sites. The known edges: NOTIFY's 8 KB cap and its amnesia about absent listeners; scheduler claims are at-most-once (a winner that crashes mid-run doesn't re-run); lease failover has its honest gap. And "one Postgres" is about *composition*, not magic. TimescaleDB or pgvector extend what the one database can do; they don't multiply the number of systems you babysit. **The rule of thumb: every stateful system must pay rent in backups, monitoring, security reviews, and 3 a.m. pages. Before adding one, ask what it does that a table, a constraint, an upsert, or a NOTIFY on the database you already run cannot.** At this tier, the answer is usually "nothing yet." Make the infrastructure diagram earn its boxes.