The PostgreSQL Migration Trap: Heaps, 3.22 Billion Forwarded Fetches, and How to Fix It in SQL Server

---

This article examines a performance problem that appears consistently after migrating PostgreSQL applications to SQL Server using database conversion tools. The performance figures used throughout are sourced from sys.dm_os_wait_stats and sys.dm_db_index_operational_stats captured on production and beta SQL Server environments under real OLTP load. The problem is not environment-specific. It happens to every team that migrates from PostgreSQL to SQL Server without accounting for one fundamental architectural difference between the two engines.

PostgreSQL does not have clustered indexes. SQL Server relies on them. When a migration tool copies a PostgreSQL database to SQL Server, it creates SQL Server tables the same way PostgreSQL stores them: as heaps, tables with no clustered index and no physical row ordering. The application runs. The data is there. Everything looks fine. Then production load arrives.

1 PostgreSQL Has No Clustered Index: The Architectural Difference Beginner

In SQL Server, a clustered index physically orders the rows of a table on disk according to the index key. When a clustered index exists on a primary key column, every row in the table is stored in primary key order. Lookups by primary key are seeks directly to the row. Non-clustered indexes on other columns contain the primary key value as the row locator and can navigate to the full row in one additional read. The clustered index is the table. This is the foundational SQL Server storage model and every table should have one according to Microsoft Learn documentation.

PostgreSQL does not have an equivalent concept. Every PostgreSQL table is a heap by architecture. Rows are stored in the order they were inserted, or in whatever order the storage manager places them after updates and vacuuming. PostgreSQL has no mechanism to physically order table rows by a key column and maintain that ordering as rows are inserted, updated, and deleted.

PostgreSQL does have a CLUSTER command. According to the official PostgreSQL documentation, the CLUSTER command physically reorders a table based on an index at the moment the command is executed. The documentation is explicit: clustering is a one-time operation. When the table is subsequently updated, the changes are not clustered. No attempt is made to store new or updated rows according to their index order. This is fundamentally different from SQL Server’s clustered index which maintains physical ordering continuously as rows change.

2 What Migration Tools Do and Why Heaps Are the Result Beginner

When a migration tool converts a PostgreSQL database to SQL Server, it reads the PostgreSQL schema and generates equivalent SQL Server DDL. Tables become SQL Server tables. Indexes become SQL Server indexes. Constraints become SQL Server constraints. But the clustered index that should anchor every SQL Server table has no PostgreSQL equivalent to read from. The migration tool cannot generate what was never there.

The result is SQL Server tables created without clustered indexes. In SQL Server, a table without a clustered index is called a heap. The migration completes successfully. The data is present. The non-clustered indexes from PostgreSQL are created. The application connects and begins writing data. From the application perspective everything is working.

The heap problem does not announce itself at migration time or during initial testing. It emerges under production load, specifically under concurrent read and write workloads where multiple sessions are simultaneously reading and modifying the same heap tables. This is when the three penalties of heap storage in SQL Server become visible: forwarded fetches, RID-level locking, and full table scans.

3 What a Heap Costs SQL Server: Forwarded Fetches, RID Locks, and Full Scans Intermediate

Forwarded fetches: the hidden I/O tax

When a row in a SQL Server heap is updated and the new version of the row is larger than the original, SQL Server may not be able to fit the expanded row on the original data page. SQL Server moves the row to a new page with enough space and leaves a forwarding pointer on the original page pointing to the new location. The original page slot now contains only a small pointer, not the row data.

Every subsequent read of that row now requires two page reads: the original page to read the forwarding pointer, then the new page to read the actual row. This is called a forwarded fetch. On a table that receives frequent updates and has been running under production load for months, a large percentage of rows may have been moved and forwarded. A query that reads one thousand rows from a forwarded heap may require two thousand page reads instead of one thousand. The I/O cost is invisible in the query plan but visible in sys.dm_db_index_operational_stats as forwarded_fetch_count.

