DEV Community: Jatin Jain Saraf

Turning Your Blockchain Indexer Into a Paid API Without Slowing Down Your Explorer

Jatin Jain Saraf — Thu, 08 Oct 2026 03:30:41 +0000

Turning Your Blockchain Indexer Into a Paid API Without Slowing Down Your Explorer

Your indexer has done the hard part. Every block, transaction, event and balance change on the chain is decoded and stored in Postgres, with indexes that make your explorer fast.

Sooner or later, someone asks the obvious question: can we get this data through an API? Wallets want balances. Analytics teams want transaction history. Trading bots want events the moment they land. Some will pay for it.

The first version is usually easy: point a few REST endpoints at the same database the explorer uses, hand out API keys, done.

Then a single customer writes a loop that pages through every transaction for a busy contract address, ten requests at a time, all day. The explorer's p99 latency climbs. The indexer's writes start competing with those reads for disk and memory, and the indexer falls behind the chain. Now your explorer shows stale blocks, every API customer gets stale data, and none of it shows up as an error anywhere. (It's the same slow-burn pattern I described in Your Blockchain Indexer Is Fine Until It Isn't, this time triggered from outside.)

Exposing indexer data is not an endpoint problem. It's an isolation problem. This post walks through a design that serves blockchain data to anyone, metered by credits and plans, at low latency, without letting that traffic touch the two things you can't afford to slow down: ingestion and your explorer.

The Kitchen and the Counter

A restaurant doesn't let customers walk into the kitchen and cook whatever they like. Customers order at the counter, from a menu, and every dish on the menu has a price that reflects how much work it takes.

Map that onto your system:

The kitchen is your primary database and indexer. It has one job: keep up with the chain. Nobody outside eats here.
The counter is your API gateway. It checks who you are, what plan you're on, and whether you've got credits left. It takes orders. It doesn't cook. (If you're choosing what that gateway should be, I compared the options in Demystifying API Gateway Patterns.)
The menu is a fixed set of endpoints, served by a query service that knows how to cook each dish one way. No arbitrary queries. If it isn't on the menu, you can't order it.
The prices are credits. A balance lookup is cheap. A page of event history costs more. The price reflects what the request costs you, not just that it happened.

Every section below is one part of that restaurant.

The Architecture at a Glance

chain ──▶ [indexer] ──writes──▶ [primary DB]
                                     │ streaming replication
                  ┌──────────────────┼──────────────────────┐
                  ▼ ▼ ▼
        [explorer replicas] [paid API replica pool] [free API replica pool]
                  ▲ ▲ ▲
             [explorer] └──────┬───────────────┘
                                            │
                                   [API query service]
                                   endpoint + cursor validation,
                                   query building, per-plan pools,
                                   timeouts, retries, response shaping
                                            ▲
                                      [API gateway] ◀──── clients + API keys
                                   auth, rate limit, credits,
                                   cache, routing
                                            │
                                 [Redis: limits, credits, cache]

Four rules hold this together:

The primary is reserved for ingestion and tightly controlled internal workloads. External API and explorer traffic never reads it.
The explorer and the API never share a replica. An API spike can't slow the explorer, and vice versa.
Every request passes the gateway first. Auth, rate limit, credit check and cache happen before a single query runs.
The gateway never talks to the database. A separate query service owns every query: it validates the endpoint and cursor, builds the query, picks the replica pool for the caller's plan, enforces timeouts and shapes the response. The gateway decides whether a request runs. The query service decides how.

That last split keeps the gateway small, fast and stateless, and puts everything that touches SQL in one place you can reason about.

Layer 1: Isolate Reads on Dedicated Replica Pools

Postgres streaming replication gives you read-only copies of the primary. Under healthy conditions they may be only milliseconds or seconds behind, but that lag depends on the workload: how much WAL the indexer generates, the network, and how busy the replica is replaying it while also serving queries. Give the API its own replica pools, separate from the explorer's.

Why this matters in production: read traffic from the API now can't evict the explorer's hot pages from memory or compete with the indexer's writes for disk on the primary. A badly behaved customer can only hurt the replica pool for their plan.

Two settings decide how that replica behaves under pressure, and they pull in opposite directions:

hot_standby_feedback = on makes the replica tell the primary which row versions its running queries still need. Your API queries stop getting cancelled, but long-running queries on the replica can now prevent vacuum on the primary from cleaning up those row versions. Dead rows pile up and the tables your indexer writes to start to bloat. That's exactly the coupling you were trying to remove.
hot_standby_feedback = off with a bounded max_standby_streaming_delay keeps the primary clean. The cost: when replaying WAL conflicts with a query on the replica, Postgres waits up to that delay and then cancels the query with canceling statement due to conflict with recovery (SQLSTATE 40001).

For a public API, I'd generally prefer the second option, provided the query service is prepared to handle cancelled reads. Catch that error, retry once on another replica in the same pool, and if that also fails, return a clean 503 with Retry-After instead of an unexplained 500. Pair it with short statement timeouts (Layer 5): a public API shouldn't be running queries long enough to conflict often in the first place. If it is, that's a menu problem, not a replica setting problem.

Replicas also lag. A replica serving a heavy query load can fall behind the primary, and an API that silently serves old data is worse than one that admits it. I've written about replication lag in blockchain indexers separately. For the API, the rule is simple: tell the client how fresh the data is. Return the latest indexed block height with every response. (The Replication & High Availability module in PostgreSQL In-Depth covers how streaming replication, lag and standby conflicts work underneath.)

X-Indexed-Height: 18452231
X-Indexed-At: 2026-10-08T09:14:02Z

Clients can then decide for themselves whether data that's two blocks old is fine for their use case.

Layer 2: Serve From Read Models, Not Raw Tables

The tables your indexer writes are shaped for ingestion. The API needs tables shaped for the questions customers ask. The most common one by far is "show me the transactions for this address, newest first."

Have the indexer maintain a read model built for exactly that question, as part of ingestion:

CREATE TABLE address_transactions (
  address bytea NOT NULL,
  ledger_version bigint NOT NULL,
  tx_hash bytea NOT NULL,
  PRIMARY KEY (address, ledger_version)
);

Here ledger_version assumes the chain gives every transaction its own unique, increasing version number, as Aptos-style ledgers do. Block height is not a substitute: a block holds many transactions, so (address, block_height) would collide as soon as an address appears twice in one block. If your chain has no per-transaction version, use a composite position such as (block_height, tx_index) in the key instead. And since one transaction can touch the same address more than once (sender and receiver, or several events), have the indexer insert with ON CONFLICT DO NOTHING, so repeated observations of the same (address, ledger_version) pair collapse into one read-model row.

The primary key index on (address, ledger_version) keeps each address's history in index order, so one address's newest transactions are always a short index range scan, whether the address has 10 transactions or 10 million. The Indexes module in PostgreSQL In-Depth explains why composite key order decides which queries an index can serve.

Then serve history with keyset pagination , never OFFSET:

SELECT ledger_version, tx_hash
FROM address_transactions
WHERE address = $1
  AND ledger_version < $2 -- the cursor from the previous page
ORDER BY ledger_version DESC
LIMIT $3;

OFFSET 50000 makes Postgres read and throw away 50,000 rows before returning the page you asked for, so every page gets slower than the last. A cursor jumps straight to the right spot in the index, so page 1,000 costs the same as page 1. (The pagination post goes deeper on this.) Return the cursor as an opaque string, so clients can't build their own and you can change the format later.

Rule for the whole menu: every endpoint must map to an index-backed access path with a bounded result size. No free-form filters, no "sort by any column", no unbounded block ranges. An events endpoint takes a maximum window of blocks per call. If a customer needs more, they page.

Layer 3: Cache by Finality

Blockchain data has a property most application data doesn't: once a block is considered final by the chain's consensus and finality rules, its canonical data can be treated as immutable for caching. A finalized transaction from last year can be treated as immutable forever.

What "final" means differs a lot between chains. Some finalize a block within seconds and never revert it. Others only become safe after a number of confirmations, and recent blocks can still be reorganized. Define finality per chain, and expose it: the indexer should track the latest finalized height separately from the latest indexed height, and the API should only treat data at or below the finalized height as immutable.

That splits your caching strategy cleanly in two:

Data	Changes?	Cache policy
Finalized blocks, finalized transaction-by-hash responses, historical events	Never	Usually safe to cache for a long time, at the CDN and in Redis
Latest block, current balances, recent history above the finalized height	Every block, and can reorg on some chains	Short TTL, about one block time
Pending, unconfirmed or not-found lookups	Constantly	Don't cache, or cache very briefly

That last row matters for transaction-by-hash: a lookup for a transaction that's still pending, or that hasn't been indexed yet, must not be cached as "not found" for an hour.

Why this matters in production: most traffic on a blockchain API is for things that already happened. If finalized responses are served from a CDN or Redis, a large share of requests never reach a replica at all, and those cache hits are where the low latency comes from.

Watch out for one failure mode. When a popular address's cached balance expires, a thousand clients can miss the cache at the same moment and send a thousand identical queries to the replica. Use request coalescing (one in-flight query per key, everyone else waits for its result). The cache stampede module in Redis In-Depth covers the patterns.

Layer 4: Plans, Credits and the Gateway

Now the part that makes this a product: who can call what, how often, and how much.

Two limits, not one

A plan needs two separate limits, because they protect against two different problems:

Rate limit (requests per second): protects your infrastructure from bursts. A customer with plenty of monthly credits still can't send 5,000 requests in one second.
Credit quota (per month): protects your business. It's what the customer is paying for.

Credits priced by cost

Charging "one request = one credit" is unfair to you. A balance lookup and a 100-row event scan are not the same amount of work. Price each endpoint by what it actually costs to serve:

Endpoint	Credits	Why
`GET /blocks/latest`	1	One row, almost always cached
`GET /tx/{hash}`	1	Point lookup; finalized results are immutable and highly cacheable
`GET /accounts/{addr}/balance`	2	Point lookup, short cache life
`GET /accounts/{addr}/transactions` (up to 100)	5	Index range scan
`GET /events?from=&to=` (bounded window)	20	Larger scan, rarely cached

These numbers are illustrative. Derive your own from measured query costs, and keep the price fixed per endpoint and page size, decided before the query runs. Customers can then predict their bill, and you never have to run a query just to find out what to charge for it.

Plans

	Free	Pro	Enterprise
Requests per second	5	50	Custom
Monthly credits	100K	10M	Custom
Max page size	25	100	100+
History depth	Recent only	Full	Full
Real-time streams	No	Yes	Yes
Replica pool	Shared free pool	Paid pool	Dedicated replica

Again, these numbers are for illustration. The structure is what matters: plans differ in more than just credits. Page size, history depth and which replica pool you land on are all levers.

Checking limits without touching the database

The gateway runs this check on every request, so it can't afford a database write per call. Keep both counters in Redis and make the check atomic with a Lua script, so that two simultaneous requests can't both spend the last credit:

-- KEYS[1] = per-second window key, e.g. rl:{keyId}:{unixSecond}
-- KEYS[2] = billing-period credits key, e.g. cr:{keyId}:{yyyymm}
-- ARGV[1] = requests/sec limit, ARGV[2] = cost of this request
-- ARGV[3] = period quota, ARGV[4] = unix time the key may expire
local reqs = redis.call('INCR', KEYS[1])
if reqs == 1 then redis.call('EXPIRE', KEYS[1], 2) end
if reqs > tonumber(ARGV[1]) then
  return {0, 'rate_limited'}
end

local used = tonumber(redis.call('GET', KEYS[2]) or '0')
if used + tonumber(ARGV[2]) > tonumber(ARGV[3]) then
  return {0, 'quota_exhausted'}
end

used = redis.call('INCRBY', KEYS[2], ARGV[2])
if used == tonumber(ARGV[2]) then
  redis.call('EXPIREAT', KEYS[2], ARGV[4])
end
return {1, tonumber(ARGV[3]) - used}

The whole script runs as one atomic step inside Redis, so there's no race between "check the balance" and "spend the credits." The atomic counters and rate limiters module explains why that matters.

Some honest notes about this script, because each one is a product decision, not just an implementation detail:

Rejected requests count toward the rate window. The counter is incremented before the limit check, so a client hammering at 10x its limit keeps its own window full, and a request that passes the rate check but fails the quota check has still used a rate slot. That's deliberate here: it makes ignoring 429s pointless. If your product should count only admitted requests, decrement on rejection or use a different limiter design.
The credits key expires at an absolute time, not after a duration. The gateway passes the end of the billing period plus your reconciliation grace window (say, a few days) to EXPIREAT. A relative TTL set on the first request of the month would expire at a different moment for every customer, possibly before billing has read the final count.
It's a fixed window per second, so a client can fit up to twice its limit into the instant where one second ends and the next begins. If that matters for your infrastructure, use a token bucket instead. The rate limiting and quotas module compares the algorithms.

Redis admits, Postgres bills

Credits represent money, so be precise about who owns what. Redis is the authoritative low-latency counter for admission: it decides, in microseconds, whether this request runs. The durable billing system in Postgres remains the source of truth for what a customer used and owes.

Every minute or so, a background job reads the Redis counters and records usage in a Postgres billing table. Make that sync idempotent (keyed by API key and time window), so a retried sync never double-charges. Same idea as idempotency keys, applied to your own billing.

Then plan for the obvious question: what happens if Redis loses the counters? A failover or restart without persistence resets usage to zero, and every customer suddenly has a full quota. Two safeguards:

When a credits key is missing mid-period, seed it from the last usage recorded in Postgres before admitting the request, rather than starting from zero.
Reconcile the two regularly. The reconciliation gap is bounded by the sync interval, assuming the billing sync itself is durable and complete. A failed or delayed sync widens it, so alert when a sync is late. Don't refund or bill from Redis directly.

The API key's plan itself (limits, quota, replica pool) should be cached in the gateway's memory for a short time, so authenticating a request costs zero database queries. Store only a hash of each API key, the same way you'd store a password, and log the key's ID, never the key.

Tell clients where they stand

Every response should include the limits, and every rejection should say why and when to retry:

RateLimit-Policy: "per-second";q=50;w=1
RateLimit: "per-second";r=37;t=1
X-Credits-Remaining: 8421337
X-Credits-Cost: 5

And when a request is rejected:

