BUG #18084: Count Mismatch Challenges During PostgreSQL Database Migration: Causes and Solutions

The following bug has been logged on the website:

Bug reference:      18084
Logged by:          Git Queries
Email address:      gitqueries0@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Microsoft Windows Server 2019 Standard
Description:

Version - PostgreSQL 10.20, compiled by Visual C++ build 1800, 64-bit

Platform - Windows

Regarding PostgreSQL, during the migration from PG 10.20 to 14.7, we will
perform a sanity check on the migrated tables using source and destination
row counts. Occasionally, we encounter count mismatches where the source
database has fewer rows, resulting in a higher count, or more rows,
resulting in a lower count.

Note: There are no migration issues, as all data rows have been successfully
migrated without data loss.

ProdDB=> select count(*) from PdtDetailsTable;
  count
---------
 1297324
(1 row)


ProdDB=> reindex table PdtDetailsTable;
REINDEX
ProdDB=> select count(*) from PdtDetailsTable;
  count
---------
 1297322
(1 row)


ProdDB=>
Upon reindexing, the count(*) query returns the correct results in the
source DB.

In the pg_log, we couldn't find any traces related to this behavior. Is
there a reason for such behavior, and how can this be addressed to prevent
future issues?