Re: Physical append-only tables

On Mon, Nov 14, 2016 at 4:45 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> For a scenario like this, would it make sense to have an option that could
> be set on an individual table making it physical append only? Basically
> VACUUM would run as normal and clean up the old space when rows are deleted
> back in history, but when new space is needed for a row the system would
> never look at the old blocks, and only append to the end.

One thing I have idly wondered about: if you had an append-only mode
that guarantees that we never write new tuples anywhere but the end of
the heap, you could create a bsearch access method that uses zero
storage and simply checks that every key inserted is >= the previous
high key before allowing the insertion to proceed. Then it could make
use of the guaranteed correlation with physical order to do scans
using a binary search of the heap. Maybe that'd be useful for some
kinds of write-only time-series data that needs to be searched by time
range. On the other hand, BRIN indexes are tiny, should be nearly as
good and are much less restrictive, so I haven't follow this thought
up.

--
Thomas Munro
http://www.enterprisedb.com