HTTP/1.1 429 Too Many Requests
RateLimit-Policy: "per-second";q=50;w=1
RateLimit: "per-second";r=0;t=1
Retry-After: 1

{"error": "rate_limited"}

RateLimit-Policy describes the limit (a quota q of 50 per window w of 1 second). RateLimit reports the current state (r requests remaining, resetting in t seconds). These come from the IETF HTTPAPI working group's RateLimit header fields draft. It's still a draft, not a published RFC, and earlier draft versions used separate RateLimit-Limit, RateLimit-Remaining and RateLimit-Reset fields, so you'll see both those and the older X-RateLimit-* convention in real APIs. Pick one, document it, and keep it stable. Credits are your own application-level concept, so they keep their own X-Credits-* headers.

Clients that can see their limits back off on their own. Clients that can't just retry harder.

Layer 5: Bulkheads So Free Users Can't Hurt Paying Ones

Plans aren't just billing. They're also a way to keep different kinds of traffic from interfering with each other.

The query service routes each tier to its own replica pool, and connects to each with that tier's own database role and timeout:

CREATE ROLE api_free LOGIN;
ALTER ROLE api_free SET statement_timeout = '2s';

CREATE ROLE api_paid LOGIN;
ALTER ROLE api_paid SET statement_timeout = '5s';

Role-level settings apply when a connection logs in. If you use PgBouncer, give each tier its own pool that logs in as that tier's role, and keep each pool small. A pool's configured connection limit is a hard ceiling on how many database sessions that tier can have executing work at the same time, no matter how many requests arrive. Pool sizing has its own traps, covered in Connection Pooling in the Serverless Era and the Connection Pooling Failure Modes module.

Why this matters in production: a viral free-tier app that suddenly sends 100x traffic fills the free pool and starts getting 429s. Paid customers, on a different pool and replica, don't notice. This is the bulkhead pattern from the three layers of failure isolation, applied to pricing tiers.

When the system as a whole is under pressure (replica lag above a threshold, CPU pinned), shed load in plan order : free tier first, then lower paid tiers. Return 503 with Retry-After, and keep the explorer and the indexer out of it entirely. The backpressure and load shedding module covers how to pick those thresholds.

Layer 6: Don't Make Clients Poll for New Blocks

A trading bot that wants every new event will poll /events?from=latest once per block time. A thousand bots doing that means a thousand identical queries per block, all returning the same rows.

Flip it around. The indexer already knows the moment a block is indexed. Have it publish new blocks and events once, to a fan-out layer (Redis Streams, a message broker, or your own WebSocket service), and push them to subscribers. The indexed data is fetched once for the fan-out path, instead of once per subscriber.

Two things to get right:

Charge for streams per message or per subscription , not per request, or a busy contract's event stream becomes free unlimited data.
Let clients resume. Every message carries its block height. When a client reconnects, it sends the last height it saw and gets the gap filled from the API, so a dropped connection never means missed events.

Layer 7: Bulk Data Doesn't Belong in the API

Some customers don't want an API. They want everything: every transaction since genesis, to load into their own warehouse. If the only tool you give them is a paginated endpoint, they will page through your entire chain, one request at a time, for days.

Give them a better door: periodic exports of finalized data (for example, daily Parquet files per table) in object storage, sold as their own plan. Exports are produced once, off a replica, during quiet hours, and downloaded any number of times at no cost to your databases.

Observability: Know Who Is Expensive

You can't price or protect what you can't see. Track, per API key:

Requests and credits spent, by endpoint
Cache hit rate
p50/p99 latency and error rate
Which keys are hitting rate limits or timeouts

Tag queries so a slow one on the replica can be traced back to a customer. Postgres's application_name connection setting, or a comment in the SQL text, can carry the key ID (never the key). When a replica slows down, you want to know in seconds whether it's one customer or everyone.

A Checklist Before You Launch

Does any external API or explorer traffic still read the primary, or share a replica pool across the two?
Does the gateway stay out of the database, with every query owned by the query service?
Does every endpoint map to an index, with a bounded result size and cursor pagination?
Is "final" defined per chain, and is only finalized data cached as immutable?
Are replica query cancellations retried and turned into clean 503s?
Are rate limits and credits checked in Redis, atomically, with zero database queries per request?
Is the billing sync idempotent, and can lost Redis counters be re-seeded from Postgres?
Does each tier have its own pool, role and statement timeout?
Does every response include the indexed height and remaining credits?
Is there a plan for real-time data that isn't polling, and for bulk data that isn't pagination?

The Takeaway

A blockchain indexer that serves only your explorer is a database. One that serves anyone, on plans, is a product, and a product needs a counter in front of the kitchen.

The design comes down to one idea: external traffic should only ever reach things that are built to absorb it. Replicas instead of the primary. A query service instead of SQL in the gateway. Read models instead of raw tables. Caches instead of replicas, wherever the data is final. Redis instead of the database for every limit check, with Postgres still keeping the books. Separate pools so one tier's spike stays its own problem.

Get that right, and selling your data stops being a risk to the explorer that made it worth buying.

Your Postgres Indexes Can Break Without a Single Line of Code Changing

Jatin Jain Saraf — Wed, 07 Oct 2026 03:47:27 +0000

Your Postgres Indexes Can Break Without a Single Line of Code Changing

The ticket says a customer can't log in. Support swears the account exists. You run the query yourself:

SELECT * FROM users WHERE username = 'jo-ann';

Zero rows.

You open the admin panel, which happens to page through the table with a sequential scan, and there she is. Same username, same spelling, no trailing spaces.

A week later a second problem shows up: two rows with the same username, in a column with a UNIQUE index on it. The constraint that was supposed to make that impossible simply let it happen.

Nobody deployed anything. No migration ran. The only change in the last month was a routine one: the Postgres container moved to a newer base image, or the replica was rebuilt on a newer OS, or the database was restored onto a fresh host with different locale data.

That routine change is the whole story. The operating system changed the collation rules used to sort text, and B-tree indexes using that collation were built under the old rules.

A Dictionary Sorted by One Alphabet, Searched by Another

A B-tree index is a dictionary. The entries are sorted, and finding a word means opening to the middle, deciding "my word comes before this page" or "after this page," and repeating until you land on the right page.

That decision, "before or after," is the only thing the search depends on. Postgres doesn't scan the dictionary. It trusts the order. (If you want the full layout of root, internal and leaf pages, Module A-5: Index Internals in the PostgreSQL In-Depth course walks through it. It's the same leaf-page structure that makes random UUID primary keys tax every insert.)

Now imagine the dictionary was printed under one set of alphabet rules, where hyphens are ignored so jo-ann sits next to joann. Years later, someone hands the reader a new rulebook where a hyphen sorts before every letter. The pages haven't moved. Every word is still printed. But the reader, following the new rules, decides jo-ann must come before joan, flips to that section, doesn't find it, and confidently reports that the word isn't in the dictionary.

That is the failure mode: the index pages were ordered under the old rules, while the comparison rules used to navigate them have changed.

Why this matters in production: Postgres does not detect this during normal operation. There is no error and no failed query. Lookups quietly miss rows, and unique indexes quietly accept duplicates, because both operations rely on the B-tree walking to the right leaf page.

Where the Sort Order Actually Lives

For a database using a libc collation such as en_US.UTF-8, which is still what most installs and container images default to, Postgres doesn't carry its own idea of alphabetical order. It delegates locale-aware comparison to the operating system's C library. On a glibc-based Linux system, that means glibc's collation implementation.

So "is jo-ann less than joan?" is answered by whatever version of glibc is installed on the machine running Postgres at that moment. Not the version that was installed when the index was built.

Most glibc upgrades change little or nothing in collation. The famous exception is glibc 2.28, released in 2018, which brought its locale data in line with a newer ISO 14651 standard and changed the relative order of a large number of strings, especially ones containing punctuation and spaces. The PostgreSQL wiki's test case is a one-liner you can run on any Linux box:

( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort

Those two strings come out in a different order depending on which side of 2.28 you're on. That change shipped in Debian 10 (buster), Ubuntu 18.10, and RHEL 8, which means a lot of databases crossed that line during routine OS upgrades.

Postgres has no way to know which glibc changes matter and which don't. So the safe assumption is that any change to the C library under an existing data directory might have changed the sort order.

Reproducing It

You can't easily swap glibc under a running server on a laptop, so I simulated it on PostgreSQL 18.6. I created two ICU collations that differ in one rule, whether punctuation is ignored, which is the same kind of rule glibc 2.28 changed:

CREATE COLLATION old_rules (provider = icu, locale = 'en-u-ka-shifted');
CREATE COLLATION new_rules (provider = icu, locale = 'en');

Same four strings, sorted under each:

old_rules: ab a c a-c ad
new_rules: a c a-c ab ad

Under the old rules, a-c is treated roughly like ac, so it lands after ab. Under the new rules, the hyphen and the space sort before letters, so a-c jumps ahead of ab.

Then I built a real table: 200,000 usernames (a third of them hyphenated) plus jo-ann, joan and johnny, with a unique index, all under old_rules:

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username text COLLATE old_rules NOT NULL
);
-- ... load 200,003 rows ...
CREATE UNIQUE INDEX users_username_key ON users (username);

At this point everything is healthy. amcheck passes and WHERE username = 'jo-ann' returns one row.

Then the "OS upgrade": I pointed the column and the index at new_rules directly in the system catalog. The index pages on disk are byte for byte what the old rules built. Only the comparison function changed. That's precisely the situation after a glibc upgrade.

Don't try this on a real database. Editing pg_index and pg_attribute by hand is how you create corruption, not how you fix it. I did it only on a disposable local test cluster, to fake the broken state an OS upgrade leaves behind. It is not part of any remediation. The real fix is further down.

Symptom 1: the row vanishes from indexed lookups

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE username = 'jo-ann';


                  QUERY PLAN
----------------------------------------------
 Index Scan using users_username_key on users
   Index Cond: (username = 'jo-ann'::text)


SELECT * FROM users WHERE username = 'jo-ann';


 id | username
----+----------
(0 rows)

Turn off index scans and read the table directly, and it's right there. (Those enable_* switches are planner debugging tools, covered in Module A-6: Query Planning. Never set them globally in production.)

SET enable_indexscan = off; SET enable_bitmapscan = off;
SELECT * FROM users WHERE username = 'jo-ann';


   id | username
--------+----------
 200001 | jo-ann

This is the "customer can't log in" ticket. The data is fine. The path to the data is wrong.

Symptom 2: the unique index accepts a duplicate

INSERT INTO users (username) VALUES ('jo-ann') RETURNING id, username;


   id | username
--------+----------
 200004 | jo-ann

INSERT 0 1

A unique index enforces uniqueness by walking to the leaf page where the new key belongs and checking whether it's already there. Under the new rules it walks to a different leaf, finds nothing, and inserts. Every guarantee in Constraints and Data Integrity that's backed by an index is only as good as the index's sort order.

And the duplicate hides itself. Ask through the index and you get one:

 index_says
------------
          1

Ask the table and you get two:

 table_says
------------
          2

Why this matters in production: this is the dangerous one. Missing lookups are visible and get reported. Duplicates in a column your application assumes is unique are silent, and they spread: two accounts, two payment records, a join that now returns double. By the time anyone notices, the bad rows have been written for weeks.

Symptom 3: the fix fails too

The obvious move is to rebuild the index:

REINDEX INDEX CONCURRENTLY users_username_key;


ERROR: could not create unique index "users_username_key_ccnew"
DETAIL: Key (username)=(jo-ann) is duplicated.

The rebuild sorts everything under the new rules, notices the duplicate the broken index let in, and refuses. Worse, a failed REINDEX CONCURRENTLY leaves its half-built index behind. Run the detection query below and you'll see users_username_key_ccnew sitting there, marked invalid, still costing write overhead on every insert until you drop it. It's the same invalid-index trap that catches failed CREATE INDEX CONCURRENTLY runs, covered in Zero-Downtime Schema Migrations.

Where This Bites in Real Systems

The common thread is any path that keeps the existing index files while changing the C library underneath them :

