5

If I have a bunch of rows in a Postgres table, then create an index, does the index get automatically updated if new data rows are inserted after it's creation?

Alternatively stated, is REINDEX needed only in some rarer scenarios, or whenever new data is added?

  • Short answer: new data gets indexed automatically. And reindex is rarely needed. – Colin 't Hart Oct 12 '18 at 7:38
2

New data gets indexed automatically. Do note however that the presence of an index will therefore slow down inserts and updates (and deletes too of course). For tables with an extremely high volume of transactions, you have to be very careful about adding indexes. For most tables in most systems this is not an issue.

reindex is rarely needed under normal circumstances. Of these rare cases, the most common is probably "bloat" caused by lots of mutations -- usually when those mutations cause data on the disk to be removed from the "middle" and added to the "end".

1

Let me quote the documentation:

REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index. There are several scenarios in which to use REINDEX:

  • An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes can become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

  • An index has become “bloated”, that is it contains many empty or nearly-empty pages. This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. See Section 24.2 for more information.

  • You have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect.

  • An index build with the CONCURRENTLY option failed, leaving an “invalid” index. Such indexes are useless but it can be convenient to use REINDEX to rebuild them. Note that REINDEX will not perform a concurrent build. To build the index without interfering with production you should drop the index and reissue the CREATE INDEX CONCURRENTLY command.

It's nothing said that REINDEX needs to be called after routine procedures such as UPDATE or INSERT calls.

Answering your question "does the index get automatically updated if new data rows are inserted after it's creation". Yes, indexes are automatically rebuilt, otherwise they don't have much sense. Rebuilding indexes in OLTP database is a part of a transaction, that's why INSERT / UPDATE / DELETE may take a lot of time in a table with massive indexes - operation won't finish until indexes are rebuilt.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.