r/programming • • Nov 16 '14

PostgreSQL vs. MS SQL Server - A comparison of two relational databases from the point of view of a data analyst

http://www.pg-versus-ms.com/
171 Upvotes

321 comments sorted by

View all comments

Show parent comments

1

u/rmxz Nov 21 '14 edited Nov 21 '14

So, if your needs are to run interactive analytics on 10TB of data - SQL Server is the best.

On a system twice as large as you described, our experience porting from one of those systems to the other was described in another comment under this article, with both CPU and disk performance charts showing how drastically the system performed before and after the switch.

The TL/DR summary of that article is that porting to Postgres saved us from having to do rather expensive hardware upgrade because it was incredibly more efficient, thanks to GIST and GIN indexes which drastically reduced CPU load; far more compact storage of XML data saving lots of I/O; and writable CTEs meaning far more efficient inserts.

1

u/bucknuggets Nov 21 '14

While postgresql certainly has a number of great features like gist, gin, and gis indexing - none of this is useful for typical analytics. Your experience with XML with 20TB of XML is no more relevant than another fellow's experience with MongoDB. In neither case - are we talking about analytics.

What it really needs to be competitive for large reporting and similar analytical applications (where you might have many queries accessing 10% of your table's data at a time) - is better range partitioning, hash-partitioning across instances, query parallelism, etc.