Road Ahead - PostgreSQL database support at ArcGIS 9.3

The following post was written by Kasia Tuszynska, a product engineer on geodatabase team specializing in PostgreSQL and the geodatabase.

The release of ArcGIS 9.3 will see an addition to the list of databases supported by ArcSDE. PostgreSQL 8.3.0 is joining the ranks of Oracle, Sql Server Informix and DB2 and is the first open source database to be supported by ArcSDE. I previously worked with SQL Server and Oracle and did not really know what to expect from Postgres, but I found it a very clever and well thought out database, I loved the online community, and I learned more from reading forums than any book out there.

We had an interesting time implementing ArcSDE for Postgres because we wanted to support the PostGIS spatial type and our own spatial type implementation st_geometry. This was a conscious decision so our customers who use both our software and open source software would not have to choose between one and the other but could use both, and potentially even keep it all in one database. We also implemented our own spatial type, st_geometry, it was designed to work with the different geodatabase functionality, we implemented it to make sure that any customer who wants to try out what geodatabases have to offer or who would choose to transition from one database to another would not loose any geodatabase functionality.

ArcSDE for Postgres will be released on Windows, Red Hat Linux and SUSE Linux. Since Postgres is open source and released with a BSD license we could include it in our installation, so if a customer wanted to install Postgres and SDE in one swoop they could do that in windows by executing one installation wizard, or two shell scripts for an install on Red Hat Linux. With SUSE we were halted by the lack of precompiled binaries for Postgres so installation will have to be done the old fashioned way – from source, but the rest of the setup can be done with scripts.

Implementing ArcSDE on Postgres had us learn the ins and out of the MVCC concurrency model, altering the way we send updates to postgres. We spent quite a long time tweaking the sql related to editing, trying to shave seconds off of the performance time. A lot of effort has also been put into the documentation, for folks already familiar with ArcSDE administration moving to postgres will probably be a snap but there a couple of small things to remember for postgres, so when in doubt please check the help. For instance, to avoid having your feature classes looking like tables in ArcCatalog and to make them accessible to everyone the “usage” privilege has to be granted on a schema to the public role. Also, installing Postgres on Windows has to be done through an admin account but the account under which Postgres (postmaster) is to run can not belong to the admin user group … Postgres idiosyncrasies like this can drive one crazy especially if you are not used to it.

This is our initial release and I am hoping that all of our testing caught every annoying little bug, but in case we missed something please do not hesitate to drop us a bug in Nimbus.