The environment documented in this article generated 3.22 billion forwarded fetches in five days on the production heap tables. The same tables with clustered primary keys on the beta environment generated zero forwarded fetches over the same period. The clustered index eliminates forwarded fetches entirely because rows are always stored in their correct ordered position and do not need forwarding pointers.

RID locks: locking by Row ID instead of key range

SQL Server locks rows during write operations. On a clustered table, the lock is taken on the index key, which enables key range locking and allows SQL Server to precisely scope the lock to the affected rows. On a heap, there is no index key to lock. SQL Server instead takes a RID lock: a lock on the Row Identifier, which is an 8-byte structure identifying the specific file, page, and slot of the row.

RID locking appears to reduce contention at first because RID locks on different rows rarely conflict with each other on a heap. This creates a misleading picture: blocking appears low on a heap because the lock granularity is per-slot, and two writers on different slots on the same page may not directly conflict. But this masks the underlying problem. Reader-writer conflicts still occur when a reader takes shared locks on pages that writers need, and the application transaction pattern determines how long those locks are held.

Full table scans: no seek path without a clustered index

Non-clustered indexes on a heap store the RID of each indexed row as the row locator. When a non-clustered index is used to find a row, SQL Server reads the index to find the RID, then reads the heap page at that RID to get the full row. If the row has been forwarded, that heap read becomes two reads. For queries that cannot use any index, the only option is a full heap scan, reading every page in the table in allocation order. On large tables this generates enormous sequential I/O that competes with all other workloads on the server.

4 The Production Numbers: What sys.dm_os_wait_stats Proves Intermediate

The following figures are drawn from a real production environment running a PostgreSQL-migrated application under concurrent OLTP load. A parallel beta environment running the same application with RCSI enabled and clustered primary keys in place was used as the comparison baseline. All metrics are sourced from sys.dm_os_wait_stats (cumulative since last restart) and sys.dm_db_index_operational_stats (forwarded fetches).

The same application is running on both environments. The same data is present on both. The same queries execute against both. The 8.9x difference in total lock wait time and the complete elimination of forwarded fetches on the beta environment is attributable to two changes only: RCSI was enabled on the beta database, and the heap tables were converted to tables with clustered primary keys.

Problem Category	Production (Heaps, No RCSI)	Beta (Clustered PKs, RCSI)
Reader-writer blocking (LCK_M_S)	271 million ms. Readers taking shared locks that conflict with writers.	12.3 million ms. RCSI eliminates shared lock waits for readers. Readers use row versions.
Forwarded fetches	3.22 billion in 5 days. Every forwarded row costs 2 page reads instead of 1.	Zero. Clustered index maintains physical row order. No forwarding pointers exist.
Full table scans	Required for queries without a usable non-clustered index. No clustered seek path.	Clustered index seek available for primary key lookups and range scans.
Writer-writer blocking	Still occurs (application transaction pattern issue)	Still occurs (same application transaction pattern issue, requires app fix)
Timeout incidents	223-second Buffer IO waits on heap scans	Resolved with index seeks, no heap scan overhead

5 Why Adding NC Indexes Does Not Fix the Problem Intermediate

The instinctive developer response to slow queries is to add non-clustered indexes. On a heap, this is the only available tool and it addresses symptoms without fixing the underlying structure. Non-clustered indexes on a heap improve specific query paths by allowing index seeks instead of full scans, but they do not eliminate forwarded fetches, do not change locking granularity from RID to key-based, and do not provide a clustered seek path for the optimizer.

Every non-clustered index added to a heap must be maintained on every INSERT, UPDATE, and DELETE. On a write-heavy table with 10 non-clustered indexes, each write operation must update 10 index structures in addition to writing the heap row. This increases write amplification and lock acquisition surface without addressing the root cause of the performance problem.