Changing the Docker base image. Bumping postgres:15-bullseye to postgres:15-bookworm keeps the Postgres major version and your data volume, and swaps glibc from 2.31 to 2.36. Moving a data directory between an Alpine image (musl, which doesn't do locale-aware sorting) and a Debian image is a much bigger jump. If your Postgres lives on a Docker volume, the volume outlives the image, which is exactly what makes this easy to miss.
Physical replicas on a different OS. Streaming replication ships the primary's index pages as-is. A replica running a different glibc answers queries against those pages with its own sort rules. It returns wrong results while it's a replica, and if it's promoted, it starts writing new entries into indexes it misreads.
pg_upgrade combined with an OS upgrade. pg_upgrade is fast precisely because it reuses the index files. If the new server runs on a newer OS, those files are now walked with new rules.
Restoring a file-level backup onto a newer host. Same mechanism as above.

Safer migration paths: pg_dump/restore and logical replication rebuild indexes on the target, under the target's rules, rather than carrying the existing index files across. That's one strong argument for logical replication in OS migrations: you pay for it in setup and time, and in exchange the collation problem disappears. I covered the full playbook for that kind of move in Cloud-to-Cloud Database Migration.

Range partitioning on a text key has the same exposure, by the way. Partition bounds are compared with the same collation, so rows can be routed to a different partition than the one that "should" hold them under the old rules.

Detection

The warning you probably never saw

PostgreSQL 18 records a database-level collation version and checks it against the version currently provided by the system on every new connection. Here's what it looks like after a move from Debian bullseye to bookworm (the message format is exactly what Postgres prints; the version numbers are illustrative, since my laptop reproduction reports different ones):

WARNING: database "app" has a collation version mismatch
DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE app REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

That's a good warning. The problem is where it goes. It's sent to the connecting client, and your application's database driver almost certainly discards it. Look for it in a psql session after any OS change. It may be the only early signal you'll get.

Find the indexes at risk

Indexes whose indexed expressions use collatable data can depend on collation, including indexes on text, varchar, char, citext, and expressions such as lower(email). Your bigint and uuid keys are unaffected. This lists the ones that are exposed (the CROSS JOIN LATERAL turns each index's collation list into rows, the pattern from The Power of LATERAL Joins):

SELECT DISTINCT i.indrelid::regclass AS table_name,
                i.indexrelid::regclass AS index_name,
                c.collname,
                c.collprovider
FROM pg_index i
CROSS JOIN LATERAL unnest(i.indcollation::oid[]) AS ic(coll)
JOIN pg_collation c ON c.oid = ic.coll
WHERE c.collprovider IN ('d', 'c', 'i')
  AND c.collname NOT IN ('C', 'POSIX')
ORDER BY 1, 2;

d is the database default, c is libc and i is ICU. If your database's default provider is builtin (more on that below), the d rows are safe and you can ignore them.

Prove it with amcheck

amcheck validates the B-tree's structural and ordering invariants under the current comparison rules. It's the B-tree equivalent of asking the reader to check every page of the dictionary against the new rulebook:

CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('users_username_key', heapallindexed => true);

On the broken index:

ERROR: item order invariant violated for index "users_username_key"
DETAIL: Lower index tid=(116,4) (points to index tid=(338,1)) higher index tid=(116,5) (points to index tid=(449,1)) page lsn=0/473D668.

bt_index_check takes AccessShareLock, the same lock mode a plain SELECT takes, so reads and writes carry on while it runs and it's safe on a live system. (Its stricter sibling, bt_index_parent_check, takes a ShareLock that blocks writes, so keep that one for maintenance windows. Lock modes and what they block are covered in Module A-13: Locking Internals.) It isn't free though. It reads the entire index, and heapallindexed => true also reads the entire table, so on a large database it's real I/O you'll want to schedule. For a whole database at once, the pg_amcheck command-line tool that ships with Postgres runs the same checks in parallel:

pg_amcheck --heapallindexed --install-missing --jobs=4 -d app

The Fix, in the Right Order

The order matters more than any single step.

1. Find duplicates through the table, not the index. Any query that uses the broken index will hide them, so force sequential scans for the session:

SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;

SELECT username, count(*)
FROM users
GROUP BY username
HAVING count(*) > 1;

2. Resolve them. This is a business decision, not a SQL one. Two "identical" accounts might each have orders attached. In the demo I kept the oldest row, but in production you'd merge or reassign first.

3. Clean up any failed rebuilds, then rebuild:

DROP INDEX CONCURRENTLY IF EXISTS users_username_key_ccnew;
REINDEX INDEX CONCURRENTLY users_username_key;

4. Verify. bt_index_check returns cleanly and WHERE username = 'jo-ann' returns exactly one row again.

5. Only then, acknowledge the new version:

ALTER DATABASE app REFRESH COLLATION VERSION;

Here is the trap. That command does not rebuild or check anything. It updates the recorded version number so the warning stops. Run it first and you've silenced the only alarm while every index is still broken. Treat it as the last line of the runbook, a signature that says "I have rebuilt everything," never as the fix itself.

Making Collation Drift Boring

Pin the C library with the Postgres version

The cheapest protection is process. Treat the base OS image as part of the database version. postgres:16.4-bookworm, not postgres:16. Changing the OS image then becomes a database migration that goes through the same review as a major version upgrade, with "reindex text indexes, then amcheck" written into the checklist.

Use a collation that can't drift with the OS

PostgreSQL 18 ships a builtin collation provider. Its C.UTF-8 locale uses Unicode code point ordering implemented inside PostgreSQL itself, so ordinary OS upgrades don't change its ordering rules:

CREATE DATABASE app
  LOCALE_PROVIDER builtin
  BUILTIN_LOCALE 'C.UTF-8'
  TEMPLATE template0;

The honest cost: code point order isn't what humans expect. Every uppercase letter sorts before every lowercase one, so Zebra comes before apple, and accented characters like é sort after z. For identifiers, emails, usernames, SKUs and other machine-oriented keys, code point ordering is often a good trade-off, because the application usually cares about deterministic equality and lookup semantics rather than human alphabetical order. For display sorting, apply a linguistic collation at query time:

SELECT name FROM products ORDER BY name COLLATE "en-x-icu";

That sort won't be served from an index unless you create one with that collation, and an index like that brings back the versioning problem, just isolated to the one index you chose.

One caveat, and it matters for anyone planning a major-version upgrade. Code point ordering is fixed, but builtin takes its case mapping (what lower() and upper() return) from Unicode tables compiled into Postgres, and the docs only promise those are stable within a major version. PostgreSQL 18 moved to Unicode 16.0, and PostgreSQL 19 (in beta as I write this) moves to Unicode 17.0. The rest of what changes in 19 is in PostgreSQL 19 vs 18: What Actually Changed. An expression index such as lower(email) is therefore worth an amcheck after a pg_upgrade across major versions, even with no OS change at all. For the same reason, the PostgreSQL 18 release notes recommend reindexing full-text search and pg_trgm indexes after pg_upgrade on clusters whose default provider isn't libc. If you need case-insensitive matching, PostgreSQL 18 also adds PG_UNICODE_FAST, a builtin locale with full Unicode case mapping that still sorts by code point.

You also can't switch an existing database's default locale in place. Moving to builtin means a new database, filled by dump and restore or by logical replication, which, conveniently, is also the safer way to do an OS migration.

ICU is better, not immune

ICU collations (provider = icu) decouple sorting from glibc and give you consistent behavior across Linux and macOS. Postgres tracks an ICU collation's version and warns when it changes. But ICU's rules do change between ICU major versions, and in most Linux images ICU comes from an OS package that upgrades along with the base image. ICU makes the drift visible and less frequent. It doesn't make it go away.

The Takeaway

An index is a promise that the data is sorted. With text, the meaning of "sorted" belongs to a library you didn't write, versioned by an OS image you might upgrade without thinking about the database at all.

So the rule is short. Any time the C library under an existing data directory changes, through a new base image, a replica on a newer OS, or pg_upgrade on a new host, assume every index on a libc or ICU collation is suspect until amcheck says otherwise. Rebuild, verify, and only then tell Postgres the new version is fine.

The OS doesn't know it's touching your database. You have to.

Go Deeper

PostgreSQL In-Depth: the full course, from storage engine to production operations
Module A-5: Index Internals: B-tree, Hash, GIN and BRIN, and how the planner sees them
Storage Engines: B-Trees vs LSM Trees, from System Design In-Depth
Why Postgres and Cassandra Made Opposite Bets on Storage Engines
Building Production-Grade Images, for pinning the base image your database runs on

References

PostgreSQL wiki: Locale data changes (glibc 2.28, affected distributions, the sort test)
PostgreSQL 18 docs: Collation support and Locale providers
PostgreSQL 18 docs: ALTER COLLATION, notes on collation versions and ALTER DATABASE
PostgreSQL 18 docs: amcheck and pg_amcheck
PostgreSQL 18 docs: REINDEX
PostgreSQL 18 release notes (Unicode 16.0, PG_UNICODE_FAST, full-text search reindex advice)
PostgreSQL 19 release notes (beta; Unicode 17.0)
Debian packages: libc6 in bullseye (2.31) and libc6 in bookworm (2.36)

Your UUID Primary Keys Are Quietly Taxing Every Insert

Jatin Jain Saraf — Tue, 06 Oct 2026 03:05:50 +0000

Nobody gets paged because of a primary key type.

The problem shows up as a slow drift. Insert latency that was 2ms last quarter is now 6ms. WAL volume grows faster than the data you write. Replica lag spikes after a checkpoint. The cache hit ratio slides from 99.9% to 97% while the queries stay exactly the same.

Then someone runs EXPLAIN on the slow query, finds nothing obviously wrong, and blames the disk.

Sometimes the decision was made years earlier, in a line like this:

id uuid PRIMARY KEY DEFAULT gen_random_uuid()

Random UUIDs (version 4) are a reasonable default for a lot of reasons. But a B-tree index pays for that randomness on every single insert, and the bill grows with the table.

A Library Where Every New Book Goes on a Random Shelf

Picture two libraries.

In the first, new books always go at the end of the last shelf. The librarian only needs to know where that one shelf is. When it fills up, they start a new shelf next to it. Every other shelf can sit untouched for years.

In the second, every new book has to go on a specific shelf chosen at random. To add a book, the librarian walks to a random aisle. Any shelf could be next, so every shelf has to stay reachable. When a full shelf receives a book, it gets split in half to make room.

A B-tree index on a bigint identity column is the first library. A B-tree on gen_random_uuid() is the second.

With sequential keys, every insert goes to the rightmost leaf page. PostgreSQL even has a fast path for this: since version 11 it caches the rightmost leaf and skips the tree descent entirely when the new key belongs there. The hot part of the index is one page plus the path to it.

With random keys, inserts are spread across the leaf level of the index. The upper levels of the tree are small and stay hot, but leaf pages make up well over 99% of a large B-tree, and all of them become the working set. Once that leaf working set grows much larger than the useful portion of shared_buffers, inserts are increasingly likely to need a page that isn't cached, and random key generation turns into extra I/O.

Why this matters in production: the trouble starts not at a particular row count but when the leaf working set outgrows memory. A table can run happily for a year, then degrade over a few weeks as the index gets bigger than your cache can comfortably hold. Nothing in your code changed.

The Benchmark

I loaded 10 million rows into three identical tables on PostgreSQL 18.6. The only difference was the primary key:

CREATE TABLE t_bigint (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, payload text);
CREATE TABLE t_v4 (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), payload text);
CREATE TABLE t_v7 (id uuid PRIMARY KEY DEFAULT uuidv7(), payload text);

Rows went in as 1,000 committed batches of 10,000, with shared_buffers = 128MB so the indexes would outgrow memory the way a real production index eventually does. Before each run I issued a CHECKPOINT and reset the WAL statistics.

	bigint identity	UUIDv4	UUIDv7
Load time	22.2s	84.2s	23.2s
WAL generated	1,581 MB	2,523 MB	1,715 MB
Full-page images	45	121,490	45
Primary key index size	214 MB	387 MB	301 MB
Avg leaf density	90.0%	70.1%	90.0%
Index blocks read from outside shared_buffers	1	3,151,242	1

In this workload, UUIDv4 took 3.8x as long as bigint and 3.6x as long as UUIDv7, generated 60% more WAL than bigint, and produced an index 29% larger than UUIDv7. UUIDv4 and UUIDv7 are the same 16-byte data type, with the same row count on the same hardware. The only difference is the order the keys arrive in.

Benchmark scope: PostgreSQL generated the UUIDv7 values itself, from a single backend, so its near-perfect locality reflects PostgreSQL's native, backend-local monotonic generation. A workload where many application servers generate UUIDv7 independently will see more interleaving, because their clocks and generators differ.

Three separate mechanisms produce that gap.

Cost 1: The Index Stops Fitting in Memory

Look at the last row. The bigint and UUIDv7 indexes were read from outside shared buffers once in total. The UUIDv4 index was read 3.15 million times.

Those reads are consistent with inserts repeatedly needing leaf pages that were no longer resident in shared buffers, forcing PostgreSQL to fetch them again. The workload only ever adds new rows, yet the buffer cache is thrashing, because those new rows land on a randomly distributed set of leaf pages.

On my laptop most of those reads were served by the operating system's page cache and a local NVMe drive. On cloud block storage, where a miss is a network round trip, I'd expect the gap to be wider, not narrower.

Why this matters in production: the hit ratio drop also hurts your read queries. Random-key inserts push the pages your SELECTs need out of shared buffers, so lookups that were fast get slower even though nobody touched them.

Cost 2: Half-Empty Pages

Leaf density tells the second story. Sequential inserts grow the right edge of the index. PostgreSQL's default B-tree fillfactor is 90%, and that target is used when building an index and when splitting the rightmost page. The page left behind is never touched again, so it stays about 90% full.

Random inserts eventually split pages in the middle of the tree, and for those PostgreSQL divides the entries roughly evenly between the two pages. Each split leaves two half-full pages, and future random inserts only partly refill them. Under a sustained random-insert workload, average occupancy settles well below the fillfactor; here it landed at 70.1%.

That's the extra index size: the same keys spread across about 30% more pages. More pages means more memory needed to cache the index, which makes Cost 1 worse. The two feed each other.

Cost 3: Full-Page Writes Inflate WAL

This is the one most people never connect to their key type.

PostgreSQL writes every change to the write-ahead log before touching the data file. That's the aircraft black box that makes crash recovery possible. But there's a catch. If the server crashes halfway through writing an 8KB page to disk, the page could be half old and half new, and a small WAL record can't repair a torn page.

So with full_page_writes = on (the default, and it should stay on), the first modification of any page after a checkpoint writes the entire 8KB page image into WAL. Later changes to that page in the same checkpoint cycle only log the small delta.

The benchmark recorded 45 full-page images in each of the bigint and UUIDv7 runs, across 10 million rows. The point isn't that sequential inserts produce zero full-page images. What matters is how many distinct existing pages get modified for the first time after a checkpoint. Sequential workloads keep modifying a small hot set of pages at the right edge. Random UUIDv4 inserts spread those first modifications across the whole leaf level.

The UUIDv4 run recorded 121,490 full-page images, compared with 45 in the bigint run. At 8 KiB per uncompressed page image, the difference represents roughly 950 MiB of page-image payload, remarkably close to the roughly 942 MB difference in total WAL between UUIDv4 and bigint. The two numbers aren't expected to match exactly, since wal_fpi counts full-page images across the whole cluster and WAL also carries record headers and other records. But the three runs were otherwise identical, so the result strongly indicates that extra full-page images are the major contributor to the WAL gap.

Why this matters in production: WAL has to be generated and persisted according to the server's durability settings. In a replicated or archived system it also adds to the data that must be shipped, replayed, and retained. So random keys don't just affect the primary. In a write-heavy workload they can increase replication traffic, replay work on replicas, and WAL archive storage. The effect peaks right after each checkpoint, when every page becomes "first touch" again, which is one reason lag charts on these systems often have a sawtooth shape.

UUIDv7: Time First, Random Second

UUIDv7 (standardized in RFC 9562) keeps the 128-bit UUID format but changes the layout. The first 48 bits are a Unix timestamp in milliseconds. After the version and variant bits, the rest is random (or, in some implementations including PostgreSQL's, partly extra timestamp precision).

SELECT uuidv7();
-- 01a10f1f-702c-75ef-9838-f47c570df364

Because the timestamp occupies the most significant portion of the UUID, UUIDv7 values generated later generally sort later when they come from the same clock source. To a B-tree that looks almost exactly like a sequence: inserts go to the right edge, pages fill densely, and very few existing pages are touched per checkpoint. The benchmark shows it: UUIDv7 landed within a few percent of bigint on load time, matched it on full-page images and leaf density, and its only real penalty is a larger index because each key is 16 bytes instead of 8.

You still keep everything that made UUIDs attractive in the first place. Application servers, mobile clients, or separate services can generate IDs without asking the database. Different services or shards can generate IDs independently with an extremely low probability of collision. You can create the ID before the INSERT, which makes idempotent retries and outbox patterns simpler.

PostgreSQL 18 ships uuidv7() natively, so you no longer need an extension or application-side generation. Its implementation uses the 12 bits immediately after the millisecond timestamp for sub-millisecond precision, helping UUIDs generated by the same backend remain ordered even when many are created within the same millisecond. It also pairs with uuid_extract_timestamp(), which pulls the creation time back out of the ID.

What UUIDv7 Still Costs

It's much better, but it isn't free, and pretending otherwise is how teams get surprised later.

It leaks creation time. Anyone who sees a UUIDv7 can recover the time value encoded in it, with millisecond precision in the standard layout, which reveals roughly when the record was created. If your IDs show up in public URLs, a competitor can estimate your signup rate by creating two accounts a week apart. For user-facing identifiers where that matters, expose a separate random token and keep the v7 key internal.

It's still 16 bytes. The benchmark's heap was 805 MB for both UUID tables against 730 MB for bigint, and the UUIDv7 index was 41% larger than bigint's. That cost repeats in every foreign key column and every index that includes the key. A table referenced by five others pays it six times.

The right edge is a single hot spot. Sequential keys of any kind concentrate inserts on one leaf page. At very high concurrency, sessions can queue for the lock on that page. This affects bigint identity too, and it takes serious write concurrency before it shows up, but it's the trade you make for locality.

Order is not guaranteed across independent generators. If several application servers generate IDs, clock skew between them means IDs are roughly time-ordered, not strictly ordered. Inserts land near the right edge instead of exactly on it. For an index that's fine. For logic that assumes "larger ID means created later," it isn't.

Which Key Should You Use?

bigint GENERATED ALWAYS AS IDENTITY : one database generates every ID and the IDs never need to be unguessable. Smallest, fastest, and the default unless you have a reason not to.
UUIDv7 : IDs are created outside the database (clients, multiple services, offline-first apps), data from several shards or regions needs to merge, or you need the ID before the row exists.
UUIDv4 : as a separate, opaque public identifier where unpredictability helps, such as resource URLs or share links, stored in a column with its own index. Not as the primary key of a high-write table. And not as an authentication token: keep those as dedicated security credentials.

Already Running on UUIDv4?

You don't need to rewrite the table. Because UUIDv4 is just a 128-bit value, you can switch the default:

ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

Old rows keep their random IDs and still sort among them. New rows all carry timestamps from right now, so they fall into one narrow slice of the key range and concentrate their inserts on a small group of pages instead of the whole index. The existing index won't shrink or repack itself. If you want the density back, rebuild it in a quiet window without blocking writes:

REINDEX INDEX CONCURRENTLY orders_pkey;

It needs extra disk space and takes longer than a plain REINDEX, and if it fails partway it leaves an invalid index behind that you have to drop. Measure WAL volume and full-page images before and after so you know what you actually gained.

To see where your own tables stand today:

-- How full are your primary key leaf pages?
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('orders_pkey');

-- How much of your WAL is full-page images?
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes)
FROM pg_stat_wal;

-- Is the index being served from memory?
SELECT idx_blks_hit, idx_blks_read
FROM pg_statio_user_indexes
WHERE indexrelname = 'orders_pkey';

A leaf density near 70% on a primary key, unusually high wal_fpi compared with a sequential-key baseline, and a growing idx_blks_read on an insert-heavy table are the fingerprint of random keys.

The Takeaway

A primary key is not just an identifier. It decides where every new row lands in the index, and that decides how much of the index has to stay in memory, how full its pages stay, and how much WAL every insert writes.

Random keys spread that work across the whole index. Time-ordered keys keep it in one place. With uuidv7() built into PostgreSQL 18, you no longer have to choose between distributed ID generation and an index that ages well.

Benchmark setup: PostgreSQL 18.6 on an Apple M2 Pro (16 GB RAM, local NVMe) in a fresh, otherwise idle cluster, shared_buffers=128MB, max_wal_size=1GB, defaults for everything else (including full_page_writes, wal_compression, synchronous_commit and fillfactor), 1,000 committed batches of 10,000 rows, CHECKPOINT and WAL stats reset before each run. Absolute times will vary on other hardware. The ratios are the point.

The TOAST Tax: How Unbounded JSONB Arrays Cost My Blockchain Indexer 5TB

Jatin Jain Saraf — Mon, 05 Oct 2026 03:33:07 +0000

How PostgreSQL TOAST turned unbounded JSONB arrays into a 5TB storage problem, and how we moved the hot path out without taking the indexer offline.

Our blockchain indexer's biggest PostgreSQL table looked healthy. Row counts made sense, the queries were simple and the indexes were in place. Then we looked at disk usage, and the numbers didn't add up. The table was taking terabytes more than its rows could explain.

The space wasn't in the table's own pages at all. It was in a second table we had never created, never queried and never named: pg_toast.pg_toast_<oid>. That's where PostgreSQL had been putting our JSONB arrays and objects. At its peak, pg_total_relation_size() for that TOAST relation was over 5TB , counting live data, dead tuples and its index.

TL;DR

Large JSONB documents don't just live in your table. PostgreSQL may compress them and move them into a separate TOAST table. That's usually invisible, until your documents get large and your workload keeps querying individual fields inside them.

In our indexer, unbounded arrays made this much worse. A bigger JSONB index wasn't the fix. We pulled the hot fields into normal columns, moved one-to-many data into child tables, and kept JSONB for the flexible data that actually benefits from it.

What actually caused the 5TB? Not TOAST. TOAST was PostgreSQL doing exactly what it was designed to do. The cause was putting unbounded one-to-many data inside a single JSONB value, then rewriting that value again and again. TOAST hid the problem. The schema created it.

The Setup: Why JSONB Felt Like the Right Call

If you've built an indexer for a Move-based chain, you know the pull. Resources have different shapes. Event payloads change by event type. A single transaction can carry a list of arguments, a list of events and a list of state changes, and each list can be any length.

Modeling all of that relationally means dozens of tables before you've shipped anything. JSONB lets you put the whole decoded transaction in one column and move on:

CREATE TABLE transactions (
  tx_hash bytea PRIMARY KEY,
  block_height bigint NOT NULL,
  payload jsonb NOT NULL -- sender, function, args[], events[], changes[]
);

I wrote about this tradeoff in Your Blockchain Indexer Is Fine, Until It Isn't. JSONB really is useful for an indexer. What I underestimated was what happens when the documents inside it get big, and arrays are exactly what makes them big.

TOAST in One Analogy: The Off-Site Storage Unit

PostgreSQL stores rows in 8KB pages, and a row (a heap tuple, in PostgreSQL terms) can't span pages. When a row is wider than TOAST_TUPLE_THRESHOLD (roughly 2KB on a default 8KB page), PostgreSQL hands it to TOAST (The Oversized-Attribute Storage Technique) to shrink it.

Think of it as an off-site storage unit :

First, PostgreSQL tries to compress the large values so the row fits again. That's like vacuum-packing your clothes so they still fit in the closet.
If the row is still too big, PostgreSQL moves values out of the row into a separate TOAST table, cut into chunks of about 2KB each.
The row keeps a small TOAST pointer (18 bytes on disk), which works like a claim ticket for the storage unit.

A common misconception is that TOAST only kicks in above 8KB. It doesn't. The roughly 2KB threshold applies to the whole row, so one fat events[] array is often enough to push a transaction over it.

This is great for keeping rows small, and it's why PostgreSQL can store values far bigger than a page at all. The catch is what happens when you need something back from the storage unit.

Want the full internals: chunk layout, storage strategies, and how the toaster picks which column to move first? The Storage Engine module of the PostgreSQL course covers them.

Cost #1: Reading One Key Can Mean Detoasting the Document

This was the query we ran most often:

SELECT tx_hash, block_height
FROM transactions
WHERE payload->>'sender' = $1
ORDER BY block_height DESC
LIMIT 20;

It looks cheap because we only want one small field. But to evaluate payload->>'sender', PostgreSQL generally has to detoast the JSONB value first. For a large, compressed, out-of-line document, that can mean reading its TOAST chunks through the TOAST table's index, rebuilding the value and decompressing it before JSONB can look at its binary layout.

JSONB's binary format really is fast to search, but only once the value is in memory. PostgreSQL doesn't jump straight to the bytes for sender inside the TOAST chunks. The trip to the storage unit happens first, for every row the filter touches. In one benchmark from Snowflake's engineering team, filtering on one JSONB key in a 10,000-row table took under 10ms when the documents were 40 bytes, and about 500ms (40x slower) when they were 40KB and TOASTed. Your numbers will differ, but the direction matches what we saw.

Two details make it worse in practice:

Projecting several fields adds up. Every JSONB expression still has to work with the underlying value, so pulling five fields out of a large document can add a lot of CPU and memory work compared to reading five plain columns.
The planner is guessing. PostgreSQL keeps no statistics on keys inside a JSONB document. For payload->>'sender' = $1, it falls back to a hardcoded selectivity estimate, so its row count predictions can be badly wrong, and so can the plans built on them.

What this looks like in production: queries that filter on one field are slow even when they return a handful of rows, CPU stays high on a read replica with no obvious hot query, and an index on block_height doesn't help as much as you expect.

Cost #2: Updating One JSONB Key Can Rewrite the Whole Value

Indexers update data. A reorg rewrites a block, a backfill adds a decoded field, a bug fix changes a format. For us it usually looked like this:

UPDATE transactions
SET payload = jsonb_set(payload, '{status}', '"finalized"')
WHERE block_height BETWEEN $1 AND $2;

jsonb_set looks like a partial update, and at the SQL level it is. Physically, though, PostgreSQL doesn't patch the changed bytes in place. Changing one key in a large TOASTed document produces a brand-new value , so you get new TOAST chunks, new TOAST index entries and new WAL for all of it. The old chunks stay behind as dead tuples until vacuum gets to them.

One nuance worth knowing: if an UPDATE doesn't touch the TOASTed column, PostgreSQL reuses the existing pointer and leaves the value alone. Updating block_height by itself is cheap. Changing payload in any way is not.

What this looks like in production: WAL volume spikes during backfills, replica lag climbs (the same problem I described in Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers), and disk keeps growing after the backfill is done.

Cost #3: The Storage Unit Needs Its Own Cleaning Crew

The TOAST table is a real table. It gets dead tuples, it needs autovacuum, it bloats, and it has its own index that bloats along with it. Because it's hidden, nobody watches it. Your dashboards say transactions is healthy while pg_toast_16421 quietly grows by hundreds of gigabytes. (For how vacuum and WAL work in the background, see Every Postgres Write Triggers Five Background Processes.)

That's how our TOAST relation got past 5TB. No single decision did it. It was every array that grew a little, every backfill that rewrote documents, and every vacuum cycle that couldn't keep up.

How to Check Whether You Have the Same Problem

You can run all of these on your own database.

1. Find your biggest TOAST tables

SELECT
  c.relname AS table_name,
  pg_size_pretty(pg_relation_size(c.oid)) AS main_heap,
  pg_size_pretty(pg_indexes_size(c.oid)) AS main_indexes,
  pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_heap,
  pg_size_pretty(pg_indexes_size(c.reltoastrelid)) AS toast_indexes,
  pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) AS toast_total,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class c
