Tuning PostgreSQL

First off, let's all make sure we are on the same page. I, Joel McCune, am by no stretch of the imagination, a database administrator. Still, I keep an instance of PostgreSQL running on my local work computer for use with ArcGIS, to create SDE databases. Recently discovered I need to do slightly more tuning to get the performance I need when doing analysis on geometric networks.

The issue I ran into involved working with USGS National Hydrology Dataset data. I needed to perform a very large volume to upstream and downstream traces. The process took two weeks to complete. What counfounded me is how this could take longer using data stored in PostgreSQL than using a file geodatabase.

Examining the system resource consumption revealed the answer. There was a lot of read and write activity going on, but very little of anything else. There was CPU and RAM to spare, but just a lot of disk activity. Something was throttling the process...the PostgreSQL configuration.

If planning on using PostgreSQL, please learn from my mistakes and edit your postgresql.conf file. This file typically is located in the same location as where you store your data. What you are looking for in this file, shared_buffers and max_locks_per_transaction, is detailed quite well in the Esri Help documentation page Memory tuning in PostgreSQL.

As a supplement I also found the PostgreSQL documentation to also be useful, especially the section on shared_buffers. It provides a good guideline, 25% of your system RAM. Hence, for my machine with 20GB of RAM, I set this to 5GB.

For those of you like myself who have a need for an SDE instance, PostgreSQL is a great option. Hopefully this will help you to get more out of your system!