The correct answer is to create a clustered primary key on the table. This reorganizes the heap into an ordered clustered table, eliminates forwarded fetches permanently, changes the row locator in all non-clustered indexes from a RID to the primary key value, and gives the optimizer a seek path for primary key queries. All existing non-clustered indexes remain valid after the conversion; SQL Server rebuilds them to use the primary key as the row locator during the clustered index creation.

6 Why Heaps Mask Blocking Instead of Fixing It Intermediate

A counterintuitive property of SQL Server heaps is that they appear to have less blocking than clustered tables in certain workload patterns. This is because RID locks on individual heap slots rarely conflict when different sessions are writing to different rows on different pages. Two writers inserting into different slots of a heap rarely block each other at the row level.

This appearance of low blocking is misleading in two ways. First, it hides the forwarded fetch I/O cost behind what looks like clean wait statistics. The server is busy doing double page reads on every forwarded row access, but this does not show up as lock blocking. It shows up as elevated Page IO Latch waits and high logical reads per query, which are harder to diagnose. Second, reader-writer blocking still occurs when readers take shared page locks that conflict with writer exclusive page locks. The production environment in this article showed 271 million milliseconds of LCK_M_S shared lock waits, which RCSI eliminates by giving readers access to row versions instead of requiring shared locks.

According to Microsoft Learn documentation on heaps: “There are sometimes good reasons to leave a table as a heap instead of creating a clustered index, but using heaps effectively is an advanced skill. Most tables should have a carefully chosen clustered index unless a good reason exists for leaving the table as a heap.” Tables that arrived as heaps from a PostgreSQL migration are not tables where there is a good reason for the heap. They are tables where the reason for the heap is that the migration tool had no clustered index concept to migrate.

7 Step 1: Enable RCSI as the Correct Interim Fix Intermediate

Read Committed Snapshot Isolation (RCSI) changes how SQL Server handles read operations under the default READ COMMITTED isolation level. Without RCSI, a reader takes a shared lock on the rows and pages it reads. If a writer holds an exclusive lock on the same data, the reader must wait. With RCSI enabled, readers access a row version from the version store in TempDB instead of taking a shared lock. Readers never wait for writers. Writers never wait for readers taking shared locks.

RCSI is the correct interim fix for the reader-writer blocking caused by heaps and the READ COMMITTED default isolation level. It eliminated 271 million milliseconds of LCK_M_S reader blocking in the production environment and is verified on the beta environment since July 14, 2026 with 72 KB of version store overhead in TempDB, negligible for any properly sized TempDB.

RCSI is enabled with a single ALTER DATABASE statement. The database remains online during the enable operation but requires a brief exclusive database lock of approximately 5 seconds to complete the mode change. Plan this during a low-traffic window.

-- Check current isolation level settings on all user databases
SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

-- Enable RCSI on a specific database
-- Requires exclusive access for approximately 5 seconds
-- Plan during low-traffic window
-- All existing connections will be rolled back during the brief lock
ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;

-- Verify RCSI is active
SELECT
    name,
    is_read_committed_snapshot_on       AS RCSI_Enabled
FROM sys.databases
WHERE name = 'YourDatabase';
-- is_read_committed_snapshot_on = 1 confirms RCSI is active

-- Confirm readers are now using snapshot versions
-- Run from any session in the database after enabling
SELECT
    transaction_isolation_level,
    CASE transaction_isolation_level
        WHEN 2 THEN 'READ COMMITTED (without RCSI uses shared locks)'
        WHEN 5 THEN 'READ COMMITTED SNAPSHOT (no shared locks on reads)'
        ELSE 'Other'
    END AS IsolationDescription
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
  AND database_id = DB_ID('YourDatabase');

8 Step 2: Convert Heaps to Clustered Primary Keys Safely Intermediate

Converting a heap to a clustered table requires creating a clustered index on the table. The recommended approach for production environments is to use the ONLINE = ON option, which allows reads and writes to continue during the index creation, and to process tables in batches starting with the smallest tables to limit blast radius if something goes wrong.

