5 ms·

I'm a big fan of Postgres and it is basically the only DB I use but the title is naive and the post is low content. SQL Server, Oracle, and DB2 are crown jewel

by kev009 8y ago

I'm a big fan of Postgres and it is basically the only DB I use but the title is naive and the post is low content. SQL Server, Oracle, and DB2 are crown jewels products and each have substantial and difficult to implement niche or extreme scale features or other sweet spots that no open source databases come close to after two decades, and they weren't sitting still during that time either.

EGreg 8y ago
Can you be more specific? I always wondered why people use them
- kev009 8y ago
  I'm not advocating their use and the default should be right and smallest fit for the job, which means sqlite or Postgres nearly always. But too often people get in this mindset that old commercial products are meritless or whatever. An example for Oracle would be RAC. This is a mature shared data architecture to provide high availability and horizontal server scaling to a DB. Deploying this is a fairly standard Oracle DBA activity. Getting something similar with open source tools is a big lift and permanent support commitment and will be for a while. Meanwhile these three commercial DBs have provided continuity for decades for features like this.
  - devy 8y ago
    > But too often people get in this mindset that old commercial products are meritless or whatever. There is a lot of that assumption. And then, there is the vendor lock-in licensing terms that commercial DB vendors utilize, in that database switching cost is just prohibitively high and impractical.
    - manigandham 8y ago
      The lock-in is not from licensing, it's from the features and performance. If you aren't using those capabilities then you didn't need that product anyway, and can use any number of migration tools to switch to a different database.
      - devy 8y ago
        Yes, most of factors for lock-in are from features and ecosystem and replacement cost. But in licensing negotiations, license up-sale, auxiliary products & services are definitely making the lock-in tighter - since you've just made the big investments, why switching?
- TylerE 8y ago
  Scaling/replication is the usual thing. Postgres certainly has a better story here than it used to, but if you want to do serious scale replication it's very much in roll-your-own territory. Like, imagine you have a few hundred terabytes of data you need to keep in-sync globally, between multiple datacenters, with many servers in each DC.
  - deepsun 8y ago
    I was under impression that only Spanner-like databases (CockroachDB and FaunaDB) are the only ones that can do cross-datacenter multi-row transactions at scale. Because they were designed exactly for this, otherwise tradeoffs taken are just too bad for the that use-case (e.g. too slow or too brittle in availability). By cross-datacenter I mean datacenters far away from each other, in different geo regions.
    - madhadron 8y ago
      You can do single master, many read replicas, which works fine for many, many use cases.
- manigandham 8y ago
  SQL Server has columnstores, in-memory tables with compiled procedures (which can be combined with columnstores), graph processing, built-in machine learning extensions, AlwaysOn availability/replication groups, Stretch and Polybase to read from external sources, integrated encryption, fantastic tooling, and the best optimizer with batch/vectorized processing. It still doesn't have proper upsert or JSON columns, so PostgreSQL wins on small usability features, but SQL Server is a serious workhorse when it comes to large scale operational and analytical systems with unmatched performance. PG barely has parallel queries or decent connection management and is a decade away from catching up to SQL server in these features.
  - BrentOzar 8y ago
    > It still doesn't have proper upsert or JSON columns Upsert (MERGE) was added in SQL Server 2008 (although it's chock full of known bugs): https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... JSON was added in SQL Server 2016, although indexing it is still tricky: https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/js...
    - manigandham 8y ago
      That's what I meant by "proper". It can do it, but PG does it better. It's one area that I wish they would invest time into considering the large developer impact it would have.
      - SigmundA 8y ago
        My understanding is they got burned by all the work put into the XML datatype and don't want to repeat it with JSON, hence the more conservative approach.
  - fanf2 8y ago
    How do Stretch and Polybase compare to Foreign Data Wrappers? Is the integrated encryption comparable to pgcrypto?
