PostgreSQL数据库行数统计异常求助：工具与COUNT(*)结果不符

Hey there, this is a super common gotcha with PostgreSQL's core mechanics and how Heroku's tooling reports row counts. Let’s break down the most likely reasons for this discrepancy and how to verify each one:

1. MVCC "Dead Tuples" (The #1 Culprit) #

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to handle concurrent reads and writes smoothly. When you delete or update rows, the old versions aren’t immediately erased—they’re marked as "dead" and stick around until a VACUUM operation cleans them up.

The rows_in_table value from pg:index_usage almost certainly counts all rows, including these dead tuples, while SELECT COUNT(*) only counts rows that are currently visible to your active transaction.

How to Verify: #

Run this query to check the approximate row count PostgreSQL stores in its internal stats (which pg:index_usage relies on):

SELECT reltuples FROM pg_class WHERE relname = 'table_one';

If this returns ~8710 (matching the pg:index_usage number) but your COUNT(*) is 5708, dead tuples are definitely the issue.

Fix: #

Clean up the dead rows and refresh stats with:

VACUUM ANALYZE table_one;

Repeat this for all tables, then re-run pg:index_usage and COUNT(*)—the numbers should align much closer.

2. Stale Statistics in pg_class #

PostgreSQL automatically updates table statistics (like reltuples) periodically, but if you’ve just deleted thousands of rows, these stats might be out of date. The pg:index_usage tool uses these approximate stats instead of running a full COUNT(*) (which is slow for large tables).

How to Verify: #

Compare the reltuples value from the query above to your COUNT(*) result. If they’re drastically different, stats are stale.

Fix: #

Force PostgreSQL to refresh stats immediately with:

ANALYZE table_one;

For best results, combine this with VACUUM as noted earlier.

3. Long-Running Transactions Blocking VACUUM #

If you have a transaction that’s been open for hours or days (like an idle connection left in a transaction state), it can hold onto snapshots of old rows. This prevents VACUUM from cleaning up dead tuples because PostgreSQL has to keep those old versions visible to the open transaction.

How to Check: #

Run this query to find hanging idle transactions:

SELECT pid, now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'idle in transaction';

If you see any transactions lingering for hours, terminate them (carefully!) with:

SELECT pg_terminate_backend(pid);

4. Partition Table Edge Cases (Less Likely) #

If table_one is a partitioned table, double-check that pg:index_usage isn’t counting rows in orphaned or hidden child tables that your COUNT(*) query isn’t picking up. Run \d+ table_one in psql to confirm if it’s partitioned and verify all child tables are included in your count.

After trying these steps, your pg:index_usage row counts should match up with your SELECT COUNT(*) results. Heroku’s Hobby plan does run automatic VACUUM, but if you’re deleting rows at a high frequency, manual runs can help catch up.

内容的提问来源于stack exchange，提问作者Adam Shone