WHERE c.relkind = 'r'
  AND c.reltoastrelid <> 0
ORDER BY pg_total_relation_size(c.reltoastrelid) DESC
LIMIT 10;

The TOAST table has its own index, so toast_total is the number that shows the real off-site footprint. If toast_total is close to or larger than main_heap, a big share of the table's physical footprint lives out of line.

2. Measure your JSONB sizes

SELECT percentile_cont(ARRAY[0.5, 0.9, 0.99])
       WITHIN GROUP (ORDER BY pg_column_size(payload)) AS p50_p90_p99_bytes
FROM transactions TABLESAMPLE SYSTEM (1);

pg_column_size() reports the size of the value as stored, after any compression. That makes it useful for seeing how big your documents really are on disk. Don't treat 2KB as a hard "TOAST or no TOAST" line for a single column, though. TOAST looks at the size of the whole row, the column's storage strategy, and whether compression can bring the row back under the target size. Look at these percentiles together with toast_total from query 1 to see what's actually happening.

3. Check compression

SELECT pg_column_compression(payload) AS method, count(*)
FROM transactions TABLESAMPLE SYSTEM (1)
GROUP BY 1;

NULL means the value isn't compressed. Otherwise you'll see pglz or lz4.

4. EXPLAIN ANALYZE, measured honestly

Run EXPLAIN (ANALYZE, BUFFERS) on your hot queries. When the JSONB is used in a filter, TOAST reads show up in the buffer counts. There's a gotcha: plain EXPLAIN ANALYZE doesn't detoast columns you only return, so SELECT payload ... looks faster than it is. On PostgreSQL 17 and later, add SERIALIZE to include that cost:

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE)
SELECT payload FROM transactions WHERE block_height = 1234567;

The Fixes, From Cheapest to Most Work

1. Switch to lz4

ALTER TABLE transactions ALTER COLUMN payload SET COMPRESSION lz4;

lz4 decompresses much faster than the older pglz default, at a similar compression ratio. PostgreSQL 19 changes the default TOAST compression to lz4 on builds with LZ4 support, as I covered in PostgreSQL 19 vs. 18. It's the cheapest change here, but it only affects newly written values. Existing rows keep their old compression until they're rewritten. Each trip to the storage unit gets faster, but you still make the trip.

2. Extract hot keys into real columns

This is the fix that matters most. If you filter, sort or join on sender all the time, it shouldn't live inside a document.

On a small or medium table, a stored generated column is the cleanest option:

ALTER TABLE transactions
  ADD COLUMN sender text GENERATED ALWAYS AS (payload->>'sender') STORED;

