topic reindex using "reindex" command in postgresql in Data Management Questions

reindex using "reindex" command in postgresql

yockee — Wed, 30 Oct 2024 03:28:06 GMT

How to reindex feature class that just been loaded into postgresql ? I load the data using Append tool.

I know I can use "rebuild Indexes (Data Management)".

But, can I use Postgresql command such as

"REINDEX (verbose, concurrently)
TABLE dbgis.bnp__attach;"

Note: this table is an attachment that contains blob data. It has 200K+ records and each row has around 1MB of blob (picture)

Re: reindex using "reindex" command in postgresql

VinceAngelo — Wed, 30 Oct 2024 20:08:34 GMT

Yes, you can manage indexes manually with SQL in pgAdmin, psql, or even arcpy.ArcSDESQLExecute().

- V

Re: reindex using "reindex" command in postgresql

yockee — Mon, 02 Dec 2024 07:14:00 GMT

Hi @VinceAngelo do I have to turn off the corresponding map service, stop editing using DML queries and quit any arcgis pro apps that access the feature class during the index rebuild ?

Thanks

Re: reindex using "reindex" command in postgresql

VinceAngelo — Mon, 02 Dec 2024 14:53:56 GMT

I never have. I do run CLUSTER, VACUUM, and REINDEX at quiet times, but "quiet" can be hard to find in 24x7 systems. REINDEX will slow down queries, but not cause them to fail. VACUUM might grab a lock; the docs will say for sure.

- V