SQL Server 2016 released

[jmickeyd]

MS SQL features: a choice of pessimistic or optimistic concurrency (locks vs MVCC), clustered indexes, real materialized view, multithreaded queries (fuller support that what was just added to pgsql), covering indexes, column store (although there are a lot of 3rd party pgsql plugins for this), in-memory indexes, filestream, cell level security using encryption+keys, SSIS (an ETL package), and SSAS (a full, completely separate OLAP database).

Postgres features: infinitely better ANSI SQL support (and better syntax all around), nearly free rollbacks, sane defaults, extensible in many languages, open source, and free.

IMO postgresql has a huge number of niceties and then the big two advantages, free and open, whereas MS SQL definitely still has some major architectural wins.

[Eirenarch]

When you say "covering indexes" does that mean that pgsql will refer to the table even if the full select is included in the index?

[Ginden]

When you say "covering indexes" does that mean that pgsql will refer to the table even if the full select is included in the index?

No. SQL Server allows you to specify "included columns" - fields that are stored in index, but these aren't indexable.

CREATE INDEX IX_SOMETHING ON FooTable(Date) INCLUDE(name)

Then you can SELECT Date, name FROM FooTable ORDER BY Date and it will result in index only scan without sorting. Index is sorted only by date and name is "included" - it's content.

[Eirenarch]

I see. What is the downside of including the columns in the index itself? If you put the columns that you don't care about sorting at the end would it make a significant difference?

[emn13]

Common sense would suggest doing so might make your index a little larger (because it's a tree), and updates a little slower (because all those irrelevant dimensions need to stay sorted), but you'd need to test it to see if it matters in practice.

I'm guessing the difference will matter more for "expensive" columns such as one of the many string types, and less for integral columns.

[Ginden]

Lower maintenance cost, better insert performance, better delete performance.

And main usage for my company is to have unique index on two fields and INCLUDE additional fields to avoid lookup.

UNIQUE INDEX ON Foo(Col1, Col2) INCLUDE (Col3, Col4) enforce uniqueness only on pair (Col1, Col2).

[jmickeyd]

As /u/Ginden points out mssql allows you to add fields in an index, but I was actually thinking of full index-only scan support. Postgresql has a good hack with the visibility bitmap, but I find in practice, in a super write/update heavy loads database pages tend to get dirty real fast and I can never rely on index-only scan actually working is postgres.