What About Postgres?
Since I wrote my post yesterday about Oracle and SQL Server, I’ve gotten a lot of positive feedback (except for one grouchy Oracle DBA) on my post. That said, I should probably stay clear of Redwood Shores anytime soon. However there was one interesting comment from Brent Ozar (b|t)
While Postgres is a very robust database that is great for custom developed applications, this customer has built a pretty big solution on top of SQL Server, so that’s not really an option.
However, let’s look at the features they are using in SQL Server and compare them to Postgres. Since this a real customer case, it’s easy to compare.
1. Columnstore indexes—Microsoft has done an excellent job on this feature, and in SQL Server 2016 new features like batch mode push-down drive really solid performance on large analytic queries. Postgres has a project for columnstore but it is not developed. There’s also this add-on feature https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/ which does not offer batch execution mode performance enhancements and frankly offers extremely mediocre performance.
You can compare this benchmark:
https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout
to the SQL Server one:
2. Always On Availability Groups—In this system design we are using readable secondaries as a method to deliver more data to customers. It doesn’t work for all systems, but in this case it works really well. Postgres has a readable secondary option, but it is far less mature than the SQL Server feature. For example, you can’t create a temp table in a readable secondary.
3. Analysis Service Tabular—There is no comparison here. Postgres has some OLAP functions that are comparable to windowing functions in T-SQL. Not an in-memory calculation engine.
4. R Services—You can connect R to Postgres. However, SQL Server’s R Services leverages the SQL Server engine to process data, unlike Postgres which uses R’s traditional approach of needing the entire dataset in memory. Once again, this would require a 3rd party plug in to work in Postgres.
5. While Postgres has partitioning, it is not as seamless as in SQL Server, and requires some level of application changes to support.
https://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
While I feel that SQL Server’s implementation of partitioning could be better, I don’t have to change any code to implement.
6. Postgres has nothing like the Query Store. There are data dictionary views that offer some level of insight, but the Query Store is a fantastic addition to SQL Server that helps developers and DBAs alike
7. Postgres has no native spatial feature. There is a plug-in that does it, but once again we are making an even bigger footprint of 3rd party add-ins to manage.
Postgres is a really good database engine, with a rich ecosystem of developers writing code for it. SQL Server on the other hand, is a mature product that has had a large push to support analytic performance and scale.
Additionally, this customer is leveraging the Azure ecosystem as part of their process, and that is only possible via SQL Server’s tight integration with the platform.
Joey, I have to make one nitpick, around geospatial being third party. No one owns the copyright to all of postgres, and you have a handful of vendors like EnterpriseDB and CommandPrompt that offer commercial support. So really its a matter of does your linux distro, (or the people that make your MSI if you’re doing postgres on windows) bundle geospatial.
So for me (and honestly as a dev vendor support is not something I never have much say in) the demarc of “third party footprint” is more along the lines of “what is bundled by my vendor” I know the MSIs I used had PostGIS checkboxes for half a decade so psotgis is no more third party to me than hadoop on SQL Server is IMHO.
That’s acceptable,..