Postgres reindex vs drop and create

Question

Is there any difference between these commands:

reindex database my_db

and

drop index my_index;
CREATE INDEX index1 ON schema1.table1 USING btree (table1_key);

where you do the second command for every single index in the database?

----- EDIT -----

An answer pointed out that the two commands have different locking behavior. Other than different locks, is there any difference in how the commands operate on the database? For example, if I took a copy of a VM and did command A (reindex) then took another copy of the VM, restored the original copy of the VM, executed command B (drop and create all indexes) and make yet another copy, would the post-A and post-B databases be identical? If not, what kinds of things would have caused the differences?

Per your edit - there is no documented behavior(s) like what you suggest, so the only way to know for sure is to perform your own tests. — Nick
– Nick, Commented Jul 19, 2016 at 20:38

Nick · Accepted Answer · 2016-07-19 20:22:14Z

10

They are slightly different.

REINDEX locks writes, but not reads.

DROP INDEX locks writes and reads, then CREATE INDEX locks writes only.

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.

Source: https://www.postgresql.org/docs/9.5/static/sql-reindex.html (under Notes)

answered Jul 19, 2016 at 20:22

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vao Tsun Over a year ago

also, you can create index concurrently then drop old one and rename new as old. which will give you longer build time, but almost online rebuild with minumum lock time. Another big advanture of drop/create - you can use it for changing location on the index, while reindex will just rebuild it.

Collectives™ on Stack Overflow

Postgres reindex vs drop and create

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related