Creating a spatial index on a geometry field is very important. The docs also state that you should perform a VACUUM ANALYZE after large update operations. But is it ever necessary to drop an existing spatial index and recreate one after significant changes to a geometry field in a table?
2
-
1At least there is usually no need to drop index because you can use reindex postgresql.org/docs/current/sql-reindex.html. I am not sure if reindex is needed if VACUUM FULL is used postgresql.org/docs/current/sql-vacuum.html. The older documentation has mentioned that indexes get rebuild by the same. Perhaps you would get better answers from the db-admin site.– user30184Commented Jun 8, 2021 at 7:30
-
2There can be speed advantages to dropping the index before large inserts and then reindexing, but otherwise I wouldn't see a need to do it– Ian Turton ♦Commented Jun 8, 2021 at 7:35
Add a comment
|
1 Answer
Im assuming your talking about the doco here
https://postgis.net/workshops/postgis-intro/indexing.html
I would probably research on general SQL DB's indexes, and under what circumstances to rebuild them.
Inserting/modifying data, will automatically build the index to include that new data. There might be some circumstances which you would consider 'significant' changes, which I would personally drop an index and recreate from scratch. These are
- Changes in geometry type for a record (point to a line geometry)
- Changes to the geometry field name (geom to geometry)
- Significant changes to the complexity of the data (eg: Neat polygons of state boundaries becoming overlapping many-vertixed donutted messes.
The above changes render the existing spatial index a bit redundant. PostGIS may automatically recreate it under some circumstances anyways (can't remember).
Comparatively, a large number of DB transactions (ie? simply inserting a large number of new records) may cause DB corruption, which then buggers up the index, but this is rare. If you have a DB which requires to be highly available, then your procedure for modifying data should be robust enough to minimize corruption. This may include a reindex procedure.
Some more info here as well:
https://dba.stackexchange.com/questions/219917/postgres-do-indexes-automatically-reindex-new-data
