вт, 6 мар. 2018 г. в 4:57, Craig Ringer <cr...@2ndquadrant.com>:
> On 3 March 2018 at 00:30, Darafei "Komяpa" Praliaskouski <m...@komzpa.net>
>> 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.
It can be done using any indexing mechanism that can get you some list of
pages containing sibling tuples for current one. For btree, GiST I see it
as inserting tuple to index first (or taking a look at the page the
insertion is going to happen at) and then looking at page numbers of tuples
in the same index page. Similar can work for SP-GiST. BRIN, just the way
I'm not sure how it can be implemented for GIN. Probably choosing the most
empty page from all the ones referenced by posting lists the inserted row
can participate in, in cuckoo-hashing manner?
> 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.
This can potentially be solved provided there is mechanism to move tuples
between pages updating all the indexes. Then autovacuum can be taught to
move tuples between ranges to make the ranges smaller. I see how we can
survive without such mechanism for exact indexes like GiST or btree, but
not for BRIN at the start.
GIS Engineer / Juno Minsk