Optimizing Postgres Write Throughput: B-Trees, HOT Updates, and Heap Fragmentation

Postgres write pain I hit on elangodev.com

My Supabase Postgres project powers blog posts, analytics events, chat messages, and dashboard metadata on elangodev.com. Read latency stayed fast after indexing slugs, but write-heavy paths — analytics inserts on every navigation and JSONB updates on draft posts — started showing autovacuum lag. I profiled HOT update ratios the same way I would on a client production app, not a textbook example.

This guide explains Heap-Only Tuple updates, index bloat, fillfactor tuning, and vacuum strategy using patterns I applied when analytics table churn grew after publishing long-form articles. If you run Supabase on a portfolio or SaaS with frequent row versions, these checks belong in your weekly maintenance habit.

The anatomy of Postgres writes

As applications scale, teams often focus on read latency via indexing strategies. On this site, blog slug lookups were easy wins. Write-heavy workloads told a different story: I/O from heap fragmentation and unnecessary index maintenance on the analytics table. Understanding how PostgreSQL manages physical storage at the tuple level became critical when I debugged slow dashboard charts — not just API response times.

Every UPDATE creates a new row version. Dead versions stay until vacuum reclaims them. Indexes that point at those versions amplify write cost when the updated columns are indexed. That is the core of write amplification on a small Supabase plan: you feel it as rising disk I/O and longer autovacuum runs long before you hit connection limits.

Understanding Heap-Only Tuple (HOT) updates

When you perform an UPDATE in PostgreSQL, it does not modify data in-place. Postgres creates a new row version (tuple). If updated columns are indexed, Postgres must update the index too — index bloat and extra I/O. Enter HOT updates.

A HOT update occurs when the new tuple fits in the same physical page as the old tuple and none of the indexed columns changed. Because the row's location relative to the index stays stable, Postgres skips the index update. That drastically reduces write amplification on tables like analytics where I append rows but occasionally patch geo fields.

On elangodev.com I keep FILLFACTOR at 85–90% on high-update tables so pages retain space for HOT chains. When hot_ratio dropped below 0.6 on analytics, I moved volatile columns to a side table — the same fix I recommend for client write-heavy entities.

ALTER TABLE analytics SET (fillfactor = 85);
ALTER TABLE posts SET (fillfactor = 90);

Monitoring HOT ratio in Supabase

Every non-HOT update creates index pointer churn. Frequent updates on indexed columns inflate B-tree depth — more page reads per lookup. I run this query in the Supabase SQL editor after major traffic spikes:

SELECT relname, n_tup_upd, n_tup_hot_upd,
  ROUND(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0), 3) AS hot_ratio,
  n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;

When I built AnalyticsTracker.jsx, I deferred inserts with requestIdleCallback so writes batch off the critical interaction path. That UX choice also reduced concurrent update pressure on the same heap pages during INP-sensitive navigation — a win I measured on this production app.

I also track n_dead_tup relative to live rows. A rising dead-tuple count with a stale last_autovacuum means vacuum is losing the race. That showed up after I ran bulk editorial rewrites against the posts table from scripts/rewrite-original-content.js.

Managing heap fragmentation

Fragmentation is a silent killer. Updated or deleted rows leave dead space until VACUUM reclaims it. In high-concurrency environments, autovacuum must keep pace. If your hot_ratio is low, the app likely triggers full index updates on every change. I applied three fixes on elangodev.com:

• Separate hot columns: I stopped updating indexed metadata columns on every analytics insert; append-only inserts HOT cleanly when indexed columns stay unchanged.
• Index discipline: I index created_at for dashboard charts but avoid indexing low-cardinality geo fields alone.
• Aggressive autovacuum on analytics: I lowered autovacuum_vacuum_scale_factor for the analytics table after blog traffic doubled post-launch.

ALTER TABLE analytics SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

JSONB updates that destroyed HOT eligibility

Early versions of my draft editor updated large JSON blobs on every keystroke — catastrophic for HOT eligibility when GIN indexes were involved. I debounced writes client-side and narrowed updates to changed keys only. That single product decision improved hot_ratio more than any server knob.

For blog content specifically, I store HTML in a text column and avoid indexing the body. Search and filtering run on slug, status, and tags. The body is fetched by primary key on the post detail route (app/blog/[slug]/page.jsx), so write cost stays on the heap, not on a GIN index of the entire article.

What I changed after profiling

My weekly checklist for the elangodev.com Supabase project:

1. Run the HOT ratio query and screenshot results into the engineering notes.
2. Confirm analytics inserts remain append-only from AnalyticsTracker.
3. After bulk rewrites or seed scripts, check n_dead_tup on posts and force VACUUM (ANALYZE) posts; if autovacuum lags.
4. Review new indexes before shipping — every index is a write tax.

High-performance Postgres on a site like mine is not abstract theory. It is how fast blog pages feel when analytics, chat, and editorial rewrites all write to the same Supabase project. Prioritize page layout health, measure HOT ratios weekly, and tune autovacuum before adding read replicas you do not need yet.

Practical takeaways for portfolio-scale Supabase

You do not need a dedicated DBA to keep a single-region Supabase project healthy. You need visibility into HOT eligibility, honest indexing choices, and application write patterns that respect how Postgres versions rows. On elangodev.com those habits kept dashboard charts snappy while I continued publishing 800+ word technical posts without turning the database into the bottleneck.

If you only remember one thing: measure hot_ratio before you add another index. Most of my write pain came from updating indexed columns unnecessarily — not from missing hardware.