4 min read
A war story about “PostgreSQL-compatible” databases and the dangerous seam under the storage engine.
We had a distributed SQL cluster behaving perfectly fine until one maintenance operation quietly destroyed every query plan in production.
The worst part: every command succeeded.
The database never crashed.
No corruption alerts.
No failed migrations.
Just millions of rows suddenly being read through sequential scans.
This happened because we treated a distributed LSM-tree database like heap-based PostgreSQL.
The result was a planner catastrophe hidden behind PostgreSQL compatibility.
The setup
We had a large partitioned transaction-processing system.
Monthly partitions.
Heavy write throughput.
Financial-style transactional workloads.
Old partitions were dropped to reclaim storage.
Disk usage barely moved.
At this point, the natural PostgreSQL instinct kicked in:
VACUUM FULL ANALYZE ...
That instinct was wrong.
The database was YugabyteDB.
That matters far more than most people think.
The first dangerous assumption
“YugabyteDB uses PostgreSQL.”
Technically true.
Operationally misleading.
YugabyteDB uses PostgreSQL’s:
- SQL parser
- planner
- executor
- catalogs
- wire protocol
But not PostgreSQL’s storage engine.
That is the seam.
PostgreSQL stores rows in heap files.
YugabyteDB stores rows in a distributed LSM-tree storage engine built on RocksDB and DocDB.
Those are completely different worlds.
The SQL layer looks identical.
The physical behavior is not.
The maintenance operation that broke production
The cluster had large partitioned tables:
- transaction tables
- reconciliation tables
- account snapshot tables
- dedupe tables
- operational batch tables
The cleanup process included:
- Dropping old partitions
- Running
VACUUM FULL ANALYZE - Expecting PostgreSQL-style disk reclaim
The disk reclaim never really happened.
But query plans absolutely changed.
Immediately after the vacuum:
- index scans disappeared
- partition pruning stopped working
- queries degraded into sequential scans
Even highly selective queries.
The smoking gun
This query exposed the issue:
SELECT reltuples, relname
FROM pg_class
WHERE reltuples != -1;
Many partitioned parent tables suddenly showed:
reltuples = 0
Not empty.
Actually populated.
But statistically empty according to the planner.
That distinction matters.
The PostgreSQL planner trusts pg_class.reltuples.
Once the planner believes a table has zero rows:
- cardinality estimation collapses
- partition pruning becomes unreliable
- index scan cost models break
- sequential scans become “cheap”
The optimizer starts behaving irrationally because its metadata became irrational.
The database still had the data.
The planner stopped believing it existed.
Why this happens in YugabyteDB
This is the core architectural mismatch.
PostgreSQL behavior
In PostgreSQL:
VACUUM FULL
→ rewrites heap file
→ ANALYZE samples new heap
→ reltuples updated correctly
Heap rewrite and statistics collection are tightly connected.
The planner has a stable physical structure to analyze.
YugabyteDB behavior
In YugabyteDB:
VACUUM FULL
→ triggers DocDB compaction
→ no heap rewrite exists
→ planner statistics become inconsistent
There is no heap file.
There is no heap rewrite.
There is no PostgreSQL-style physical storage layer underneath.
The command still exists for compatibility.
But its assumptions no longer hold.
This is where “PostgreSQL-compatible” stops being PostgreSQL.
The hidden trap with partitioned tables
The issue becomes much worse with partitioned tables.
When parent partition metadata reports:
reltuples = 0
the planner struggles to estimate partition cardinality.
That damages partition pruning.
Instead of scanning:
1 relevant partition
the planner starts considering:
every partition
On large distributed systems, this becomes devastating very quickly.
Especially under transactional workloads.
The fix
The recovery process was surprisingly simple once the actual problem was understood.
Reset statistics to “unknown”:
UPDATE pg_class
SET reltuples = -1;
Then rerun standalone:
ANALYZE ...
without VACUUM FULL.
Why this works:
-1tells the planner statistics are unknown- planner falls back to safer defaults
- standalone
ANALYZEsamples live DocDB tablets correctly - valid row estimates return
- index scans return
- partition pruning returns
Latency normalized after statistics recovery.
The bigger lesson
This incident was not caused by a database bug.
It was caused by an architectural assumption.
The dangerous phrase is:
“It’s PostgreSQL-compatible.”
Compatibility at the SQL layer does not imply compatibility at the storage layer.
That distinction becomes critical for:
- VACUUM behavior
- compaction
- storage reclaim
- planner statistics
- WAL assumptions
- heap maintenance
- physical clustering
- page-level operations
Most ORM-based applications never hit this seam.
Operational tooling absolutely does.
What should be used instead
In distributed LSM-tree databases:
Good
DROP PARTITION
ANALYZE
native compaction tools
automatic background compaction
Dangerous
VACUUM FULL
heap-oriented assumptions
physical rewrite assumptions
For YugabyteDB specifically, disk reclaim belongs to:
DocDB compaction
not heap rewrites.
That is a storage-engine concern, not a PostgreSQL heap concern.
The LLM problem
The original recommendation came from an AI assistant.
The recommendation was perfectly valid for PostgreSQL.
The database was not actually PostgreSQL.
This is becoming a larger operational problem across the industry.
LLMs often reason from SQL syntax compatibility instead of storage architecture.
That works until maintenance commands appear.
The moment commands touch physical layout, storage semantics matter more than SQL compatibility.
The real verification question is never:
“Does this database support PostgreSQL syntax?”
The real question is:
“Does this database preserve PostgreSQL storage semantics?”
Those are completely different things.
What changed after this incident
Three operational rules were added immediately:
- Never run
VACUUM FULLon YugabyteDB - Treat PostgreSQL-compatible systems as separate databases operationally
- Verify storage-engine behavior before using PostgreSQL maintenance commands
A monitoring rule was also added:
Detect reltuples = 0 on non-empty partitioned tables
because this failure mode is extremely silent.
Nothing crashes.
The planner simply becomes progressively detached from reality.
Final takeaway
Distributed SQL databases are not “better PostgreSQL.”
They are hybrid systems.
PostgreSQL SQL layer.
Distributed storage layer.
Consensus replication.
Tablet sharding.
LSM compaction.
Eventually you hit the seam between those worlds.
VACUUM FULL is one of those seams.
And production is usually where you discover it.
Products from this work
Available
Aika Stream
A remote-first player for media sources you are authorized to use. Bring your own content, then browse, search, and play.
In development
Airo Coins
A private space for organizing essential financial records, with locked access and local-first boundaries. It is not a cryptocurrency, token sale, investment product, bank, or financial adviser.
Platform
Airo
The shared foundation under the modules: interface, local-first services, and native performance boundaries.


