Hi,
Hi, I glad to see that you interested in that too.
I think this is a good feature and I think it will be very useful to have.
I have already mentioned some related problems and possible
improvements in my presentation.
http://www.slideshare.net/AnastasiaLubennikova/indexes-dont-mean-slow-inserts
Last two slides concern to this thread. Briefly, I've suggested to
think about insertion buffer. Something very similar to it is already
implemented in BRIN. It does not index last data from heap, while the
number of last pages is less than pages_per_block.
Do you mean GIN-like usage of insertion buffer (here it is called
"pending list")?
So that we have to combine search in the main tree and in the insert buffer?
Actually this is what I want to avoided (because at least in case of GIN
pending list cause significant degrade of performance,
while up-to-date state of full text index is rarely required).
The next point, I've thought about is a bulk update. Problem is that
update like "UPDATE mytable set a = a+1;" causes N searches from the
root of B-tree. I looks very strange to me, and I'd like to fix it
somehow. The obvious solution is to update all tuples on the page at a
time, and keep the number of last updated page. But, maybe it's a bit
off-thread here.
Bulk update is the second question (but very important).
First I just want to be able to append index concurrently, not blocking
insert.
One interesting approach of solving this problem is discussed in this
article:
https://mark.zealey.org/2016/01/08/how-we-tweaked-postgres-upsert-performance-to-be-2-3-faster-than-mongodb
Them are using materialized views to build indexes in background.
Interesting idea, but copying content of the whole table just to be
able to build index concurrently seems to be overkill.
This approach seems like a tricky crutch to me. And I agree that it
requires a lot of extra work.
It will be very interesting to know how people are using materialized views.
Delayed building of indexes seems to be one of the popular use cases,
although requiring large overhead, first of all storage overhead.
Please notice that such alter table statement, changing condition for
partial index, is not supported now.
Don't you think, that this feature could be used in a very wrong way?
Do not take it as criticism, just a bit of thoughts.
Everything which can be misused, will be misused:)
But I do not worry much about it...
If it can address real challenges, then it will be good thing in any case.
Ideally we should be able to alter everything. Naive implementation of
such alter clause can just to build new index with temporary name, then
drop old index and rename new index.
There was the discussion of the patch for partial indexes.
http://postgresql.nabble.com/PATCH-index-only-scans-with-partial-indexes-td5857568.html
Since I haven't watched it closely, It seems to be open still. I
think it'll be interesting to you.
So small patch...
Why it was not accepted?
I do no see any problems with it...
--
Anastasia Lubennikova
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
