But What about Postgres?

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 3^rd party plug-in 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 3^rd 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.