9

I create index when table is created, then insert data into table.
I think when the data in the table has been indexed, no need to run a REINDEX even if i delete or insert new data.
Is this right? Or each time, i need to run a REINDEX.

If I need to import a large amount of data into an existing indexed table should it be better to run a REINDEX?
When should I run a REINDEX?

Please tell me. Thanks!

Improve this question
4
  • Does the documentation answer your question? Commented Mar 4, 2021 at 6:33
  • Thanks for your support! Does this mean that a REINDEX is not theoretically required... Commented Mar 4, 2021 at 6:51
  • If data older than 30 days (about 7000 records) is deleted, is it necessary to run a REINDEX!!? Commented Mar 4, 2021 at 6:56
  • As the documentation says, it is not required for any of the situations in your question. The only time I've used it is when building a concurrent index failed. What is more important is vacuum to prevent bloat and analyze to keep the statistics used for query planning up to date. These are also done automatically, but sometimes they need a little tweaking. See confluence.atlassian.com/kb/… And 7000 records is too small to worry about. Commented Mar 4, 2021 at 6:58

1 Answer 1

Reset to default
17

You never need to run REINDEX to maintain data integrity; all data modifications will update the index.

In normal situations, you never need to REINDEX. In particular, bulk insert will never fragment an index more than it was before.

Indexes get bloated if you delete lots of rows or if you run UPDATEs faster than autovacuum can keep up with. Then you may need to REINDEX them.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.