Re: All Taxi Services need Index Clustered Heap Append

On 3 March 2018 at 00:30, Darafei "Komяpa" Praliaskouski <me(at)komzpa(dot)net>
wrote:

> I gave this all some thought and it looks like it all could have not
> happened if Postgres was able to cluster heap insertions by (id, ts) index.
> We're ok with synchronuous_commit=off, so amplified write won't immediately
> hit disk and can get cooled down in progress. Clustering doesn't require
> perfect sorting: we need to minimize number of pages fetched, it's ok if
> the pages are not consecutive on disk.
>

I'm surprised nobody has mentioned BRIN yet.

Ever since BRIN was introduced, I've thought it would be very interesting
to use it + the freespace map for coarse-grained tuple routing in heap
inserts. Try to find the best-match range with BRIN and look for free space
there. I think there was discussion of this early on, so you may want to
look up the BRIN threads.

The main challenge probably comes when a range is exhausted. Your writes
would start spilling over into new heap pages and get intermixed again.
Some support for pre-allocating new ranges would be needed.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services