CREATE INDEX CONCURRENTLY idx_tx_sender ON transactions (sender);

PostgreSQL keeps the column in sync, it's normally stored inline with the row, it gets real planner statistics, and reading the small extracted value doesn't require detoasting the original JSONB document. In the same Snowflake benchmark, filtering on a stored generated column took 5ms, against 500ms for the same filter on the TOASTed JSONB.

It isn't free, though. Every write to payload now also computes and stores sender, and the column and its index take disk. You're paying a small write cost for a big read saving, which is the right trade for a key you read constantly.

PostgreSQL 18 gotcha: generated columns are now VIRTUAL by default, which means the value is computed on read. If your goal is to get a frequently queried JSONB key out of TOAST, write STORED explicitly. A virtual column detoasts the document every time it's read, which is exactly what you're trying to avoid.

3. Expression indexes for known lookups

If you can't add a column yet, an expression index stores the extracted value in the index itself:

CREATE INDEX CONCURRENTLY idx_tx_payload_sender
  ON transactions ((payload->>'sender'));

A lookup by sender can now find matching rows through the index without opening the storage unit, as long as you don't also SELECT payload. The index fixes the lookup path, not the cost of fetching the document itself. PostgreSQL also collects statistics on indexed expressions, so the planner gets real data to estimate with instead of a hardcoded guess. The downsides are the same as any index (write overhead and disk), plus the query has to use exactly the same expression for the index to be used.

This works best for exact matches. For a prefix search like LIKE 'a%', PostgreSQL uses the index to narrow down the rows, then re-applies the original condition to each one, which means detoasting again. Snowflake measured exactly this: 80ms through an expression index, against 5ms on a real column.

4. GIN, for what it's good at

CREATE INDEX CONCURRENTLY idx_tx_payload_gin
  ON transactions USING GIN (payload jsonb_path_ops);

SELECT tx_hash FROM transactions
WHERE payload @> '{"function": "0x1::coin::transfer"}';

GIN is good at containment questions like "which documents contain this key and value?" It doesn't make extracting a value any cheaper. jsonb_path_ops stores hashes, so candidate rows get rechecked against the real document, and that recheck detoasts. On large documents, GIN indexes also get very large themselves. Use GIN when you really don't know the query shape ahead of time, not as a replacement for real columns on hot keys.

5. Normalize unbounded arrays

Everything above works around the actual mistake. An array that can grow without limit doesn't belong in a single column value. events[] is a one-to-many relationship, and relational databases already have a tool for that:

CREATE TABLE tx_events (
  tx_hash bytea NOT NULL,
  event_index int NOT NULL,
  event_type text NOT NULL,
  data jsonb NOT NULL, -- small, per-event, usually well under 2KB
  PRIMARY KEY (tx_hash, event_index)
);

CREATE INDEX ON tx_events (event_type);

Each event is now its own small row. A query like "all transfer events for this coin" becomes an index scan instead of unpacking thousands of documents. JSONB is still there, but sized for what it's good at: a small, flexible bag of fields per event, not a whole transaction's history in one value.

If you still need the full raw payload for replays or debugging, move it out of the hot table entirely, into a narrow tx_raw (tx_hash, payload) table or object storage. The hot table stays small, and the big documents are read only when someone actually needs them.

Here's the shape before and after:

BEFORE
transactions
├── tx_hash
├── block_height
└── payload (JSONB)
    ├── sender
    ├── function
    ├── args[]
    ├── events[] ← grows without limit
    └── changes[] ← grows without limit
           │
           ▼
      TOAST table (5TB+)

AFTER
transactions
├── tx_hash
├── block_height ← indexed
├── sender ← indexed, hot path, inline
└── payload (JSONB) ← cold, flexible fields only
           │
           ▼
      TOAST table (small)

tx_events
├── tx_hash
├── event_index
├── event_type ← indexed
└── data (JSONB) ← small, usually stays inline

The Zero-Downtime Migration

At 5TB, don't casually add a stored generated column with ALTER TABLE. PostgreSQL has to rewrite the whole table and its indexes, and the ACCESS EXCLUSIVE lock that ALTER TABLE ... ADD COLUMN takes is held until the transaction commits. The rewrite copies every value, TOAST included, and needs free disk for that second copy until it finishes. At this size, that's a long stretch (realistically hours) where the indexer can neither read nor write.

So we did it in steps that are each cheap or non-blocking:

add nullable column → install trigger → backfill in batches → CREATE INDEX CONCURRENTLY → switch reads


-- Defining the trigger function takes no lock on the table
CREATE FUNCTION set_tx_sender() RETURNS trigger AS $$
BEGIN
  NEW.sender := NEW.payload->>'sender';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Steps 1 and 2 need brief table locks: fail fast instead of queueing, then retry
SET lock_timeout = '5s';

-- 1. Instant: a nullable column with no default is a metadata-only change
ALTER TABLE transactions ADD COLUMN sender text;

-- 2. Keep new and changed rows in sync from now on
CREATE TRIGGER trg_tx_sender
  BEFORE INSERT OR UPDATE OF payload ON transactions
  FOR EACH ROW EXECUTE FUNCTION set_tx_sender();

RESET lock_timeout;

-- 3. Backfill history in small block ranges, one committed transaction per range
UPDATE transactions
SET sender = payload->>'sender'
WHERE block_height >= $1 AND block_height < $2
  AND sender IS NULL;

-- 4. Index without blocking writes
CREATE INDEX CONCURRENTLY idx_tx_sender ON transactions (sender);

A few things make this work at scale:

"Instant" still needs a lock. ALTER TABLE needs a brief ACCESS EXCLUSIVE lock and CREATE TRIGGER a SHARE ROW EXCLUSIVE one. If a long-running query is holding the table, the DDL waits, and every query that arrives after it queues behind it. A short lock_timeout makes it fail fast so you can retry. More on that trap in Why a Single ALTER TABLE Can Take Down Your Whole Database.
Order matters. The trigger goes in before the backfill starts, so every row written during the migration is already correct. The backfill only has to cover history.
The backfill doesn't rewrite the existing TOAST values. It reads each document from TOAST once, and after that, the sender lookup no longer needs to detoast the original payload. Because it never modifies payload, PostgreSQL reuses the existing TOAST pointers, and the trigger (which only fires on UPDATE OF payload) stays out of the way.
Commit every batch. Run the backfill from a script, one transaction per block range, never as one giant UPDATE. A multi-hour transaction stops vacuum from cleaning up any row that dies while it runs, across the whole database, and if it fails at hour five, all of that work rolls back.
The heap still churns. The UPDATE itself (not the TOAST reads) gives every backfilled row a new tuple version in the main table, and all of that is WAL-logged. Let autovacuum (or a manual VACUUM) catch up between batches, and size the batches by what you can observe, WAL volume and replica lag, rather than a round row count.
Switch reads last. Once the index is built, change the application from payload->>'sender' to sender, check the new plan with EXPLAIN (ANALYZE, BUFFERS), and only then think about dropping any expression index you added as a stopgap.

The same pattern (new structure, trigger for new writes, batched backfill, concurrent index, cut over reads) also works for moving events[] into tx_events.

When JSONB Is Still the Right Answer

None of this means JSONB is bad. It works well when:

Documents usually stay small enough that rows stay inline
You read the whole document at once, so the trip to TOAST is worth making
The structure really is unknown or user-defined, and you rarely filter on it

It goes wrong when a document grows without limit and you keep querying single keys inside it. In many indexers, both of those show up sooner than you expect. JSONB isn't the mistake. Using it in place of modeling hot fields and unbounded relationships is.

The Rule I Use Now

JSONB is for the shape you don't know yet. Columns are for the keys you query. Child tables are for anything that's a list.

PostgreSQL won't warn you when your rows start relying heavily on TOAST. It quietly starts building a storage unit, hands each row a claim ticket and keeps going. Run the TOAST size query above on your largest tables this week. If the off-site number is bigger than the table itself, you already know which fix to start with.

Every Postgres Write Triggers Five Background Processes. Here's What Each One Actually Does.

Jatin Jain Saraf — Mon, 14 Sep 2026 05:05:17 +0000

An UPDATE statement returns in two milliseconds. From where you're sitting, that's the whole story: the row changed, the client got an acknowledgment, done. Underneath, Postgres just did five separate jobs to make that write durable, recoverable, reusable, and still fast for the next query to plan correctly. Most of the time you never see any of them. Then a dashboard shows periodic latency spikes with no slow query in sight, or pg_stat_activity shows a backend "stuck" on something with a name you've never had to learn, and you're suddenly debugging a part of Postgres that has been running the entire time.

This is a walkthrough of that background layer: the Write-Ahead Log, checkpoints, the wait events that make both of them visible, and the vacuum/analyze cycle that cleans up after every write and keeps the query planner honest.

WAL: the black box that makes crash recovery possible

Postgres never writes a change directly to a table's data file first. It writes a record of the change to the Write-Ahead Log (WAL) first, and only later, asynchronously, does the actual data file get updated. This is the same idea as an aircraft's flight data recorder: it doesn't prevent anything from going wrong, but if the plane goes down, the black box is what lets you reconstruct exactly what happened, in order, right up to the last recorded moment.

If Postgres crashes between "WAL record written" and "data file updated," the data file is allowed to be behind. On restart, Postgres replays the WAL from the last confirmed checkpoint forward and reconstructs every change that was durably logged but not yet applied to the heap. That's the entire crash-recovery guarantee, and it's also the entire replication guarantee: a replica is, at its core, a process continuously replaying WAL records shipped from the primary.

Why this matters in production: synchronous_commit controls whether a transaction waits for its WAL record to actually hit disk before returning success to the client. Turn it off (or set it to a weaker level like local) and writes get faster, because you're no longer waiting on an fsync. You're also now willing to lose the last few transactions if the server crashes before that WAL hits disk. That's a real trade-off teams make deliberately for high-throughput, loss-tolerant write paths, and a real trade-off teams make by accident when they copy a config from a benchmark blog post.

Checkpoints: the point WAL gets allowed to forget the past

WAL can't grow forever, and replaying an unbounded WAL after a crash would make recovery take unbounded time. A checkpoint is Postgres periodically flushing every dirty page sitting in shared buffers out to the actual data files, so that everything the WAL described up to that point is now durably reflected on disk. Once a checkpoint completes, the WAL segments before it are no longer needed for crash recovery and can be recycled or removed (subject to what replication and archiving still need them for).

Checkpoints run on a schedule controlled by two settings: checkpoint_timeout (time-based, five minutes by default) and max_wal_size (a soft cap on how much WAL can accumulate before a checkpoint is forced early). Whichever threshold is hit first triggers the checkpoint.

Why this matters in production: a checkpoint means writing out every dirty page at once, which is a burst of I/O. If that burst happens all at once, you get the classic "checkpoint spike," periodic latency jumps that correlate with wall-clock time, not query volume. checkpoint_completion_target exists specifically to spread that I/O out over the checkpoint interval instead of dumping it all at once. If your latency graphs show a sawtooth pattern with a period matching your checkpoint_timeout, that's usually the first setting worth checking, not the query itself.

Wait events: the window into both of the above

pg_stat_activity has two columns, wait_event_type and wait_event, that tell you what a backend is actually blocked on right now, if it's blocked on anything. This is the diagnostic surface that turns "the query is slow" into "the query is waiting on something specific":

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

The wait_event_type column groups events into categories: Lock (waiting on a row, table, or advisory lock held by another transaction), LWLock (waiting on an internal lightweight lock protecting shared memory structures), IO (waiting on an actual disk read or write), BufferPin, IPC, Timeout, and a few others. A backend showing IO wait events tied to WAL activity, or an LWLock wait tied to the WAL insert path, is a backend that's been caught behind the exact mechanism described above: it's trying to get its change durably logged and something (usually disk throughput, sometimes a checkpoint mid-flush) is making it wait its turn.

Why this matters in production: the specific wait event names attached to WAL and checkpoint activity have changed across major Postgres versions as the I/O statistics system was reworked, so don't memorize a table of exact strings from a blog post (including this one) and assume it matches your version. Instead, learn the query above, run it against your own instance during a slow period, and read whatever wait_event_type/wait_event pair comes back against the current docs for your version. That habit outlives any specific release.

Vacuum: cleaning up after MVCC, not just reclaiming disk

Postgres never overwrites a row in place on UPDATE or DELETE. MVCC (multi-version concurrency control) means an UPDATE writes a new row version and marks the old one dead, and a DELETE just marks a row dead, because other transactions that started before yours might still need to see the old version. Those dead row versions don't disappear on their own. VACUUM is the process that finds them, confirms no transaction still needs them, and marks that space reusable by future inserts on the same table.

