We use SQL Spatial native data for 90% of our spatial analysis.
Our catch is we have to edit the data in a Geodatabase then use FME Workbench (through our Data Interoperability Extension that comes with our ESRI enterprise license agreement) to move the data into SQL Server as native spatial data.
[EDIT] The ability to edit spatial data within ArcMap gives all the editing capabilities of that software. This to me is important as keeping the SQL Server / Microsoft / ArcGIS technologies together works best for consistency, support, etc.
FME simply bridges that gap, and gives us Spatial ETL solutions that neither Microsoft nor ArcGIS provide on their own.
I started looking at how to use a server solution, but it wasn't going to work basically due to the fact that I needed Server to edit native SQL spatial data. Another consideration at the time, and I don't know where this ended up because I stopped looking, was if the 'feature data service' was going away or not...
I wrote some more info on how we use FME to update our SDE, but this can easily apply to updating a production SQL environment as well: What is the best way to update a production ArcSDE database from staging?
The reason you're having so much difficulty using ArcGIS and PostgreSQL together is that you're not doing it right.
You've got three basic problems, in reverse order of severity:
- You're trying to mix-n-match PostGIS syntax with the
SDE.ST_Geometry datatype
All database products have a mechanism for distinguishing between function libraries, principally by using database schemas to keep them separate. But because that can be tiresome (wise, but tiresome), they also provide a mechanism for specifying a preference. Unfortunately, PostgreSQL's error messages around this feature don't say "No, no, to use the sde.ST_Geometry type, vice geometry, you need to prefix the function with 'sde.'"
- You're judging the Spatial Types and Functions standard against PostGIS
Esri was one of the first organizations to have standards-compliant geometry libraries in SQL databases, starting with DB2 and Informix (these date back to the same time when "SQL" was being added to PostgreSQL, and before PG support for ANSI SQL-92). Those libraries have been ported to all databases except Microsoft SQL Server (because it doesn't support datablade/extension/custom type libraries). PostGIS is standards-compliant, but they have scores more functions than that, mostly because they wanted to, and PostgreSQL was open enough for it to happen. Esri has a basic suite of functions that meet the standards, and type support across many RDBMS engines, some which have limits on the number of functions that can exploit custom types. It would be nice if Esri supported extra functions, but it is in no way a requirement to make effective use of the type. But consider this: The majority of extra PostGIS functions are for doing things that Esri has been doing for decades, and are available within Desktop (making them available to all RDBMS platforms, equally).
- You're not utilizing the out-of-the box capabilities of ArcGIS with PostgreSQL
In your question you state you want to use PostGIS and ArcGIS together. Well, starting at ArcGIS 10.4.0, you can, and you don't even need to enable an enterprise geodatabase to do so. You have two choices: You can use native RDBMS capabilities out of the box, with Query Layers accessing the tables for display and tables with PostGIS geometry created via SQL or read-write ArcGIS database access, or you can enable the enterprise geodatabase, and instruct ArcGIS to create PostGIS data by default by changing the GEOMETRY_STORAGE keyword parameters to PG_GEOMETRY (in fact, that's the only option in RDS instances, since Amazon doesn't allow the sde.ST_Geometry function library to be installed in the black-box database). So it all comes down to what you want to do in this instance. ArcGIS doesn't care which methodology you choose (except that you won't get multiversion geodatabases, and geodatabase replication, and archive history, etc... without an enterprise geodatabase). Other than some quirkiness with Sort (Data Management) with PostgreSQL source tables, I haven't found any issues in PG interaction with ArcGIS 10.4.x.
Best Answer
This answer appears to require an Enterprise Geodatabase (ArcSDE) and ArcGIS for Desktop at an Advanced/Standard license level.
The answer seems to be valid with ArcGIS 10.1 for Desktop and possibly some later versions.
Your PostgreSQL / PostGIS database must meet these version requirements: http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//015100000075000000
Second, you have to register the tables with the Geodatabase. This page gives you more details on how to get to an existing PostGIS table with ArcGIS: http://resources.arcgis.com/en/help/main/10.1/index.html#//002p0000006v000000