Are PostgreSQL tables automatically re-indexed after an update?

Question

If I index a PostgreSQL table and then update it, do I need to re-index the table or is it automatically re-indexed?

Can someone provide a link to PostgreSQL documentation for further reading? I've got this so far: https://www.postgresql.org/docs/9.1/static/sql-createindex.html

score 2 · Accepted Answer · edited Jul 20 '16 at 15:38

2

indexes in PostgreSQL do not need maintenance or tuning

You do not need to re-index manually.

For more details, please also read https://www.postgresql.org/docs/current/static/monitoring-stats.html

edited Jul 20 '16 at 15:38

answered Jul 20 '16 at 12:54

Bartosz Bilicki

12,599
13
71
113

score 2 · Answer 2 · edited Jul 20 '16 at 15:38

From further reading in the PostgreSQL documentation:

Once an index is created, no further intervention is required: the system will update the index when the table is modified, and it will use the index in queries when it thinks doing so would be more efficient than a sequential table scan. But you might have to run the ANALYZE command regularly to update statistics to allow the query planner to make educated decisions. See Chapter 14 for information about how to find out whether an index is used and when and why the planner might choose not to use an index.

See: https://www.postgresql.org/docs/current/static/indexes-intro.html

Are PostgreSQL tables automatically re-indexed after an update?

2 Answers2