SQL Server → Postgres Migration Nobody Talks About

Fragmentation Bug That Can Cost You Weeks

Every migration conference talk, every vendor whitepaper, every how to move off proprietary databases thread on Reddit it’s all Oracle → Postgres. And fair enough, that’s the bigger, louder migration. Oracle licensing costs alone have pushed thousands of companies toward Postgres.

SQL Server → Postgres gets almost no airtime. Which is strange, because it’s not a small migration path plenty of shops are quietly doing it. They just aren’t writing about it.

I have done one. 9TB, production, ORM-heavy application, real users, real SLAs. And the thing that nearly broke us wasn’t on anyone’s migration checklist.

Setup

Schema conversion: done. Datatype mapping: done. T-SQL stored procedures rewritten into PL/pgSQL: done, painfully, but done. Indexes recreated with equivalent definitions on the Postgres side: done.

By every standard pre-cutover checklist, we were green across the board.

Then the application went live on Postgres for stress testing and the ORM-generated queries exact same queries, same predicates, same expected index usage were slow. Not slightly slower, needs tuning slow. Materially, user-facing-latency slow.

Part that wasted our time

First instinct: missing indexes. Checked the indexes existed. Matched the predicates. The planner was even picking them. EXPLAIN ANALYZE showed index scans, not sequential scans. On paper, everything looked correct.

So if the right index is being used, why is it this slow?

We burned real hours chasing planner statistics, work_mem, connection pool settings the usual suspects when Postgres should be fast and isn’t. None of it moved the needle meaningfully.

Actual answer: the indexes were fragmented. Badly.

Why this happens on this specific migration path, and not others

If you’ve spent your career on SQL Server, fragmentation is a background concern. You’ve got scheduled maintenance plans doing index rebuilds/reorganizes, fill factor tuned per workload, and the app almost never feels it directly because the DBA tooling around SQL Server has been dealing with this problem for decades in a very visible, very manageable way.

Postgres doesn’t fragment in the same way, and it doesn’t get maintained the same way by default either.

Postgres uses MVCC. Every UPDATE doesn’t modify a row in place it writes a new row version and marks the old one dead. Those dead tuples sit there until VACUUM reclaims them. Indexes point at physical locations (tuple pointers), so when rows move around under update-heavy workloads, indexes accumulate dead entries and lose their tight, sequential layout.

That’s bloat, and bloat in an index behaves exactly like fragmentation the index is technically valid and technically used, but it’s now doing far more I/O per lookup than it should.

Now add the ORM into the picture. ORMs are notorious for generating update patterns that are far from bloat-friendly: wide UPDATE statements that touch indexed columns unnecessarily, no batching, no awareness of Postgres’s HOT (Heap-Only Tuple) update optimization, which only kicks in when the update doesn’t touch any indexed column.

Touch an indexed column on every update which most ORM-generated save() calls do without you asking them to and you lose HOT updates entirely. Every single update now forces an index update too, and bloat accumulates far faster than a lean, hand-written update path would produce.

So: correct schema, correct indexes, correct query plans on paper and still 5x the I/O it should be doing, because nobody told Postgres this index needs attention the way SQL Server’s maintenance plans quietly had been doing for years on the old system.

What actually fixed it

- Autovacuum tuning per table, not just database-wide defaults high-churn tables needed far more aggressive settings than Postgres ships with out of the box

- Bloat monitoring added to standard health checks, so this gets caught in staging next time, not three months post-cutover

- A longer-term conversation with the app team about batching updates and avoiding unnecessary writes to indexed columns

- Clean up index bloat where heap size was around 100GB but index size of table was 1TB+.

Currently we are reviewing each index def, validating if it requires it took us hours to reach this point.

Once Postgres migration is completed with expected results, will share complete series of challenges we went through.

None of this is exotic. It’s all standard Postgres operational knowledge.

The problem was never technical difficulty it was that this wasn’t on the migration checklist, because migration checklists are almost all written from an Oracle → Postgres lens, where this specific failure mode doesn’t show up the same way.

Actual lesson

If you’re migrating from SQL Server to Postgres, don’t just migrate the schema and the indexes. Migrate the maintenance mindset.

SQL Server has spent decades normalizing scheduled index maintenance as an invisible background job. Postgres expects you to understand MVCC, vacuum, and bloat as first-class operational concerns from day one not something you reach for after users start complaining.

The query plan being correct tells you the optimizer picked the right index. It tells you nothing about whether that index is healthy.