The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/sql-reindex.html
Description:
Paragraph in question:
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.
"REINDEX locks out writes but not reads of the index's parent table" -- It
seems that REINDEX locks out both reads and writes on the parent table.
"also takes an exclusive lock on the specific index being processed" -- The
SELECT that I provided performs a seqscan and doesn't use "the specific
index being processed"
I've tested this with the following test case:
> pgbench -i -s 100
> psql -c "create index foo_idx on pgbench_accounts (bid)"
> psql -c "reindex index foo_idx" & psql -c "select abalance from
pgbench_accounts limit 1" & psql -c "select * from pg_stat_activity where
state notnull"; psql -c "select * from pg_locks";
It seems that the "select abalance from pgbench_accounts limit 1" gets
blocked by the REINDEX, and doesn't return any values until the REINDEX is
completed, even though the SELECT query doesn't seem to access foo_idx. I'm
not sure if this is a bug in the code or a bug in the documentation--who
gets to make that call?