3 ms·

Posts like this always make a little sad, because at work, we use Microsoft SQL Server[0], and for private programming, I usually prefer SQLite. Still, in some

by krylon 8y ago

Posts like this always make a little sad, because at work, we use Microsoft SQL Server[0], and for private programming, I usually prefer SQLite. Still, in some ways, Postgres feels like somebody invented a time machine and brought back a database engine from the future. Sigh Maybe I will find a good reason to use Postgres one of these days... ;-) [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).

gigatexal 8y ago
We have found service broker to be one of the most brittle things. But I agree that the engine is really great. At the startup we use Postgres and as a former sql server DBA I love it. Auto sharding in 11 will be awesome.
- iKSv2 8y ago
  " Auto sharding in 11 will be awesome." This with some form of multi-master without a single cordinator or something similar would be amazing. Too much to ask though
tomnipotent 8y ago
Will always have a soft spot in my heart for MSSQL, it's always been a relative pleasure to work with (even though I had to use Windows). I worked at the company that spawned MySpace, and after we were acquired by FOX I led a project to build a white-label social networking platform that could be re-used across FOX properties. We were getting 12-14k QPS back in 2008 on a single 32-bit quad-core AMD Opteron (1st gen) with a DAS (in a time when SANs ruled supreme). That was the first season of American Idol that the website didn't crash during broadcast. 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.
- manigandham 8y ago
  SQL 2017 is still the fastest mainstream RDBMS and now runs on Linux too, using that same PAL architecture to abstract the differences. Features like columnstores, in-memory tables and graph processing make it one of the most versatile systems today. If only it had simple upserts, array types and proper json support, small details that make a big difference.
  - tomnipotent 8y ago
    > SQL 2017 is still the fastest mainstream RDBMS 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.
ako 8y ago
From the future? Most new features have been available in Oracle for years. Still prefer postgresql to Oracle, but Oracle deserves some credits.
clhodapp 8y ago
On a relative scale with other DBMS's, Postgres is one of the most featureful, nicest-to-use databases. In reality, though, it's really terrible... It's just the least terrible. 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!
- ris 8y ago
  I think RDBMSs will always feel slightly "special-case"-y when compared, as you seem to be doing, with general purpose programming languages - remember that SQL was never designed to be a turing complete language (insert someone showing it doing CTE tricks that make it so...). It instead exposes a number of primitives that a database is generally good at reasoning about and in return it can "see through" a lot of the queries the user writes and find efficient ways of performing them. 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.
EvanAnderson 8y ago
Nice to see love for Service Broker. Sometimes I feel like I'm the only person in the world who knows about it.

by krylon 8y ago

gigatexal 8y ago

We have found service broker to be one of the most brittle things. But I agree that the engine is really great. At the startup we use Postgres and as a former sql server DBA I love it. Auto sharding in 11 will be awesome.

iKSv2 8y ago
" Auto sharding in 11 will be awesome." This with some form of multi-master without a single cordinator or something similar would be amazing. Too much to ask though

tomnipotent 8y ago

Will always have a soft spot in my heart for MSSQL, it's always been a relative pleasure to work with (even though I had to use Windows). I worked at the company that spawned MySpace, and after we were acquired by FOX I led a project to build a white-label social networking platform that could be re-used across FOX properties. We were getting 12-14k QPS back in 2008 on a single 32-bit quad-core AMD Opteron (1st gen) with a DAS (in a time when SANs ruled supreme). That was the first season of American Idol that the website didn't crash during broadcast. 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.

manigandham 8y ago
SQL 2017 is still the fastest mainstream RDBMS and now runs on Linux too, using that same PAL architecture to abstract the differences. Features like columnstores, in-memory tables and graph processing make it one of the most versatile systems today. If only it had simple upserts, array types and proper json support, small details that make a big difference.
- tomnipotent 8y ago
  > SQL 2017 is still the fastest mainstream RDBMS 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.

ako 8y ago

From the future? Most new features have been available in Oracle for years. Still prefer postgresql to Oracle, but Oracle deserves some credits.

clhodapp 8y ago

On a relative scale with other DBMS's, Postgres is one of the most featureful, nicest-to-use databases. In reality, though, it's really terrible... It's just the least terrible. 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!

ris 8y ago
I think RDBMSs will always feel slightly "special-case"-y when compared, as you seem to be doing, with general purpose programming languages - remember that SQL was never designed to be a turing complete language (insert someone showing it doing CTE tricks that make it so...). It instead exposes a number of primitives that a database is generally good at reasoning about and in return it can "see through" a lot of the queries the user writes and find efficient ways of performing them. 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.

EvanAnderson 8y ago

Nice to see love for Service Broker. Sometimes I feel like I'm the only person in the world who knows about it.