Does postgresql index update on inserting new row?

Question

Sorry if this is a dumb question but do i need to reindex my table every time i insert rows, or does the new row get indexed when added?

score 32 · Accepted Answer · answered Jul 07 '13 at 07:55

32

From the manual

Once an index is created, no further intervention is required: the system will update the index when the table is modified

answered Jul 07 '13 at 07:55

so surely by using a column that is history that will be updated a lot depending on what the user is doing that table will update the index (Even if the index isn't on the update column) surely a better practice would be put the update column in a table on its own with a user id relation so that the main focus of the user table is just on data that don't get updated very often and then there is a userupdate table which is more meaning full. – Luke Franklin Dec 08 '16 at 10:14

score 6 · Answer 2 · edited Jan 21 '21 at 21:59

6

http://postgresguide.com/performance/indexes.html

I think when you insert rows, the index does get updated. It maintains the sort on the index table as you insert data. Hence there are performance issues or downtimes on a table, if you try adding large number of rows at once.

edited Jan 21 '21 at 21:59

Nikunj Kakadiya

2,689
2
20
35

answered Jul 07 '13 at 05:25

Wingston Sharon

1,030
2
11
21

score 3 · Answer 3 · answered Jul 07 '13 at 11:48

3

On top of the other answers: PostgreSQL is a top notch Relational Database. I'm not aware of any Relational Database system where indices are not updated automatically.

answered Jul 07 '13 at 11:48

A.H.

63,967
15
92
126

memeplex · Answer 4 · 2019-09-16T23:51:33.893

It seems to depend on the type of index. For example, according to https://www.postgresql.org/docs/9.5/brin-intro.html, for BRIN indexes:

When a new page is created that does not fall within the last summarized range, that range does not automatically acquire a summary tuple; those tuples remain unsummarized until a summarization run is invoked later, creating initial summaries. This process can be invoked manually using the brin_summarize_new_values(regclass) function, or automatically when VACUUM processes the table.

Although this seems to have changed in version 10.

Does postgresql index update on inserting new row?

4 Answers4