topic Re: PostgreSQL Enterprise Geodatabase performance using PG_GEOMETRY in Data Management Questions

PostgreSQL Enterprise Geodatabase performance using PG_GEOMETRY

ClaudioRuschel — Thu, 06 Jan 2022 20:07:44 GMT

I have been using by default the "PG_GEOMETRY" type to store features in Enterprise Geodatabase hosted in PostgreSQL. This strategy is very interesting when building and loading the database. But, in a production environment where most feature classes are accessed by geoservices in read-only mode, can the difference in the performance of accessing features kept in "PG_GEOMETRY" compared to "ST_GEOMETRY" be significant?

Re: PostgreSQL Enterprise Geodatabase performance using PG_GEOMETRY

George_Thompson — Thu, 06 Jan 2022 21:01:17 GMT

I think that we would need some more details on the clients accessing the data, data type (point / lines / polys), any advanced behavior, etc.

Do you have updated indexes / statistics?

Is the data editing / updated often?

Any additional details would be helpful.

Re: PostgreSQL Enterprise Geodatabase performance using PG_GEOMETRY

ClaudioRuschel — Fri, 07 Jan 2022 11:21:53 GMT

George, it is a general question. The geodatabases that I´m mentioning usually support applications like spatial data infrastructures with hundred of geoservices acccessing feature layers, almost allways on a read-only way. I once read a long time ago that internally ArcGIS would convert the PostGIS format to ST_Geometry for any operation. In other words, theoretically there would be a performance difference considering the same server environment and changing only the storage format. I have not found this issue documented in current versions of Enterprise Geodatabase. Therefore, I am questioning whether, in practice, there might be any noticeable difference in the performance of a geoservice considering only the variation of the geometry's storage format type.

Re: PostgreSQL Enterprise Geodatabase performance using PG_GEOMETRY

George_Thompson — Fri, 07 Jan 2022 12:22:04 GMT

Thanks for the additional information. I have not heard of ArcGIS converting PG --> ST before.

In terms of performance, I would say that they are "equal" depending on many factors. The ArcGIS clients will work with both.

The best course of action would be to do some testing within your environment. I have seen clients use both formats within the same Enterprise Geodatabase based on the need.

Others may be able to chime in on what they have seen.