[0] Do not get me wrong - if you can ignore the price tag, MSSQL is a great database engine. It has never given me any trouble I have not asked for, and it has some very nice features of its own (e.g. the Service Broker).
[0] Do not get me wrong - if you can ignore the price tag, MSSQL is a great database engine. It has never given me any trouble I have not asked for, and it has some very nice features of its own (e.g. the Service Broker).
One problem is that all those fancy features are released before they are done and never completed afterwards. Thus, Postgres is a mess of special cases that prevent things from working well together. Here are just a few examples I can list off the top of my head:
* You can't make arrays of domain types (domain types are a way to constrain the allowed values of some underlying more-primitive type)
* You can't parse JSON into a composite that has composites as any of its field types
* You can't do update..returning or insert...returning as a subquery and have to fall back to CTEs
* Composites are not allowed to contain instances of themselves within any of their fields
* Table inheritance doesn't work well with uniqueness constraints
Additionally, both the text and binary data exchange formats are completely unspecified with the binary format being completely undocumented and the text format being woefully underdocumented. The text format is also just really really messy and the results that you get back from the server are very irregular... It's pretty much impossible to write a Postgres driver that is anything other than a pile of hacks produced by reading the code and guessing. Pretty much none of them properly handle the more completed cases (most just punt on composites, for example).
Anyway: Postgres has a lot of wonderful tech in it that would take many, many years to duplicate and I would pick it from the field of actually-available RDBMSes every time but... it's still deeply terrible.
All that is to say: Postgres very much feels (to me) like tech from the awful, edge-casey past that is simply so useful and so hard to replicate that we are stuck with it!
If you want a general purpose language as your database engine, you'll likely end up with something more like MongoDB where for any moderately complex query you'll end up hand-writing a single static query plan for everything.
This is probably why people seem less fussed about some of the asymmetries you identify in RDBMSs. Different philosophies.
Do you mean recursive type definitions? Or? Who does allow that? And what would you use it for in an SQL context? Please elaborate, I'm curious what you are getting at here.
> Table inheritance doesn't work well with uniqueness constraints
This got somewhat better in postgresql 11 (beta1 is available now) in that unique works for partitioned tables. I don't know if that also includes inherited tables, they are similar but not the same. Partitioned tables are the case most people are interested in anyway.
SQL Server 2005 included SQLOS, bypassing the OS for direct memory and disk access that did wonders for performance. Parallel query execution in 2010 further cemented performance gains.
If only it had simple upserts, array types and proper json support, small details that make a big difference.
This has been my experience as well, though I haven't used it since SQL 2012. I dreadfully miss SQL Server Management Studio (SSMS), it is BY FAR the best IDE/GUI for a database I've ever come across.