Plain VACUUM does not shrink the file on disk (only VACUUM FULL does, by rewriting the whole table, which takes an exclusive lock). It reclaims space within the existing file for future rows to reuse. autovacuum runs this automatically, table by table, once the number of dead tuples crosses a threshold (autovacuum_vacuum_threshold plus a percentage of the table's row count, autovacuum_vacuum_scale_factor, which defaults to 20%). Vacuum also has a second job unrelated to space at all: it's what prevents transaction ID wraparound, a much rarer but far more serious failure mode where an un-vacuumed table's transaction ID counter runs out of room entirely.

Why this matters in production: vacuum generates its own WAL records, since it's modifying pages, so a large autovacuum run on a big table shows up as real write volume, not a free background chore. It also competes for the same I/O bandwidth a checkpoint is using, which is why an aggressive autovacuum kicking off right as a checkpoint is flushing dirty pages is a common, specific cause of a latency spike that doesn't correlate with any query change. Database Efficiency 101 goes deeper on the bloat side of this; the piece worth adding here is that vacuum isn't an isolated background job, it's sharing the same WAL and I/O path as everything above.

ANALYZE: the step that keeps the planner honest

ANALYZE is a separate operation from vacuum, even though autovacuum triggers both and people often say "autovacuum" to mean either. ANALYZE samples a table's rows and updates the planner's statistics (row counts, most common values, distribution histograms) stored in pg_statistic. The query planner uses those statistics, not a live count, to estimate how many rows a WHERE clause will match and decide whether a sequential scan, index scan, or bitmap heap scan will be cheapest.

Stale statistics don't cause a query to return wrong results. They cause the planner to misjudge how many rows it's dealing with, and a bad row-count estimate is one of the most common root causes behind "this query used to be fast and now it isn't," with no schema change and no data corruption involved, just a planner making a good decision on outdated information. autovacuum_analyze_threshold and autovacuum_analyze_scale_factor (10% by default) control when this runs automatically, similarly to vacuum's thresholds, and an insert-heavy, delete-free table (an append-only events table, for instance) still needs ANALYZE on a schedule even though it may rarely need vacuuming for dead tuples.

Why this matters in production: after a large bulk load, either via a migration or an initial data import, running ANALYZE explicitly before serving real traffic is worth the few seconds it costs. Waiting for autovacuum to notice on its own schedule means the planner may make its first few hours of decisions on statistics from before the table had any real data in it.

Putting the five back together

None of these are separate systems bolted onto Postgres. They're one pipeline: WAL makes every write durable and replicable, checkpoints periodically settle that WAL's guarantees into the actual data files and bound how long crash recovery takes, wait events are the live diagnostic surface showing you when a backend is stuck somewhere in that path, vacuum reclaims the space MVCC leaves behind and prevents wraparound, and ANALYZE keeps the planner's model of your data close enough to reality to keep picking good plans. A slow query is sometimes a bad query. Just as often, in a system that's been running fine for months, it's one of these five processes falling behind, and pg_stat_activity is the first place that tells you which one.

Your ORM Isn't Lying to You. It's Just Not Telling You Everything.

Jatin Jain Saraf — Thu, 03 Sep 2026 13:32:46 +0000

An ORM is a translator sitting in a business meeting. It's fluent, it's fast, and after a while you stop paying attention to the original language entirely. You trust it. Most days that trust is fine. Then one sentence gets mistranslated, the deal falls apart, and you realize you never actually learned the language yourself. You just learned to rely on someone who did.

That's what Prisma, Drizzle, TypeORM, and Sequelize do to your relationship with Postgres. They don't lie. They just don't tell you everything, and the gaps are exactly where production incidents live.

This post is not "ORMs are bad, use raw SQL." Most apps should use one. It's a map of the specific places where the ORM's model of your database and Postgres's actual behavior diverge, so you know where to look when something is slow, wrong, or gone.

Why teams reach for an ORM in the first place

Before the shortcomings, the honest case for using one:

Types generated from your schema. Rename a column, your build breaks at compile time instead of at 2am in production.
Migrations as code. Schema changes are version-controlled, reviewable in a pull request, and reproducible across environments.
Boilerplate CRUD disappears. Most application code touching the database is SELECT, INSERT, UPDATE, DELETE on one or two tables. An ORM turns that into a few lines instead of a hand-written query and a row-mapping function, every time.

None of that is wrong. The problem is what it costs you when you stop thinking about the SQL underneath.

The N+1 query, hiding in plain sight

This is the failure mode that catches the most teams, because the ORM makes it look identical to the correct version.

const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

That's 1 query to fetch users, plus N more queries, one per user, to fetch their posts. With 10 users you might not notice. With 10,000 users, those extra round trips can turn an otherwise simple endpoint into a timeout.

The dangerous part isn't that this pattern exists. Every engineer knows N+1 queries are bad. The dangerous part is that the ORM's relational syntax looks the same whether it's doing this or a single join:

const users = await prisma.user.findMany({
  include: { posts: true },
});

Relation loading isn't necessarily equivalent to the join you would write by hand. Depending on the ORM and its configuration, that one line can compile down to a single query with a join, or to a batch of separate queries stitched together in application code. Prisma, for example, defaults to the second approach for include on one-to-many relations: it batches the related rows with a SQL IN clause rather than emitting a native JOIN, unless you're on a relation mode or raw query that says otherwise. You can't reliably tell which one you got just by reading the application code. Turn on query logging and look.

Why this matters in production: N+1 doesn't necessarily mean N new connections. Queries can reuse the pool. The problem is the number of database round trips. Under concurrent traffic, many requests running their own N+1 loops can keep pooled connections busy for longer, increasing pool contention and request latency. This is where an ORM's convenience directly creates the incident.

The SQL your ORM actually generates is not the SQL you'd write

Turn on query logging for a week on a real Prisma or TypeORM project and read what comes out. It is rarely what you'd write by hand.

With nested includes, some ORMs, Prisma among them, can issue several separate queries and stitch the results together in application code instead of one query with joins. That's not a bug, it's a design trade-off, and it varies by ORM, version, and configuration. But it means EXPLAIN ANALYZE on the query you think you're running and the query Postgres actually saw can tell two different stories, and only one of them is visible in your codebase.

Why this matters in production: if you've read through the query planning module of the PostgreSQL In-Depth course, you know Postgres picks between a sequential scan, an index scan, and a bitmap heap scan based on cost estimates for the specific query it receives. An ORM that reshapes your intent into three smaller queries instead of one joined query gives the planner three separate decisions to make, rather than one plan for the operation as a whole. You lose the planner's ability to optimize across the whole operation.

The fix isn't abandoning the ORM. It's treating query logging as mandatory, not optional, and periodically running EXPLAIN ANALYZE on queries that matter to your application's hot paths or have noticeable latency.

Migrations: the auto-diff can silently drop data

Most ORMs generate migrations by diffing your schema file against the last known state and producing SQL. This works well for additive changes. It gets dangerous on renames.

Rename a column in your schema file, and a schema diff doesn't always have enough information to know that you intended a rename rather than a drop-and-add. Some ORMs can detect renames, while others may generate a drop-and-add migration depending on the schema change and migration workflow. The generated migration can be:

ALTER TABLE users DROP COLUMN full_name;
ALTER TABLE users ADD COLUMN display_name text;

instead of the safe version:

ALTER TABLE users RENAME COLUMN full_name TO display_name;

The first version runs without error. It also silently discards every value in that column on deploy.

Why this matters in production: a migration that runs clean and drops a column's worth of data doesn't announce itself. Nobody gets an error. You find out when a support ticket comes in asking why a field is suddenly empty. This is one of the highest-leverage things to check before merging any ORM-generated migration: read the actual SQL it produces, not just the schema diff you wrote.

Connection pooling: every ORM has different defaults, and it matters more than ever in serverless

If you've read Connection Pooling in the Serverless Era, you already know that each connection Postgres accepts consumes server resources, whether or not it's doing anything, and enough concurrent connections becomes a significant resource cost. ORMs each ship their own default pool size, their own idle-timeout behavior, and their own assumptions about how long your process lives.

Those defaults were mostly written assuming a long-lived server process. In a serverless or edge function, where every invocation can be a fresh process, an ORM's default pool of, say, 10 connections means every cold start tries to open 10 connections to Postgres. Multiply that by concurrent invocations during a traffic spike and you can exhaust your database's max connection limit in seconds, well before you exhaust CPU or memory on the compute side.

Why this matters in production: this is a common cause of "it works locally, it falls over under load" for teams deploying ORM-based apps to serverless platforms. The fix usually involves an external pooler (PgBouncer, RDS Proxy, or a managed equivalent) sitting between your functions and Postgres, plus explicitly tuning the ORM's own pool size down, not trusting the default. One trap worth knowing before you reach for a pooler: PgBouncer's transaction-mode pooling, the mode most serverless setups need, doesn't preserve a session across queries, and ORMs that rely on prepared statements (Prisma and TypeORM both do, by default) can fail against it in ways that only show up under load. Check your pooler's mode against your ORM's prepared-statement behavior before you assume adding one fixes everything.

Write-heavy systems: where the ORM's convenience becomes the bottleneck

Everything above assumes a fairly typical application: reads dominate, writes happen when a user submits a form or an admin edits a record. That's most CRUD apps, and it's exactly where an ORM's ergonomics pay for themselves. It is not a blockchain indexer, an event pipeline, or anything else ingesting a continuous, high-volume stream of writes. Those systems are where an ORM's row-at-a-time model of the world stops being a convenience and starts being the bottleneck.

A blockchain indexer is a clean example because the write pattern is unforgiving: every new block can carry hundreds or thousands of events (transfers, contract calls, state changes), and all of them need to land in Postgres before the indexer can call itself caught up. Three places an ORM adds real cost in that path:

Row-by-row INSERT instead of COPY. Postgres's COPY protocol is built specifically for loading large volumes of rows in one streamed operation, and it is meaningfully faster than even a well-batched multi-row INSERT. Most ORMs don't expose COPY at all. Their bulk-insert APIs (createMany, bulkCreate, and similar) are a real improvement over inserting one row per statement, but under the hood they're still building INSERT statements with many value tuples, not streaming through COPY. For an indexer processing thousands of events per block, that gap compounds every single block.
Object hydration on the way in and out. An ORM that maps every row to and from a model instance is doing real work per row: validation, type coercion, applying defaults. That cost is invisible at CRUD volumes and very visible at ingestion volumes, where it adds CPU and allocation overhead to every event in the stream, not just the ones a human is waiting on.
More, smaller transactions than the workload needs. An ORM's ergonomic defaults tend toward one transaction per logical operation. At indexer-scale throughput, that can mean far more transaction and WAL overhead than a design that deliberately batches writes into fewer, larger transactions per block.

Why this matters in production: all three of these show up as the same symptom eventually, insert throughput that can't keep up with the source it's indexing, and the indexer falls further behind with every block. If you've read Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers, this is the same category of problem from a different angle: it's not always replication configuration that causes an indexer to lag, sometimes it's the write path itself, one ORM abstraction away from the bulk-loading tools Postgres actually gives you for this job.

None of this means an ORM is disqualified from a write-heavy system. It means the write path deserves the same scrutiny as any other hot path in this article: measure it, and if it's the bottleneck, that's exactly the place to drop to COPY, hand-batched multi-row INSERTs, or a dedicated bulk-loading library, and keep the ORM everywhere else it's not in the way.

When the abstraction stops being useful

Every ORM's query builder is designed around the common case: filter, sort, join, paginate. The moment your requirement moves past that, you're negotiating with the abstraction instead of using it. In practice, that means:

Window functions (running totals, ranking within groups) — most ORM query builders don't model these at all.
Recursive CTEs (org charts, threaded comments, category trees) — almost always raw SQL, even in projects that otherwise avoid it entirely.
Bulk operations — an ORM's .update() in a loop is N statements; a single UPDATE ... WHERE id = ANY($1) is one. The difference compounds fast on large batches.
ON CONFLICT (upsert) and RETURNING — supported by some ORMs, but often with a narrower API than the SQL clause itself allows, especially bulk upserts (ON CONFLICT DO UPDATE across many rows at once) and the newer OLD/NEW aliases in RETURNING.
SKIP LOCKED for queue-style workloads — the standard pattern for letting multiple workers pull from the same table without blocking on each other's locked rows. Most ORM query builders have no vocabulary for it at all, so it's raw SQL from the start.
Partial and expression indexes — PostgreSQL can use indexes defined with a WHERE clause or an expression, but the generated SQL still needs to satisfy the conditions that make those indexes usable. An ORM won't necessarily make that obvious from the application code.
JSONB queries with GIN indexes — the operators that make these fast (@>, ?, #>>) are Postgres-specific and rarely have first-class ORM support.
Row-level and advisory locking — SELECT ... FOR UPDATE, pg_advisory_lock, and friends are concurrency primitives most ORMs expose thinly or not at all.
Reading EXPLAIN ANALYZE output — no ORM will do this for you. It's the one skill that stays entirely yours no matter which tool you pick.

None of this means avoid the ORM for these cases. It means know, ahead of time, that you'll drop to raw SQL here, and treat that as normal rather than a sign something went wrong.

Is your ORM a black box? It depends which one

So what should you actually use?

Not all ORMs hide the same amount, and the trade-off usually runs opposite to how much boilerplate they remove:

Approach	Abstraction	SQL visibility	Best fit
Prisma	High	Medium	CRUD-heavy product development
TypeORM	High	Medium	Traditional TypeScript applications, decorator-based models
Sequelize	High	Medium	Mature Node.js codebases already built around it
Drizzle	Medium	High	Type-safe apps that still want SQL-shaped queries
`pg` / `node-postgres`	Low	Full	SQL-heavy code and hot paths

There's no winner in that table on purpose. A team shipping CRUD-heavy internal tools benefits enormously from Prisma's or Sequelize's ergonomics and rarely hits the edges above. A team running a write-heavy, high-traffic public API needs to know exactly which SQL is running on every hot path, and might be better served by Drizzle or hand-written queries where it matters most. Plenty of production codebases mix approaches deliberately: an ORM for the CRUD majority, raw SQL for the handful of queries that are actually hot.

The actual takeaway

The ORM isn't the problem. Forgetting that an ORM is generating database operations is the problem. It doesn't remove the need to understand what Postgres is doing, it just moves the moment you're forced to learn it, from day one of the project to the incident where your database is drowning in unnecessary queries because the ORM issued forty small UPDATE statements instead of one bulk operation.

Use the ORM for what it's good at: the 80% of your code that's routine CRUD, type-safe and fast to write. But keep query logging on, read the SQL your ORM generates for anything on a hot path, and read the actual migration SQL before it hits production, not just the schema diff. The ORM is a very good translator. It is still worth knowing enough of the language yourself to catch it when it gets a sentence wrong.

A Reliability-Hardening Sprint: Isolation, Guardrails, and a Query Plan That Went From 45 Million to 206

Jatin Jain Saraf — Tue, 01 Sep 2026 06:58:59 +0000

A Reliability-Hardening Sprint: Isolation, Guardrails, and a Query Plan That Went From 45 Million to 206

The easiest reliability work to justify is the kind that follows an outage. It's much harder to carve out time for the kind that doesn't, fixes made because a risk is visible and worth closing, not because it already caused damage. Most of what's in this piece shipped in a single week without a single one of these changes being a response to a live incident. One of them was a response to an external report, handled the way you'd want it handled. Together they're a good picture of what proactive reliability work actually looks like, as opposed to the reactive kind that gets written up more often because it comes with a dramatic story attached.

Deciding which environments actually need their own failure domain

Multiple blockchain environments were originally sharing a single default Redis instance for caching. That's a reasonable starting point when the system is small, and a real liability once one environment's traffic pattern starts affecting another's: a cache invalidation problem, a memory pressure spike, or a slow operation in one environment can degrade or contaminate the cache for every other environment sharing that same instance, even though those environments have nothing to do with each other functionally. The fix wasn't to give every environment its own instance unconditionally, it was to identify which ones actually warranted the isolation and pull just those out: mainnet and one specific microchain, the one carrying enough independent traffic and importance to justify it, each got their own dedicated Redis instance, enforced strictly enough in code that the service refuses to start if that instance's address isn't configured. Everything else still shares a common default instance. Nothing about this fix was triggered by an incident. It closed a risk that was visible in the architecture before it had the chance to become one, and it did so by isolating exactly the environments that needed it rather than paying the operational cost of isolating all of them.

A timeout is a blast-radius decision, not a performance tweak

Every GraphQL resolver in the backend service, every query and every mutation, got wrapped with a wall-clock timeout, applied schema-wide rather than added resolver by resolver. It's tempting to think of a timeout as a performance optimization, something you add to make slow things feel faster. That's not really what this fix does. Its actual job is bounding how much damage a single slow or hanging query can do to everything around it: without a timeout, one resolver stuck waiting on a slow database call or a hung upstream request can hold a connection, a worker thread, or a piece of shared capacity indefinitely, degrading the whole service for requests that have nothing to do with the slow one. A timeout doesn't make the underlying slow query fast. It makes sure that whatever caused it to be slow stays contained to that one request instead of spreading.

The number worth quoting on its own

A separate fix, found during a broader review of query performance, is the single most quotable result in this entire piece. A function fetching network-wide transaction statistics was filtering its query by a timestamp column instead of the table's actual partition key. That distinction sounds academic until you see what it costs: filtering on the wrong column meant Postgres couldn't use partition pruning at all, forcing a full scan across every partition, including one that alone held over 400 gigabytes of data. The query planner's own estimated cost for that query, captured at the time, was just under 45 million. Correcting the filter to use the actual partition key was the first fix, and it dropped that same estimated cost to 206, from a query the planner expected to be genuinely expensive to one it treated as nearly free from a single change of which column a filter clause referenced. The system has since moved a step further than that filter fix. The current version of this function doesn't scan the partitioned transaction table at all, even with a correct filter: it reads instead from a separate, precomputed stats snapshot table, sidestepping the scan entirely. The comment left in that code still cites the original ~45 million cost as the reason the snapshot approach exists, a permanent, visible record of the problem the current design was built to avoid.

A related fix in the same family addressed a query fetching transaction lists that was missing partition-key predicates specifically inside its join clauses, which defeated the database's runtime partition pruning and forced scans across well over a hundred partitions where only a small number should have been touched. Both fixes are variations on the same underlying lesson: a partitioned table only delivers its performance benefit if every query touching it, including the parts buried inside joins and CTEs, actually filters on the partition key. Miss it in one place, and the partitioning strategy silently stops helping for that specific query while still looking correct.

An external report handled the right way

Not every fix here was found internally. An unauthenticated search endpoint was found, by an external report rather than an internal audit, to accept oversized input in a way that could trigger server errors. The response was layered rather than a single patch: a hard length cap on the search input rejects oversized requests outright before they reach anything expensive, the cache key for a search result is now a hash of the input rather than the input itself, so even a maliciously oversized string can't be used to construct an arbitrarily large cache key and exhaust cache memory that way, and a dedicated GraphQL security library was added on top, enforcing limits on query depth, aliases, and token count, with batched HTTP requests disabled outright so those same limits can't be sidestepped by sending an array of operations instead of one. The one control deliberately left off was a cost-limiting layer, everything else in that same security library was turned on. The detail worth noting isn't the specific fix, it's that an externally-reported issue got a defense-in-depth response instead of the narrowest possible patch that would have made the specific report stop reproducing.

The small bug that's a reminder about write-path consistency

One more fix, smaller in scope but worth including for what it represents: an address being inserted into a detail table without the padding format that the corresponding foreign-key target table always expected, causing insert failures against that constraint. It's a small, almost mundane bug, but it's a recognizable category: two different code paths writing related data that quietly disagree about a formatting convention neither one documented explicitly. It's the same underlying failure shape as the type-mismatch issues described elsewhere in this series, just at a smaller scale and caught before it caused a bigger one.

What this demonstrates

None of these seven changes, cache isolation, resolver timeouts, two separate partition-pruning fixes, a security hardening response, and a small write-path consistency fix, are individually dramatic. What makes them worth writing up together is that none of them were forced by an outage severe enough to demand attention. They were found by looking, in the middle of a normal week, for risks that hadn't yet become incidents, and closing them anyway. That's a much less visible kind of engineering work than fixing something already on fire, and it's usually the reason there's less to write up after the fact, not because less happened, but because nothing broke.

A Type Migration Across Three Services: VARCHAR to BIGINT at Scale

Jatin Jain Saraf — Tue, 01 Sep 2026 06:57:59 +0000

A Type Migration Across Three Services: VARCHAR to BIGINT at Scale

A block height stored as text instead of a number sounds like a small, easily-forgiven decision, and for a long time it is. The chain's own RPC layer returns numeric values like block height as strings in the first place, because some of those numbers exceed what can be safely represented as a standard number in JavaScript, so storing them as text on the way in feels like the path of least resistance. The cost of that decision doesn't show up until someone runs a range query and Postgres compares those "numbers" alphabetically instead of numerically, silently returning wrong results. Fixing that meant migrating the column's actual type from text to a proper integer at its source, the indexer that originally writes it, and then getting every downstream reader of that same column to agree with the new type. The migration itself turned out to be the least risky part of the whole change.

Why the column type was the easy part

Changing a column's underlying type in Postgres is a well-understood operation. The genuine risk in a change like this isn't the ALTER TABLE itself, it's every piece of application code downstream that was written against the old type's behavior without ever stating that assumption explicitly. A function that concatenates a block height into a string for logging, a comparison that relies on lexicographic ordering without realizing it, a serialization layer that formats a string differently than it formats a number, none of these show up as compile errors. They show up as runtime bugs, in production, only when that specific code path executes against the newly-typed column for the first time.

That's exactly what happened here. The migration itself landed cleanly across the backend service and the socket server, both of which read from the same underlying column. Two follow-up hotfixes were needed shortly after, both in functions that hadn't been touched by the migration directly but that broke because they'd been implicitly relying on the column's old string type when computing values like the latest block height and network-wide statistics. Neither hotfix was a large fix. Both were necessary, and both were the direct, predictable cost of a type change rippling through code that had never explicitly declared its assumption about that type in the first place.

What coordinating across services actually requires

The reason this qualifies as a coordination problem, not just a database migration, is that three services, the indexer writing the column, and the backend and the socket server reading it, are all deployed independently, on their own release cycles, each with its own copy of code that depends on this column's type. A schema change made once, in one place, has to be correctly consumed by every service reading that data, and there's no compiler shared across service boundaries to catch a mismatch between what the database now returns and what a given service's code still expects. The two hotfixes here are exactly what "coordination worked, but wasn't perfect on the first attempt" looks like in practice: the underlying migration itself never had to be rolled back or redone, but the code on the consuming side needed a second pass once the new type actually reached it in production.

What this demonstrates

The lesson here isn't "test your migrations more," though that's true of any migration. It's that a type change at the schema level is never really contained to the schema. It's a contract change with every piece of code, across every service, that reads that column, and the services most likely to break are the ones that were never explicitly written against a documented type contract in the first place, because they were written back when the column's actual type was assumed rather than verified. Catching both breakages quickly, shipping targeted hotfixes rather than a broader rollback, and confirming the underlying migration itself held throughout is the realistic version of what "coordinating a schema change across services" looks like when it goes well: not zero surprises, but small, contained ones, caught and fixed fast.

Built for a Multi-VM Future: The RPC v4 Readiness Work

Jatin Jain Saraf — Tue, 01 Sep 2026 06:56:59 +0000

Built for a Multi-VM Future: The RPC v4 Readiness Work

Some engineering work happens in response to an incident. This work happened in response to a feature that hadn't fully shipped yet. Supra's chain infrastructure introduced a new RPC version explicitly designed to support multiple virtual machines processing transactions within the same block, Move and EVM to start, with more planned. At the time this readiness work began, the indexing stack was still built entirely around the older RPC version and its Move-only assumptions. The task wasn't fixing something broken. It was mapping, precisely, everything that would break once the new capability went fully live, before it did.

A wrapper key that touches everything downstream

The documented change between RPC versions looks small in isolation: several top-level fields on a transaction, the sender, the payload, the output, the authenticator, gain a wrapper key identifying which virtual machine produced that data. The interesting part is that the codebase's own type definitions have already started anticipating this shape ahead of the actual rollout: the response types for these fields already model a VM-keyed wrapper alongside the currently-used one, with a placeholder branch for a second VM sitting unused in the type layer today, well before that second VM's data ever arrives over the wire. That's a reasonable way to de-risk a breaking change, let the types absorb the new shape early, in a place where being wrong costs a compile error, rather than doing it all at once under time pressure once the new format is actually live.

Type-level readiness isn't the same as working end-to-end, though, and that gap is the real scope of what's left. Every place in both the indexer and the backend that reads a transaction's sender, payload, output, or authenticator still needs to actually branch on which VM produced it and unwrap accordingly, across multiple files in both repositories, not just have a type that permits doing so. Event parsing, which currently reads from one fixed location in the response, needs equivalent VM-aware branching. None of these are individually difficult changes. All of them need to happen together, correctly, and be exercised against real second-VM data before the system can claim genuine multi-VM support rather than a type layer that's merely ready for it.

Why this is a cross-cutting change, not a parser update

The reason this readiness work spans far more than the RPC-consuming code is what multi-VM support actually means at the data layer. A single block will be able to contain transactions from more than one virtual machine simultaneously, and those transaction types don't share a schema. An EVM transaction's meaningful fields, sender, recipient, calldata, value, look nothing like a Move transaction's sender, function, and payload. That difference has to be handled at every layer: the indexer needs a distinct parser per virtual machine, plus a column identifying which VM produced each row, since a single flat schema can't represent both shapes cleanly. The database question is still genuinely open rather than settled: either add a dedicated table per virtual machine, one for EVM alongside the existing Move-shaped ones, and eventually a Solana or Sui equivalent, or keep one shared transaction table and add a JSONB column to hold whatever VM-specific fields don't fit the common shape. Neither is free. Separate tables keep every VM's data cleanly typed and indexed but mean every cross-VM query, "show me the last hundred transactions regardless of which VM ran them," has to union across tables that don't share a schema. A shared table with a JSONB escape hatch keeps queries simple at the cost of reintroducing exactly the kind of untyped, hard-to-index column this same series describes paying down elsewhere. That tension hasn't been resolved yet, and naming it as unresolved is more honest than picking a side before the second VM actually arrives and reveals which cost matters more in practice. Even something as basic as the wallet triggers described elsewhere in this series need updating regardless of which path gets picked, because EVM addresses use a different length and format than Move addresses, and any logic keyed on address shape needs to handle both correctly. The backend needs VM-aware logic in every resolver that returns transaction details, and the frontend needs to render two structurally different transaction types side by side in the same block view, with address formatting that doesn't assume every wallet address looks the same.

Doing the scoping before the deadline, not during it

At the time of this work, a multi-VM development network already existed and was publicly reachable, with a frontend deployed against it, but the actual multi-VM parsing, schema, and UI work hadn't been built yet, meaning that live deployment likely only reflected Move transactions in practice despite technically running against multi-VM-capable infrastructure. The rollout plan follows a pattern the team had already used successfully once before, when the previous RPC version transition happened: enable the new endpoint behind a feature flag on the testnet environment first, validate the full indexing pipeline end to end against real data in that shape, and only then flip the same flag on production once the chain itself makes the new version available there.

What this demonstrates

The value in this piece of work isn't a shipped feature, because at the time of writing, the full multi-VM parsing and schema support hadn't been built yet either. The value is in having already mapped, field by field and file by file, exactly what breaks and where, ahead of the deadline that would otherwise force that mapping to happen under pressure. Readiness work like this rarely gets noticed when it goes well, because going well looks like nothing happened: the feature arrives, the flag flips, and the system handles it. The alternative, discovering the breaking changes live, in production, once transactions from a second virtual machine actually start arriving, is the outcome this scoping work exists specifically to prevent.

Becoming the Readiness Gate for Another Team's Scale-Up

Jatin Jain Saraf — Tue, 01 Sep 2026 06:55:59 +0000

Becoming the Readiness Gate for Another Team's Scale-Up

Most engineering work gets justified by its own metrics: faster, cheaper, more reliable. Some of the most consequential work gets justified by someone else's roadmap depending on it. That was the actual shape of the concurrency and trigger-removal work described elsewhere in this series. It wasn't scoped because SupraScan's own dashboards demanded it. It was scoped because a separate team, building automated on-chain task execution, had explicitly tied their own launch decision to it.

A capability that was ready, gated by a system that wasn't yet

The automation team had already done real validation work of their own: their Automation V2 system had been privately tested to handle 100,000 concurrent tasks on a private test network. That's not a small number, and it's not a hypothetical claim either, it was measured. And yet, that team explicitly agreed not to raise the live, on-chain capacity limits for real automated tasks on a real network until the indexer's ability to handle that scale was confirmed.

That's an unusual kind of commitment for one team to make around another team's infrastructure, and it only makes sense once you understand what those capacity limits actually control. The chain enforces a default cap on how many tasks a single account can register, alongside a separate cap on the total gas budget those tasks can consume, plus a lower, distinct ceiling for tasks registered through a governance process rather than a normal account. None of these caps are hard-coded ceilings in the underlying protocol logic; every one of them is adjustable through a standard configuration update, with no code change required. The only thing standing between the current limit and a much higher one is a decision to raise it, which means the real gate on scaling automation usage was never a protocol constraint. It was confidence that the systems consuming the resulting transaction volume, this indexer chief among them, wouldn't buckle the way it already had once, during the four-wallet contention incident that first exposed the trigger-locking problem this same work fixed.

Why the dependency was structurally sound, not just cautious

It would be easy to read this as one team simply being conservative. The more precise read is that the dependency was well-scoped because of how automation execution actually behaves on-chain: each registered task executes at most once per block. That means the worst-case number of executions in any single block scales linearly with whatever the capacity cap is set to, not multiplicatively or unpredictably. A team raising that cap can reason precisely about the additional load it introduces, which is exactly the kind of predictable, boundable increase that makes "we'll raise this once the downstream system is proven ready" a decision you can actually commit to, rather than a vague promise to be careful later.

That precision is also what made this indexer's readiness work legible as a real gate rather than a soft suggestion. The automation team didn't need to trust a general assurance that "the indexer should be fine." They could point to a specific configuration value, know exactly how it maps to worst-case block-level load, and wait for confirmation that the exact failure mode which had already happened once, contention on a small number of hot wallet rows under concurrent load, had been genuinely closed.

What this demonstrates

The trigger-removal and concurrency work described in this series would have been worth doing on its own merits, the four-wallet incident alone justified it. What makes it a more interesting case study than a standard reliability fix is that a different team's product roadmap was explicitly, formally waiting on it. That's a different kind of leverage than fixing your own system's stability. It's being the reason someone else's already-validated capability sat unused on a real network until the work described here shipped, verified, across every environment. Infrastructure work rarely gets to point at a concrete, named dependency like that. When it does, it's worth naming directly instead of folding it quietly into a general reliability narrative.

What It Would Take to Hit 500K TX/Sec, and Why That's the Honest Answer

Jatin Jain Saraf — Tue, 01 Sep 2026 06:54:59 +0000

What It Would Take to Hit 500K TX/Sec, and Why That's the Honest Answer

Capacity planning conversations have a predictable failure mode: someone names a big target number, and the answer that gets built is optimized for sounding capable of hitting it, not for being honest about whether it actually can. This is the opposite of that. A benchmark measured SupraScan's sustained throughput at the time, three concrete scaling levers were evaluated with honest multipliers instead of aspirational ones, and the combined answer, done as carefully as possible, still fell 25 times short of a 500,000 transaction per second target. The useful output of this exercise wasn't a bigger number. It was knowing exactly why that number wasn't reachable with this architecture, and what would actually be required if it mattered enough to pursue.

The baseline, and where the bottleneck actually was

At the time of this planning exercise, a benchmark run measured roughly 110 to 118 transactions per second sustained, across ten running instances. The instinct when a number looks low is to assume the database is the constraint. It wasn't. In the same benchmark window, Postgres itself sat at 50 percent CPU idle, meaning it had considerable spare capacity at that load. The actual bottleneck was the RPC layer: 34 socket-hangup errors inside a 37-second window, with latency climbing past 600 milliseconds. The system wasn't write-constrained. It was constrained by how fast data could be safely pulled out of the chain node in the first place.

Three levers, evaluated without rounding up

Three concrete changes were assessed, each given a multiplier grounded in what it actually does rather than what would sound good in a roadmap slide.

Switching from a pull-based RPC pattern, roughly two RPC calls per block today, to a push-based WebSocket feed removes the single biggest blocker at the current scale: a thundering-herd effect on the RPC endpoint that gets worse as more instances are added, and which currently caps how many instances can even run productively. But this lever is explicitly a ceiling-remover, not a throughput multiplier on its own. It adds zero additional database write capacity by itself; it only clears the obstacle currently in front of scaling out further.

Sharding write traffic is the only one of the three levers that actually multiplies database capacity, because the current architecture writes through a single Postgres primary. Splitting into 8 to 16 shards was judged a plausible, though explicitly not linear, 8 to 16x increase in write ceiling. The honest caveats matter here: cross-shard queries, anything resembling a leaderboard spanning many wallets, get meaningfully harder once data is split this way, and a real, observed pattern of hot-shard skew, one specific address firing continuous automated self-transactions, actively eats into the theoretical gain by concentrating load unevenly across whichever shard that address lands on.

Replacing trigger or sync-based wallet counting with write-ahead-log-derived counters targets the hot-row contention problem directly, the same contention responsible for the multi-day lag incident described elsewhere in this series. Its honest impact is bounded: an estimated 20 to 50 percent reduction in per-transaction database work, explicitly not a throughput multiplier in its own right.

The math, done honestly, and the verdict it produces

Combining all three levers, removing the RPC bottleneck, applying an 8 to 16x sharding gain, and applying a 1.3 to 1.5x reduction from WAL-based counting, produces a combined estimate in the range of 2,000 to 20,000 transactions per second. Against the measured 118 TPS baseline, that's a genuinely significant 15 to 150x improvement, nothing to dismiss. (Production has since grown to 500–1,000 TPS. Even from there, 500K is 500 to 1,000 times away.)

Measured against a 500,000 transaction per second target, even the optimistic end of that range falls roughly 25x short. That's the actual verdict: not reachable this way. Getting to 500,000 for real wouldn't mean tuning the current design harder. It would require a different architecture tier entirely: dozens to hundreds of shards rather than the 8 to 16 evaluated here, a move away from row-by-row ORM inserts with multiple dependent child-table writes per transaction toward bulk or columnar ingestion, since that per-transaction write pattern doesn't scale to that volume regardless of how many shards it's split across, and very likely a genuinely distributed database engine built for that scale, not a set of independently-sharded single-node Postgres instances glued together.

Why saying no was the right call

The easy version of this exercise ends with a roadmap slide showing a path to 500,000 TPS built out of optimistic rounding at each step. The honest version treats a target that large as an architecture-tier decision, a different system built differently from the ground up, not a tuning destination you inch toward by stacking incremental wins. The WAL-based counting work is still worth doing on its own terms, the hot-row contention it targets is real and worth fixing regardless. But it doesn't get oversold internally as a credible step toward a 500,000-tier system, because the math says plainly that it isn't one. Knowing the difference between a real, bounded improvement and a genuine step toward a stated target is most of what capacity planning actually is.

From a Redis Relay to a Direct Feed: Rebuilding the Real-Time Pipeline

Jatin Jain Saraf — Tue, 01 Sep 2026 06:53:59 +0000

From a Redis Relay to a Direct Feed: Rebuilding the Real-Time Pipeline

The old design for getting live block and transaction data onto SupraScan's frontend worked, and that's part of what makes it a useful case study. Nothing was broken. The indexer published every block and transaction to Redis pub/sub channels, a socket server subscribed to those channels and rebroadcast the data to connected browsers, and a second, entirely separate Redis stream fanned raw transaction data out to an external team's automated systems. It was a working relay with an extra hop, and the decision to remove that hop, across five separate repositories, is a good example of what disciplined deletion actually looks like.

Old: an extra hop through Redis

  Chain Node --> Indexer --> Redis (pub/sub) --> Socket Server --> Browser
                                  |
                                  +--> Stream Redis --> External team

New: the socket server talks to the chain directly

  Chain Node <==WS==> Socket Server --> Browser
                            |
                            +--> Redis (last 10 blocks / 20 txs, reconnect snapshot only)

The shape of the fix is visible in that comparison: the old path had the indexer sitting between the chain and the live feed even though the indexer's own job is writing to Postgres, not serving live updates. The new path removes it from that role entirely, and Redis goes from being the transport to being a small cache for one specific case, a client reconnecting mid-session.

The new shape: a client, not a relay

The replacement inverts the socket server's role. Instead of subscribing to an internal Redis channel that the indexer populates, the socket server itself becomes a WebSocket client, connecting directly to each blockchain node's own live feed for every environment it serves. The moment a message arrives from the node, it's broadcast straight to every connected browser, with no buffering interval and no per-tick cap holding messages back to smooth them out. Redis still plays a role, but a much smaller one: the last handful of blocks and transactions get cached there purely so a client that reconnects mid-session has something to show immediately, a snapshot, not the primary transport for live data anymore.

Proving the old path was actually dead before deleting it

The discipline here is in what happened before any code was removed, not in the replacement architecture itself. Before deleting the old pub/sub channels, a live subscriber count check against both the QA and production Redis instances confirmed those channels had zero active subscribers. That's a deliberate step, not an assumption: the team didn't reason their way to "nobody should be using this anymore," they checked, live, against production, and only then treated it as confirmed dead traffic. On top of that, the socket server's environment-enable configuration was flipped off for the two environments being migrated, on both QA and production, before the code that implemented the old path was even merged, specifically so there was no window where a live user could hit a code path that had already been half-removed.

The rollout itself crossed five separate repositories, and the merge order mattered: a shared library and the frontend went first, then the socket server, then the backend, then the indexer last. Each merge required a clean build and a grep-verified check that every deleted symbol genuinely had no remaining references anywhere in that repo, before moving on to the next one in the sequence.

Scope that grew on purpose, and a landmine avoided

One piece of functionality got folded into this same change deliberately, not accidentally: a service that computed live transactions-per-second by scanning a sliding window of recent blocks on every single block processed. It turned out to be more computationally expensive than either of the two systems that had already superseded it elsewhere in the stack, a lighter-weight live poll directly against the chain node's own metrics endpoint for the homepage display, and a completely separate metrics-accumulator pipeline feeding the analytics page. Removing a redundant, expensive computation while already touching this code was the right call, and it was made with the author's knowledge rather than discovered as scope creep after the fact.

One specific risk was flagged and handled explicitly during planning: the socket server's environment-enable configuration and its corresponding WebSocket routes had to be removed together, in the same change. Removing one without the other would either leave a live route pointing at handler code that no longer existed, or leave a stale configuration blocking routes that had already been deleted, both of which are the kind of half-finished cleanup that causes an outage days later when someone finally notices.

A second, opposite kind of care went into what wasn't removed. The indexer has its own internal publish and subscribe loop, structurally identical to the exact pattern being deleted everywhere else in this change. But this one is dual-purpose: the indexer's own main processing loop reads its current chain height from that same channel. Removing it under the assumption that it matched the pattern being cleaned up everywhere else would have broken block ingestion itself, not just an external, already-dead feed. It was explicitly flagged and kept, a reminder that the same-looking code in two places doesn't always mean the same thing.

The number worth keeping

A separate but related change in the same era replaced a cold-connect page load's dependency on a live RPC round-trip with a Redis-backed cache instead. The measured result: homepage cold-start hydration latency dropped from 60 seconds to 230 milliseconds. That's not a rounding improvement, it's a roughly 260x reduction in the worst-case time a new visitor waited before seeing live data, and it shipped as part of the same broader effort to make the real-time path leaner and less dependent on hops that weren't earning their cost.

What was accepted, not fixed

The new architecture isn't presented as flawless, and two specific limitations were knowingly accepted rather than solved. There's no per-client replay or acknowledgment: a client that disconnects and reconnects gets whatever the current cached snapshot happens to be, not a replay of exactly what it missed while offline. And the upstream connection from the socket server to each blockchain node has a roughly one-second reconnect gap, during which a block can genuinely be missed by that specific connection. Both are documented, known tradeoffs of favoring a simpler, more direct architecture over a more complete but heavier one, not oversights discovered later.

What this demonstrates

Deleting a working system safely is a different skill than building a new one. Every step here, checking live subscriber counts before removing channels, flipping configuration off before merging code, sequencing five repositories in a specific dependency order, and correctly distinguishing a truly dead code path from a structurally identical but load-bearing one, is about reducing the risk of the deletion itself, not about the destination architecture being clever. The destination here is genuinely simpler than what it replaced. Getting there without an outage was the actual work.

DEV Community: Jatin Jain Saraf

Turning Your Blockchain Indexer Into a Paid API Without Slowing Down Your Explorer

Turning Your Blockchain Indexer Into a Paid API Without Slowing Down Your Explorer

The Kitchen and the Counter

The Architecture at a Glance

Layer 1: Isolate Reads on Dedicated Replica Pools

Layer 2: Serve From Read Models, Not Raw Tables

Layer 3: Cache by Finality

Layer 4: Plans, Credits and the Gateway

Two limits, not one

Credits priced by cost

Plans

Checking limits without touching the database

Redis admits, Postgres bills

Tell clients where they stand

Layer 5: Bulkheads So Free Users Can't Hurt Paying Ones

Layer 6: Don't Make Clients Poll for New Blocks

Layer 7: Bulk Data Doesn't Belong in the API

Observability: Know Who Is Expensive

A Checklist Before You Launch

The Takeaway

Your Postgres Indexes Can Break Without a Single Line of Code Changing

Your Postgres Indexes Can Break Without a Single Line of Code Changing

A Dictionary Sorted by One Alphabet, Searched by Another

Where the Sort Order Actually Lives

Reproducing It

Symptom 1: the row vanishes from indexed lookups

Symptom 2: the unique index accepts a duplicate

Symptom 3: the fix fails too

Where This Bites in Real Systems

Detection

The warning you probably never saw

Find the indexes at risk

Prove it with amcheck

The Fix, in the Right Order

Making Collation Drift Boring

Pin the C library with the Postgres version

Use a collation that can't drift with the OS

ICU is better, not immune

The Takeaway

Go Deeper

References

Your UUID Primary Keys Are Quietly Taxing Every Insert

A Library Where Every New Book Goes on a Random Shelf

The Benchmark

Cost 1: The Index Stops Fitting in Memory

Cost 2: Half-Empty Pages

Cost 3: Full-Page Writes Inflate WAL

UUIDv7: Time First, Random Second

What UUIDv7 Still Costs

Which Key Should You Use?

Already Running on UUIDv4?

The Takeaway

The TOAST Tax: How Unbounded JSONB Arrays Cost My Blockchain Indexer 5TB

TL;DR

The Setup: Why JSONB Felt Like the Right Call

TOAST in One Analogy: The Off-Site Storage Unit

Cost #1: Reading One Key Can Mean Detoasting the Document

Cost #2: Updating One JSONB Key Can Rewrite the Whole Value

Cost #3: The Storage Unit Needs Its Own Cleaning Crew

How to Check Whether You Have the Same Problem

1. Find your biggest TOAST tables

2. Measure your JSONB sizes

3. Check compression

4. EXPLAIN ANALYZE, measured honestly

The Fixes, From Cheapest to Most Work

1. Switch to lz4

2. Extract hot keys into real columns

3. Expression indexes for known lookups

4. GIN, for what it's good at

5. Normalize unbounded arrays

The Zero-Downtime Migration

When JSONB Is Still the Right Answer

The Rule I Use Now

Related Articles

Every Postgres Write Triggers Five Background Processes. Here's What Each One Actually Does.

WAL: the black box that makes crash recovery possible

Checkpoints: the point WAL gets allowed to forget the past

Wait events: the window into both of the above

Vacuum: cleaning up after MVCC, not just reclaiming disk