Step 1 (enabling RCSI) must be completed before Step 2. The combination of RCSI and clustered PKs produces the full improvement. Doing the heap conversion before enabling RCSI means the conversion runs under the default isolation level where readers and writers can still block each other during the operation.

-- Find all heap tables on the instance
-- These are the tables that need clustered primary keys
SELECT
    DB_NAME()                           AS DatabaseName,
    SCHEMA_NAME(t.schema_id)            AS SchemaName,
    t.name                              AS TableName,
    p.rows                              AS ApproxRows,
    SUM(a.total_pages) * 8 / 1024       AS TotalSizeMB,
    -- Forwarded fetch count for this heap
    ios.forwarded_fetch_count
FROM sys.tables                         t
JOIN sys.indexes                        i   ON t.object_id = i.object_id
    AND i.type = 0                          -- 0 = heap
JOIN sys.partitions                     p   ON t.object_id = p.object_id
    AND p.index_id = 0
JOIN sys.allocation_units               a   ON p.partition_id = a.container_id
LEFT JOIN sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
    ON t.object_id = ios.object_id AND ios.index_id = 0
WHERE t.is_ms_shipped = 0
GROUP BY
    t.schema_id, t.name, p.rows, ios.forwarded_fetch_count
ORDER BY TotalSizeMB ASC;   -- smallest first for batched conversion

-- Convert a heap to a clustered table by adding a clustered primary key
-- ONLINE = ON allows reads and writes to continue during conversion
-- ONLINE = ON requires Enterprise Edition
-- For Standard Edition use a maintenance window and omit ONLINE = ON

-- Pattern 1: Table has no primary key column - add an identity column and make it the PK
-- Do this for tables that came from PostgreSQL without a natural key

-- Step A: Add an identity column
ALTER TABLE dbo.YourHeapTable
ADD ID INT IDENTITY(1,1) NOT NULL;

-- Step B: Add the clustered primary key on the new identity column
-- This converts the heap to a clustered table and rebuilds all NC indexes
ALTER TABLE dbo.YourHeapTable
ADD CONSTRAINT PK_YourHeapTable PRIMARY KEY CLUSTERED (ID)
WITH (ONLINE = ON);          -- reads and writes continue during this operation

-- Pattern 2: Table has a natural key column already - add the clustered PK directly
ALTER TABLE dbo.YourHeapTable
ADD CONSTRAINT PK_YourHeapTable PRIMARY KEY CLUSTERED (NaturalKeyColumn)
WITH (ONLINE = ON);

-- Verify the table is no longer a heap after conversion
SELECT
    t.name                              AS TableName,
    i.type_desc                         AS IndexType
FROM sys.tables                         t
JOIN sys.indexes                        i ON t.object_id = i.object_id
    AND i.index_id IN (0, 1)            -- 0 = heap, 1 = clustered
WHERE t.name = 'YourHeapTable';

-- Batched conversion script for multiple heap tables
-- Process smallest tables first to limit blast radius
-- Review the list before executing

DECLARE @TableName  NVARCHAR(256);
DECLARE @Schema     NVARCHAR(128);
DECLARE @SQL        NVARCHAR(MAX);
DECLARE @Batch      INT = 0;
DECLARE @BatchSize  INT = 5;    -- convert 5 tables per run, adjust as needed

DECLARE heap_cursor CURSOR FOR
    SELECT TOP (@BatchSize)
        SCHEMA_NAME(t.schema_id)    AS SchemaName,
        t.name                      AS TableName
    FROM sys.tables t
    JOIN sys.indexes i ON t.object_id = i.object_id AND i.type = 0
    JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id = 0
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    WHERE t.is_ms_shipped = 0
    GROUP BY t.schema_id, t.name
    ORDER BY SUM(a.total_pages) ASC;    -- smallest first

