Re: Physical append-only tables

On Mon, Nov 14, 2016 at 2:35 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Magnus Hagander wrote:
>
> > But then consider the same table. Except rows are typically updated once
> or
> > twice when they are new, and *then* go read only. And we also have a
> > process that at some point deletes *some* old rows (but not all - in
> fact,
> > only a small portion).
> >
> > In this case, the next INSERT once VACUUM has run is likely to stick a
> > "new" row somewhere very "far back" in the table, since there is now free
> > space there. This more or less completely ruins the BRIN index usability,
> > as the "old" blocks will now contain a single row from a "new" series.
>
> Yeah. When we initially discussed BRIN, there was a mention of allowing
> a BRIN index to guide new tuple location -- something like
> auto-clustering, if you will. We haven't discussed the exact details
> but I think something along those lines is worth considering.
>

What I'm talking about is something that would be a lot simpler than
auto-clustering. I'm not even talking about trying to detect if the row was
about to go into the right place -- this might be expensive and certainly
more complicated. I'm only talking about a simple case where we *never* put
anything anywhere other than at the end of the table, period. That should
make the check both cheap and simple.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/