AtomSlice
中Get Started

Indie hacker

Chinese Tech Article → English X Thread

Turn a 3,000-word Chinese technical deep-dive into an English X thread: one mechanism per post, identifiers intact.

· Case study ·Chinese

Illustrative example: the scenario and figures below are for illustration only, not a client result and not a performance claim.

Before → After

Before

A Chinese draft nobody outside China can read

Language
Chinese
Audience
Domestic readers only

After

A publishable English asset

Language
English
Audience
English-speaking buyers and builders
Output
7 tweets

01What went in

Chinese source

PostgreSQL 的 MVCC 会把死元组一直留着，直到 VACUUM 把它们清掉；写入一快，死元组的堆积速度就超过清理速度，表于是越写越大。

核心论点：PostgreSQL MVCC 保留死元组直到 VACUUM 清理。高写入吞吐下，死元组产生速度 > autovacuum 清理速度 → 表膨胀 → 索引膨胀 → 膨胀索引上的全表扫描 → 延迟飙升。

关键数字：

  • 4000 万行表，2000 写入/秒
  • Autovacuum 触发阈值：20% 死元组
  • 实际膨胀：表 340%、索引 280%
  • P99 延迟：12ms → 2400ms

应用方案：autovacuum_vacuum_scale_factor = 0.01、autovacuum_vacuum_cost_limit = 10000、为热点查询加部分索引。

02Five angles out of one article

"Everyone tells you 'PostgreSQL handles concurrency automatically.' The data shows: default autovacuum fails at 2K writes/sec on 40M rows. Here's the fix."
  • Targets a widespread misconception ("PostgreSQL just works")
  • Leads with concrete failure data (not theory)
  • Promises a specific, reproducible fix
The cross-market context panel in AtomSlice: three framings flagged before rewriting
Angles extracted · AtomSlice

03What came out

Tweet 1 (Hook)

PostgreSQL's "automatic" vacuum is a lie at scale.

40M rows. 2K writes/sec. Default settings → 340% table bloat. P99 latency 12ms → 2,400ms.

Your database isn't broken. Your autovacuum config is.

🧵 7 tweets on the fix that saved our production DB.

Tweet 2 (The Mechanism)

MVCC keeps every dead row version until VACUUM reclaims it.

High write throughput = dead tuples accumulate faster than autovacuum wakes up.

Default trigger: 20% dead tuples. At 2K writes/sec, you hit 340% bloat before autovacuum even starts its second cycle.

Tweet 3 (The Symptom)

What 340% bloat looks like:

  • Table: 40GB → 136GB (dead tuples)
  • Indexes: 8GB → 22GB (dead pointers)
  • Sequential scans on bloated indexes = CPU at 100%, P99 at 2.4s

Your monitoring shows "high CPU." The root cause is vacuum debt.

Tweet 4 (The Fix — Config)

Three settings that changed everything:

autovacuum_vacuum_scale_factor = 0.01    -- trigger at 1%, not 20%
autovacuum_vacuum_cost_limit = 10000     -- 10x default IO budget
autovacuum_max_workers = 4               -- parallel vacuum (if cores allow)

Restart PostgreSQL. Watch pg_stat_progress_vacuum.

Tweet 5 (The Fix — Indexes)

Partial indexes for hot queries > full indexes on bloated tables.

-- Before: full index on 40M rows (22GB with bloat)
CREATE INDEX ON events (user_id, created_at);

-- After: partial index on last 30 days (2.1GB, 95% of queries)
CREATE INDEX ON events (user_id, created_at) 
WHERE created_at > NOW() - INTERVAL '30 days';

Query latency: 2,400ms → 8ms. Index size: 22GB → 2.1GB.

Tweet 6 (The Monitoring)

Add these to your dashboards before you need them:

-- Bloat ratio (run weekly)
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as bloat
FROM pg_tables WHERE schemaname = 'public';

-- Autovacuum lag
SELECT * FROM pg_stat_progress_vacuum;

Tweet 7 (CTA + Takeaway)

Default autovacuum = "good enough for hobby projects."

Production at scale = you own the vacuum strategy.

The fix isn't magic. It's respecting the physics: dead tuples don't delete themselves. You configure the reaper, or the reaper buries you.

Reuse the same moves

  1. 01
    Lead with the failure number, not the fix"340% bloat" stops the scroll; the config is the payoff, not the hook.
  2. 02
    Keep the code block that changed the outcomeReaders forward reproducible configs. They do not forward summaries.
  3. 03
    One concept per tweetMVCC, the symptom, the config fix, the index fix, the monitoring — five beats, seven tweets, none of them crowded.
  4. 04
    Name the monitoring you addedA fix without a way to confirm it stays fixed is a rumor with better formatting.
  5. 05
    Translate the conclusion, not the proseKeep `autovacuum_vacuum_scale_factor` exactly as written; rewrite only the framing around it.
  6. 06
    End on the mechanism, not a CTA"Dead tuples don't delete themselves" is the line people quote back to you.

Keep reading