Re: GUC for cleanup indexes threshold.

On 2/24/17 11:26 AM, Robert Haas wrote:
> I think we need to come up with some set of tests to figure out what
> actually works well in practice here. Theories are a good starting
> point, but good vacuum behavior is really important, and a patch that
> changes it ought to be backed up by at least some experimental
> evidence.

I think something else worth considering is that if we had some method
of mapping heap TIDs back to indexes then a lot (all?) of these problems
would go away. 10+ years ago the idea of keeping such a mapping would
probably be untenable, but with resource forks and how much cheaper
storage is maybe that's no longer the case.

For btree I think this could be done by keeping a second btree ordered
by ctid that points either to index entries or even just to whole index
pages. At ~ 20 bytes per entry, even a 1B row index would take ~20GB.

Page splits are obviously a big issue. Maybe it's safe to update the
ctid map for every item that gets moved when a split happens.

Would a ctid map work for other indexes as well?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)