> If you strictly have an OLTP workload, with lots of simultaneous
> connections issuing queries across small chunks of data, then
> PostgreSQL would be a good match for SQL server.
This matches my observations. In fact, PostgreSQL's MVCC seems to work
heavily in my favor in OLTP workloads.
> On the other-hand, if some of your work load is OLAP with a few
> connections issuing complicated queries across large chunks of data,
> then PostgreSQL will not perform as well as SQL server. SQL server
> can divide processing load of complicated queries across several
> processor, while PostgreSQL cannot.
While I agree with this in theory, it may or may not have a big impact
in practice. If you're not seeing multi-cpu activity spike up on your
MSSQL box during complex queries, you aren't likely to benefit much.
You can test by timing a query with and without a query hint of MAXDOP
1
select * from foo with (MAXDOP = 1)
which limits it to one processor. If it runs just as fast on one
processor, then this feature isn't something you'll miss.
Another set of features that could swing performance in MSSQL's favor
are covering indexes and clustered indexes. You can sort-of get around
clustered indexes being unavailable in PostgreSQL - especially on
low-churn tables, by scheduling CLUSTER commands. I've seen
discussions recently that one or both of these features are being
looked at pretty closely for inclusion in PostgreSQL.