- krylon 8y ago
  At work, we run SQL Server, because we use several third-party applications that use it. And once it's there and paid for, if we need another database, it is easier to just dump it on the server that is already there, rather than run a second RDBMS server. I am a little torn about this - on the one hand it strikes me as laziness that all these Windows developers will just pick SQL Server because it is so convenient (good integration with Visual Studio, drivers are present by default in .Net Framework). On the other hand, in terms of performance and reliability, I cannot say a single bad word about SQL Server. Another thing I like is that one can use accounts from an Active Directory domain as database accounts, so you do not have to duplicate group structures and such, if you use elaborate database permissions. I have come to like T-SQL, which integrates some procedural features into SQL, so SQL Server does not need a separate language for writing stored procedures, triggers, and so forth. Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature. Postgres offers many features SQL Server lacks, e.g. the array of data types available in Postgres is amazing, or the fact that it has enums. SQL Server has some features Postgres lacks, for example the Service Broker, which is very interesting, but apparently non-trivial to use correctly.
  - gaius 8y ago
    Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature. Embedded R in SQL Server as well. Postgres is my default database choice, or SQLite if it's small, but SQL Server is a solid, high performance product and I'll gladly run it for the most demanding workloads.
akerro 8y ago
Oracle SQL Server is like 50 years old
- pkaye 8y ago
  I thought Oracle was founded in 1977 so it would be closer to 40 years.
  - kevindqc 8y ago
    Correct! First available release was in 1979 according to the wiki, so almost 40 years. https://en.wikipedia.org/wiki/Oracle_Database#Releases_and_versions https://en.wikipedia.org/wiki/Oracle_Database#Releases_and_v...
rolleiflex 8y ago
One thing Postgres definitely doesn't do, and I wish it did, is embedded. This is usually the place that people use SQLite, for example, apps that are clients, but still need to do substantial work on the client side. The problem is, SQLite does have a few limitations that it doesn't make obvious: some of the more complex SQL syntax that Postgres supports are missing, but more importantly, it can only ever do one write or read to the database predictably. (Don't believe me? Try this: https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894 https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894) I know that SQLite supports concurrent writes and reads technically, but it predictably leads to 'database is locked' issues. In practice you have to use it in a fashion that it only ever does one thing, read, or write. No multiple writes. No single write and multiple readers, no single write and single reader. Just read — or write. (Edit: WAL doesn't help with this either.) Or you're in a world of hurt. I'd love to be able to move to Postgres just to get away from that.
- manigandham 8y ago
  This is almost always a driver/wrapper problem, which seems to be the case here. SQLite supports multiple readers without issue that only block slightly when a write transaction is committed, and there's also WAL mode with even better concurrency that avoids locks altogether for reads.
  - rolleiflex 8y ago
    I've had the same problem with Python, not just Go. If it's a driver problem, it's a very widespread one, which would point out to SQLite driver API being in need of some love if so many folks who write SQLite drivers are making the same mistake.
    - blattimwind 8y ago
      The Python driver is buggy-by-default. This has been documented in recent versions [1], and is kinda sane for the undo log, but not so good if you want to use snapshot isolation. I guess many bindings would make the same decision. Basically they delay the emission of BEGIN until the first DML/DDL is executed. If you fix this (which is easy), then WAL works just fine. Note that transactions, as is standard, span from BEGIN/COMMIT to COMMIT/ROLLBACK. See e.g. https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#transaction-isolation-level https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#t... and https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... [1] Really it's more like "encoded": https://docs.python.org/3/library/sqlite3.html#controlling-transactions https://docs.python.org/3/library/sqlite3.html#controlling-t... -- you already need to know the issue to understand that this section specifies the root cause.
judgardner 8y ago
Agree. I have been using Postgres and Oracle in production since ~2000 (oracle 8i). Postgres is an amazing project but the oracle core database has billions of R&D dollars. Don't confuse the oracle core database with the awful applications they hang off of it. Oracle will still destroy pg for most OLAP workloads, now you have to decide if the insane licencing cost is worth it. Also just want to add an oracle developers post that I find hilarious... https://news.ycombinator.com/item?id=18442941 https://news.ycombinator.com/item?id=18442941
- shdh 8y ago
  > https://news.ycombinator.com/item?id=18442941 https://news.ycombinator.com/item?id=18442941 This was a gem. Really makes you appreciate not having to work on such a codebase.

