Обсуждение: Locking considerations of REINDEX

Поиск
Список
Период
Сортировка

Locking considerations of REINDEX

От
Pavan Deolasee
Дата:

The documentation [1] claims that REINDEX does not block readers on the table.

"REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. REINDEX locks out writes but not reads of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index. In contrast, DROP INDEX momentarily takes an exclusive lock on the parent table, blocking both writes and reads. The subsequent CREATE INDEX locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans."

But AFAICS get_relation_info() tries to lock every index and since REINDEX will be holding a AEL on the index being reindexed, get_relation_info() blocks. Since get_relation_info() gets into every read path, wouldn't a concurrent REINDEX pretty much block every read access to the table, even if REINDEX not holding AEL on the table itself?

I wonder if we just need fix the docs to or if we actually regressed at some point in the history or if we have a bug in the implementation? It mostly seems like a case of wrongly written docs even though in theory it might be possible to skip an index being rebuilt. That may lead to surprisingly worse plans getting chosen, leading to more trouble. Or may be someday we would have ability so that the existing queries can continue to read from the old physical index, new queries will shift to the new index and eventually the old index's storage will be dropped when nobody can see it.

Thanks,
Pavan



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

Re: Locking considerations of REINDEX

От
Peter Geoghegan
Дата:
On Wed, Jul 4, 2018 at 5:08 AM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
> But AFAICS get_relation_info() tries to lock every index and since REINDEX
> will be holding a AEL on the index being reindexed, get_relation_info()
> blocks. Since get_relation_info() gets into every read path, wouldn't a
> concurrent REINDEX pretty much block every read access to the table, even if
> REINDEX not holding AEL on the table itself?

Not necessarily -- prepared statements may not block.

> I wonder if we just need fix the docs to or if we actually regressed at some
> point in the history or if we have a bug in the implementation? It mostly
> seems like a case of wrongly written docs even though in theory it might be
> possible to skip an index being rebuilt.

I still agree with this, though. The practical distinction between
getting an AEL on the table and what REINDEX does is pretty much
indistinguishable from zero.

-- 
Peter Geoghegan