I would add to this list how Postgres makes solving problems in side projects fun. Write a little pl/pgsql rather than building a trigger or service worker system. Play with all the postgis stuff when you're messing around with maps. I've never felt frustrated when toying around and having Postgres playing support.
PL/pgSQL is great to cut down on network round-trips for relatively localized changes, though it can be surprising for folks used to keeping all the work in the app layer. DBs can also become CPU bound bottlenecks.
I have yet to play with postgis specifically, i have read many good things. Thinking of un-archiving some old side projects and experimenting with postgis!
PostGIS is amazing. The types of queries you can write continually blow my mind. One warning is that ultra fancy queries can be pretty slow if you're going beyond a million rows. Some tips:
• Materialized views with indexing are an easy way to solve many speed issues where you'd like to use a fancy query quickly.
• The GEOGRAPHY data type is great for data integrity, but often slower for queries. I've made sure our primary data is stored as GEOGRAPHY then added expression indices [1] casting to GEOMETRY. Spatial joins and filters can be done on the casted column where faster queries are needed.
• Source data is often very high resolution. If you don't need it, simplifying high accuracy data (5m or whatever) to something much lower resolution (500m, 1km, or whatever) in a derived view or table using PostGIS' simplification functions can greatly improve spatial predicate performance.
If you get your GIS data into PostGis, then you should try QGis, to visualize it. It helped also fix some bug and find some unexpected stuff, for example - that in postgis (and gis databases in general?) the order of coordinates is different from what you expect from using a gps in everyday life.
Now.. which order was the correct one? I have forgotten after not doing gis stuff for some years now.
The coordinate order is (X, Y) just like in math (and XYZ if you want to use a vertical coordinate) which differs from our colloquial (latitude, longitude). This is easier to remember if you remember PostGIS supports many non-lat/lon non-WGS84 coordinate systems.