OPEN heap_cursor;
FETCH NEXT FROM heap_cursor INTO @Schema, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
        ALTER TABLE [' + @Schema + N'].[' + @TableName + N']
        ADD MigratedPK INT IDENTITY(1,1) NOT NULL;

        ALTER TABLE [' + @Schema + N'].[' + @TableName + N']
        ADD CONSTRAINT [PK_' + @TableName + N'_Migrated]
        PRIMARY KEY CLUSTERED (MigratedPK)
        WITH (ONLINE = ON);
    ';

    PRINT 'Converting: ' + @Schema + '.' + @TableName;
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM heap_cursor INTO @Schema, @TableName;
END

CLOSE heap_cursor;
DEALLOCATE heap_cursor;

9 Step 3: Fix the Long Transaction App Pattern Intermediate

The third problem is independent of heaps and RCSI, and it is one that migration validation often misses. Transaction patterns that perform acceptably in PostgreSQL under its MVCC concurrency model can produce severe blocking in SQL Server under the default READ COMMITTED isolation level. PostgreSQL’s architecture handles reader-writer concurrency differently, which means long-running transactions that were never a visible problem in the source database become an immediate blocking source in SQL Server. Migration validation should include concurrency testing under production-representative load, not only functional correctness testing.

In this pattern, the application opens a transaction, performs a write operation, then makes an external API call while the transaction is still open, and commits only after the API call returns. The external API call takes 50 to 502 seconds. During this entire period, the exclusive locks taken by the write operation inside the transaction are held.

Any other session that needs to write to the same rows is blocked for the duration of the API call. RCSI cannot help here. Exclusive locks for write operations are always taken regardless of isolation level. This is documented behavior: RCSI protects readers by giving them row versions instead of shared locks, but writers always need and hold exclusive locks. The only fix is to change the application transaction boundary.

	Current Pattern (Wrong)	Correct Pattern
Transaction scope	BEGIN TRAN, write row, call external API (50-502 sec), COMMIT	BEGIN TRAN, write row, COMMIT, then call external API outside transaction
Lock held during API call	Yes. Exclusive lock held for up to 502 seconds.	No. Exclusive lock released at COMMIT before API call.
Other writers blocked	Yes, for up to 502 seconds per API call.	No. Lock released at COMMIT, other writers can proceed immediately.
RCSI helps?	No. RCSI only helps readers. Exclusive locks always block other writers.	Not needed for this. Short transactions release locks quickly regardless.

-- Detect active sessions holding locks with long-running transactions
-- Look for sessions where open_transaction_count > 0 and wait_time is high

SELECT
    s.session_id,
    s.open_transaction_count,
    r.wait_type,
    r.wait_time / 1000              AS WaitTimeSec,
    r.blocking_session_id,
    DB_NAME(r.database_id)          AS DatabaseName,
    LEFT(t.text, 200)               AS QueryText,
    s.login_time,
    DATEDIFF(SECOND, s.login_time, GETDATE()) AS SessionAgeSec
FROM sys.dm_exec_sessions           s
LEFT JOIN sys.dm_exec_requests      r   ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections   c   ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.is_user_process = 1
  AND s.open_transaction_count > 0
ORDER BY s.open_transaction_count DESC, WaitTimeSec DESC;

10 How to Detect Heaps and Measure Forwarded Fetches Beginner

The following scripts identify all heap tables on an instance and measure their forwarded fetch counts. Run these on any SQL Server that received data from a PostgreSQL migration to assess the scope of the problem before it becomes a production incident.

-- Full heap assessment: find all heaps with forwarded fetch counts
-- Run in each database that received migrated data

