Re: [GENERAL] looking for a globally unique row ID

W dniu 16.09.2017 o 12:43, Tomas Vondra pisze:
> On 09/16/2017 11:57 AM, Rafal Pietrak wrote:
>>
>> <disclaimer>
>>     I'M NOT POSTGRES HACKER. THIS IS JUST NA INTUITION.
>> </disclaimer>
>>
>> W dniu 15.09.2017 o 21:30, Christopher Browne pisze:
>>> On 15 September 2017 at 14:45, Adam Brusselback
>>> <adambrusselback@gmail.com> wrote:
[-----------]
>> This is probably postgresql-hackers knowledge, but I'd appreciate if
>> you elaborate: why "concurrent vacuum" of two table with common index
>> is such a problem?
>>
> 
> Because it means vacuums on partitions will clash on the shared (global)
> index, unlike today with only local indexes. Vacuum happens in about
> three basic phases:
> 
> 1) Remove dead tuples from tables, reduce them to item pointers and
> stash the row IDs in a list.
> 
> 2) Scan all indexes on the table, remove items with IDs on the list.
> 
> 3) Scan the table again, finally removing the item pointers.
> 
> Currently this happens on partition level, so the individual vacuums
> don't interfere by modifying the same index. With global indexes (at
> least when implemented as a single object) that would no longer be the
> case, and the consequences are a bit terrifying ...

This looks hard. True.

I'm wondering if the "global index" problem was deeply discussed before
(like on postgres-hackers) ... or discussions died out early due to all
this implementation obstacles?

In the later case, I'd like to tackle (shake) it a little here.
Obviously not to the point of implementation, but may be just to file
some pros and cons of one way or another to implement it.

So regarding the 1/2/3 above. Is it feasible (?) would it work (?) if:
1. concurrent vacuum is executed concurrently like today.
2. obviously all those descendent & being vacuumed tables will get a
lock by the vacuum process.
3. global index is at this point "opened", so access to other
descendents proceed?
4. the global index may experience some locking due to the above "other
descendent tables" actions, but tuple pointers relating to tables
currently being vacuumed are not effected.... no need to lock??
5. the concurrent processed doing (1) above may take long, but eventualy
they finish and all the collected lists of tuples for the "global index"
to purge are ready.
6. now the process of vacuuming the global index may proceed, as only
this index need updates, and it shouldn't take long - all the work
downstairs is done. Thus the global index will only get blocked for a
minimum amount of time.


[---------------]
> 
> It's a bit foolish to say you would live with the performance hit when
> you don't know how serious would it be. It may be fine but it may also
> be much worse than you expected.

very true. I admit.

But on the other hand, today you have a situation, without any tools for
such occasion. IMHO that's worse.

And with thousands of partitions we are talking billions of records.
This is not an average Joe's (like myself) scenario. Event if my 6
levels of inheritance rise 10-fold (which is very unlikely and on the
other hand ... very large), I'm way away from thousands.


> 
> I ran into a number of people who used global indexes on other DBs, and
> their position is mostly "I'll never use that, because it pretty much
> eliminates all benefits of partitioning." So we need to thread pretty
> carefully here, not to run into the same limitations.

True again.

And again, there should be a way to do it right :)


-R


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general