Re: Maintenance question / DB size anomaly...

Kurt Overberg <kurt(at)hotdogrecords(dot)com> writes:
> Drat! I'm wrong again. I thought for sure there wouldn't be a
> wraparound problem.

Well, I'm not sure what it is now. You showed some invisible tuples
with XMINs of
XMIN: 1489323584 CMIN: 1 XMAX: 0 CMAX|XVAC: 0
XMIN: 1489323590 CMIN: 2 XMAX: 0 CMAX|XVAC: 0
XMIN: 1489323592 CMIN: 1 XMAX: 0 CMAX|XVAC: 0
but the nextXID is
1490547335
which is not that far ahead of those --- about 1.2 million transactions,
or less than a day's time according to the clog timestamps, which
suggest that you're burning several million XIDs a day. Perhaps you've
wrapped past them since your earlier check --- if you try the same
"select where ctid = " queries now, do they show rows?

The other thing that's strange here is that an 8.0 installation should
be pretty aggressive about recycling pg_clog segments, and yet you've
got a bunch there. How far back do the files in pg_clog go --- what's
the numeric range of the filenames, and the date range of their mod
times? Have you checked the postmaster log to see if you're getting any
complaints about checkpoint failures or anything like that? It would
also be useful to look at the output of
select datname, age(datfrozenxid) from pg_database;

regards, tom lane