Is there a real different between hosted and local Postgres geodatabase

Question

We have some  simple large layers (about 1M features) that we like to display and use in Dashboards with some statistics.

We can save them to hosted database (that is really Postgres)  or build our  out Postgres database.

There are some advantages and disadvantage to both option.

Do you think the performances should be different?

Do you think esri build  the Postgres of the hosted in a different way that we install a local Postgres?

Thanks

MarcoBoeringa · Answer

Honestly, that hardly sound like anything needing "special configuration". It is a tiny database by all measures nowadays. As said, I have run a PostgreSQL database with >2.5 B records on a single server. Secondly, any PostgreSQL configuration is also tightly coupled with the actual hardware specs. E.g. something like the 'shared_buffers' setting is almost always recommended to set as a percentage of your RAM, so unless you already know or share your configuration here, there is no sensible recommendation.

That said, the three things that really will make a difference:

- Do no run from HDD, use NVMe attached PCIe SSDs. It is hardly a question nowadays, but up to maybe five years ago, I still regularily read about people putting their database on HDD, which kills performance compared to modern NVMe SSDs.

- Switch off 'synchronous_commit' in the postgresql.conf file. This is probably the single most effective configuration you can make if you do not need it (no replication to standby servers) to enhance performance on writes / UPDATEs.

- Set an appropriate 'shared_buffers' setting to give PostgreSQL breathing room in RAM.

All other settings are likely to only contribute minor, especially in a minor use case like yours.

ModyBuchbinder · Answer

Hi Marco

We are going to use local PostgreSQL to keep very few (5-10) layers with about 1-2M features each.

We need best performance with drawing and some statistics in dashboards.

Can you give a few hints about the configuration assuming that I do not have the time to become an expert in PostgreSQL configuration?

Thanks

MarcoBoeringa · Answer

I am not sure what you mean. If the hosted database is based on PostgreSQL, there is no "magic spell" to make it better than any other PostgreSQL installation.

In the distant past, ArcSDE - one of the backbones of ESRI database technology - supported the 'RAW' spatial storage, which, due to being directly convertible to the ESRI internal geometry representation in applications, was generally regarded as a more performant option over databases 'native' geometry implementations for ESRI software (although ESRI never really made much noise about this or admitted to it). However, 'RAW' is past in favor of 'native' or ESRI ST_Geometry geometry storage in all databases AFAIK.

If with your remark, you are just referring to PostgreSQL configuration as done with e.g. the 'postgresql.conf' file, I suggest you start delving into the wonderful world of PostgreSQL configuration and read up about the subject. There is definitely stuff you need to adjust in there, as the default settings are not suited for large (Post-)GIS databases, but just the absolute minimum to get a server running on minimal hardware.