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?
Add a comment
|
4 Answers
From the manual
Once an index is created, no further intervention is required: the system will update the index when the table is modified
1 Comment
Luke Franklin
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.
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.
Comments
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.
Comments
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.
