Re: Physical append-only tables

On 11/24/16 8:18 AM, Bruce Momjian wrote:
>> What if we used BRIN to find heap pages where the new row was in the
>> page BRIN min/max range, and the heap page had free space. Only if that
>> fails do we put is somewhere else in the heap.
>>
>>
>> That would certainly be useful. You'd have to figure out what to do in the case
>> of multiple conflicting BRIN indexes (which you shouldn't have in the first
>> place, but that won't keep people from having them), but other than that it
>> would be quite good I think.
> This idea is only possible because the BRIN index is so small and easy
> to scan, i.e. this wouldn't work for a btree index.

ISTM a prerequisite for any of this is a way to override the default FSM
behavior. A simple strategy that forces append-only would presumably be
very cheap and easy to do after that. It could also be used to allow
better clustering. It would also make it far easier to recover from a
heavily bloated table that's too large to simply VACUUM FULL or CLUSTER,
without resorting to the contortions that pg_repack/pg_reorg have to.
--
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)