Re: [HACKERS] 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)