by kev009 8y ago

EGreg 8y ago

Can you be more specific? I always wondered why people use them

kev009 8y ago
I'm not advocating their use and the default should be right and smallest fit for the job, which means sqlite or Postgres nearly always. But too often people get in this mindset that old commercial products are meritless or whatever. An example for Oracle would be RAC. This is a mature shared data architecture to provide high availability and horizontal server scaling to a DB. Deploying this is a fairly standard Oracle DBA activity. Getting something similar with open source tools is a big lift and permanent support commitment and will be for a while. Meanwhile these three commercial DBs have provided continuity for decades for features like this.
- devy 8y ago
  > But too often people get in this mindset that old commercial products are meritless or whatever. There is a lot of that assumption. And then, there is the vendor lock-in licensing terms that commercial DB vendors utilize, in that database switching cost is just prohibitively high and impractical.
  - manigandham 8y ago
    The lock-in is not from licensing, it's from the features and performance. If you aren't using those capabilities then you didn't need that product anyway, and can use any number of migration tools to switch to a different database.
    - devy 8y ago
      Yes, most of factors for lock-in are from features and ecosystem and replacement cost. But in licensing negotiations, license up-sale, auxiliary products & services are definitely making the lock-in tighter - since you've just made the big investments, why switching?
TylerE 8y ago
Scaling/replication is the usual thing. Postgres certainly has a better story here than it used to, but if you want to do serious scale replication it's very much in roll-your-own territory. Like, imagine you have a few hundred terabytes of data you need to keep in-sync globally, between multiple datacenters, with many servers in each DC.
- deepsun 8y ago
  I was under impression that only Spanner-like databases (CockroachDB and FaunaDB) are the only ones that can do cross-datacenter multi-row transactions at scale. Because they were designed exactly for this, otherwise tradeoffs taken are just too bad for the that use-case (e.g. too slow or too brittle in availability). By cross-datacenter I mean datacenters far away from each other, in different geo regions.
  - madhadron 8y ago
    You can do single master, many read replicas, which works fine for many, many use cases.
manigandham 8y ago
SQL Server has columnstores, in-memory tables with compiled procedures (which can be combined with columnstores), graph processing, built-in machine learning extensions, AlwaysOn availability/replication groups, Stretch and Polybase to read from external sources, integrated encryption, fantastic tooling, and the best optimizer with batch/vectorized processing. It still doesn't have proper upsert or JSON columns, so PostgreSQL wins on small usability features, but SQL Server is a serious workhorse when it comes to large scale operational and analytical systems with unmatched performance. PG barely has parallel queries or decent connection management and is a decade away from catching up to SQL server in these features.
- BrentOzar 8y ago
  > It still doesn't have proper upsert or JSON columns Upsert (MERGE) was added in SQL Server 2008 (although it's chock full of known bugs): https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... JSON was added in SQL Server 2016, although indexing it is still tricky: https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/js...
  - manigandham 8y ago
    That's what I meant by "proper". It can do it, but PG does it better. It's one area that I wish they would invest time into considering the large developer impact it would have.
    - SigmundA 8y ago
      My understanding is they got burned by all the work put into the XML datatype and don't want to repeat it with JSON, hence the more conservative approach.
- fanf2 8y ago
  How do Stretch and Polybase compare to Foreign Data Wrappers? Is the integrated encryption comparable to pgcrypto?
