Re: All Taxi Services need Index Clustered Heap Append

On 3 March 2018 at 00:30, Darafei "Komяpa" Praliaskouski <me@komzpa.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.