SELECT
    SCHEMA_NAME(t.schema_id)            AS SchemaName,
    t.name                              AS TableName,
    p.rows                              AS ApproxRows,
    SUM(a.total_pages) * 8 / 1024       AS TotalSizeMB,
    ISNULL(ios.forwarded_fetch_count, 0) AS ForwardedFetches,
    CASE
        WHEN ISNULL(ios.forwarded_fetch_count, 0) > 1000000
            THEN 'CRITICAL: Over 1M forwarded fetches'
        WHEN ISNULL(ios.forwarded_fetch_count, 0) > 100000
            THEN 'HIGH: Over 100K forwarded fetches'
        WHEN ISNULL(ios.forwarded_fetch_count, 0) > 0
            THEN 'ELEVATED: Forwarded fetches present'
        ELSE 'OK: No forwarded fetches yet'
    END                                 AS Assessment
FROM sys.tables                         t
JOIN sys.indexes                        i
    ON t.object_id = i.object_id AND i.type = 0
JOIN sys.partitions                     p
    ON t.object_id = p.object_id AND p.index_id = 0
JOIN sys.allocation_units               a
    ON p.partition_id = a.container_id
LEFT JOIN sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
    ON t.object_id = ios.object_id AND ios.index_id = 0
WHERE t.is_ms_shipped = 0
GROUP BY t.schema_id, t.name, p.rows, ios.forwarded_fetch_count
ORDER BY ForwardedFetches DESC, TotalSizeMB DESC;

-- Check RCSI status and lock wait profile together
SELECT
    d.name                                      AS DatabaseName,
    d.is_read_committed_snapshot_on             AS RCSI_Enabled,
    ws.wait_type,
    ws.wait_time_ms,
    ws.waiting_tasks_count
FROM sys.databases                              d
CROSS JOIN (
    SELECT TOP 5 wait_type, wait_time_ms, waiting_tasks_count
    FROM sys.dm_os_wait_stats
    WHERE wait_type LIKE 'LCK%'
    ORDER BY wait_time_ms DESC
) ws
WHERE d.database_id = DB_ID()
ORDER BY ws.wait_time_ms DESC;

11 Catching the Problem at Migration Time with MigrateIQ Beginner

The three-step fix described in Sections 7 through 9 took 68 days and is still not fully complete at the time this article is written. Step 2, the heap-to-clustered-PK conversion of 50 tables, was reverted after the initial attempt due to application impact. Step 3 has received no developer response in 68 days. The interim fix of RCSI is working but the root cause remains. All of this was avoidable.

The heap problem is not a production firefighting problem. It is a migration configuration problem. Every table that arrives in SQL Server without a clustered primary key is a future performance incident. The right time to make the decision is before the migration runs, not months later under production pressure.

MigrateIQ is a SQL Server migration tool built specifically to catch this class of problem at configuration time. During the migration setup phase, MigrateIQ analyzes the source schema and surfaces every table without a primary key in a dedicated Heaps review tab with an explicit warning: “Tables without a primary key create heaps on SQL Server. Heaps can cause blocking, forwarded records, and RID lookup overhead under concurrent workloads.”

For each heap table, the DBA chooses one of three actions before the migration runs: Add Identity PK (MigrateIQ adds a clustered identity primary key during migration), Leave as Heap (the DBA has reviewed the table and accepts the heap for a specific reason), or Flag for Review (mark for a later decision before go-live). An Accept All AI option applies the recommended action across all detected heaps at once based on table analysis.

References

• Microsoft Learn: Heaps (Tables without Clustered Indexes) (primary source for heap behavior, forwarded fetches, RID locking)
• Microsoft Learn: Clustered and Nonclustered Indexes Described
• Microsoft Learn: SQL Server Transaction Locking and Row Versioning Guide (RCSI, shared locks, exclusive locks)
• Microsoft Learn: sys.dm_db_index_operational_stats (forwarded_fetch_count)
• PostgreSQL Documentation: CLUSTER (one-time operation, not maintained on updates)
• SQLYARD: MigrateIQ Migration Tool
• SQLYARD: SQL Server Blocking vs Deadlocks
• SQLYARD: SQL Server Heaps: When to Use Them and When to Avoid Them
• SQLYARD: SQL Server Performance Tuning: The Complete Guide