krylon 8y ago
At work, we run SQL Server, because we use several third-party applications that use it. And once it's there and paid for, if we need another database, it is easier to just dump it on the server that is already there, rather than run a second RDBMS server. I am a little torn about this - on the one hand it strikes me as laziness that all these Windows developers will just pick SQL Server because it is so convenient (good integration with Visual Studio, drivers are present by default in .Net Framework). On the other hand, in terms of performance and reliability, I cannot say a single bad word about SQL Server. Another thing I like is that one can use accounts from an Active Directory domain as database accounts, so you do not have to duplicate group structures and such, if you use elaborate database permissions. I have come to like T-SQL, which integrates some procedural features into SQL, so SQL Server does not need a separate language for writing stored procedures, triggers, and so forth. Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature. Postgres offers many features SQL Server lacks, e.g. the array of data types available in Postgres is amazing, or the fact that it has enums. SQL Server has some features Postgres lacks, for example the Service Broker, which is very interesting, but apparently non-trivial to use correctly.
- gaius 8y ago
  Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature. Embedded R in SQL Server as well. Postgres is my default database choice, or SQLite if it's small, but SQL Server is a solid, high performance product and I'll gladly run it for the most demanding workloads.

akerro 8y ago

Oracle SQL Server is like 50 years old

pkaye 8y ago
I thought Oracle was founded in 1977 so it would be closer to 40 years.
- kevindqc 8y ago
  Correct! First available release was in 1979 according to the wiki, so almost 40 years. https://en.wikipedia.org/wiki/Oracle_Database#Releases_and_versions https://en.wikipedia.org/wiki/Oracle_Database#Releases_and_v...

rolleiflex 8y ago

One thing Postgres definitely doesn't do, and I wish it did, is embedded. This is usually the place that people use SQLite, for example, apps that are clients, but still need to do substantial work on the client side. The problem is, SQLite does have a few limitations that it doesn't make obvious: some of the more complex SQL syntax that Postgres supports are missing, but more importantly, it can only ever do one write or read to the database predictably. (Don't believe me? Try this: https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894 https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894) I know that SQLite supports concurrent writes and reads technically, but it predictably leads to 'database is locked' issues. In practice you have to use it in a fashion that it only ever does one thing, read, or write. No multiple writes. No single write and multiple readers, no single write and single reader. Just read — or write. (Edit: WAL doesn't help with this either.) Or you're in a world of hurt. I'd love to be able to move to Postgres just to get away from that.

manigandham 8y ago
This is almost always a driver/wrapper problem, which seems to be the case here. SQLite supports multiple readers without issue that only block slightly when a write transaction is committed, and there's also WAL mode with even better concurrency that avoids locks altogether for reads.
- rolleiflex 8y ago
  I've had the same problem with Python, not just Go. If it's a driver problem, it's a very widespread one, which would point out to SQLite driver API being in need of some love if so many folks who write SQLite drivers are making the same mistake.
  - blattimwind 8y ago
    The Python driver is buggy-by-default. This has been documented in recent versions [1], and is kinda sane for the undo log, but not so good if you want to use snapshot isolation. I guess many bindings would make the same decision. Basically they delay the emission of BEGIN until the first DML/DDL is executed. If you fix this (which is easy), then WAL works just fine. Note that transactions, as is standard, span from BEGIN/COMMIT to COMMIT/ROLLBACK. See e.g. https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#transaction-isolation-level https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#t... and https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... [1] Really it's more like "encoded": https://docs.python.org/3/library/sqlite3.html#controlling-transactions https://docs.python.org/3/library/sqlite3.html#controlling-t... -- you already need to know the issue to understand that this section specifies the root cause.

judgardner 8y ago

Agree. I have been using Postgres and Oracle in production since ~2000 (oracle 8i). Postgres is an amazing project but the oracle core database has billions of R&D dollars. Don't confuse the oracle core database with the awful applications they hang off of it. Oracle will still destroy pg for most OLAP workloads, now you have to decide if the insane licencing cost is worth it. Also just want to add an oracle developers post that I find hilarious... https://news.ycombinator.com/item?id=18442941 https://news.ycombinator.com/item?id=18442941

shdh 8y ago
> https://news.ycombinator.com/item?id=18442941 https://news.ycombinator.com/item?id=18442941 This was a gem. Really makes you appreciate not having to work on such a codebase.