Speed difference MS SQL Server 2019 to PostgreSQL/PostGIS 12 for spatial data

My take on the spatial data component of MSSQL Server echoes the documentation - basic spatial capabilities, and anything more complex is handled by 3rd parties.

What this means to us is that MSSQL Server can handle some basic Spatial SQL functions that get a bit of information from an overlapping polygon, a quick distance or area of few features, etc.

It isn't built and doesn't perform well for doing massive spatial operations on thousands or millions of features - what is otherwise called "geoprocessing". This is where ESRI has picked up and said 'if you're going to store spatial data in a database, it is best managed/edited/analysed by our GUI'.

PostGIS, on the other hand, has been built not just for the simple stuff, but for the complex bulk processing of spatial and non-spatial data in the database, and as such, performs much faster. This has been true for all student/boundary/demographic analysis we perform in our system using postgis.

To its credit, however, MSSQL Server was a great 'gateway' into SQL and spatial data. PostgreSQL with PostGIS is just the best.

Spatial algorithms such as buffer are complex, with a lot of moving parts. It only takes one inefficient design decision or data structure to reduce performance. Conversely, sometimes it's possible to find a different approach that can improve performance dramatically.

The PostGIS buffer code (which originates in JTS/GEOS) has some optimizations which really help to improve performance. For example, since buffers tend to "blur out" fine detail along boundaries, the algorithm simplifies boundaries before running the actual buffer. This can dramatically reduce the number of points being computed, and hence increase performance.